What would be more efficient in storing some temp data (50k rows in one and 50k in another) to perform come calculation. I'll be doing this process once, nightly.
How do you check the efficiency when comparing something like this?
+6
A:
The results will vary on which will be easier to store the data, in disk (#temp) or in memory (@temp).
A few excerpts from the references below
- A temporary table is created and populated on disk, in the system database tempdb.
- A table variable is created in memory, and so performs slightly better than #temp tables (also because there is even less locking and logging in a table variable). A table variable might still perform I/O to tempdb (which is where the performance issues of #temp tables make themselves apparent), though the documentation is not very explicit about this.
- Table variables result in fewer recompilations of a stored procedure as compared to temporary tables.
- [Y]ou can create indexes on the temporary table to increase query performance.
Regarding your specific case with 50k rows:
As your data size gets larger, and/or the repeated use of the temporary data increases, you will find that the use of #temp tables makes more sense
References:
boflynn
2009-04-21 19:37:25
In my experience 50K would be too much for a table var but I don't have hard data to back that. I typically try to keep table vars < 5K.
brendan
2009-04-21 19:39:06
You can create additional indexes on # tables whereas you can't on vars.
Joe
2009-04-21 19:47:31
A temp variable will overflow to disk also when you pump enough data into it, 50K rows will push it to disk partially
SQLMenace
2009-04-21 19:48:10
Thanks for the note, Joe. I added a note about indices.
boflynn
2009-04-21 19:49:50
Two benefits of indexes on temp tables- these can be non-unique (while table vars are only unique through constraints)- but the single biggest advantage comes from unexpected ally: *statistics* are "cheap" indexes and these are *auto* created on temp tables by the query optimizer (pstatman) -- makes a huge difference.
wqw
2009-04-22 10:53:04
-1 table variable is not stored in memory. It is stored in tempdb
Gabriel McAdams
2010-01-06 00:03:00
+3
A:
There can be a big performance difference between using table variables and temporary tables. In most cases, temporary tables are faster than table variables. I took the following tip from the private SQL Server MVP newsgroup and received permission from Microsoft to share it with you. One MVP noticed that although queries using table variables didn't generate parallel query plans on a large SMP box, similar queries using temporary tables (local or global) and running under the same circumstances did generate parallel plans.
More from SQL Mag (subscription required unfortunately, I'll try and find more resources momentarily)
EDIT: Here is some more in depth information from CodeProject
TheTXI
2009-04-21 19:38:37
Thanks for the tip. I came across the codeproject article just after posting this question.
Saif Khan
2009-04-21 19:42:28