October 7, 2003 at 3:04 pm
In everything I've read it seems that using a table var is supposed to perform much faster then a temp table. I'm not seeing proof of this in code though...I wrote some code that populates several tables and then retrieves from them all joined together...when I use Temp tables for the three largest tables (which are still really really small) I return in 6 seconds...with table vars I give up after a minute and a half. What gives? ANy help or insight here would be greatly appreciated. Thanks.
K Leb
October 8, 2003 at 3:03 am
What your server seutp like?
If I remember correctly...
temp tables are created as full tables and involve disk access
var tables are created in memory (Memory permitting)
So, if your machine has fast disk access but limited memory then the temp tables could be out performing due to the var tables having to be paged from disk to memory.
Dan
October 8, 2003 at 3:45 am
Kleb, I did a couple tests awhile ago between the two types.
Differences become apparent when you work with large tables (1000's or rows)
It might have been the topic I had where someone said that SQL retains a normal temp table (#) in memory until it decides it's gonna be to big, then writes it to disk. (Can someone confirm this?)
This would explain the lack of difference between the two with a small number of rows.
As far as a table variable goes, when you have one with 1000's of rows, I can light up the disks. I assume that this is because it's either "converting it" and writing to disk or starting to use the swap file.
I normally use variable tables if I know they are gonna remain small.
As always, use the fastest, least expensive method.
Cheers,
Crispin
Edited by - crappy on 10/08/2003 03:46:04 AM
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
October 8, 2003 at 9:25 am
Thanks for the input guys, I guess it's in part my opinion of small...1000's of rows is what I'm dealing with and for SQL server I consider that small. Thing is I need this code to execute from a UDF and they don't support temp tables only table vars. I thought I read something somewhere about being able to trick the UDF into working with temp tables but I can't remember if that was a server setting that had to be changed. Any one what I'm refering to in better detail?
K Leb
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply