strange query performance - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

strange query performance

Author

Topic

annqueue
Starting Member

12 Posts

Posted - 2007-05-31 : 15:32:09

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?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-05-31 : 15:40:46

It's practically impossible to assist you with this without seeing your code.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

annqueue
Starting Member

12 Posts

Posted - 2007-05-31 : 17:24:00

Okay, here's the code. Keep in mind this is generated by an Axapta front end. We are looking at rewriting the query, but we'll have to figure out how to make Axapta produce the modified query.

Thanks for any help!

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)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-05-31 : 17:26:58

This query is not valid T-SQL code. So are you using SQL Server for your database?

Tara Kizer
http://weblogs.sqlteam.com/tarad/

annqueue
Starting Member

12 Posts

Posted - 2007-05-31 : 18:43:44

Yes.

What's really mystifying me is how the same query comes up with different plans, despite the fact that the rowcounts for the underlying data aren't different. There's more rowcount variation within the two sets (data that runs vs. data that doesn't) than between the two sets.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-05-31 : 19:00:24

Please post the query that would work inside Query Analyzer.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-05-31 : 19:04:34

Actually it is valid SQL (using ODBC anyway).

Can you give us more information about the tables? Like their size, indexes, etc?

I prefer to use JOINs, but EXISTS might be the most effecient way to write this query. Hard to say without more insight into the data.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-05-31 : 19:30:11

quote:
Originally posted by Lamprey

Actually it is valid SQL (using ODBC anyway).

I'm saying it's not valid T-SQL. You wouldn't be able to run it successfully in Query Analyzer. Since most of us do not program using ODBC, we'll need the valid T-SQL code in order to play around with the query.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-05-31 : 19:47:51

quote:
Originally posted by tkizer

quote:
Originally posted by Lamprey

Actually it is valid SQL (using ODBC anyway).

I'm saying it's not valid T-SQL. You wouldn't be able to run it successfully in Query Analyzer. Since most of us do not program using ODBC, we'll need the valid T-SQL code in order to play around with the query.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

Yeah, I can't test in QA. However, it runs just fine in Managemetn Studio (for what it is worth). :)

annqueue
Starting Member

12 Posts

Posted - 2007-06-05 : 15:02:26

It works just fine in my SQL query analzyer. Are you trying it in 2005 instead of 2000?

I've updated the statistics on the tables involved and added an index. 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... well... 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!

annqueue
Starting Member

12 Posts

Posted - 2007-06-12 : 21:17:09

Maybe it's these lines that's making it choke:
AND B.CREATEDDATE>={ts '2007-05-22 00:00:00.000'}
AND B.CREATEDDATE<={ts '2007-05-22 00:00:00.000'}
if you replace it with
AND B.CREATEDDATE>=convert(datetime, '2007-05-22 00:00:00.000')
AND B.CREATEDDATE<=convert(datetime, '2007-05-22 00:00:00.000')
does that help?

Is there something about how the data is arranged on disk that would cause it to need a bookmark lookup for some rows and not others? That's another difference I've seen between "fast" and "slow" days. Showplan says that step takes a fair bit of time.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources