SQL Server Performance

Using Top x in SubQuery

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by soccerdad, Mar 20, 2007.

  1. soccerdad New Member


    The code snipet (from a larger Storeed Procedure) below returns records under 1 second with SQL 2000, with SQL 2005 it takes about 9 seconds. Any issues known using the Top X from within a subquery?

    INNER JOIN
    Item ITEM ON
    (ISNULL ((SELECT TOP 1 IntItemID
    FROM InventoryTran IT
    WHERE IT.IntCompanyID = 'DV' AND
    IT.IntIssueReceipt = 'R' AND
    IT.IntLotID = SLT1.SltWIPFGLotID AND
    IT.IntTranDate <= '2007-03-15'

    ORDER BY IT.IntTranDate DESC), SodItemID) = ITEM.ItmItemCode)
  2. FrankKalis Moderator

    Any difference in the execution plans? All else being equal?

    --
    Frank Kalis
    Moderator
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de
  3. soccerdad New Member

    It does seem to use the same indexes as SQL 2000. Other than that, it is diferent.

    I have also added new stats that brought down the time from 2min 30s.
  4. Adriaan New Member

    Same hardware?
  5. soccerdad New Member

    No, the SQL 2005 is running on 2 Quad Cores, 16 GB Ram running Win 2003 x64 and SQL 2005 X64. The desk sub system is considerably bigger and faster than our SQL 2000 box.

    SQL 2000 is all 32 bit on 2 x 3 Ghz Xeon with 2 GB Ram

    Every other SP or View we have runs in a fraction of the time than SQL 2000.
  6. Adriaan New Member

    You might be suffering from unwanted parallellism - try adding OPTION (MAXDOP 1) to the end of the query.
  7. soccerdad New Member

    I tried your suggestion. Even 2-8, no matter what the setting, it always comes back at the same time. I read an article not long ago that SQL 2005 sp2a may have some changes to parallellism. Not sure if this would directly affect my situation or not?
  8. Adriaan New Member

    It's quite an unhappy join, so it's a small wonder that SQL 2000 is smart enough to resolve the matter quickly. Couldn't you rewrite this as a correlated subquery in the WHERE clause?

Share This Page