SQL Server Performance Forum – Threads Archive

Avoiding table spooling

Hi. I have a query which is causing table spooling when run on one database, but not on others. Would this be due to the indexing being different, or something else? Also, is there a way that I can avoid the table spooling and the use of the tempdb since it’s causing it to take much longer to execute. The query is: SELECT elem_value from metadata a , status b where a.uri = b.uri and b.status = ‘Final’ and elem_name = ‘URL’ and a.uri in ( select uri from metadata where elem_name = ‘HarvestDepth’ and elem_value = ‘1’ union select distinct uri from metadata s where uri not in (select uri from metadata where elem_name = ‘HarvestDepth’)) order by elem_value and the metadata table is as follows: create table metadata( uri varchar(32) not null, elem_name varchar(12<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> not null, seq_number integer not null, elem_value varchar(512) null, index_value varchar(10) null, lower_index_value varchar(10) null ) with pk of (uri, elem_name, seq_number). I tried changing it to this: SELECT elem_value FROM metadata a, status b WHERE a.uri = b.uri AND b.status = ‘Final’ AND elem_name = ‘URL’ AND a.uri IN ( SELECT uri FROM metadata WHERE (elem_name = ‘HarvestDepth’ AND elem_value = ‘1’ ) OR uri NOT IN (SELECT uri FROM metadata WHERE elem_name = ‘HarvestDepth’)) ORDER BY elem_value to remove the union. Is there anything else I could do to improve its performance and reduce the spooling? Thanks!

Actually the table spooling only happens on certain runs of the query on certain databases. Does anyone know what could cause this?

Try the below query — SELECT elem_value
FROM metadata a, status b
WHERE a.uri = b.uri AND b.status = ‘Final’ AND a.elem_name = ‘URL’
and ( exists ( select 1 from metadata b where b.uri = a.uri and
b.elem_name = ‘HarvestDepth’ and b.elem_value = ‘1’ )
or
not exists ( select 1 from metadata c where c.uri = a.uri and
c.elem_name = ‘HarvestDepth’ )
)
order by elem_value

Thanks for your reply. I tried comparing your suggested query with the two variations that I posted, but it ran slower than both of them.

Not sure how it will go in terms of performance, but how about: SELECT elem-value
FROM metadata a INNER JOIN status b on a.uri = b.uri
INNER JOIN (SELECT uri FROM metadata WHERE (elem_name = ‘HarvestDepth’ AND elem_value = ‘1’) OR elem_name <> ‘HarvestDepth’) c on a.uri = c.uri
WHERE b.status = ‘Final’ AND a.elem_name = ‘URL’
ORDER BY elem_value Ben ‘I reject your reality and substitute my own’ – Adam Savage

]]>