Using IS NULL operator query runs slow in 2008, but less than a second in SQL 2000

Question

Post reply

Using IS NULL operator query runs slow in 2008, but less than a second in SQL 2000

Jamie Wormald

Mr or Mrs. 500

Points: 540
More actions
October 29, 2010 at 1:58 am

#240516

This query below retreive 17 rows and run is less than a second in SQL 2000, but since upgrade to SQL 2008 R2 it take 3 minutes 20 seconds. If you changes the query so it executes with ANSI_NULLS OFF and changes the query to have a null in the IN query then it execute in a second. You could also use ISNULL function to remove the NULL with some other value and look for that and it executes in less than a second. But if you us the operator IS NULL in conjunction with the IN query it takes 3 mintues 20 seconds.
Has anything changed in SQL 2008 to cause this issue.
SELECT
reg.registrarId,
reg.ianaId,
reg.registrarName,
reg.clientId,
reg.enabled,
ISNULL(txn.balance, 0.00) AS [balance],
reg.alertBalance,
reg.disableBalance,
et.enabledTypeName
FROM
Registrar reg
JOIN EnabledType et
ON et.enabledTypeCode = reg.enabled
LEFT JOIN [Transaction] txn
ON txn.registrarId = reg.registrarId
WHERE
txn.transactionid IS NULL
OR txn.transactionid IN
(
SELECT MAX(transactionid)
FROM [Transaction]
GROUP BY registrarid
)
ORDER BY
reg.registrarName ASC
Registrar table has 15 rows, primary key on registrarId
EnabledType table has 3 rows, primary key on enabledTypeCode
[Transaction] table has 31100 rows, primary key on transactionId
I have rebuild the indexes and updated stats, no change.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

SwePeso SSC-Dedicated Points: 39752 More actions · Answer 1

What if you write a better performing query?

SELECTreg.registrarId,

reg.ianaId,

reg.registrarName,

reg.clientId,

reg.[enabled],

txn.balance,

reg.alertBalance,

reg.disableBalance,

et.enabledTypeName

FROMRegistrar AS reg

INNER JOINEnabledType AS et ON et.enabledTypeCode = reg.[enabled]

LEFT JOIN(

SELECTregistrarId,

ISNULL(balance, 0) AS Balance,

ROW_NUMBER() OVER (PARTITION BY registrarId ORDER BY transactionid DESC) AS RecID

FROM[Transaction]

) AS txn ON txn.registrarId = reg.registrarId

AND txn.recID = 1

ORDER BYreg.registrarName ASC

N 56°04'39.16"
E 12°55'05.25"

Jamie Wormald Mr or Mrs. 500 Points: 540 More actions · Answer 2

Thanks for the reply, but my main concern about this is why this is occuring as we may be migrating lots of other systems accross and I need to know what may break and why.

Jamie

SwePeso SSC-Dedicated Points: 39752 More actions · Answer 3

Per se, the queries don't "break", they just happened to be slower in your case.

The internal query optimizer has changed 3 times since SQL Server 2000. Changes were made in SQL Server 2005, SQL Server 2008 and also in SQL Server 2008R2.

If you do get the time, please run my suggestion a few times and report back the time it take to complete.

N 56°04'39.16"
E 12°55'05.25"

Jamie Wormald Mr or Mrs. 500 Points: 540 More actions · Answer 4

Tried your query and it does what si required, but I am still worried about the question, why has it query performance changed in SQL 2008?

SwePeso SSC-Dedicated Points: 39752 More actions · Answer 5

3 minutes 20 seconds for "15 record table to 31100 record table"-join is WAY to long if you ask me.

Do you have the same amount of memory? Same storage devices?

Are you now using virtualization?

There are many many factors in this question which we cannot answer.

Can you post the good execution plan from SQL Server 2000, and the bad execution plan from SQL Server 2008R2?

Attach and post them in ".sqlplan" file format (xml).

And post the time my rewritten query took?

N 56°04'39.16"
E 12°55'05.25"

Grant Fritchey SSC Guru Points: 398718 More actions · Answer 6

There are a number of cases, certainly not a majority by any stretch of the imagination, where performance is worse in 2008 than it is in 2000. Usually this is in areas where the optimizer in 2000 was a little less precise than it is in 2008, so the choices made sometimes lead to better performance. The vast majority of the time it's the opposite.

As was shown, there are better ways to right the query that will result in more consistent behavior. This is usually the case when the performanc is slower.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

alen teplitsky SSC-Dedicated Points: 30014 More actions · Answer 7

Jamie Wormald (10/29/2010)
Tried your query and it does what si required, but I am still worried about the question, why has it query performance changed in SQL 2008?

MS changed a lot of code

we had a query that used IN and it ran fine in SQL 2000. in 2005 it would run for 10 minutes on large result sets and spike the CPU to 100%. MS said they changed the optimizer due to memory issues in 2000. after we upgraded to 64 bit hardware and 32GB of RAM the query ran faster. in the meantime the devs broke it into 2 queries

UMG Developer SSChampion Points: 13482 More actions · Answer 8

A couple questions:

* Have you re-built all your indexes and statistics since you upgraded the database?

* Are you running SQL Server 2008 R2 CU4? (And have you turned on trace flag 4199?)

Microsoft has recently fixed a number of issues in CUs where performance was better in SQL Server 2000 than 2005/2008/2008R2.

Here are a couple:

http://support.microsoft.com/kb/976410/

http://support.microsoft.com/kb/2222998/

R2 CU4 finally fixed one issue I had where a query took about 2 minutes on 2005 and 4 hours on 2008 R2. The combination of CU4 and trace flag 4199 got the query back to running in under 2 minutes.

Jamie Wormald Mr or Mrs. 500 Points: 540 More actions · Answer 9

To conclude this post,we have re-written the query to run in sql 2008. Re-indexing and updateing stats had no effect. We are on the lastest service pack/update.

I guess this is something we will have to live with.