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:
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.PrimaryKeyI am running SQL Server 2005 SP2.