IMprove performance

Question

IMprove performance

ramrajan

SSCommitted

Points: 1737
More actions
February 11, 2015 at 7:18 am

#314572

The below query taking very long time to up few records also,
Can anyone please rewrite this query for good performance with INNER joins.
SELECT
t.*
FROM inventory AS t
INNER JOIN dbo.Sub AS ss ON ss.SubsystemID = t.ComponentID
INNER JOIN dbo.const AS cs ON ss.ParentID = cs.SystemID
AND ss.ParentLevelType = 7
LEFT JOIN (dbo.loc AS sl
INNER JOIN dbo.locmaster AS fm6 ON sl.FacilityId = fm6.FacilityID)
ON cs.ParentLevelID = 6
AND cs.ParentID = sl.SubLocationId
LEFT JOIN dbo.locmaster AS fm5 ON cs.ParentLevelID = 5
AND cs.ParentID = fm5.FacilityID

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Alan Burstein SSC Guru Points: 61141 More actions · Answer 1

Note the article in my signature about best practices on getting help. We would need some DDL to help you out. If you could attach the execution plan that would be good too.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Table definitions, index definitions and execution plan please.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

ramrajan (2/11/2015)
The below query taking very long time to up few records also,
Can anyone please rewrite this query for good performance with INNER joins.
SELECT
t.*
FROM inventory AS t
INNER JOIN dbo.Sub AS ss ON ss.SubsystemID = t.ComponentID
INNER JOIN dbo.const AS cs ON ss.ParentID = cs.SystemID
AND ss.ParentLevelType = 7
LEFT JOIN (dbo.loc AS sl
INNER JOIN dbo.locmaster AS fm6 ON sl.FacilityId = fm6.FacilityID)
ON cs.ParentLevelID = 6
AND cs.ParentID = sl.SubLocationId
LEFT JOIN dbo.locmaster AS fm5 ON cs.ParentLevelID = 5
AND cs.ParentID = fm5.FacilityID

This looks like a perfectly good query with a well-designed nested join. Whoever coded it knew what they were doing. Changing the join types is unlikely to have a dramatic effect on performance, but will almost certainly break the query. What's more important to you, correct results or a small performance improvement?

If you want to maintain the accuracy of the query whilst improving the performance, then post the actual execution plan as a .sqlplan attachment.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden