Table variable vs temp table 4

[unclejimbob]

Hello all, as a newbie to SQL 2005 I started using table vars in my sp code - until I realised that for sps that returned large result sets this could be a really bad thing ;-) Now that I have identified and fixed the source of my non-executing sps does anyone know of a sizing document/white paper that describes the parameters that determine at what point you should stop using table vars and start using temp tables (and vice versa) ?

cheers
ujb

 

[TysonLPrice]

I didn't post them but if you Google on:

sql 2005 temporary tables versus table variables

You get a bunch of hits.

 

[unclejimbob]

Yes I usually google it first off, then I consult some local SQL Server-specific newsgroups, then I read through any Microsoft doco that may be lying around, then I consult the multitude of SQL Server and SQL tomes on my bookshelf, and finally I consult with our local SQL chappie.

Thus far the most appropriate answer I have come up with is this:
'In general, you use table variables whenever possible except when there is a significant volume of data and there is repeated use of the table...However, each scenario may be different. Microsoft recommends that you test if table variables are more helpful than temporary tables for a particular query or stored procedure.'

http://support.microsoft.com/default.aspx/kb/305977

which translates roughly to - 'try it out for yourself' - which is essentially where I came in

Problem 'solved', end of thread. Have a good one guys and girls.

cheers
ujb

 

[davejam]

Not sure how anyone else feels on this but i personally use table vars over temp tables, infact i'm a devout hater of temp tables.

i find using table variables to be more useful because of the use of the memory and the total easy control, at the end of the day you will end up pulling your temp table into memory anyway.

i have created calculation stored procs for scientific analysis analysis along with accounting functions that have dealt with thousands of records in one sitting, calculating complex results into table vars and updating back to main table and have never found a real lag in it (aside from the first time it runs) but once coimpiled its fine.

working on one now that creates end of month and end of year figures for a client base of thousands... so far in my testing i have no issues, you need to make sure you're using them to their best advantage, only use what you need, and try and access them sequentially as this stops it having to jump around.

hope this is some insight

daveJam

*two wrongs don't make a right..... but three lefts do!!!!*

 

[mrdenny]

As a general base line if the table is over a couple of thousand records it should be a temp table. That number will shift depending on the width of the table. The smaller the rows the more rows you can have without hurting the SQL Server that much.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[mrdenny]

As a general base line if the table is over a couple of thousand records it should be a temp table. That number will shift depending on the width of the table. The smaller the rows the more rows you can have without hurting the SQL Server that much.

Large tables should be done as temp tables so that you can index then to increase performance while working on them.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[ptheriault]

I have found table variables perform better than temp tables. I would try it in your code using both to see which is faster. Also, I usually put a PK on my larger tables variables. I have greatly increased performance on some querries. I used to use temp tables only but there are some advatages in table variables.

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[Lamprey13]

As to performance you would want to test and test again. Depending on what you are doing dicates which you would want to use. You would want to read up on both. There are too many things to list, but just a couple of things about table variables: They do not have statistics (temp tables do); If they get to large they are swaped to disk (like a temp table); Table variables will cuase less recomilations of stored procs; They are not affected by transactions (which is good and bad). etc....

So, the moreal is, to understand the beast you want to play with before it bites you.

 

[unclejimbob]

Thanks guys for the responses, to clarify when I say I'm a newbie I mean I've been using it for more than 1 and less than 2 years, after 2 years I get to say I'm 'dangerous' and after 3 then I'm 'competent'

Reading the publicly available material before my intial post I guess I know as much as anyone about how to declare and use both table vars and temp tables and their pros and cons, I just happened to have hit a brick wall with a particular usage - in this case writing a stored procedure to find duplicates within a medium sized table (500,000 rows) with no indexes on it (apart from the PK) and then putting the results into a table var. As a result the table var would hold about 20,000 rows. The standard here is to use a table var to return a result set to the client from a stored procedure, it gets populated by an SQL statement so its essentially just a holding area. The problem was that when I used a table var it wouldn't populate, the SQL statement just kept running without returning a result set, when I swapped to using a temp table it worked. As an aside I later discovered that the server upon which the sp was being run had a large chunk of memory allocated for other purposes - an aspect over which I have no control. When this had been freed I went back to using table vars my sp ran successfully to completion (same result as davejam) - but I'm not going to risk it and will stay with a temp table. Also have had a lot of fun trying different SQL to actually get the duplicates out in a decent space of time and have learned a bit along the way about how SQL Server reacts to things like correlated sub queries, self joins, derived tables, table vars , probe tables etc, etc - basically all the things that I know how InterBase reacts to I have to re-learn for SQL Server, which is jolly good fun.

Once again, thanks for the help, I hope to be able to return it when I learn a bit more.

cheers
ujb

 

[gmmastros]

So... Let me tell you a story...

My application is installed at about 100 sites. Places ranging from connecticut, to california (and lots of places in between).

When we installed in Canada, we ran in to a problem. First with the date format (had I known then what I know now). Then, later with temp tables. Let me explain.

Temp tables are actually stored in the TempDB. As such, the default collation for string columns is the same as the default collation for the database, which is the same as the default collation for the server. The problem was the the collation for the database I put on their server was not the same. Who cares, right? Well, for some queries, I was loading a temp table and then later using values from the temp table. I started running in to collation problems. Once I switched them over to table variables, the problem went away completely.

Now, don't mistake this story as reason to NOT use temp tables. With any luck, you'll never have the same problem I did. And if it ever happens to you, at least you'll be a little more prepared for it.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom