SQL Server 2000 vs SQL Server 2005

Question

SQL Server 2000 vs SQL Server 2005

Santhoshkumar KB

SSCertifiable

Points: 5066
More actions
June 9, 2009 at 1:28 pm

#384741

Hi,
The below query runs within a subsecond in SQL 2000 Query Analyzer,
but the same query takes around 5 seconds to run on SQL Server 2005
SELECT tbl1.c1,tbl1.c2 FROM tbl1
WHERE ISNULL(tbl1.c3,'Y') = 'Y' AND
tbl1.c1 NOT IN (SELECT tbl2.c1 FROM tbl2 WHERE tbl2.c2 IN
(SELECT tbl3.c3 FROM tbl3 WHERE tbl3.c2 =--==========Note this equal sign
(SELECT tbl3.c2 FROM tbl3 WHERE tbl3.c1 = 20)))
When the same query when changed as below
(Note: "=" sign is changed to "IN")
It runs within a subsecond in both SQL 2000 and SQL 2005
SELECT tbl1.c1,tbl1.c2 FROM tbl1
WHERE ISNULL(tbl1.c3,'Y') = 'Y' AND
tbl1.c1 NOT IN (SELECT tbl2.c1 FROM tbl2 WHERE tbl2.c2 IN
(SELECT tbl3.c3 FROM tbl3 WHERE tbl3.c2 IN--==========Note this equal sign changed to IN
(SELECT tbl3.c2 FROM tbl3 WHERE tbl3.c1 = 20)))
Please can I know the reason as why its taking more time in SQL 2005 if "=" sign is present?
(tbl3.c2 contains NULL as well)
Thanks,
KB
Thanks,
Santhosh
Human Knowledge Belongs To The World !!

Viewing 7 posts - 1 through 6 (of 6 total)

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

Can you post both execution plans? Can't really figure out the difference without looking at those.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Santhoshkumar KB SSCertifiable Points: 5066 More actions · Answer 2

GSquared (6/9/2009)
Can you post both execution plans? Can't really figure out the difference without looking at those.

I am sorry, right now I do not have permissions to see execution plan.

Somehow I got Execution plan only for SQL 2005.

Please find the attachments

Will this be helpful for you?

Thanks,

KB

Thanks,
Santhosh

Human Knowledge Belongs To The World !!

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Favor please.

Post the table DDL and some sample data for the tables along with the expected results. I was looking at your query and I think it may be much more complex than it really needs to be.

Santhoshkumar KB SSCertifiable Points: 5066 More actions · Answer 4

Lynn Pettis (6/9/2009)
Favor please.
Post the table DDL and some sample data for the tables along with the expected results. I was looking at your query and I think it may be much more complex than it reall needs to be.

Ya sure I will provide the sample data....

Its really time up for me... (its 2:42 AM)...going to bed...

will definitely post tomorrow...

Thanks,

KB

Thanks,
Santhosh

Human Knowledge Belongs To The World !!

Santhoshkumar KB SSCertifiable Points: 5066 More actions · Answer 5

Lynn Pettis (6/9/2009)
Favor please.
Post the table DDL and some sample data for the tables along with the expected results. I was looking at your query and I think it may be much more complex than it really needs to be.

Hi Lynn,

Here is the sample data attached.

Names, numbers in sample data have been changed for some reasons.

AR_PID (parent ID) in table #tblAR- This column has NULL values.

I think that as mentioned in

http://msdn.microsoft.com/en-us/library/ms188048.aspx

for ANSI_NULLS, equality operator must be avoided.

but why is the query executing faster in SQL 2000 and slow in SQL Server 2005.

What will be the final solution for these types of queries?

Thanks,

KB

Thanks,
Santhosh

Human Knowledge Belongs To The World !!

Paul White SSC Guru Points: 150467 More actions · Answer 6

KB,

Your script contains an error - there is an unwanted comma before each UNION ALL.

Having fixed that and run it, the query runs sub-second on my SQL 2K5 SP3 Dev (32 bit).

Is the script truly representative of your problem? I note there are no indexes, for example.

Execution statistics below, and my actual execution plan is attached.

[font="Courier New"](16 row(s) affected)

Table '#tblAR. Scan count 15, logical reads 28, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table '#tblRA. Scan count 1, logical reads 16, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table '#tblDO. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 57 ms.

[/font]

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi