Re: Table Variable vs Temporary Table

From: Yoshi (yoshi_at_home.com)
Date: 11/24/04 

Date: Wed, 24 Nov 2004 09:01:17 -0600

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: FTS Performance in SQL 2005
    ... Can you post you query plans and the output of statistics IO ... SQL Server MVP ... Because i've set the MAX sql-server memory to 3.5 GB instead of 4.0 GB ... cost relative to the whole batch, ...
    (microsoft.public.sqlserver.fulltext)
  • Re: ISA Firewall service in SBS 2003 R2 stopped and event 21192 recorded in Application log
    ... Microsoft ISA Server Web Proxy ... minimum query memory is not available. ... Not met something similar with ISA Server, but with SQL Server. ...
    (microsoft.public.windows.server.sbs)
  • RE: ViewState vs. Database
    ... query instead of caching the data. ... Since that can save webserver memory. ... database server, I'll first consider caching data at server-side (such as ...
    (microsoft.public.dotnet.general)
  • Re: FTS Performance in SQL 2005
    ... Because i've set the MAX sql-server memory to 3.5 GB instead of 4.0 GB ... its the pipe between the CPU and Memory that could be the bottleneck. ... and that you have a covering index on the columns in the query. ... Plan window will then give you a percentage query cost relative ...
    (microsoft.public.sqlserver.fulltext)
  • Re: FTS Performance in SQL 2005
    ... Now if I do a query directly to the field I would theoretically need: ... SQL Server MVP ... Because i've set the MAX sql-server memory to 3.5 GB instead of 4.0 GB ... cost relative to the whole batch, ...
    (microsoft.public.sqlserver.fulltext)