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

  • 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

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

  • 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).

  • 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)

  • 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

  • Are you using join or not ???

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

    table A WITH (NOLOCK) for example

    Cheers,

    Rishi

  • 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.

  • 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)

  • 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.

  • 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?

  • 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.

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

    Cliff

  • 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).

  • 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.

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

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