2

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

urlreader
  • 6,319
  • 7
  • 57
  • 91
  • 1
    Maybe you could show your code. Reusing a table variable implies a loop that is probably not necessary. And even with a loop a table variable might not be necessary. Seems there may be many layers to this onion. The more peeling you do for us, the better your end solution will be. – Aaron Bertrand Jul 23 '12 at 21:29
  • I don't understand the logic you posted there. `select...select...update...join` - what does that mean? How do you go from May to June? How does your code know to run reports for those two months? Do you have two almost identical queries hard-coded with those dates? – Aaron Bertrand Jul 23 '12 at 21:41
  • 1
    I think you are attempting to micro-optimize here. Sadly because of your query design choices this is going to be like shaving a yak's legs to make him swim faster. – Aaron Bertrand Jul 23 '12 at 22:00

2 Answers2

19

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.

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
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.

Ted Spence
  • 2,598
  • 1
  • 21
  • 21
  • yes, maybe that's the way to do it. just curious, it should have a way to 'reset' the table variable since it is a variable, shouldn't it? thanks. – urlreader Jul 23 '12 at 21:51
  • How is this any different from deleting from a table variable? Is your allegation that a #temp table is always better than a table variable? – Aaron Bertrand Jul 23 '12 at 21:53
  • I think when there are lots of data in the #table, drop and then create #table is much faster than delete the data directly. – urlreader Jul 23 '12 at 21:57
  • @urlreader What is "lots of data"? Have you tested this? How can you drop and create a #temp table, with the same name, in the same batch? This should get blocked at compile time. Also note that what you suggested is not what this answer suggests. – Aaron Bertrand Jul 23 '12 at 21:59
  • I thought the reason he couldn't modify the table parameter is because it was submitted as "READONLY". In that case, I thought he should be using a temp table instead. – Ted Spence Jul 23 '12 at 22:06
  • yes, you are correct. looks like I'll have to delete from the @table directly, and see whether it is really slow. thanks. appreciate the help. – urlreader Jul 23 '12 at 22:07
  • `Table variable` not table-valued parameter. – Aaron Bertrand Jul 23 '12 at 23:20