問題描述
我正在某個日期時間段(即 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.
這篇關于重用表變量?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！