Re: Table Variable vs Temporary Table

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Alejandro Mesa (AlejandroMesa_at_discussions.microsoft.com)
Date: 11/24/04 

Date: Wed, 24 Nov 2004 07:15:05 -0800

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
> >
> >
> >
>
>
>

Relevant Pages

  • RE: Considering buying a server
    ... The level of expertise and the cost is dependent on alot of factors. ... I support a small 7 workstation and 1 server configuration. ... Or go to one of the product lauches for office 2003 or sbs 2003. ... given by Microsoft, as well as alot of information that could be invaluable in your decision process. ...
    (microsoft.public.backoffice.smallbiz2000)
  • Re: NSLU2 and python - a love story ?
    ... server, but for normal home-use with not too much traffic it seems to ... the NSLU2, maybe they don't advertise it on their web-page, but who ... They got similar products based on or running linux which also ... alot of noise, are big and uses alot of eletricity. ...
    (comp.lang.python)
  • Re: Using Access 2000 w/FP2003 w/XP Home Edition
    ... server to work. ... >> to develop anything other than a static web sites. ... >> to have alot of code done that I can tweak. ... >I've never used a template - I can't help you there. ...
    (microsoft.public.frontpage.programming)
  • Re: Remove a domain that has no server for it and nothing in it
    ... I read through alot of the URLs bellow, ... >> is another tool for 2003 server. ... >298450 Deletion of Critical Objects in Active Directory ... >Active Directory Domain Controllers ...
    (microsoft.public.windows.server.active_directory)
  • Re: Newbie: SQL Server connection
    ... can set a boolean value and then you know the complicated query is the ... which will return the status of the connection to database abc ... > Yes but the exception can, in principle depend on a few things (server not ... >> You can catch the exception and inform the user. ...
    (microsoft.public.dotnet.languages.vb)