Problem With Query Timeout

Hallo,

I have the following Query running on my local SQL Server 2005 Standard Edition on Windows XP, and it needs only 2 sec. to execute. Then I transfered it to our production database, which is an SQL Server 2005 Datacenter Edition on a quad-core Xeon with 4GB Memory and the Query always runs in a Timeout. The version of my local Database is 9.0.3054 and the Production Version is 9.0.3042. 3042 seems to be SP2.

Here the Query:


SELECT i.lid AS userlid, i.lgroupid AS grouplid, i.strgroupname, j.xgssnid

FROM (SELECT kruser.lid, kruser.stremployeeno, krgroupusers.lgroupid, krusergroup.strgroupname

FROM kruser INNER JOIN krgroupusers ON kruser.lid = krgroupusers.luserid

INNER JOIN krusergroup ON krgroupusers.lgroupid = krusergroup.lid

WHERE (NOT (kruser.strusername LIKE 'i_%')) AND

(kruser.stremployeeno <> '') AND

(kruser.stremployeeno IS NOT NULL)

) AS i

LEFT OUTER JOIN

(SELECT TOP (100) PERCENT xgssnid, AppRolle

FROM (SELECT DISTINCT xwebmodul + '_' + xrolle AS AppRolle, xgssnid

FROM xzztmpuserimport

UNION

SELECT DISTINCT 'nav_' + xwebmodul AS AppRolle, xgssnid

FROM xzztmpuserimport AS a

) AS b

ORDER BY xgssnid, AppRolle

) AS j

ON i.stremployeeno = j.xgssnid AND i.strgroupname = j.AppRolle

WHERE (i.lgroupid > 105) AND (i.lid > 100) AND

(NOT (i.strgroupname LIKE 'i_%')) AND

(j.xgssnid IS NULL)



This Query filters all internal Applicationroles of a user which must be deleted for the specific user, because he didn't need them longer. Can it be, that it was a bug in the older Servicepack and it runs on my PC because of the newer one? My problem is also, that I didn't know what ServiecPack brings Version 3042 to 3054.

I also tried splitting the Query in 3 subqueries and using this 3 in a resulting 4th. It's the same!

Has anyone an idea

Thanks for your help
Hans