重用表变量?
我正在某个日期时间段(即 05/01/2012 到 05/31/2012)对数据库运行复杂查询列表.然后我需要为 06/01/2012 到 06/30/2012 运行相同的查询.然后加入结果用于报告目的.
I'm running a list of complex query on a database for certain datetime period (i.e. 05/01/2012 to 05/31/2012). Then I need run the same queries for 06/01/2012 to 06/30/2012. then join the results for report purpose.
在查询中，我使用了几个表变量来保存临时数据.因为数据库很大，表变量也很大.有没有办法重用这些表变量?
in the queries, I used several table variables to hold temporary data. Because the database is large, the table variable size is large too. Is there a way that I can reuse these table variables?
DROP，截断将不起作用.我是否必须从@table 中删除所有数据?如果是这样，它会很慢吗?因为@table 有大量数据，我是否必须对它进行批量删除?
DROP, Truncate will not work. Do I have to delete all data from @table? If so, will it be slow? DO I have to do batch delete for the @table since it has lots of data?
顺便说一句，我必须将所有查询放在一个 SP 文件中，由于系统的设计方式，无法调用函数或其他 SP.
BTW, I have to put all queries in one SP file, can not call function or other SP because of the way the system was designed.
谢谢
==============================
=============================
查询中没有循环.它的工作原理如下:
there is no loop in the queries. it works like:
选择.....选择.....更新.....加入......
select ..... select ..... update ..... join ......
对日期 05/01/2012 至 05/31/2012 执行一组查询.然后需要对 06/01/2012 到 06/30/2012 的同一组查询.
do a set of queries for date 05/01/2012 to 05/31/2012. then need to the same set of queries for 06/01/2012 to 06/30/2012.
在查询中，里面有很多逻辑，所以我们不能将这两个合并为一组查询.由于系统设计，我们不能调用函数或SP进行查询.必须先做第一组查询，然后按顺序做第二组查询.
in the queries, there are lots of logical inside, so we can not combine these 2 into one set of queries. because of system designed, we can not call function or SP for the query. have to do the 1st set of query, then the 2nd set of query in sequence.
问题是数据太多，@table太大了.如果我们可以重用@table，就可以解决问题.
the problem is there is too much data, the @table is too large. if we can reuse @table, it will solve the problem.
谢谢
================================
===============================
是的，现在，相同的代码，在 2 个不同的日期时间间隔内重复两次.但是，在代码中，它有一些逻辑内部，根据日期时间之间的差异进行不同的处理.抱歉，我无法发布实际代码.但是，这个过程就像一个以不同日期时间段为参数的SP.
yes, right now, the same codes, repeat twice for 2 different datetime interval. however, in the code, it has some logical inside, different process based on the difference between the datetime. Sorry, I can not post the actual code. But, the process is like a SP with different datetime period as parameters.
但是，在这种情况下我不能使用 SP/function，因此必须对相同的代码进行两次硬编码.理想情况下，每次重复代码时都需要使用不同的@table(现在，我需要重复 3 次)，但是由于数据大小，如果我重复 3 次，@table 就太大了(每个都需要多个 @table做逻辑部分).
However, I can not use SP/function in this case, so have to hard code the same code twice. ideally, will need use different @table for each time I repeat the code (right now, I need repeat 3 times), but because of the data size, @table is too large if I repeat 3 times (needs multiple @table in each to do the logical part).
也许我最好使用临时表?所以我可以在开始新的重复"时删除它?
maybe I had better use temporary table? so I can drop it when start a new 'repeat'?
谢谢
推荐答案
表变量未记录在当前数据库中，并且不受事务的约束.为什么你认为截断或删除会比删除快?你试过这个吗?
A table variable isn't logged in the current database, and isn't subject to transactions. Why do you think a truncate or drop would be any faster than a delete? Have you tried this?
DECLARE @f TABLE(id INT);
INSERT @f SELECT 1;
BEGIN TRANSACTION;
DELETE @f WHERE id = 1;
ROLLBACK TRANSACTION;
SELECT id FROM @f;
没有结果.现在，如果删除完全记录在当前数据库中(这使得 DELETE 比普通用户表的 TRUNCATE 慢)，您会期望 DELETE 已回滚，并且 SELECT 应该已返回数据.但是不，删除不是事务的一部分.那么，您可以从逻辑上得出结论，如果允许后者，DELETE 和 TRUNCATE 将非常相似，如果不完全相同.
No results. Now, if the delete were fully logged in the current database (which is what makes DELETE slower than TRUNCATE for a normal user table), you would expect the DELETE to have been rolled back, and the SELECT should have returned data. But no, the delete is not part of the transaction. You could logically conclude, then, that DELETE and TRUNCATE would be quite similar, if not identical, were the latter allowed.
如果必须使用表变量，只需使用删除.如果您发现它很慢，那可能不是因为删除，而可能是因为您在循环中重新使用表变量，而不是使用基于集合的操作.但是，当然，您比我们中的任何人都处于更好的位置，以测试如果您使用两个不同的 @table 变量与重新使用单个表变量并在两者之间发出删除，您的代码会慢多少.但我仍然认为你的整个过程需要重新调查，因为它在很多层面上对我来说都不是最佳选择.
If you must use a table variable, just use a delete. If you find it slow, it's probably not because of the delete, it's probably because you're re-using a table variable in a loop, rather than using a set-based operation. But of course you are in a better position than any of us to test how much slower your code would be if you use two different @table variables vs. re-using a single table variable and issuing a delete in between. But I still think your whole process needs to be re-investigated because it sounds sub-optimal to me on many levels.
相关文章