3

I ran the same query on the same workstation against two different databases that yielded completely different execution plans. The structure and indexes are the same. The only difference is the data. The strange thing is the data in “database1” is much less than the data in “database2” and the performance is worse (database2 takes ½ the time as database1). You’ll notice an obvious design flaw in the ItemProperties table and we plan on changing the structure of this poorly designed table, however before that takes place I’m looking for optimization ideas. Here is the info for both databases.

Database1: [Actual Execution Plan] https://www.brentozar.com/pastetheplan/?id=HyVv5-PE-

Database2: [Actual Execution Plan] https://www.brentozar.com/pastetheplan/?id=rJrzjbDVW

  • 2
    Did you check if statistics are up to date on both tables? DBCC SHOW_STATISTICS(N'TableName','IndexName') . – S4V1N Jul 3 '17 at 13:35
  • 3
    In general, just having different data and therefore, different correct "statistics" - can produce a different execution plan. That's what statistics are for. So having wrong statistics - left out of date - may cause a bad execution plan. Such as when in fact the query is investigatin the latest data added, which is what isn't in the statistics that are stale. – Robert Carnegie Jul 3 '17 at 20:58
  • 1
    Can you add a tag for what database/version you are using? Such as [sql-server-2012] I assume it is sql-server database based on the explain plans. The [sql] tag is for Structured Query Language questions so that can remain. – Mark Stewart Jul 6 '17 at 18:54
2

Differences noticed:

  • The slow plan uses version 120 cardinality estimator
  • The fast plan uses version 70 cardinality estimator

You could try using query trace flag 9481 to force use of version 70 - just append OPTION (QUERYTRACEON 9481).

Next, the ItemProperties table is handled differently by both cases. The fast plan assumes that there will be at most one property in each join, while the slow plan assumes that there will be two. Assuming that there is in fact only one property value for all cases in question, you could replace the left joins with inner queries. This should result in identical plans on both servers.

EDIT Another optimization opportunity involves using PIVOT operator to fetch all properties at once, like so:

SELECT
vw_Items.ID
,vw_Items.ResolvedEntityID
,vw_Items.ResolvedDescription
,vw_Items.ItemProductNumber
,vw_Items.ItemDescription
,vw_Items.FilteringType
,vw_Items.ItemCategory
,vw_Items.IsDefault
,vw_Items.IsInventoryType
,vw_Items.Active
,vw_Items.PlantFloorVisible
,[dbo].f_ParseDistanceValue([Length]) AS 'Length'
,[dbo].f_ParseDistanceValue(Width) AS Width
,ISNULL(MaterialDescription.[Description], '(none)') AS 'Material'
,ISNULL(GradeDescription.[Description], '(none)') AS 'Grade'
,ISNULL(MillingDescription.[Description], '(none)') AS 'Milling'
,ISNULL(StateDescription.[Description], '(none)') AS 'State'
FROM
vw_Items
-- Join to get properties
left join (
    select [ItemEntityID],[Property],[Value] from [dbo].[ItemProperties]) p
    PIVOT (MAX(VALUE) for [Property] in (Material,Grade,Milling,State,Length,Width))properties on vw_Items.[EntityID] = properties.[ItemEntityID]
LEFT JOIN DescriptionEntity  AS MaterialDescription ON properties.Material = MaterialDescription.EntityID
LEFT JOIN DescriptionEntity  AS GradeDescription ON properties.Grade = GradeDescription.EntityID
LEFT JOIN DescriptionEntity  AS MillingDescription ON properties.Milling = MillingDescription.EntityID
LEFT JOIN #DescriptionEntity  AS StateDescription ON properties.[State] = StateDescription.EntityID

With PIVOT the server will access the ItemProperties table only once, which will likely result in reduced IO.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.