Performance between SP3 and SP4

20 Apr 2006 2:14 PM

Warren

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

20 Apr 2006 3:06 PM

pcleiter

Having you made sure that statistics are up to date on both servers/databases?

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?

Show quoteHide quote

"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
>
>

21 Apr 2006 7:38 AM

Warren

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.

Thread options