Re: Table Variable vs Temporary Table

From: Tibor Karaszi (tibor_please.no.email_karaszi_at_hotmail.nomail.com)
Date: 11/24/04 

Date: Wed, 24 Nov 2004 16:29:26 +0100

> This is what I understand... table variables are created in memory (RAM).

This is a myth, understandable, however as they are called "variables". But in fact they are handled
internally very much the same way as temp tables (on difference is that there is slightly less
logging to the transaction log for table variables). However, statistics can very well explain the
effect, so using temp tables for lather data sets is generally a good idea as statistics is more and
more important the more data you have... 

-- 
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Yoshi" <yoshi@home.com> wrote in message news:udkw2Zj0EHA.2012@TK2MSFTNGP15.phx.gbl...
> 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: Table Variable vs Temporary Table
    ... Pro SQL Server 2000 Database Design - ... >> if the server has limited RAM and is not a dedicated SQL Server, ... >> alot of data and the server doesn't have not RAM/memory, ... >>> We have a query that declares a table variable, ...
    (microsoft.public.sqlserver.programming)
  • Re: the higher the cos , the faster?
    ... > believe to be the best order of operations to do the query. ... > can check all of the possible ways of executing the query, ... > possible to let SQL Server do its thing. ... > would I even consider using temp tables. ...
    (microsoft.public.sqlserver.programming)
  • Re: SQL Views - embedded view work-a-rounds
    ... I've written the same query up using temp. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)
  • Re: Using muliple smaller SPs and Functions better than using 1 big SP?
    ... > Same for temp tables - except very rare cases where the optimizer goofs ... > complex query and let SQL Server figure out the best way to process it. ... FROM CTE c1 ... ...
    (comp.databases.ms-sqlserver)
  • Re: Insert query takes lot of time
    ... While we got the tables and the query, we did not get any details on ... o Replace table variable with temp tables. ... variables, you kill parallelism. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (comp.databases.ms-sqlserver)
Loading