Reuse a table variable?

Question

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, 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?

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.

thanks

=============================

there is no loop in the queries. it works like:

select ..... select ..... update ..... join ......

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.

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.

the problem is there is too much data, the @table is too large. if we can reuse @table, it will solve the problem.

thanks

===============================

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.

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'?

thanks

Answer 1

Sure, but what you really want is not a table parameter, instead, you want a temporary table.

CREATE TABLE #my_temp_table (column1 int, column2 varchar(max), ...)
INSERT INTO #my_temp_table (column1, column2) VALUES (...)
-- Use the temporary table here
DELETE FROM #my_temp_table
INSERT INTO #my_temp_table (column1, column2) VALUES (...)
-- Use the temporary table again
DROP TABLE #my_temp_table

EDIT: The submitter may be saying "I can't use a table variable because it is passed to my stored procedure with the flag READONLY". In that case, he may get some mileage out of converting it to a temporary table.

As a side note, although SQL Server's documentation claims this is not the case, I have seen instances where temp tables perform better than table variables. I believe this was because I put my TEMPDB on a separate disk, which had lots of IO capacity available.

Please do keep in mind, though, that temporary tables suffer from naming problems - you can lock up a stored procedure if it attempts to create a naming conflict for temporary tables. Table variables don't suffer from that problem.

Answer 2

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;

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.

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.