SQL Server Performance Forum – Threads Archive

strange query performance

I’ve got a query with two subselects (one inside the other, to boot). It actually runs fine on most of the data, but hangs on a few days’ worth out of the whole table (the where clause selects by day). The row counts don’t look different between the days that run fine and those that don’t. The query plan shows that the days that don’t run use a merge or hash join, and those that do run use nested loops. I added an index that caused it to switch from a hash to a merge join, but it didn’t help performance. This is all odd enough, but here’s the kicker: I removed and reinstalled replication on this database, and after that, the days that don’t run are different: some days that used to run now don’t, and some days that didn’t run before run fine now. [?] Can anyone shed some light on this behavior and how I might improve performance?

---

Have you checked the execution plan for the queries with and without adding the index?
Also post the sample query to advise more in detail. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

---

The plan used the index that I added, but the problem area seems to be a join between custpackingslipjour and custpackingsliptrans. The days that run quickly use nested loops, and those that run slowly use a hash or merge join. more detail than you probably need: original query, produced by code in Axapta:
SELECT MAX(A.PACKINGSLIPID),A.SALESID
FROM bmssa.CUSTPACKINGSLIPJOUR A(NOLOCK)
WHERE A.DATAAREAID=’dmo’
AND EXISTS (SELECT ‘x’ FROM CUSTPACKINGSLIPTRANS B(NOLOCK)
WHERE B.DATAAREAID=’dmo’
AND B.PACKINGSLIPID=A.PACKINGSLIPID
AND B.CREATEDDATE>={ts ‘2007-05-16 00:00:00.000’}
AND B.CREATEDDATE<={ts ‘2007-05-16 00:00:00.000’}
AND B.ITEMID<>’AdminFee’ AND B.QTY>0
AND EXISTS (SELECT ‘x’ FROM bmssa.SALESLINE C(NOLOCK)
WHERE C.DATAAREAID=’dmo’ AND C.SALESID=B.SALESID
AND C.ITEMID=B.ITEMID
AND (C.CMTPROGRAMCODE=’Microsoft’ OR C.CMTPROGRAMCODE=’Microsoft – MLDP’ OR C.CMTPROGRAMCODE=’MicrosoftMSDDP’) ) )
GROUP BY A.SALESID ORDER BY A.SALESID OPTION(FAST 4) I’ve updated the statistics on the tables involved. The counts are 120K for custpackingslipjour, 910K for custpackingsliptrans, and 1.2M for salesline. The individual days, though, have counts more like 250, 2000, and 1100. There’s a lot of variation in count by individual days – more within the set of days that run and those that don’t than between the two sets. The indexes… here’s custpackingslipjour:
DATAAREAID, ORDERACCOUNT, DELIVERYDATE, PACKINGSLIPID
DATAAREAID, SALESID, PACKINGSLIPID
DATAAREAID, PACKINGSLIPID, DELIVERYDATE
DATAAREAID, RECID
DATAAREAID, REFNUM, SALESID, DELIVERYDATE
DATAAREAID, LEDGERVOUCHER, DELIVERYDATE custpackingsliptrans:
DATAAREAID, CREATEDDATE
DATAAREAID, SALESID, DELIVERYDATE, PACKINGSLIPID, LINENUM
DATAAREAID, RECID
DATAAREAID, INVENTTRANSID and salesline:
DATAAREAID, INVENTDIMID
DATAAREAID, ITEMID, RECID
DATAAREAID, RECID
DATAAREAID, SALESID, LINENUM
DATAAREAID, SALESID, SALESSTATUS
DATAAREAID, SALESTYPE
DATAAREAID, SALESSTATUS, ITEMID
DATAAREAID, INVENTTRANSID I rewrote the query to use joins and it runs acceptably quickly in QA that way. Our Axapta person is still trying to rewrite the code to produce this modified query. The query plan still differs by day, though: SELECT MAX(A.PACKINGSLIPID),A.SALESID
FROM bmssa.CUSTPACKINGSLIPJOUR A(NOLOCK)
inner join custpackingsliptrans b
on B.PACKINGSLIPID=A.PACKINGSLIPID
inner join bmssa.salesline C
on C.SALESID=B.SALESID
AND C.ITEMID=B.ITEMID
WHERE A.DATAAREAID=’dmo’
and B.DATAAREAID=’dmo’
AND B.CREATEDDATE>={ts ‘2007-05-22 00:00:00.000’}
AND B.CREATEDDATE<={ts ‘2007-05-22 00:00:00.000’}
AND B.ITEMID<>’AdminFee’ AND B.QTY>0
and C.DATAAREAID=’dmo’
AND (C.CMTPROGRAMCODE=’Microsoft’ OR C.CMTPROGRAMCODE=’Microsoft – MLDP’ OR C.CMTPROGRAMCODE=’MicrosoftMSDDP’)
GROUP BY A.SALESID ORDER BY A.SALESID OPTION(FAST 4) any insights would be most helpful!

---

Also ensure that your indexes are being rebuilt often, along with statistics being updated. ————————————————————–
Brad M. McGehee, SQL Server MVP
Technical Editor/Moderator www.SQL-Server-Performance.Com
Director of DBA Education for www.Red-Gate.Com
www.sqlbrad.com
www.sqlHawaii.com

---

Is it possible that it changes joins because one is more optimal than the other depending on the the number of rows…? Tradeco wholesalers, drop shippers & trade network

---

One would think that, but then why does the query take more than 10 times as long for the slow days? And I’ve found that there’s more variation within the sets than between them. I’m thinking it’s something with the internals and how the "slow" days happen to be arranged on disk. The slow days involve a bookmark lookup and the fast days don’t. I’ve updated statistics on the tables involved and it didn’t change anything. But I used the default sampling… is there some other sampling I should try?

---

http://www.sql-server-performance.com/statistics.asp
http://sqlserver-qa.net/blogs/tools…statistics-auto-update-statistics-option.aspx Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

---

]]>