#Temp vs Table Variable - Bit of a Bug (er!)

Question

Post reply

#Temp vs Table Variable - Bit of a Bug (er!)

vinny_1973

SSCommitted

Points: 1832
More actions
October 4, 2005 at 8:10 am

#87958

Hey all
Ok... found a nice one that i wudn't mind explaining, as i haven't been able to figure out why this happens and certainly aint seen it b4!
1) I have a table VARIABLE (id varchar(15), Descript varchar(800)) which has been populated with data (~60000) from a significantly larger table.
2) The temp table is then involved (RIGHT OUTER JOINED) in a query that uses this subset of data.
The behaviour i'm seeing is that if i just do the join but do not have the temp tables ID or Descript fields in the SELECT clause, the query runs fine and comes back after about 3 secs. However, if i DO put ID or Descript in the SELECT clause, the query never returns ... well ok ... I actually, i got bored after 5mins!?
OK ... that's puzzle 1 for you ... puzzle 2 is that if i change the table variable to an actual #Temp table, i do not get this behaviour ... and when i include the the Descript field from the #temp table, all is well and the query runs in about 3secs!
Am probably gonna have a bit more of a play with the profiler this aft to see what the table variable is causing ... but if anyone else has already seen this and has an explanation, i'd certainly be curious!
Cheers
Vinny
Query Examples :
DECLARE @tabDesc TABLE (ID varchar(15), Descript varchar(800) )
--- populate table ---
THIS QUERY DOESNT WORK
   SELECT   d.ID,
     ddt.Descript AS DESCRIPTION,
   FROM          dbo.tmp_PO_Detail d
     LEFT OUTER JOIN dbo.tmp_PO_Detail_User du ON d.ID = du.ID
     LEFT OUTER JOIN @tabDesc ddt ON d.ID = ddt.ID
     LEFT OUTER JOIN dbo.tmp_PO_Line_Detail_Status lds ON d.LINE_NUMBER = lds.ITEM_NBR
        AND d.PO_NBR = lds.ID
        AND d.PO_REF = lds.REFERENCE
THIS QUERY DOES WORK
   SELECT   d.ID,
   FROM          dbo.tmp_PO_Detail d
     LEFT OUTER JOIN dbo.tmp_PO_Detail_User du ON d.ID = du.ID
     LEFT OUTER JOIN @tabDesc ddt ON d.ID = ddt.ID
     LEFT OUTER JOIN dbo.tmp_PO_Line_Detail_Status lds ON d.LINE_NUMBER = lds.ITEM_NBR
        AND d.PO_NBR = lds.ID
        AND d.PO_REF = lds.REFERENCE
THIS QUERY DOES WORK
SELECT id, Descript FROM @tabDesc

USING
CREATE TABLE #tabDesc (ID varchar(15), Info varchar(800) )
---Populate Table---
ALL QUERIES WORK

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Maybe this can help.

http://support.microsoft.com/default.aspx?scid=kb;en-us;305977

vinny_1973 SSCommitted Points: 1832 More actions · Answer 2

Cheers Remi

This statement's about the only thing that may make sense :

"Table variables do not maintain statistics like temporary tables can. Statistics cannot be created on table variables through automatic creation or by using the CREATE STATISTICS statement. Therefore, for complex queries on large tables, the lack of statistics may deter the optimizer to determine the best plan for a query, thus affecting the performance of that query."

... although in my case, i think it's detered the optimizer from coming up with a plan, period ... or sent it down the shops for some milk!?

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

It's not the first time I hear of performance problems when talking about table variables . Looks like you're past the threashold of the server and you need to do back to a #temp table.

Jeff Gray SSChampion Points: 10667 More actions · Answer 4

Also from that MSKB article:

"Non-clustered indexes cannot be created on table variables, other than the system indexes that are created for a PRIMARY or UNIQUE constraint. That can influence the query performance when compared to a temporary table with non-clustered indexes."

So, you can still create an index on the table variable, and that might help out.

instead of

DECLARE @tabDesc TABLE (ID varchar(15), Descript varchar(800) )

try

DECLARE @tabDesc TABLE (ID varchar(15) primary key clustered, Descript varchar(800) )

That MIGHT help. But then again, it might be better to use a temp table. Or perhaps an inline view. YMMV, Batteries not included, not valid with any other offer, etc...

hth JG

vinny_1973 SSCommitted Points: 1832 More actions · Answer 5

I've just switched back to a usual temp table now ... profiled the sproc in question, and am not getting an recompilations (which is why i usually go for variables instead!), so am happy!

As for mentioning views ... the whole idea of using a temp table was to get away from the view that the process was using ... SO FAR, i've knocked 50s off the whole thing (down to 13s), so not planning on going backwards!?