January 22, 2009 at 2:54 pm
Hi,
Can anyone offer any suggestions on how we might tune the following?
select a.ApplicationCode as "Application Id",
at.TransactionDateTime as "Transaction Date",
at.TransactionNumber as "Transaction Number",
at.TransactionTokenKey as "Transaction Token",
eg.EigenReferenceNumber as "Reference Number",
eg.EigenApprovalCode as "Approval Code",
eg.EigenAmountas "Amount",
m.MessageId as "Message Code",
m.SystemMessage as "System Message"
fromApplication a,
ApplicationTransaction at,
Activity act,
EigenDetail eg,
Message m
where a.ApplicationId = at.ApplicationId and
at.TransactionNumber = eg.TransactionNumber and
at.TransactionTokenKey = act.TransactionNumber and
act.MessageId = m.MessageId and
a.ApplicationCode = 'TEST' and
at.TransactionTokenKey = 'X108923' or
at.TransactionNumber = 'X108923'
order by at.TransactionDateTime asc
We tried running in through the tuning wizard but it didn't produce any results. We have to be careful about too many indexes here because the application is mostly used for inserting new records. This query is used for a seldom run report so although I want to make it faster, it's not as important as making sure the application inserts remain fast.
Any suggestions would be appreciated. Thanks.
January 22, 2009 at 2:58 pm
I don't see anything horribly wrong with the query. A few minor details, like adding the domain to the table names, might get some tiny improvement. But the main thing is going to be the indexes and execution plan.
Can you post the execution plan? Save it as a file and upload it as an attachment to a post.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 22, 2009 at 3:23 pm
Here is the estimated execution plan as a jpg. I don't think I can upload the actual sqlplan file on here.
January 22, 2009 at 3:55 pm
Have you tried updating the statistics on the tables
Also, you could try the MAXDOP 1 option to prevent the parallel plan
David
January 23, 2009 at 12:39 am
Jessica (1/22/2009)
I don't think I can upload the actual sqlplan file on here.
Zip it and attach it. If possible, for the actual exec plan, not the estimated.
The jpg doesn't have all the info. It's missing the tooltips and properties of all of the operators
There are a few nested loops with warnings. I can't see what the warnings are, it may be missing join predicates. It looks, from the size of the lines, that the number of rows is increasing through the query, so there may be an unintended cross join in there somewhere.
Lots of scans, clustered index scans. Low cost or not, those are full table scans and should be looked at. It may be that the costing's off because of bad estimates (bad stats)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 23, 2009 at 12:53 am
I think I've found the major problem. You're missing brackets in the where clause. ANDs have precedence over ORs, so the way your where clause is written (with an or at the end with no brackets) means that the where clause is interpretted like thiis.
where (a.ApplicationId = at.ApplicationId and
at.TransactionNumber = eg.TransactionNumber and
at.TransactionTokenKey = act.TransactionNumber and
act.MessageId = m.MessageId and
a.ApplicationCode = 'TEST' and
at.TransactionTokenKey = 'X108923') or
at.TransactionNumber = 'X108923'
So the rows that have a transaction number of X108923 are exempt from any joins or conditions.
I would suggest that you rewrite the FROM and WHERE as follows
FROM Application a
INNER JOIN ApplicationTransaction at on a.ApplicationId = at.ApplicationId
INNER JOIN Activity act on at.TransactionTokenKey = act.TransactionNumber
INNER JOIN EigenDetail eg on at.TransactionNumber = eg.TransactionNumber
INNER JOIN Message m act.MessageId = m.MessageId
WHERE
a.ApplicationCode = 'TEST' and
(at.TransactionTokenKey = 'X108923' or at.TransactionNumber = 'X108923')
ORDER BY at.TransactionDateTime asc
I believe that's what you actually intended.
The joins in the FROM are easier to read (and is the preferred syntax) and the brackets around the OR in the where ensure that the presedence is correct.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply