jehhynes


I am building a table-value UDF that joins a significant-sized pivot table (ends up with 10000 rows) to another significant-size table. The query takes about 3 minutes, and I noticed that the memory allocation for the sqlserver process goes through the roof.

I tried selecting the pivot table into a temp table, and joining on that, and it is real fast - about 2 seconds. However, since you can't use a temp table in a UDF, I needed to use a table variable instead. Changing the temp table to a table variable gave me the same slow results - about 3 minutes. Does anyone have a clue why this would make any difference My SQL looks like the following:

Code Snippet

declare @temp table

(

[PrimaryKey] bigint,

[CategoryName] varchar(15),

[Description] varchar(255),

[LongDescription] varchar(1000)

)

insert into

@temp

select

PrimaryKey,

convert(varchar(15),[CategoryName]),

convert(varchar(255),[Description]),

convert(varchar(1000),[LongDescription])

from

history pivot

(

max(oldvalue)

for colname

in ([CategoryName],[Description],[LongDescription])

) as tn

select

*

from

Categories

left outer join @temp on Categories.CategoryID=@temp.PrimaryKey

I am running SQL Server 2005 SP2.



Re: Joining on a pivot table is sluggish

Louis Davidson


Try putting a primary key constraint on your Primary Key Column. No guarantees, but it might help.

Code Snippet

declare @temp table

(

[PrimaryKey] bigint PRIMARY KEY,

[CategoryName] varchar(15),

[Description] varchar(255),

[LongDescription] varchar(1000)

)

If that doesn't help, check out the plan of the two queries (and post them), it might show something. Is categoryId the primary key for Categories







Re: Joining on a pivot table is sluggish

jehhynes

Adding the primary key definition didn't help anything. The real problem is when inserting into the table variable, not performing the join. Once the data is in the table variable, it will work fine. The join only time the join works slowly is if I either use a CTE or a derived table to do a brute join. It seems like this in-memory operation involving a pivot table is somehow causing it to perform slowly. It's slow inserting into a table variable, or simply doing the join. The only solution I've found so far is to use a temp table, which I can't use in a UDF.

I looked at the execution plans using a table variable vs a temp table, and they are essentially the same. I'm not sure how to upload a picture onto the forum, but here are the major tasks for each:

% for temp table / % for table variable

Clustered index scan: 48% / 46 %

Hash match: 10% / 17%

Clustered index seek 41% / 35%

Another odd thing I noticed about the pivot table performance is that it works much faster if I add an extra column to the table. I.e, if I add a blank bit column to the source table, and don't define it as one of the pivoted fields, it's a lot faster. Or rather, I should say it is really slow if I don't do this (even using a temp table).

=== Edited by jehhynes @ 05 May 2007 8:08 PM UTC===
Following is the table definition for my source table that I am pivoting:

CREATE TABLE [dbo].[history](

[colname] [varchar](50) NOT NULL,

[primarykey] [bigint] NOT NULL,

[oldvalue] [sql_variant] NULL,

[perfcol] [bit] NULL

) ON [PRIMARY]


perfcol is the empty bit column i added which increased the performance