Re: Table Variable vs Temporary Table
From: Louis Davidson (dr_dontspamme_sql_at_hotmail.com)
Date: 11/24/04
- Next message: smk23: "SP to ADO interaction"
- Previous message: Mal .mullerjannie_at_hotmail.com>: "RE: Wierd error"
- In reply to: Alejandro Mesa: "Re: Table Variable vs Temporary Table"
- Next in thread: Mike: "Re: Table Variable vs Temporary Table"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 24 Nov 2004 11:27:48 -0600
It probably was, and certainly no fault to be assigned here :) I just think
back to how I felt when I was a newbie and consider what detail I would like
to read about a problem and just added some color.
-- ---------------------------------------------------------------------------- Louis Davidson - drsql@hotmail.com SQL Server MVP Compass Technology Management - www.compass.net Pro SQL Server 2000 Database Design - http://www.apress.com/book/bookDisplay.html?bID=266 Note: Please reply to the newsgroups only unless you are interested in consulting services. All other replies may be ignored :) "Alejandro Mesa" <AlejandroMesa@discussions.microsoft.com> wrote in message news:3000ADF3-D8D3-4A4A-8F27-BBE47FA4D5C9@microsoft.com... >I did not mention it, thought that mentioning that a new execution plan is > being generated was enough. > > mea culpa!!! > > > > AMB > > "Louis Davidson" wrote: > >> And in some cases this can be a good thing. Temporary tables act more >> like >> physical tables, and as such can give better performance. Best practice >> is >> to only use local variable tables when you are working with small >> datasets, >> where things like stats don't make a difference. Too much data and they >> become too much overhead (as Yoshi said a few posts back :) >> >> -- >> ---------------------------------------------------------------------------- >> Louis Davidson - drsql@hotmail.com >> SQL Server MVP >> >> Compass Technology Management - www.compass.net >> Pro SQL Server 2000 Database Design - >> http://www.apress.com/book/bookDisplay.html?bID=266 >> Note: Please reply to the newsgroups only unless you are interested in >> consulting services. All other replies may be ignored :) >> >> "Alejandro Mesa" <AlejandroMesa@discussions.microsoft.com> wrote in >> message >> news:74E4B1E2-DD76-473F-9D0B-43C831AF481B@microsoft.com... >> > Temporary tables also cause sp recompilation (a new execution plan). >> > >> > INF: Frequently Asked Questions - SQL Server 2000 - Table Variables >> > http://support.microsoft.com/default.aspx?scid=kb;en-us;305977 >> > >> > >> > >> > AMB >> > >> > "Yoshi" wrote: >> > >> >> Craig, >> >> >> >> We had a similiar issue where a developer wrote a Stored Procedure >> >> using >> >> a >> >> temporary "memory" variable. The process took all day. I changed it to >> >> a >> >> temporary table and it ran under 3 hours. >> >> >> >> This is what I understand... table variables are created in memory >> >> (RAM). >> >> They are supposed to be faster because of this. However, I have found >> >> that >> >> if the server has limited RAM and is not a dedicated SQL Server, table >> >> variables can be quite slow. I believe if the table variable is >> >> processing >> >> alot of data and the server doesn't have not RAM/memory, then it swaps >> >> to >> >> disk. >> >> >> >> My rule is if your dealing with alot of data, then use a temporary >> >> table >> >> otherwise use the table variable. >> >> >> >> Y >> >> >> >> >> >> "CB" <craig.bryden@derivco.com> wrote in message >> >> news:unzTDNj0EHA.3808@tk2msftngp13.phx.gbl... >> >> > Hi >> >> > >> >> > I have discoverred a situation where the performance between using a >> >> > table >> >> > variable and a temporary table is remarkably different. >> >> > We have a query that declares a table variable, then populates it >> >> > (this >> >> > is >> >> > quick) and then this is used in a very complicated query where it is >> >> > being >> >> > referenced many times in sub-queries. The query takes about 180 >> >> > seconds >> >> > to >> >> > run. >> >> > >> >> > I changed the query to use a temporary table. There were no logic >> >> > changes. >> >> > In fact I simply did a search and replace on the table name (i am >> >> > aliasing >> >> > the table everywhere). Using this method the query runs in 5 >> >> > seconds. >> >> > >> >> > Can anyone please explain this difference to me? I did expect that >> >> > the >> >> > two >> >> > would not be the same but this is ridiculous. >> >> > >> >> > Any help would be appreciated >> >> > >> >> > Thanks >> >> > >> >> > Craig >> >> > >> >> > >> >> > >> >> >> >> >> >> >> >> >>
- Next message: smk23: "SP to ADO interaction"
- Previous message: Mal .mullerjannie_at_hotmail.com>: "RE: Wierd error"
- In reply to: Alejandro Mesa: "Re: Table Variable vs Temporary Table"
- Next in thread: Mike: "Re: Table Variable vs Temporary Table"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
