March 19, 2008 at 10:10 pm
Hi,
Which table will give better and faster result either @table or #table when using in stored procedure?
Thanks,
Peer Md.
March 20, 2008 at 2:02 am
From my experience I've found there's very little difference between the two when using a low number of rows...but when using thousands or tens of thousands of rows I've found that temporary tables perform much better than table variables.
Best thing to do is test it with both and make your own mind up based on your findings.
March 20, 2008 at 2:39 am
peer_mohamed2k (3/19/2008)
Hi,Which table will give better and faster result either @table or #table when using in stored procedure?
Thanks,
Peer Md.
Have a look at http://support.microsoft.com/?kbid=305977
There are some differences that will affect performance (if you use tempt tables in a specific way (see BOL), your stored procedures that are them will be recompiled every time; table variables have no statistics; table variables are not transactional, so if you have savepoints you should be careful; table variables can have horrible performance if you have a large number of rows in them, and this varies from one SQL Server version to another; no non-clustered indexes on table variables; etc.)
Regards,
Andras
March 21, 2008 at 7:59 am
Really, it depends on what you're using them for. But it's often considered a good practice to use temp tables instead of table variables, because the advantages for table variables are very limited, while the advantages for temp tables can be quite substantial.
The main advantage of table variables is they don't cause a proc to recompile if you define them later in the proc. A table variable defined after DML commands (insert/update/delete = DML), will cause the proc to recompile on each run. So will defining a cursor on a temp table. The recompile can be a problem if multiple people/processes are using the same proc at the same time.
If, on the other hand, you define your temp tables at the beginning of the proc, and don't use cursors on them, you don't have that problem. Since using cursors is almost always a bad idea anyway, this is an easy problem to avoid.
Temp tables have a number of advantages:
They can be indexed (big advantage if you put a lot of data into them). Just remember to create the index when you create the temp table, before any DML commands, to avoid the recompile issue.
They can have statistics. This means selects and joins on them can be more efficient and faster than on table variables. Again, mainly an advantage if you have large amounts of data in them.
They can be used to pass data from one proc to another proc, if the first one executes the second one.
They can have transactions and commit/rollback. (The only time this is not an advantage is if you want to use a table variable to hold data that you want to do something with after a rollback that would otherwise take the data back out of the table.)
If you're storing only a few rows of data, and don't care about transactions on it, then table variables are fine. Since indexes aren't used till a table goes over 200 rows, that's pretty much the threshhold at which you really should use a temp table even if you normally use table variables.
One of the reasons to use temp tables as much as possible is to maintain a standard. If you always use temp tables, you don't have to worry about "should I use a temp table this time?"
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 21, 2008 at 9:29 am
I tend to avoid both when I can, but I also go by the advice that GSquared has given. That's a good summary of both of them.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply