Professional Web Applications Themes

SQL Server 2000 SP3: Why is Table variable slower than temp tables? - Microsoft SQL / MS SQL Server

Hello, Recently, I am trying to optimize some old stored procedures. In the process, I found out in the online help that a "table variable" is supposed to perform better than temp tables. However, I have found exactly the opposite. Whereas using temp tables, I can obtain query results in less than one second. Using a table variable took me more than 20 seconds! For the comparison, the stored procedures are almost the same. I've just replaced CREATE TABLE #temp (...) with DECLARE temp TABLE (...). It consists of only an insert to the table followed by a join select ...

  1. #1

    Default SQL Server 2000 SP3: Why is Table variable slower than temp tables?

    Hello,

    Recently, I am trying to optimize some old stored procedures. In the
    process, I found out in the online help that a "table variable" is
    supposed to perform better than temp tables. However, I have found
    exactly the opposite. Whereas using temp tables, I can obtain query
    results in less than one second. Using a table variable took me more
    than 20 seconds!

    For the comparison, the stored procedures are almost the same. I've just
    replaced CREATE TABLE #temp (...) with DECLARE temp TABLE (...). It
    consists of only an insert to the table followed by a join select of
    about 4000 rows. I wonder if this is related to the the amount of memory
    available to the SQL server?

    Any ideas? Thanks!

    Francisco
    FL Guest

  2. #2

    Default Re: SQL Server 2000 SP3: Why is Table variable slower than temp tables?

    FL
    Perhaps table variable used in SP result a few recompilations.
    Have you checked Profile to determine a behavior your strored procedure.



    "FL" <frleong_NOSPAMhotmail.com> wrote in message
    news:3F0B92EB.82F16Dhotmail.com...
    > Hello,
    >
    > Recently, I am trying to optimize some old stored procedures. In the
    > process, I found out in the online help that a "table variable" is
    > supposed to perform better than temp tables. However, I have found
    > exactly the opposite. Whereas using temp tables, I can obtain query
    > results in less than one second. Using a table variable took me more
    > than 20 seconds!
    >
    > For the comparison, the stored procedures are almost the same. I've just
    > replaced CREATE TABLE #temp (...) with DECLARE temp TABLE (...). It
    > consists of only an insert to the table followed by a join select of
    > about 4000 rows. I wonder if this is related to the the amount of memory
    > available to the SQL server?
    >
    > Any ideas? Thanks!
    >
    > Francisco

    Uri Dimant Guest

  3. #3

    Default Re: SQL Server 2000 SP3: Why is Table variable slower than temp tables?

    One reason can be that table variables doesn't carry statistics. Temp tables does.

    --
    Tibor Karaszi, SQL Server MVP
    Archive at: [url]http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver[/url]


    "FL" <frleong_NOSPAMhotmail.com> wrote in message news:3F0B92EB.82F16Dhotmail.com...
    > Hello,
    >
    > Recently, I am trying to optimize some old stored procedures. In the
    > process, I found out in the online help that a "table variable" is
    > supposed to perform better than temp tables. However, I have found
    > exactly the opposite. Whereas using temp tables, I can obtain query
    > results in less than one second. Using a table variable took me more
    > than 20 seconds!
    >
    > For the comparison, the stored procedures are almost the same. I've just
    > replaced CREATE TABLE #temp (...) with DECLARE temp TABLE (...). It
    > consists of only an insert to the table followed by a join select of
    > about 4000 rows. I wonder if this is related to the the amount of memory
    > available to the SQL server?
    >
    > Any ideas? Thanks!
    >
    > Francisco

    Tibor Karaszi Guest

  4. #4

    Default Re: SQL Server 2000 SP3: Why is Table variable slower than temp tables?

    But can it justify the huge performance difference between two of them?
    I enabled Trace in the Query yzer and I've found that even the
    "INSERT INTO tablevar SELECT * FROM source" statement is much slower
    than "INSERT INTO #temptable SELECT * FROM source". The source is
    independent and external and has no correlations or joins with the
    destination table. Is there any configuration setting I need to look at?

    Francisco

    Tibor Karaszi wrote:
    >
    > One reason can be that table variables doesn't carry statistics. Temp tables does.
    >
    > --
    > Tibor Karaszi, SQL Server MVP
    > Archive at: [url]http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver[/url]
    >
    > "FL" <frleong_NOSPAMhotmail.com> wrote in message news:3F0B92EB.82F16Dhotmail.com...
    > > Hello,
    > >
    > > Recently, I am trying to optimize some old stored procedures. In the
    > > process, I found out in the online help that a "table variable" is
    > > supposed to perform better than temp tables. However, I have found
    > > exactly the opposite. Whereas using temp tables, I can obtain query
    > > results in less than one second. Using a table variable took me more
    > > than 20 seconds!
    > >
    > > For the comparison, the stored procedures are almost the same. I've just
    > > replaced CREATE TABLE #temp (...) with DECLARE temp TABLE (...). It
    > > consists of only an insert to the table followed by a join select of
    > > about 4000 rows. I wonder if this is related to the the amount of memory
    > > available to the SQL server?
    > >
    > > Any ideas? Thanks!
    > >
    > > Francisco
    FL Guest

  5. #5

    Default Re: SQL Server 2000 SP3: Why is Table variable slower than temp tables?

    I'mjust thinking out loud, but could the reason for better performance in
    temp tables be that they are indexed?

    A table variable cannot have an index, and neither can a derived table (to
    the best of my knowledge).

    Joining on an indexed feild is just about always going to be faster as a
    full table scan is not required.

    If your temp table is not indexed, and is still faster then I have no idea,
    but i suspect there is a line of code there somewhere creating the index =)

    cheers

    "Delbert Glass" <delbertnoincoming.com> wrote in message
    news:etr7SymRDHA.2676TK2MSFTNGP10.phx.gbl...
    > Use a derived table instead of either of those.
    >
    > Bye,
    > Delbert Glass
    >
    > "FL" <frleong_NOSPAMhotmail.com> wrote in message
    > news:3F0B92EB.82F16Dhotmail.com...
    > > Hello,
    > >
    > > Recently, I am trying to optimize some old stored procedures. In the
    > > process, I found out in the online help that a "table variable" is
    > > supposed to perform better than temp tables. However, I have found
    > > exactly the opposite. Whereas using temp tables, I can obtain query
    > > results in less than one second. Using a table variable took me more
    > > than 20 seconds!
    > >
    > > For the comparison, the stored procedures are almost the same. I've just
    > > replaced CREATE TABLE #temp (...) with DECLARE temp TABLE (...). It
    > > consists of only an insert to the table followed by a join select of
    > > about 4000 rows. I wonder if this is related to the the amount of memory
    > > available to the SQL server?
    > >
    > > Any ideas? Thanks!
    > >
    > > Francisco
    >
    >

    Tommy Guest

  6. Moderated Post

    Default Re: SQL Server 2000 SP3: Why is Table variable slower than temp tables?

    Removed by Administrator
    FL Guest
    Moderated Post

  7. #7

    Default Re: SQL Server 2000 SP3: Why is Table variable slower than temp tables?

    A table variable can have an index, if it is declared with
    a primary key or unique constraint.

    If you look at the estimated execution plan for the following,
    you'll see the index is used.

    declare t table (
    pk int primary key,
    c1 varchar(10) unique,
    c2 datetime
    )

    insert into t
    select OrderID, CustomerID+rtrim(OrderID), OrderDate
    from Northwind..Orders

    select * from t
    where c1 like 'V%'

    Steve Kass
    Drew University

    Tommy wrote:
    >I'mjust thinking out loud, but could the reason for better performance in
    >temp tables be that they are indexed?
    >
    >A table variable cannot have an index, and neither can a derived table (to
    >the best of my knowledge).
    >
    >Joining on an indexed feild is just about always going to be faster as a
    >full table scan is not required.
    >
    >If your temp table is not indexed, and is still faster then I have no idea,
    >but i suspect there is a line of code there somewhere creating the index =)
    >
    >cheers
    >
    >"Delbert Glass" <delbertnoincoming.com> wrote in message
    >news:etr7SymRDHA.2676TK2MSFTNGP10.phx.gbl...
    >
    >
    >>Use a derived table instead of either of those.
    >>
    >>Bye,
    >>Delbert Glass
    >>
    >>"FL" <frleong_NOSPAMhotmail.com> wrote in message
    >>news:3F0B92EB.82F16Dhotmail.com...
    >>
    >>
    >>>Hello,
    >>>
    >>>Recently, I am trying to optimize some old stored procedures. In the
    >>>process, I found out in the online help that a "table variable" is
    >>>supposed to perform better than temp tables. However, I have found
    >>>exactly the opposite. Whereas using temp tables, I can obtain query
    >>>results in less than one second. Using a table variable took me more
    >>>than 20 seconds!
    >>>
    >>>For the comparison, the stored procedures are almost the same. I've just
    >>>replaced CREATE TABLE #temp (...) with DECLARE temp TABLE (...). It
    >>>consists of only an insert to the table followed by a join select of
    >>>about 4000 rows. I wonder if this is related to the the amount of memory
    >>>available to the SQL server?
    >>>
    >>>Any ideas? Thanks!
    >>>
    >>>Francisco
    >>>
    >>>
    >>
    >>
    >
    >
    >
    >
    Steve Kass Guest

  8. #8

    Default Re: SQL Server 2000 SP3: Why is Table variable slower than temp tables?


    I tried making an example like your description,
    and the table variable was better than the temp table
    not way worse.

    Perhaps the table variable proc is loading or waiting for the source or it
    rows
    After which the source or its rows are readily available for the temp table
    proc.

    I would suggest you keep up the hunt for what's really happening.
    Perferably, getting to complete the hunt before modify your code.

    Bye,
    Delbert Glass


    Delbert Glass Guest

  9. #9

    Default Re: SQL Server 2000 SP3: Why is Table variable slower than temp tables?

    I am also just thinking out loud but could the difference be select into
    versus Insert into? One being a logged operation and one not? I have no
    idea if table var inserts are logged (not certain it would make sense
    but...) but other Inserts are. I have played with table vars and found them
    good for some things but temp tables and select into seem to be the faster
    of the two.

    good luck
    tonyo

    "Delbert Glass" <delbertnoincoming.com> wrote in message
    news:etr7SymRDHA.2676TK2MSFTNGP10.phx.gbl...
    > Use a derived table instead of either of those.
    >
    > Bye,
    > Delbert Glass
    >
    > "FL" <frleong_NOSPAMhotmail.com> wrote in message
    > news:3F0B92EB.82F16Dhotmail.com...
    > > Hello,
    > >
    > > Recently, I am trying to optimize some old stored procedures. In the
    > > process, I found out in the online help that a "table variable" is
    > > supposed to perform better than temp tables. However, I have found
    > > exactly the opposite. Whereas using temp tables, I can obtain query
    > > results in less than one second. Using a table variable took me more
    > > than 20 seconds!
    > >
    > > For the comparison, the stored procedures are almost the same. I've just
    > > replaced CREATE TABLE #temp (...) with DECLARE temp TABLE (...). It
    > > consists of only an insert to the table followed by a join select of
    > > about 4000 rows. I wonder if this is related to the the amount of memory
    > > available to the SQL server?
    > >
    > > Any ideas? Thanks!
    > >
    > > Francisco
    >
    >

    Tony-O Guest

  10. #10

    Default Re: (Solved) SQL Server 2000 SP3: Why is Table variable slower than temptables?

    I think I've found the problem. Apparently, the collation of the table
    variable is different. (why?). After explicitly setting the collation
    (database_default is not sufficient, even though I am pretty sure that
    hey are the same, Latin1_General_CI_AS) in the declare table_var TABLE
    (...), the performance went back to normal.

    Francisco

    Tony-O wrote: 
    FL Guest

  11. #11

    Default Re: SQL Server 2000 SP3: Why is Table variable slower than temp tables?

    Sorry I missed that I guess.

    tonyo

    "Delbert Glass" <com> wrote in message
    news:et20X1$phx.gbl... 
    >
    > The answer should be no
    > because he told us he used CREATE TABLE
    > to create the temp table which means he could not
    > being using SELECT INTO unless he misinformed us.
    >
    > Bye,
    > Delbert Glass
    >
    >[/ref]


    Tony-O Guest

Similar Threads

  1. SP Temp Tables
    By navdeep virk in forum Informix
    Replies: 6
    Last Post: February 18th, 04:30 PM
  2. Temp tables and jdbc 2.21.jc3 on IDS 9.4.UC1
    By Rajesh Kapur in forum Informix
    Replies: 0
    Last Post: August 26th, 09:59 PM
  3. Linking Informix tables to SQL Server 2000
    By Scott Adams in forum Informix
    Replies: 1
    Last Post: July 21st, 03:29 PM
  4. Accessing Temp Tables
    By Karthik Nagaraj in forum Microsoft SQL / MS SQL Server
    Replies: 6
    Last Post: July 12th, 11:26 PM
  5. Question about optimizer with (big) temp tables
    By Eric Mamet in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 2nd, 08:44 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139