After SQL Server 2008 upgrade, query with UNION runs very slowly

We migrated from SQL Server 2000 to 开发者_StackOverflow社区2008 and a query for some users (in certain database roles) runs very slowly when we use UNION operator. I tried it with UNION ALL

The query is like below

SELECT  'PONumber'= '','POId'=''

UNION 

SELECT DISTINCT 
                'PONumber'=PONumber, 
                'POId'=RTRIM(CONVERT(varchar(32),po.POId) )
FROM  PurchaseOrder po JOIN  ... JOIN ..... JOIN ...................

If I just remove the first part (and UNION) and run the second query the results are returned immediately.

Any suggestions?

I don't know how it would have reacted in SQL 2000, but using UNION instead of UNION ALL is going to cause a sort operation when the results of the two queries are merged (to get rid of duplicates). That can be a significant amount of processing, depending on the size of your result set.

Also, tempdb usage change pretty dramatically between SQL 2000 and SQL 2008. A sort operation is likely to use tempdb, so you should check the media for your tempdb and make sure that it has plenty of space, etc.