January 7, 2011 at 11:33 am
I am trying to decide whether to use temp table or table variable in stored procedure. Based requirements there would be anywhere from 500 - 50000 records in the temporary table / variable.
I am aware that index/statistics cannot be created on table variable and for large number of records temp table are preferred choice .. but is 50,000 records too large for table variable ?
It will be used to join with multiple delete/update statements in stored procedure.
Thank You.
January 7, 2011 at 11:46 am
You can create a PKey index on a table variable but thats it.. As far as the choice between them, I had two thoughts:
1. If the table is narrow, like a row length of 200 bytes (all datatype lengths and such) then the total space used per row, you can fit about 40 rows-per-page which is about 1250 pages, so about 10MB.. That doesn't feel too big..
2. Test it both ways. I have used this method many times, I usually add a little hook to get the time before a section starts and a diff of the time when that section ends. I run it several times each way and compare.
I will point out that I ran into a case where I used BOTH for the same data, that the first half of a sproc ran substantially better with table variables and the latter half ran substantially better with temp tables, even considering the time it took to copy from one to the other. This was determined through a number of runs and I determined the point that the process ran better for each, at that point I copied the data from one to the other. The point here is that I found this out by trying each and not getting hung up on either one, I used what was best given actual data and actual cases. This allowed me to make a decision that would be very difficult to second guess since testing showed which was better.
If you happen to find a WIDE difference it is often great to add comments to the sproc to explain WHY you chose one over the other, this helps when a developer other than yourself looks at the code and questions your design choice.
CEWII
January 7, 2011 at 11:46 am
More information would help. What columns are you going to index on? What are your joins going to look like to your permanent tables? Generally, that's considered to be a LOT of rows for a table variable. But it wouldn't be hard at all to test it both ways and see how it performs in your environment.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
January 7, 2011 at 12:08 pm
Temporary data has only two columns -
ID - int.
CODE - varchar(40).
CODE is used in join condition.
January 7, 2011 at 12:21 pm
Hi,
There are a lot of discussions about table variable vs temporary table regarding efficiency. The conclusion is usually that testing should decide. Of course, the basic differences should be understood.
Here is a good article of many:
TempDB:: Table variable vs local temporary table
Cheers
January 7, 2011 at 12:29 pm
So it sounds like you are joining to code to get id. Ok, provided Code is unique you could easily index it in a table variable.
I tend to take a different view of table variables, 50,000 very narrow rows trouble me less than 1,000 very wide rows, but I also tend to test it both ways if I even think it *could* matter. Your scenario really is about 2.5MB of space, which really isn't much..
DECLARE @YourTableVariable TABLE ( Code varchar(40) NOT NULL PRIMARY KEY CLUSTERED, ID int NOT NULL )
I would probably fill it with a query with an ORDER BY so it doesn't thrash the pages on the write, but this is something you can test.
CEWII
January 7, 2011 at 12:30 pm
akhandels (1/7/2011)
Temporary data has only two columns -ID - int.
CODE - varchar(40).
CODE is used in join condition.
If you index the table variable on [Code], and if the table to be joined also is indexed on [Code], then the optimizer may consider it to be a trivial join and get it right even in the absence of statistics. You may get different performance at different volumes. If the 50k size is something you can count on, test it both ways. If the volume is going to vary, you might play it safe and use a #temp table.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
January 7, 2011 at 3:45 pm
I have to partially disagree with you. I have seen "throw it all at the optimizer and let it figure it out", I have seen monster 8, 12, and 16 table joins in a report query that performed like molasses in the antarctic, where breaking pieces of it out to either temp objects (tables/variables) or something like a CTE increased performace 100X. I am not a fan of creating a view of data unless it will be used often and consistently or for just one query.. If that query were executed hundreds or thousands of times a day, I'd reconsider..
The advent of CTE's do reduce the need for temp tables and table variables, but does not negate it.
CEWII
January 7, 2011 at 3:52 pm
Elliott Whitlow (1/7/2011)
The advent of CTE's do reduce the need for temp tables and table variables, but does not negate it.CEWII
Elliott, I'll have to disagree with you here. A CTE doesn't materialize, and is optimized inline with whatever query it is used in. It's as if you had simply used a (SELECT FROM) as drvTable in the middle of the query. The optimizer treats it as though it were an unindexed view.
CTE's definately simplify reading code, and allow for a recursion mechanic which will operate externally afaik, but a CTE alone does nothing more then ease readibility.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 7, 2011 at 4:03 pm
Craig Farrell (1/7/2011)
Elliott Whitlow (1/7/2011)
The advent of CTE's do reduce the need for temp tables and table variables, but does not negate it.CEWII
Elliott, I'll have to disagree with you here. A CTE doesn't materialize, and is optimized inline with whatever query it is used in. It's as if you had simply used a (SELECT FROM) as drvTable in the middle of the query. The optimizer treats it as though it were an unindexed view.
CTE's definately simplify reading code, and allow for a recursion mechanic which will operate externally afaik, but a CTE alone does nothing more then ease readibility.
I guess the position I was taking overall was that CTE's *can* in some case reduce the need. However, from my earlier responses you can guess, I am still a fan of temp tables and table variables. And they do still have a lot of uses precisely for the reasons you specified. As far as readability, that wasn't really on my mind, I look at them as temporary views, but I am thinking we largely agree..
CEWII
January 7, 2011 at 4:12 pm
Elliott Whitlow (1/7/2011)
Craig Farrell (1/7/2011)
Elliott Whitlow (1/7/2011)
The advent of CTE's do reduce the need for temp tables and table variables, but does not negate it.CEWII
Elliott, I'll have to disagree with you here. A CTE doesn't materialize, and is optimized inline with whatever query it is used in. It's as if you had simply used a (SELECT FROM) as drvTable in the middle of the query. The optimizer treats it as though it were an unindexed view.
CTE's definately simplify reading code, and allow for a recursion mechanic which will operate externally afaik, but a CTE alone does nothing more then ease readibility.
I guess the position I was taking overall was that CTE's *can* in some case reduce the need. However, from my earlier responses you can guess, I am still a fan of temp tables and table variables. And they do still have a lot of uses precisely for the reasons you specified. As far as readability, that wasn't really on my mind, I look at them as temporary views, but I am thinking we largely agree..
CEWII
We definately agree, I just wanted to make sure the difference was clear that a CTE can't replace optimization power of a temp/var table. I figured you weren't making that mistake, but hey, we all have off days. 😀
The power of the temp table/tablevar to deal with the one off proc that needs things just so is far too powerful to toss out the window because of 'perfect coding techniques'. Perfect for one query means screwed up for another... something Celko constantly forgets with his ivory tower online persona.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 7, 2011 at 4:13 pm
CELKO (1/7/2011)
Neither approach is good. Your mindset is still thinking with magnetic tape scratch files. Read data to a tape, then hang the tape and run the next program in the sequential process. Now, compare that to an RDBMS declarative approach -- put everything you can in one statement and trust the optimizer.
If you truly believe what you said above, then you should stop writing SQL and let an ORM do all the work for you. Worse advice has never been made especially where performance is concerned.
Of course, you don't truly believe what you said because you keep using and advocating the mag tape and assembly logic of a "push stack" to do your "Adjacency List to Nested Set" conversion.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 7, 2011 at 4:15 pm
OH SNAP!
CEWII
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply