Performance between SP3 and SP4

We have the following select statement
SELECT 1 FROM DAT_SAMPLES D , WRK_SELECT W
WHERE W.TRANSACTIONID = 53364
AND D.ID = W.RECORDID
AND D.APPROVAL IS NOT NULL
AND NOT ( D.APPROVAL = 1 AND D.APPROVEDBY = 'SYSTEM' )
There are approx 350000 records in DAT_SAMPLES with a clustered index
on ID
and 1 record in WRK_SELECT
Executing the above query in SQLServer 2000 with SP3 takes < 1 second
Executing the same query against the same database connected to
SQLServer 2000 SP4 take approx 30 seconds
Looking at the execution plans SP3 performs a Nested Loop/inner join,
but SP4 performs a Merge Join/Inner Loop
I have attempted to rewrite the query using EXISTS and IN with the same
results.
Has anybody else come across similar performance issues with SP4Having you made sure that statistics are up to date on both servers/database
s?
I do recall having read about different algorithms being used between SPs,
but can't remember which SPs (might have been 2 & 3.)
Also, might a FROM...WITH help?
"Warren" wrote:

> We have the following select statement
> SELECT 1 FROM DAT_SAMPLES D , WRK_SELECT W
> WHERE W.TRANSACTIONID = 53364
> AND D.ID = W.RECORDID
> AND D.APPROVAL IS NOT NULL
> AND NOT ( D.APPROVAL = 1 AND D.APPROVEDBY = 'SYSTEM' )
> There are approx 350000 records in DAT_SAMPLES with a clustered index
> on ID
> and 1 record in WRK_SELECT
> Executing the above query in SQLServer 2000 with SP3 takes < 1 second
> Executing the same query against the same database connected to
> SQLServer 2000 SP4 take approx 30 seconds
> Looking at the execution plans SP3 performs a Nested Loop/inner join,
> but SP4 performs a Merge Join/Inner Loop
> I have attempted to rewrite the query using EXISTS and IN with the same
> results.
> Has anybody else come across similar performance issues with SP4
>|||I had updated all statistics and also tried a FROM... WITH, neither had
any effect.
I have identified the problem being that the WRK_SELECT.RECORDID is a
DECIMAL(10,0) and the DAT_SAMPLES.ID is a DECIMAL(9,0). This did not
cause any problem with SP3, but with SP4 it only works if I change
RECORDID to DECIMAL(9,0), or use CONVERT in the query.