Performance issue for a query with order by clause in SQL 2005 SP2

Question

Post reply

Performance issue for a query with order by clause in SQL 2005 SP2

sntiwary

Hall of Fame

Points: 3475
More actions
March 5, 2008 at 2:47 am

#120329

I have a query which is running slower in SQL 2005 SP2, syntax as mentioned below.
SELECT DISTINCT TOP 200 Table1.col1,Table2.col1,Table3.col1 FROM Table1(NOLOCK), Table1(NOLOCK), Table1(NOLOCK) WHERE and order by table3.col1
Please let me know if there is any known issue in sql 2005 with the order by clause because the same query runs faster in SQL 2004 SP4
Also the same query run slower in first run and then faster in the subsequent run in Sql 2005 but no issue in SQL 2000. This issue I am facing after upgrading SQL 2005 SP2 from SQL server 2000 SP4
Any help will be much appreciated
thanks in adavance
--SNT

Viewing 15 posts - 1 through 15 (of 18 total)

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

Giri Duddu SSCommitted Points: 1638 More actions · Answer 1

just add index on the sort column, it will help ur querry and then it will be same on both the servers

SQLZ SSChampion Points: 12872 More actions · Answer 2

Did you update statistics when you upgraded to SQL 2005?

Check out the execution plans on both servers and see if they're different (assuming you have access to the SQL 2000 server).

Karl
source control for SQL Server

Jeff Moden SSC Guru Points: 1003926 More actions · Answer 3

Please post the actual query... there's no way the one you posted actually works.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sqlagentman SSCarpal Tunnel Points: 4484 More actions · Answer 4

As for the reason why it runs quicker the second time through it's because the execution plan has been cached.

- Tim Ford, SQL Server MVPhttp://www.sqlcruise.comhttp://www.thesqlagentman.com http://www.linkedin.com/in/timothyford

Amit Tiwari-332074 SSC Veteran Points: 236 More actions · Answer 5

Amit Tiwari-332074

SSC Veteran

Points: 236

March 6, 2008 at 11:01 am

#786217

Are you using join or not ???

Rishi Girdhar SSC Veteran Points: 200 More actions · Answer 6

Please post a full query and always use hint (NOLOCK) with 'WITH'.

table A WITH (NOLOCK) for example

Cheers,

Rishi

Cliff Jones SSChampion Points: 10547 More actions · Answer 7

There is a known issue if you are missing a join predicate in one of your join conditions. Refer to KB article:

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

We have seen several instances where queries with joins which are missing a join predicate perform much worse in SQL 2005 compared to SQL 2000. Examine the Query Plan to determine if there is a 'No Join Predicate' warning.

Jeff Moden SSC Guru Points: 1003926 More actions · Answer 8

That's useful... thank you very much.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Bob Fazio SSChampion Points: 10794 More actions · Answer 9

sntiwary (3/5/2008)
I have a query which is running slower in SQL 2005 SP2, syntax as mentioned below.
SELECT DISTINCT TOP 200 Table1.col1,Table2.col1,Table3.col1 FROM Table1(NOLOCK), Table1(NOLOCK), Table1(NOLOCK) WHERE and order by table3.col1

As mentioned this couldn't be the query.

Table1, Table1, Table1... Where ??? AND ??? Order by Table3.col1

Please try again. Fyi if this is even close.

I suspect you might be trying to do something like the following, but it would be nice to verify;

;WITH table1(col1) AS (

SELECT 1 col1

UNION ALL

SELECT 2 col1

UNION ALL

SELECT 3 col1

UNION ALL

SELECT 4 col1

UNION ALL

SELECT 5 col1

UNION ALL

SELECT 6 col1

)

SELECT DISTINCT TOP(200) Table1.col1,Table2.col1,Table3.col1

FROM table1,table1 TABLE2,table1 table3

ORDER BY table3.col1

Please read http://www.sqlservercentral.com/articles/Best+Practices/61537/">

http://www.sqlservercentral.com/articles/Best+Practices/61537/

and help us help you.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 10

Cliff Jones (3/6/2008)
There is a known issue if you are missing a join predicate in one of your join conditions. Refer to KB article:
http://support.microsoft.com/kb/942444/
We have seen several instances where queries with joins which are missing a join predicate perform much worse in SQL 2005 compared to SQL 2000. Examine the Query Plan to determine if there is a 'No Join Predicate' warning.

Cliff - that's good to know - but it left me confused. Are they talking about not specifying INNER/OUTER, or skipping a join clause altogether and doing it in the WHERE clause?

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Cliff Jones SSChampion Points: 10547 More actions · Answer 11

No, the queries can have what looks like a perfectly formed join condition. But when you examine the query plan you will see a warning indicating 'No Join Predicate' indicating that 2 of the tables involved do not have a predicate (when joined). Adding a an Option (Force Order) to the query produces a completely different plan and the warning disappears (in some cases). That's how you know that this is the problem. Most of the queries that I have seen that perform better on SQL 2000 are exhibiting this problem. Cumulative update 4 to SP 2 is supposed to solve the problem.

Cliff Jones SSChampion Points: 10547 More actions · Answer 12

I guess I should have said 'a perfectly formed outer join', using a LEFT Join for example.

Cliff

Bob Fazio SSChampion Points: 10794 More actions · Answer 13

I have CU4 on all my systems, so I can't test this.

However as I read it, even though you have a valid join, it may interpret it wrong resulting in a Cartesian join (which of course would be slower).

Cliff Jones SSChampion Points: 10547 More actions · Answer 14

Yes, that is what I am seeing. I will have CU 4 installed this week-end so I can verify that it goes away afterwards. But if you can't reproduce it then there is hope that it will resolve the problem, however the KB arcticle states that you must have the 2 trace flags on to implement this feature.