Performance enhancement and cleaner query?

tech_1 · December 7, 2015, 7:04pm

I am trying to figure out how to improve the performance of the following complicated query. It takes around 3-5 minutes (literally) for data to come back. I am also wondering what is the best way to split out the columns/queries so they bring back the same results but in a nice manageable chunk (if possible).

With
FirstHoursWheelPosition AS
(SELECT Id, TreadId, ActivityWheelPositionId, HourMeterReading, ROW_NUMBER() OVER (PARTITION BY activityWheelPositionId
ORDER BY activitydatetime, recordcreateddatetime) AS rnfirst
FROM activity
WHERE activitywheelpositionid IS NOT NULL AND HourMeterReading IS NOT NULL),

LastHoursWheelPosition AS
(SELECT Id, TreadId, ActivityWheelPositionId, HourMeterReading, ROW_NUMBER() OVER (PARTITION BY activityWheelPositionId
ORDER BY activitydatetime DESC, recordcreateddatetime DESC) AS rnlast
FROM activity
WHERE activitywheelpositionid IS NOT NULL AND HourMeterReading IS NOT NULL),

FirstDistanceWheelPosition AS
(SELECT Id, TreadId, ActivityWheelPositionId, DistanceMeterInMIcrons, ROW_NUMBER() OVER (PARTITION BY activityWheelPositionId
ORDER BY activitydatetime, recordcreateddatetime) AS rnfirst
FROM activity
WHERE activitywheelpositionid IS NOT NULL AND DistanceMeterInMicrons IS NOT NULL),

LastDistanceWheelPosition AS
(SELECT Id, TreadId, ActivityWheelPositionId, DistanceMeterInMicrons, ROW_NUMBER() OVER (PARTITION BY activityWheelPositionId
ORDER BY activitydatetime DESC, recordcreateddatetime DESC) AS rnlast
FROM activity
WHERE activitywheelpositionid IS NOT NULL AND DistanceMeterInMicrons IS NOT NULL),

InitialTreadDepth AS
(SELECT Id, TreadId, RemainingTreadDepthInnerInMicrons, RemainingTreadDepthCenterInMicrons, RemainingTreadDepthOuterInMicrons, ROW_NUMBER()
OVER (PARTITION BY TreadId
ORDER BY activitydatetime, recordcreateddatetime) AS rnfirst
FROM activity
WHERE ((RemainingTreadDepthInnerInMicrons IS NOT NULL) OR
(RemainingTreadDepthCenterInMicrons IS NOT NULL) OR
(RemainingTreadDepthOuterInMicrons IS NOT NULL))),

LastTreadDepth AS
(SELECT Id, TreadId, RemainingTreadDepthInnerInMicrons, RemainingTreadDepthCenterInMicrons, RemainingTreadDepthOuterInMicrons, ROW_NUMBER()
OVER (PARTITION BY TreadId
ORDER BY activitydatetime DESC, recordcreateddatetime DESC) AS rnlast
FROM activity
WHERE ((RemainingTreadDepthInnerInMicrons IS NOT NULL) OR
(RemainingTreadDepthCenterInMicrons IS NOT NULL) OR
(RemainingTreadDepthOuterInMicrons IS NOT NULL))),

InitialPurchase As
(SELECT Activity.Id, Activity.TreadId,Activity.ActivityDateTime,ROW_NUMBER() OVER (PARTITION BY TreadId
ORDER BY activitydatetime , recordcreateddatetime ) AS rnfirst
FROM activity
join activitytype on activity.activitytypeid = activitytype.id and activitytype.Description = 'Purchase' ),

FirstMount As
(SELECT Id, TreadId, ActivityWheelPositionId, ActivityDateTime, ROW_NUMBER() OVER (PARTITION BY TreadId
ORDER BY activitydatetime, recordcreateddatetime) AS rnfirst
FROM activity
WHERE activitywheelpositionid IS NOT NULL ),

CurrentStatus As
(SELECT Activity.Id, TreadId,SiteHierarchyId, StatusId,Status.Description, case when Status.Description = 'In-Service' then ActivityWheelPositionId else null end as ActivityWheelPositionId,ActivityDateTime,Activity.RimId, ROW_NUMBER() OVER (PARTITION BY TreadId
ORDER BY activitydatetime desc, recordcreateddatetime desc) AS rnlast
FROM activity left join status on activity.statusid = status.id ),

LastDamage As
(SELECT Activity.Id, Activity.TreadId,Activity.ActivityDateTime,ActivityTireDamage.TireDamageId,ActivityTireDamage.TireDamageCategoryId,ActivityTireDamage.Comment, ROW_NUMBER() OVER (PARTITION BY TreadId
ORDER BY activitydatetime desc, recordcreateddatetime desc) AS rnlast
FROM activitytiredamage
join activity on activitytiredamage.activityid = activity.id ),

-- Used "+ 0" in max function to force bit to an integer so it could be used in the max function. If any repairs cause a RearOnly situation, then it should show as a rear only tire. A null value indicates no repairs
TireRepair As
(Select Activity.TreadId,max(ActivityTireRepair.CausedRearOnly + 0) as RepairLevel from Activity Join ActivityTireRepair on Activity.Id = ActivityTireRepair.ActivityId group by Activity.TreadId),

TotalCost As
(Select Activity.TreadId,Sum(ActivityTireCost.Amount) as TotalCost from Activity join ActivityTireCost on Activity.Id = ActivityTireCost.ActivityId group by Activity.TreadId)

SELECT        Tread.Id AS TreadId, Sum(LastHoursWheelPosition.HourMeterReading - FirstHoursWheelPosition.HourMeterReading) + COALESCE (Tread.UntrackedHours, 0) 
                          AS TreadTotalHours, Sum(LastDistanceWheelPosition.DistanceMeterInMicrons - FirstDistanceWheelPosition.DistanceMeterInMicrons) 
                          + COALESCE (Tread.UntrackedDistanceInMicrons, 0) AS TreadTotalDistanceInMicrons, 
                          InitialTreadDepth.RemainingTreadDepthInnerInMicrons AS InitialTreadDepthInnerInMicrons, 
                          InitialTreadDepth.RemainingTreadDepthCenterInMicrons AS InitialTreadDepthCenterInMicrons, 
                          InitialTreadDepth.RemainingTreadDepthOuterInMicrons AS InitialTreadDepthOuterInMicrons, LastTreadDepth.RemainingTreadDepthInnerInMicrons, 
                          LastTreadDepth.RemainingTreadDepthCenterInMicrons, LastTreadDepth.RemainingTreadDepthOuterInMicrons

  					  ,TotalCost.TotalCost

  					  ,InitialPurchase.ActivityDateTime as InitialPurchaseDate
  					  ,FirstMount.ActivityDateTime as FirstMountDate
  					  ,CurrentStatus.SiteHierarchyId
  					  ,SiteHierarchy.Name as LocationName
  					  ,CurrentStatus.Description as CurrentStatus
  					  ,ActivityWheelPosition.VehicleId
  					  ,Vehicle.VehicleNumber
  					  ,WheelBasePositionNaming.Name as WheelPosition
  					  ,case when not WheelPositionOrder.Description = 'Across the Axles Left to Right Starting at Left Front' then WheelBasePosition.DisplayOrderAround else WheelBasePosition.DisplayOrderAcross end as WheelPositionDisplayOrder  -- stated as a "NOT" since a rare and impossible null should be counterclockwise display as well.
  					  ,LastHoursWheelPosition.HourMeterReading
  					  ,LastDistanceWheelPosition.DistanceMeterInMicrons
  					  ,TireDamage.DamageCode
  					  ,TireDamage.Description as DamageDescription
  					  ,TireDamageLocation.Location as DamageLocation
  					  ,case when TireDamageCategoryInstance.Id is null and TireDamageCategoryAlternate.Id is null then TireDamageCategoryMain.Description else TireDamageCategoryInstance.Description end as DamageCategory
  					  ,LastDamage.Comment as DamageComment
  					  ,TireRepair.RepairLevel  --  A null value indicates no repairs, a 0 indicates some repairs, and a 1 indicates some repairs for a tire that can only be on the rear.
  					 
  					 -----
  					 ,Tread.TreadNumber				
  						,Tread.Used				
  						,Tread.PurchasedUsed				
  						,Tread.TrackHours				
  						,Tread.TrackDistance				
  						,Tread.Active				
  						,Tread.TestTire				
  						,Tread.OriginalEquipment				
  						,Tread.Comment as TreadComment				
  						,Tread.Invalid				
  						,Tread.InvalidDescription				
  						,Tread.Circumference				
  						,TDesc.SerialNumber				
  						,TDesc.BrandNumber				
  						,TDesc.ManufacturerAbbreviation				
  						,TDesc.ManufacturerName				
  						,TDesc.Pattern				
  						,TDesc.PatternAbbreviation				
  						,TDesc.TireSize				
  						,TDesc.TRADisplay				
  						,TDesc.PlyOrPlyRating				
  						,TDesc.CompoundName				
  						,TDesc.CompoundSpec2				
  						,TDesc.Article				
  						,TDesc.OriginalTreadDepthInMicrons
  						,CurrentStatus.RimId
  							
  		
  						,rtrim(				
  						case when TDesc.ManufacturerAbbreviation is null or len(TDesc.ManufacturerAbbreviation) = 0 then '' else TDesc.ManufacturerAbbreviation + ' ' end +				
  						case when TDesc.PatternAbbreviation is null or len(TDesc.PatternAbbreviation) = 0 then '' else TDesc.PatternAbbreviation + ' ' end +				
  						case when TDesc.TireSize is null or len(TDesc.TireSize) = 0 then '' else TDesc.TireSize + ' ' end +				
  						case when TDesc.PlyOrPlyRating is null or len(ltrim(rtrim(TDesc.PlyOrPlyRating))) = 0 then '' else ltrim(rtrim(TDesc.PlyOrPlyRating)) + ' ' end +				
  						case when TDesc.TRADisplay is null or len(TDesc.TRADisplay) = 0 then '' else TDesc.TRADisplay + ' ' end +				
  						case when TDesc.CompoundName is null or len(ltrim(rtrim(TDesc.CompoundName))) = 0  or TDesc.CompoundName = 'NA' or TDesc.CompoundName = 'N/A' then '' else ltrim(rtrim(TDesc.CompoundName)) + ' ' end +				
  						case when TDesc.CompoundSpec2 is null or len(ltrim(rtrim(TDesc.CompoundSpec2))) = 0 or TDesc.CompoundSpec2 = 'NA' or TDesc.CompoundSpec2 = 'N/A' then '' else ltrim(rtrim(TDesc.CompoundSpec2)) + ' ' end +				
  						case when Tread.TreadNumber is null or Tread.TreadNumber = 0 then '' else 'Retread ' + cast(Tread.TreadNumber as nvarchar) End				
  						)				
  						as CompactTireDescription

  					 -----

 FROM            Tread 
Left OUter Join vwTreadDescription TDesc on Tread.Id = TDesc.TreadId

LEFT OUTER JOIN LastHoursWheelPosition ON Tread.Id = LastHoursWheelPosition.TreadId

LEFT OUTER JOIN FirstHoursWheelPosition ON LastHoursWheelPosition.ActivityWheelPositionId = FirstHoursWheelPosition.ActivityWheelPositionId AND
FirstHoursWheelPosition.rnfirst = 1

LEFT OUTER JOIN LastDistanceWheelPosition ON Tread.Id = LastDistanceWheelPosition.TreadId

LEFT OUTER JOIN FirstDistanceWheelPosition ON LastDistanceWheelPosition.ActivityWheelPositionId = FirstDistanceWheelPosition.ActivityWheelPositionId AND
FirstDistanceWheelPosition.rnfirst = 1

LEFT OUTER JOIN InitialTreadDepth ON Tread.Id = InitialTreadDepth.TreadId and InitialTreadDepth.rnfirst = 1

LEFT OUTER JOIN LastTreadDepth ON Tread.Id = LastTreadDepth.TreadId and LastTreadDepth.rnlast = 1

Left Outer Join InitialPurchase on Tread.Id = InitialPurchase.TreadId and InitialPurchase.rnfirst = 1

Left Outer Join FirstMount on Tread.Id = FirstMount.TreadId and FirstMount.rnfirst = 1

Left Outer Join CurrentStatus on Tread.Id = CurrentStatus.TreadId and CurrentStatus.rnlast = 1
Left Outer Join SiteHierarchy on CurrentStatus.SiteHierarchyId = SiteHierarchy.Id
left outer join SiteHierarchyPreference on SiteHierarchy.Id = SiteHierarchyPreference.SiteHierarchyId
Left Outer Join ActivityWheelPosition on CurrentStatus.ActivityWheelPositionId = ActivityWheelPosition.Id
Left outer join Vehicle on ActivityWheelPosition.VehicleId = Vehicle.Id
Left outer join WheelBasePosition on ActivityWheelPosition.WheelBasePositionId = WheelBasePosition.Id
Left Outer Join WheelBasePositionNaming on WheelBasePosition.Id = WheelBasePositionNaming.WheelBasePositionId and SiteHierarchyPreference.WheelPositionNamingId = WheelBasePositionNaming.WheelPositionNamingId

Left outer Join WheelPositionOrder on SiteHierarchyPreference.WheelPositionOrderId = WheelPositionOrder.Id

Left Outer Join LastDamage on Tread.Id = LastDamage.TreadId and LastDamage.rnlast = 1
Left OUter Join TireDamage on LastDamage.TireDamageId = TireDamage.Id
Left outer join TireDamageLocation on TireDamage.TireDamageLocationId = TireDamageLocation.Id
Left outer join TireDamageCategory TireDamageCategoryInstance on LastDamage.TireDamageCategoryId = TireDamageCategoryInstance.Id
Left outer join TireDamageCategory TireDamageCategoryMain on TireDamage.TireDamageCategoryId = TireDamageCategoryMain.Id
Left outer join TireDamageCategory TireDamageCategoryAlternate on TireDamage.AlternateTireDamageCategoryId = TireDamageCategoryAlternate.Id

Left Outer Join TireRepair on Tread.Id = TireRepair.TreadId

Left outer join TotalCost on Tread.Id = TotalCost.TreadId

 WHERE 
     (LastHoursWheelPosition.rnlast IS NULL OR LastHoursWheelPosition.rnlast = 1) 
  	AND   (LastDistanceWheelPosition.rnlast IS NULL OR LastDistanceWheelPosition.rnlast = 1)
  	AND   (LastTreadDepth.rnlast IS NULL OR LastTreadDepth.rnlast = 1)

  	AND   (InitialTreadDepth.rnfirst IS NULL or InitialTreadDepth.rnfirst = 1) 
  	and   (InitialPurchase.rnfirst is null or InitialPurchase.rnfirst = 1)
  	and   (FirstMount.rnfirst is null or FirstMount.rnfirst = 1)
  	and   (CurrentStatus.rnlast is null or CurrentStatus.rnlast = 1)
  	and   (LastDamage.rnlast is null or LastDamage.rnlast = 1)

  	-- The next 6 OR statements make it so that the data don't show up unless there is at least one record among these
  	AND   (
  		  (LastHoursWheelPosition.Id IS NOT NULL) 
  		  OR
              (LastDistanceWheelPosition.Id IS NOT NULL)
  		  OR
              (LastTreadDepth.Id IS NOT NULL)
  		  OR
  		  (InitialPurchase.Id IS NOT NULL)
  		  OR
  		  (FirstMount.Id IS NOT NULL)
  		  OR 
  		  (CurrentStatus.Id IS NOT NULL)
  		  OR 
  		  (LastDamage.Id IS NOT NULL)
  		  )

 GROUP BY Tread.Id, InitialTreadDepth.RemainingTreadDepthInnerInMicrons, InitialTreadDepth.RemainingTreadDepthCenterInMicrons, 
                          InitialTreadDepth.RemainingTreadDepthOuterInMicrons, LastTreadDepth.RemainingTreadDepthInnerInMicrons, 
                          LastTreadDepth.RemainingTreadDepthCenterInMicrons, LastTreadDepth.RemainingTreadDepthOuterInMicrons, Tread.UntrackedHours, 
                          Tread.UntrackedDistanceInMicrons

  					  ,TotalCost.TotalCost

  					  ,InitialPurchase.ActivityDateTime
  					  ,FirstMount.ActivityDateTime
  					  ,CurrentStatus.SiteHierarchyId
  					  ,SiteHierarchy.Name
  					  ,CurrentStatus.Description
  					  ,ActivityWheelPosition.VehicleId
  					  ,Vehicle.VehicleNumber
  					  ,WheelBasePositionNaming.Name
  					  ,case when not WheelPositionOrder.Description = 'Across the Axles Left to Right Starting at Left Front' then WheelBasePosition.DisplayOrderAround else WheelBasePosition.DisplayOrderAcross end
  					  ,LastHoursWheelPosition.HourMeterReading
  					  ,LastDistanceWheelPosition.DistanceMeterInMicrons
  					  ,TireDamage.DamageCode
  					  ,TireDamage.Description
  					  ,TireDamageLocation.Location
  					  ,case when TireDamageCategoryInstance.Id is null and TireDamageCategoryAlternate.Id is null then TireDamageCategoryMain.Description else TireDamageCategoryInstance.Description end
  					  ,LastDamage.Comment
  					  ,TireRepair.RepairLevel
  					  
  				   ,Tread.TreadNumber				
  						,Tread.Used				
  						,Tread.PurchasedUsed				
  						,Tread.TrackHours				
  						,Tread.TrackDistance				
  						,Tread.Active				
  						,Tread.TestTire				
  						,Tread.OriginalEquipment				
  						,Tread.Comment				
  						,Tread.Invalid				
  						,Tread.InvalidDescription				
  						,Tread.Circumference				
  						,TDesc.SerialNumber				
  						,TDesc.BrandNumber				
  						,TDesc.ManufacturerAbbreviation				
  						,TDesc.ManufacturerName				
  						,TDesc.Pattern				
  						,TDesc.PatternAbbreviation				
  						,TDesc.TireSize				
  						,TDesc.TRADisplay				
  						,TDesc.PlyOrPlyRating				
  						,TDesc.CompoundName				
  						,TDesc.CompoundSpec2				
  						,TDesc.Article				
  						,TDesc.OriginalTreadDepthInMicrons
  						,CurrentStatus.RimId

GO

TaraKizer · December 7, 2015, 7:44pm

I would start by checking where the problem is. Add SET STATISTICS IO ON above the query and then run the whole script. Post the output of the stats io here so that we can help analyze it.

tech_1 · December 7, 2015, 8:28pm

Thanks. I am using MS Azure too and with that i get the 3-5 min query execution.
Here is the output:

Warning: Null value is eliminated by an aggregate or other SET operation.

(2581 row(s) affected)
Table 'Worktable'. Scan count 20, logical reads 199, physical reads 0, read-ahead reads 19399, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Workfile'. Scan count 162, logical reads 19088, physical reads 721, read-ahead reads 18367, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Activity'. Scan count 11, logical reads 6798, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Tread'. Scan count 2, logical reads 28672, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Casing'. Scan count 1, logical reads 16194, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TireSpec'. Scan count 1, logical reads 125, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TreadDesign'. Scan count 2, logical reads 32, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Manufacturer'. Scan count 2, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TRA'. Scan count 2, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TireSize'. Scan count 2, logical reads 10, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Ply'. Scan count 2, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Compound'. Scan count 2, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TireSpecForReview'. Scan count 1, logical reads 3729, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ActivityType'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ActivityTireDamage'. Scan count 1, logical reads 21, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Status'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SiteHierarchy'. Scan count 1, logical reads 257, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SiteHierarchyPreference'. Scan count 1, logical reads 210, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'WheelPositionOrder'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Vehicle'. Scan count 1, logical reads 997, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ActivityWheelPosition'. Scan count 1, logical reads 27, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'WheelBasePosition'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'WheelBasePositionNaming'. Scan count 1, logical reads 15, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TireDamageCategory'. Scan count 3, logical reads 305, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TireDamageLocation'. Scan count 1, logical reads 152, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TireDamage'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ActivityTireRepair'. Scan count 1, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ActivityTireCost'. Scan count 1, logical reads 32, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

TaraKizer · December 7, 2015, 10:16pm

I would start by examining the Tread and Casing tables. You might be able to add indexes to get those reads down. The reads don't look too unreasonable though, so you could be right that your query could benefit from a refactor. I don't have any input on the code though.

ScottPletcher · December 7, 2015, 10:49pm

That GROUP BY is horrendously huge and will destroy your performance. If at all possible, get the totals first using only the specific tables required to sum them, and then join that derived table to the other tables.

If that doesn't help performance enough, further re-work will be more effort.

tech_1 · December 8, 2015, 2:25am

Thank you all.
ScottPletcher: can you kindly guide to what you are saying about joining the derived table to the other tables after getting the totals first? What do you mean here?

ScottPletcher · December 8, 2015, 5:22pm

The CTEs aren't affected, so I'll just post the re-coded main SELECT:

SELECT        Tread.Id AS TreadId, 
                          HoursWheelPosition_Total.HourMeterReading_Total + COALESCE (Tread.UntrackedHours, 0) AS TreadTotalHours, 
                          DistanceWheelPosition_Total.TreadTotalDistanceInMicrons_Total + COALESCE (Tread.UntrackedDistanceInMicrons, 0)
                              AS TreadTotalDistanceInMicrons,
                          InitialTreadDepth.RemainingTreadDepthInnerInMicrons AS InitialTreadDepthInnerInMicrons, 
                          InitialTreadDepth.RemainingTreadDepthCenterInMicrons AS InitialTreadDepthCenterInMicrons, 
                          InitialTreadDepth.RemainingTreadDepthOuterInMicrons AS InitialTreadDepthOuterInMicrons, LastTreadDepth.RemainingTreadDepthInnerInMicrons, 
                          LastTreadDepth.RemainingTreadDepthCenterInMicrons, LastTreadDepth.RemainingTreadDepthOuterInMicrons

						  ,TotalCost.TotalCost

						  ,InitialPurchase.ActivityDateTime as InitialPurchaseDate
						  ,FirstMount.ActivityDateTime as FirstMountDate
						  ,CurrentStatus.SiteHierarchyId
						  ,SiteHierarchy.Name as LocationName
						  ,CurrentStatus.Description as CurrentStatus
						  ,ActivityWheelPosition.VehicleId
						  ,Vehicle.VehicleNumber
						  ,WheelBasePositionNaming.Name as WheelPosition
						  ,case when not WheelPositionOrder.Description = 'Across the Axles Left to Right Starting at Left Front' then WheelBasePosition.DisplayOrderAround else WheelBasePosition.DisplayOrderAcross end as WheelPositionDisplayOrder  -- stated as a "NOT" since a rare and impossible null should be counterclockwise display as well.
						  ,LastHoursWheelPosition.HourMeterReading
						  ,LastDistanceWheelPosition.DistanceMeterInMicrons
						  ,TireDamage.DamageCode
						  ,TireDamage.Description as DamageDescription
						  ,TireDamageLocation.Location as DamageLocation
						  ,case when TireDamageCategoryInstance.Id is null and TireDamageCategoryAlternate.Id is null then TireDamageCategoryMain.Description else TireDamageCategoryInstance.Description end as DamageCategory
						  ,LastDamage.Comment as DamageComment
						  ,TireRepair.RepairLevel  --  A null value indicates no repairs, a 0 indicates some repairs, and a 1 indicates some repairs for a tire that can only be on the rear.

						 -----
						 ,Tread.TreadNumber				
							,Tread.Used				
							,Tread.PurchasedUsed				
							,Tread.TrackHours				
							,Tread.TrackDistance				
							,Tread.Active				
							,Tread.TestTire				
							,Tread.OriginalEquipment				
							,Tread.Comment as TreadComment				
							,Tread.Invalid				
							,Tread.InvalidDescription				
							,Tread.Circumference				
							,TDesc.SerialNumber				
							,TDesc.BrandNumber				
							,TDesc.ManufacturerAbbreviation				
							,TDesc.ManufacturerName				
							,TDesc.Pattern				
							,TDesc.PatternAbbreviation				
							,TDesc.TireSize				
							,TDesc.TRADisplay				
							,TDesc.PlyOrPlyRating				
							,TDesc.CompoundName				
							,TDesc.CompoundSpec2				
							,TDesc.Article				
							,TDesc.OriginalTreadDepthInMicrons
							,CurrentStatus.RimId


							,rtrim(				
							case when TDesc.ManufacturerAbbreviation is null or len(TDesc.ManufacturerAbbreviation) = 0 then '' else TDesc.ManufacturerAbbreviation + ' ' end +				
							case when TDesc.PatternAbbreviation is null or len(TDesc.PatternAbbreviation) = 0 then '' else TDesc.PatternAbbreviation + ' ' end +				
							case when TDesc.TireSize is null or len(TDesc.TireSize) = 0 then '' else TDesc.TireSize + ' ' end +				
							case when TDesc.PlyOrPlyRating is null or len(ltrim(rtrim(TDesc.PlyOrPlyRating))) = 0 then '' else ltrim(rtrim(TDesc.PlyOrPlyRating)) + ' ' end +				
							case when TDesc.TRADisplay is null or len(TDesc.TRADisplay) = 0 then '' else TDesc.TRADisplay + ' ' end +				
							case when TDesc.CompoundName is null or len(ltrim(rtrim(TDesc.CompoundName))) = 0  or TDesc.CompoundName = 'NA' or TDesc.CompoundName = 'N/A' then '' else ltrim(rtrim(TDesc.CompoundName)) + ' ' end +				
							case when TDesc.CompoundSpec2 is null or len(ltrim(rtrim(TDesc.CompoundSpec2))) = 0 or TDesc.CompoundSpec2 = 'NA' or TDesc.CompoundSpec2 = 'N/A' then '' else ltrim(rtrim(TDesc.CompoundSpec2)) + ' ' end +				
							case when Tread.TreadNumber is null or Tread.TreadNumber = 0 then '' else 'Retread ' + cast(Tread.TreadNumber as nvarchar) End				
							)				
							as CompactTireDescription				

						 -----

 FROM            Tread 
 
 LEFT OUTER JOIN (
         SELECT lhwp.TreadID, SUM(lhwp.HourMeterReading - COALESCE( fhwp.HourMeterReading, 0 )) AS HourMeterReading_Total
         FROM LastHoursWheelPosition lhwp
         LEFT OUTER JOIN FirstHoursWheelPosition fhwp ON lhwp.ActivityWheelPositionId = fhwp.ActivityWheelPositionId AND fhwp.rnfirst = 1
         WHERE lhwp.rnlast = 1
         GROUP BY lhwp.TreadID
 ) AS HoursWheelPosition_Total ON HoursWheelPosition_Total.TreadID = Tread.Id
 
 LEFT OUTER JOIN (
         SELECT ldwp.TreadID, SUM(ldwp.DistanceMeterInMicrons - COALESCE( fdwp.DistanceMeterInMicrons, 0 )) AS TreadTotalDistanceInMicrons_Total
         FROM LastDistanceWheelPosition ldwp
         LEFT OUTER JOIN FirstDistanceWheelPosition fdwp ON ldwp.ActivityWheelPositionId = fdwp.ActivityWheelPositionId AND fdwp.rnfirst = 1
         WHERE ldwp.rnlast = 1
         GROUP BY ldwp.TreadID
 ) AS DistanceWheelPosition_Total ON DistanceWheelPosition_Total.TreadID = Tread.Id
 
 LEFT OUTER JOIN vwTreadDescription TDesc on Tread.Id = TDesc.TreadId

 LEFT OUTER JOIN LastHoursWheelPosition ON Tread.Id = LastHoursWheelPosition.TreadId

 LEFT OUTER JOIN FirstHoursWheelPosition ON LastHoursWheelPosition.ActivityWheelPositionId = FirstHoursWheelPosition.ActivityWheelPositionId AND 
                          FirstHoursWheelPosition.rnfirst = 1 

 LEFT OUTER JOIN LastDistanceWheelPosition ON Tread.Id = LastDistanceWheelPosition.TreadId 

 LEFT OUTER JOIN FirstDistanceWheelPosition ON LastDistanceWheelPosition.ActivityWheelPositionId = FirstDistanceWheelPosition.ActivityWheelPositionId AND 
                          FirstDistanceWheelPosition.rnfirst = 1

 LEFT OUTER JOIN InitialTreadDepth ON Tread.Id = InitialTreadDepth.TreadId and InitialTreadDepth.rnfirst = 1

 LEFT OUTER JOIN LastTreadDepth ON Tread.Id = LastTreadDepth.TreadId and LastTreadDepth.rnlast = 1

 Left Outer Join InitialPurchase on Tread.Id = InitialPurchase.TreadId and InitialPurchase.rnfirst = 1

 Left Outer Join FirstMount on Tread.Id = FirstMount.TreadId and FirstMount.rnfirst = 1

 Left Outer Join CurrentStatus on Tread.Id = CurrentStatus.TreadId and CurrentStatus.rnlast = 1
 Left Outer Join SiteHierarchy on CurrentStatus.SiteHierarchyId = SiteHierarchy.Id
 left outer join SiteHierarchyPreference on SiteHierarchy.Id = SiteHierarchyPreference.SiteHierarchyId
 Left Outer Join ActivityWheelPosition on CurrentStatus.ActivityWheelPositionId = ActivityWheelPosition.Id
 Left outer join Vehicle on ActivityWheelPosition.VehicleId = Vehicle.Id
 Left outer join WheelBasePosition on ActivityWheelPosition.WheelBasePositionId = WheelBasePosition.Id
 Left Outer Join WheelBasePositionNaming on WheelBasePosition.Id = WheelBasePositionNaming.WheelBasePositionId and SiteHierarchyPreference.WheelPositionNamingId = WheelBasePositionNaming.WheelPositionNamingId

 Left outer Join WheelPositionOrder on SiteHierarchyPreference.WheelPositionOrderId = WheelPositionOrder.Id


 Left Outer Join LastDamage on Tread.Id = LastDamage.TreadId and LastDamage.rnlast = 1
 Left OUter Join TireDamage on LastDamage.TireDamageId = TireDamage.Id
 Left outer join TireDamageLocation on TireDamage.TireDamageLocationId = TireDamageLocation.Id
 Left outer join TireDamageCategory TireDamageCategoryInstance on LastDamage.TireDamageCategoryId = TireDamageCategoryInstance.Id
 Left outer join TireDamageCategory TireDamageCategoryMain on TireDamage.TireDamageCategoryId = TireDamageCategoryMain.Id
 Left outer join TireDamageCategory TireDamageCategoryAlternate on TireDamage.AlternateTireDamageCategoryId = TireDamageCategoryAlternate.Id 

 Left Outer Join TireRepair on Tread.Id = TireRepair.TreadId

 Left outer join TotalCost on Tread.Id = TotalCost.TreadId

 WHERE 
       (LastHoursWheelPosition.rnlast IS NULL OR LastHoursWheelPosition.rnlast = 1) 
		AND   (LastDistanceWheelPosition.rnlast IS NULL OR LastDistanceWheelPosition.rnlast = 1)
		AND   (LastTreadDepth.rnlast IS NULL OR LastTreadDepth.rnlast = 1)

		AND   (InitialTreadDepth.rnfirst IS NULL or InitialTreadDepth.rnfirst = 1) 
		and   (InitialPurchase.rnfirst is null or InitialPurchase.rnfirst = 1)
		and   (FirstMount.rnfirst is null or FirstMount.rnfirst = 1)
		and   (CurrentStatus.rnlast is null or CurrentStatus.rnlast = 1)
		and   (LastDamage.rnlast is null or LastDamage.rnlast = 1)

		-- The next 6 OR statements make it so that the data don't show up unless there is at least one record among these
		AND   (
			  (LastHoursWheelPosition.Id IS NOT NULL) 
			  OR
              (LastDistanceWheelPosition.Id IS NOT NULL)
			  OR
              (LastTreadDepth.Id IS NOT NULL)
			  OR
			  (InitialPurchase.Id IS NOT NULL)
			  OR
			  (FirstMount.Id IS NOT NULL)
			  OR 
			  (CurrentStatus.Id IS NOT NULL)
			  OR 
			  (LastDamage.Id IS NOT NULL)
			  )

tech_1 · December 9, 2015, 3:13pm

Thanks Scott. Unfortunately it does not execute due to binding errors, since previously in the original query it was referencing the sub queries/outer queries but with your refactor the no longer exist so things like LastHoursWheelPosition.HourMeterReading refrains the query from executing.

will see if I can sort these errors out.

ScottPletcher · December 9, 2015, 4:25pm

No, sorry, you still need to include ALL the CTEs in your query -- the WITH ... up until the SELECT -- I just didn't re-post them all since I didn't change that part, and because a filter where I work things virtually every CTE I try to post is somehow "sql injection" and rejects it.

So, include everything from your original query from:
With
FirstHoursWheelPosition AS
up to the SELECT statement itself. Then, remove the original SELECT statement and substitute mine. We may see total differences because of joins in the original SELECT causing extra rows that are totalled, but we can deal with that if/when it happens.