October 7, 2008 at 3:52 pm
Hi I have a select query with many joins, sub queries and text searching. How can I optimize this with a greater performance as this query get stucks in the locks.....
Here is the query
SELECT DISTINCT
TOP (700) b.BOMID, b.BOMName, b.QutNo, b.OrdNo, b.priceStatus, h.BOMUpdDate, b.CreatedBy, b.SoldToAcc, b.JobID, b.FolderName, b.JobName,
j.isAccountTeam, j.TeamName, j.TeamID, b.MarkForArchival, '0' AS ClaimedUser, b.ChangeRequest, b.InqNo, h.SubmittedBy, h.SubmittedDate, b.SSO,
b.BomIdRefNum, b.ChangeRequestCnt, sm.ControllerUserID, sd.AccessRights, b.Status, b.SO_OwnerID, b.FolderLevelNum, b.FolderLevelName,
h.BidDate, b.IsIICR, b.IICRParent, b.TransferMode, h.DocType
FROM BOMDetails AS b INNER JOIN
HeaderInfo AS h ON b.BOMID = h.BOMID
INNER JOIN
(SELECT BOMID, MAX(BOMUpdDate) AS maxupd
FROM HeaderInfo WITH (nolock)
GROUP BY BOMID) AS bb ON h.BOMID = bb.BOMID AND h.BOMUpdDate = bb.maxupd
INNER JOIN
JobAssignmentDetails AS j ON b.JobID = j.JobID
INNER JOIN
SalesTeamMaster AS sm ON j.TeamID = sm.SalesTeamID
INNER JOIN
SalesTeamDetails AS sd WITH (NOLOCK) ON sm.SalesTeamID = sd.SalesTeamID
WHERE (LOWER(b.BOMName) LIKE LOWER('%%')) AND (LOWER(b.BOMID) LIKE LOWER('%%')) AND (LOWER(b.QutNo) LIKE LOWER('%%')) AND
(b.SoldToAcc NOT IN ('0000000000')) AND (b.SSO = 'TEST_SSO') AND (j.MemberID = 'sureshk') AND (j.isAccountTeam = 'CustomerTeam') AND
(sd.MemberID = 'sureshk') AND (b.CRExternal <> 5) AND (b.CRInternal <> 5) AND (h.BOMUpdDate > GETDATE() - 15) OR
(LOWER(b.BOMName) LIKE LOWER('%%')) AND (LOWER(b.BOMID) LIKE LOWER('%%')) AND (LOWER(b.QutNo) LIKE LOWER('%%')) AND
(b.SSO = 'TEST_SSO') AND (j.MemberID = 'sureshk') AND (j.isAccountTeam = 'CustomerTeam') AND (sd.MemberID = 'sureshk') AND (b.CRExternal <> 5)
AND (b.CRInternal <> 5) AND (h.BOMUpdDate > GETDATE() - 15) AND (b.CreatedBy = 'sureshk')
UNION
SELECT DISTINCT
TOP (700) b.BOMID, b.BOMName, b.QutNo, b.OrdNo, b.priceStatus, h.BOMUpdDate, b.CreatedBy, b.SoldToAcc, b.JobID, b.FolderName, b.JobName,
'' AS isAccountTeam, '' AS TeamName, '' AS TeamID, b.MarkForArchival, '0' AS ClaimedUser, b.ChangeRequest, b.InqNo, h.SubmittedBy,
h.SubmittedDate, b.SSO, b.BomIdRefNum, b.ChangeRequestCnt, '' AS ControllerUserID, '' AS AccessRights, b.Status, b.SO_OwnerID,
b.FolderLevelNum, b.FolderLevelName, h.BidDate, b.IsIICR, b.IICRParent, b.TransferMode, h.DocType
FROM BOMDetails AS b INNER JOIN
HeaderInfo AS h WITH (NOLOCK) ON b.BOMID = h.BOMID INNER JOIN
(SELECT BOMID, MAX(BOMUpdDate) AS maxupd
FROM HeaderInfo AS HeaderInfo_1 WITH (nolock)
GROUP BY BOMID) AS BB_1 ON h.BOMID = BB_1.BOMID AND h.BOMUpdDate = BB_1.maxupd
WHERE (LOWER(b.BOMName) LIKE LOWER('%%')) AND (LOWER(b.BOMID) LIKE LOWER('%%')) AND (LOWER(b.QutNo) LIKE LOWER('%%')) AND
(b.SoldToAcc NOT IN ('0000000000')) AND (b.SSO = 'TEST_SSO') AND (b.CRExternal <> 5) AND (b.CRInternal <> 5) AND (b.BOMID NOT IN
(SELECT b.BOMID
FROM BOMDetails AS b INNER JOIN
JobAssignmentDetails AS j WITH (NOLOCK) ON b.JobID = j.JobID
WHERE (j.MemberID = 'sureshk'))) AND (h.BOMUpdDate > GETDATE() - 15) OR
(LOWER(b.BOMName) LIKE LOWER('%%')) AND (LOWER(b.BOMID) LIKE LOWER('%%')) AND (LOWER(b.QutNo) LIKE LOWER('%%')) AND
(b.SSO = 'TEST_SSO') AND (b.CRExternal <> 5) AND (b.CRInternal <> 5) AND (b.BOMID NOT IN
(SELECT b.BOMID
FROM BOMDetails AS b INNER JOIN
JobAssignmentDetails AS j WITH (NOLOCK) ON b.JobID = j.JobID
WHERE (j.MemberID = 'sureshk'))) AND (h.BOMUpdDate > GETDATE() - 15) AND (b.CreatedBy = 'sureshk')
ORDER BY h.BOMUpdDate DESC
October 7, 2008 at 5:08 pm
Very pretty!
Without digging in too deep, I'll throw out a couple things...
Is the DB case sensitive? If not, there's no point in doing all the LOWERs, and that will just ensure you don't use any indexes
If I followed the parenthesis right, you have the equivilent of:
A and B and C or D and E and F
when what you probably want is
(A and B and C) or (D and E and F)
in this case, your "D" is (LOWER(b.BOMName) LIKE LOWER('%%')), which will always be true and you'll always get every record back.
I'm going to guess that you usually put something inbetween the %% - if not, take them out, or only include that portion of the query when there is a value to put in.
Chad
October 8, 2008 at 7:42 am
If you also get an execution plan, that will allow focus on the parts of the query that are causing the most problem.
First thing that jumped out at me was the use of DISTINCT. That's a pretty serious performance hit and is usually included when the query is returning incorrect data because of bad joins or bad filters or is just a habit that people have developed because of past queries with incorrect data. Do you really need it and why?
Next thing, NOLOCK hint everywhere are extremely dangerous. Everyone says you get dirty reads and they think, "Oh, I'll see "Sam" when it's supposed to be "Same" I can deal with that." But the real problem is, because of pages being rearranged during data modification, you can actually get extra copies of the same row (although the DISTINCT can help there, accidently) or way too few rows. Use that hint very sparingly and very judiciously. Actually, use ALL hints very sparingly and very judiciously.
You can actually move this:
(SELECT BOMID, MAX(BOMUpdDate) AS maxupd
FROM HeaderInfo WITH (nolock)
GROUP BY BOMID) AS bb ON h.BOMID = bb.BOMID AND h.BOMUpdDate = bb.maxupd
Up into the JOIN criteria of the HeaderInfo table and get rid of the max and group by so that it looks like this:
INNER JOIN HeaderInfo AS h
ON b.BOMID = h.BOMID
and h.BOMUpdDate = (SELECT TOP 1 h2.BOMUpdDate
FROM HeaderInfo h2
WHERE h2.BOMID = h.BOMID
ORDER BY BOMUpdDate DESC)
I've found that approach to be quite a bit faster (an article on it is coming out in the next SQL Standard)
By the way, a UNION is a distinct operation. Having the DISTINCT in each query just slows things down unnecessarily.
I'd have to see the execution plan and the structure to add to the conversation.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 8, 2008 at 8:48 am
Hi,
Thanks for the reply. I have attaches here with the execution plan, please have a look and I would appreciate your suggestions/comments.
Thanks...
RV
October 8, 2008 at 8:51 am
Thanks Chad,
I am attaching here with the execution plan of the query..... Please respond to that and let me know what can be done to fix this.
Regards,RV
October 8, 2008 at 9:37 am
In addition to the query itself, you might want to check the statistics on your indexes. I don't know how many rows the query ought to return, but the index [_dta_index_BOMDetails_38_773577794__K1_K16] on the BOMDetails table in two different seek operations is over 34% of the estimated cost of the query. The estimated row count is 1. That's a bit of disparity in the number of rows and cost, especially when you look at related sections of the plan which are all one row operations joining together on LOOP joins. I'll be the actual execution plan looks different than the estimated plan.
Which column(s) on BOMDetails is the clustered index? That table is close to half the estimated cost of the query.
Look at NodeId 70, the Loop Join. It's got an estimated 300 rows looping against an estimated 1. Normally you'd see the smaller value on the top half of that operation. Again, check the statistics, but I think you need to evaluate the database design as well as the query design. I don't see a single clustered index referenced here. Based on the index scans and table scans, you could probably benefit from a clustered index on the tables.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 8, 2008 at 3:02 pm
Any chance you could incorporate our suggestions, verify that the output is still correct, and repost an updated query?
I'm going to go with a guess that there are two sets of parenthesis missing in both WHERE clauses, and what you really wanted was WHERE (a whole bunch of stuff with ANDs) OR (a whole bunch of stuff with ANDs). If that is the case, then the whole where clause for the 2nd query could be rewritten like this (and the 1st query rewritten similarly), the differences between the two being consolidated into the single OR at the bottom:
(LOWER(b.BOMName) LIKE LOWER('%%'))
AND (LOWER(b.BOMID) LIKE LOWER('%%'))
AND (LOWER(b.QutNo) LIKE LOWER('%%'))
AND (b.SSO = 'TEST_SSO')
AND (b.CRExternal <> 5)
AND (b.CRInternal <> 5)
AND (b.BOMID NOT IN
(SELECT b.BOMID
FROM BOMDetails AS b
INNER JOIN JobAssignmentDetails AS j WITH (NOLOCK) ON b.JobID = j.JobID
WHERE (j.MemberID = 'sureshk')
)
)
AND (h.BOMUpdDate > GETDATE() - 15)
AND ((b.CreatedBy = 'sureshk') OR (b.SoldToAcc NOT IN ('0000000000'))
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply