SQL Server Performance Forum – Threads Archive

Using Top x in SubQuery

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)

---

Any difference in the execution plans? All else being equal? —
Frank Kalis
Moderator
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de

---

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.

---

Same hardware?

---

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.

---

You might be suffering from unwanted parallellism – try adding OPTION (MAXDOP 1) to the end of the query.

---

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?

---

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?

---

]]>