Simplify a complex query
2014-06-09 09:21:49
I need to get my head round this query. I need to amend it but would like to simplify it first:
Select Case len(snxxxxxxxx) When 1 Then '000' + snxxxxxxxx When 2 Then '00' + snxxxxxxxx When 3 Then '0' + snxxxxxxxx Else snxxxxxxxx End As snxxxxxxxx, mfpidxxxxxxxx, FPIDxxxxxxxxx, MFPSxxxxxxxx, ANxxxxxxx, ADxxxxxxxxxxx, ASDxxxxhxhxxxx, ASNxxxolnsdf, Case len(Cast(APxxxxlkjbluhvkugvy As Varchar(3))) When 1 Then '00' + Cast(APxxxxlkjbluhvkugvy As Varchar(3)) When 2 Then '0' + Cast(APxxxxlkjbluhvkugvy As Varchar(3)) Else Cast(APxxxxlkjbluhvkugvy As Varchar(3)) End As APxxxxlkjbluhvkugvy, AIIDxxxxxlkhluyfv, PBIDxxxxxkjbsjfsdhbd, PPIDxxxx;jobsdlugvsjhvbs, NOPxxxxjksvulhvsdlhvbs, ISxxxxjhasgvukgvsjhvbshk, MAIDxxxklbsuvshvbsljkn, VPOIDxxxxljhbsulhvsjksbls, GOLTIDxxxxlknkljbnjkvhhf, LTSxxxjhgcaycsjhskljhliygv, POIGIDxxxxjlhvbsjlhvbsuhvsssss, POGSxxxxkhbshgcshgvsjhvbskbjs, GPOIDxxxxxjhvasysykgcskgvsykgs, PLxxxxjhgvbasjvsjgvsjvs, SLxxxxrfgycashcshgvshgvjhsg, SSLxxxxlihbdluhvbdlhvbdlhbd, SELxxxxtyackkuvsykgcskjm, IPXxxxxyfgcyyywgijsnlkjnsksj, IPYxxxxycfsghsgysysgbsjsjsklsm, COISNxxxxyhsgshsiosksl;sjshskssskugvxfjc AS MSxxxxjhsgvshsuksoihstrs FROM ( Select snxxxxxxxx,mfpidxxxxxxxx,FPIDxxxxxxxxx,MFPSxxxxxxxx,ADxxxxxxxxxxx,ASDxxxxhxhxxxx,ANxxxxxxx,ASNxxxolnsdf,Row_Number () Over( Partition By snxxxxxxxx,FPIDxxxxxxxxx,ANxxxxxxx,ASNxxxolnsdf order by snxxxxxxxx,FPIDxxxxxxxxx,ANxxxxxxx,ASNxxxolnsdf,MerchAreaINxxxxihsbkuhsgvysdjsuihsgvblihb ASC,COISNxxxxyhsgshsiosksl;sjshskssskugvxfjc ASC) As APxxxxlkjbluhvkugvy,AIIDxxxxxlkhluyfv,PBIDxxxxxkjbsjfsdhbd,PPIDxxxx;jobsdlugvsjhvbs, NOPxxxxjksvulhvsdlhvbs,ISxxxxjhasgvukgvsjhvbshk, MAIDxxxklbsuvshvbsljkn,VPOIDxxxxljhbsulhvsjksbls,GOLTIDxxxxlknkljbnjkvhhf,LTSxxxjhgcaycsjhskljhliygv,POIGIDxxxxjlhvbsjlhvbsuhvsssss,POGSxxxxkhbshgcshgvsjhvbskbjs,COISNxxxxyhsgshsiosksl;sjshskssskugvxfjc, GPOIDxxxxxjhvasysykgcskgvsykgs,PLxxxxjhgvbasjvsjgvsjvs,SLxxxxrfgycashcshgvshgvjhsg,SSLxxxxlihbdluhvbdlhvbdlhbd,SELxxxxtyackkuvsykgcskjm,IPXxxxxyfgcyyywgijsnlkjnsksj,IPYxxxxycfsghsgysysgbsjsjsklsm FROM ( SELECT snxxxxxxxx,FPIDxxxxxxxxx,mfpidxxxxxxxx,MFPSxxxxxxxx,ANxxxxxxx,ADxxxxxxxxxxx,ASDxxxxhxhxxxx, CASE ASNxxxolnsdf WHEN 999 THEN CASE ASDxxxxhxhxxxx WHEN 'NO AISLE' THEN 1 WHEN 'NO FIXTURE' THEN 2 ELSE 2 END ELSE ASNxxxolnsdf END AS ASNxxxolnsdf, APxxxxlkjbluhvkugvy,MerchAreaINxxxxihsbkuhsgvysdjsuihsgvblihb,MAIDxxxklbsuvshvbsljkn,COISNxxxxyhsgshsiosksl;sjshskssskugvxfjc,VPOIDxxxxljhbsulhvsjksbls,GOLTIDxxxxlknkljbnjkvhhf,LTSxxxjhgcaycsjhskljhliygv,POIGIDxxxxjlhvbsjlhvbsuhvsssss,POGSxxxxkhbshgcshgvsjhvbskbjs, GPOIDxxxxxjhvasysykgcskgvsykgs,PLxxxxjhgvbasjvsjgvsjvs,SLxxxxrfgycashcshgvshgvjhsg,SSLxxxxlihbdluhvbdlhvbdlhbd,SELxxxxtyackkuvsykgcskjm,IPXxxxxyfgcyyywgijsnlkjnsksj,IPYxxxxycfsghsgysysgbsjsjsklsm,PPIDxxxx;jobsdlugvsjhvbs, PBIDxxxxxkjbsjfsdhbd, ISNULL(NOPxxxxjksvulhvsdlhvbs,1) AS NOPxxxxjksvulhvsdlhvbs,ISNULL(ISxxxxjhasgvukgvsjhvbshk,0) AS ISxxxxjhasgvukgvsjhvbshk,AIIDxxxxxlkhluyfv FROM ( SELECT dbo.tblStore.SCxxxxrtfdctfgghghhhh AS snxxxxxxxx, dbo.tblFloorPlan.FPIDxxxxxxxxx, dbo.tblMasterFloorPlan.mfpidxxxxxxxx, dbo.tblMasterFloorPlan.MFPSxxxxxxxxID, dbo.tblStatus.Status AS MFPSxxxxxxxx, ISNULL(dbo.tblAisleInstance.ANxxxxxxx, 999) AS ANxxxxxxx, LTRIM(RTRIM(ISNULL(dbo.tblAisleInstance.Description, 'NA'))) AS ADxxxxxxxxxxx, CASE LTRIM(RTRIM(ISNULL(dbo.tblAisleInstance.ASDxxxxhxhxxxx, ''))) WHEN '' THEN CASE WHEN FixtureCount = 0 OR FixtureCount IS NULL THEN 'NO FIXTURE' WHEN FixtureCount > 0 THEN 'NO AISLE' END ELSE dbo.tblAisleInstance.ASDxxxxhxhxxxx END AS ASDxxxxhxhxxxx, ISNULL(dbo.tblAisleInstance.ASNxxxolnsdf, 999) AS ASNxxxolnsdf, dbo.tblAisleInstance.AIIDxxxxxlkhluyfv AS AIIDxxxxxlkhluyfv, dbo.tblMerchandisableAreaPosition.MAPDxxxgshsilskosyhsfsjk AS APxxxxlkjbluhvkugvy, dbo.tblVaultMerchandisableArea.INxxxxihsbkuhsgvysdjsuihsgvblihb AS MerchAreaINxxxxihsbkuhsgvysdjsuihsgvblihb, dbo.tblVaultMerchandisableArea.VaultMAIDxxxklbsuvshvbsljkn AS MAIDxxxklbsuvshvbsljkn, dbo.tblVaultMerchandisableAreaDetail.COISNxxxxyhsgshsiosksl;sjshskssskugvxfjc, dbo.tblVaultPlanObject.VPOIDxxxxljhbsulhvsjksbls, CASE WHEN dbo.tblVaultPlanObject.GOLTIDxxxxlknkljbnjkvhhf IS NULL THEN MissingLifeTimes.MissingGOLTIDxxxxlknkljbnjkvhhf WHEN dbo.tblVaultPlanObject.GOLTIDxxxxlknkljbnjkvhhf = 0 THEN MissingLifeTimes.MissingGOLTIDxxxxlknkljbnjkvhhf ELSE dbo.tblVaultPlanObject.GOLTIDxxxxlknkljbnjkvhhf END AS GOLTIDxxxxlknkljbnjkvhhf, CASE WHEN tblLTSxxxjhgcaycsjhskljhliygv.STATUS IS NULL THEN MissingLifeTimes.MissingLTSxxxjhgcaycsjhskljhliygv ELSE tblLTSxxxjhgcaycsjhskljhliygv.STATUS END AS LTSxxxjhgcaycsjhskljhliygv, dbo.tblVaultPlanObject.POIGIDxxxxjlhvbsjlhvbsuhvsssss, dbo.tblVaultPlanObject.POGSxxxxkhbshgcshgvsjhvbskbjs, dbo.tblGlobalPlanObject.GPOIDxxxxxjhvasysykgcskgvsykgs, dbo.tblVaultPlanObject.ActualLength / 2.54 AS PLxxxxjhgvbasjvsjgvsjvs, dbo.tblVaultPlanObject.SLxxxxrfgycashcshgvshgvjhsg / 2.54 AS SLxxxxrfgycashcshgvshgvjhsg, dbo.tblVaultPlanObject.SSLxxxxlihbdluhvbdlhvbdlhbd / 2.54 AS SSLxxxxlihbdluhvbdlhvbdlhbd, dbo.tblVaultPlanObject.SELxxxxtyackkuvsykgcskjm / 2.54 AS SELxxxxtyackkuvsykgcskjm, dbo.tblVaultPlanObject.IPXxxxxyfgcyyywgijsnlkjnsksj, dbo.tblVaultPlanObject.IPYxxxxycfsghsgysysgbsjsjsklsm, dbo.tblGlobalPlanObject.User_int_2 AS PPIDxxxx;jobsdlugvsjhvbs, dbo.tblGlobalPlanObject.User_int_1 AS PBIDxxxxxkjbsjfsdhbd, NOPxxxxjksvulhvsdlhvbs, ISxxxxjhasgvukgvsjhvbshk, ROW_NUMBER()OVER ( PARTITION BY dbo.tblVaultPlanObject.POIGIDxxxxjlhvbsjlhvbsuhvsssss ORDER BY dbo.tblVaultPlanObject.VPOIDxxxxljhbsulhvsjksbls ASC) AS SplitNumber FROM dbo.tblStoreCategoryHierarchy AS tblStoreCategoryHierarchy_1 WITH (NOLOCK) INNER JOIN dbo.tblMasterFloorPlan WITH (NOLOCK) INNER JOIN dbo.tblFloorPlan WITH (NOLOCK) INNER JOIN dbo.tblFloorPlanAnalysisDetail WITH (NOLOCK) ON dbo.tblFloorPlan.FPIDxxxxxxxxx = dbo.tblFloorPlanAnalysisDetail.FloorPlanInstanceID INNER JOIN dbo.tblFloorPlanAnalysisHeader WITH (NOLOCK) ON dbo.tblFloorPlanAnalysisDetail.FloorPlanAnalysisID = dbo.tblFloorPlanAnalysisHeader.FloorPlanAnalysisID INNER JOIN dbo.tblVaultPlanObject WITH (NOLOCK) ON dbo.tblFloorPlan.FPIDxxxxxxxxx = dbo.tblVaultPlanObject.FPIDxxxxxxxxx INNER JOIN dbo.tblVaultMerchandisableAreaDetail WITH (NOLOCK) ON dbo.tblVaultPlanObject.VPOIDxxxxljhbsulhvsjksbls = dbo.tblVaultMerchandisableAreaDetail.VaultChildObjectID INNER JOIN dbo.tblGlobalPlanObject WITH (NOLOCK) ON dbo.tblVaultPlanObject.PlanObjectGlobalID = dbo.tblGlobalPlanObject.GPOIDxxxxxjhvasysykgcskgvsykgs INNER JOIN dbo.tblMerchandisableAreaPosition WITH (NOLOCK) ON dbo.tblVaultMerchandisableAreaDetail.MerchandisableAreaPositionID = dbo.tblMerchandisableAreaPosition.MerchandisableAreaPositionID INNER JOIN dbo.tblVaultMerchandisableArea WITH (NOLOCK) ON dbo.tblVaultMerchandisableAreaDetail.VaultMAIDxxxklbsuvshvbsljkn = dbo.tblVaultMerchandisableArea.VaultMAIDxxxklbsuvshvbsljkn ON dbo.tblMasterFloorPlan.mfpidxxxxxxxx = dbo.tblFloorPlan.mfpidxxxxxxxx INNER JOIN dbo.tblStore WITH (NOLOCK) INNER JOIN dbo.tblStoreProject WITH (NOLOCK) ON dbo.tblStore.StoreID = dbo.tblStoreProject.StoreID ON dbo.tblMasterFloorPlan.StoreProjectID = dbo.tblStoreProject.StoreProjectID INNER JOIN dbo.tblStatus WITH (NOLOCK) ON dbo.tblMasterFloorPlan.MFPSxxxxxxxxID = dbo.tblStatus.StatusID INNER JOIN dbo.tblStoreCategoryHierarchyDetail WITH (NOLOCK) ON dbo.tblStore.StoreID = dbo.tblStoreCategoryHierarchyDetail.StoreID ON tblStoreCategoryHierarchy_1.StoreCategoryHierarchyID = dbo.tblStoreCategoryHierarchyDetail.StorecategoryHierarchyID INNER JOIN dbo.tblRetailer WITH (NOLOCK) INNER JOIN dbo.tblStoreCategoryHierarchy WITH (NOLOCK) ON dbo.tblRetailer.RetailerID = dbo.tblStoreCategoryHierarchy.RetailerID ON tblStoreCategoryHierarchy_1.ParentCategoryHierarchyID = dbo.tblStoreCategoryHierarchy.StoreCategoryHierarchyID LEFT OUTER JOIN dbo.tblVaultAisleMerchandisableAreaInstance WITH (NOLOCK) LEFT OUTER JOIN dbo.tblAisleInstance WITH (NOLOCK) ON dbo.tblVaultAisleMerchandisableAreaInstance.AIIDxxxxxlkhluyfv = dbo.tblAisleInstance.AIIDxxxxxlkhluyfv ON dbo.tblVaultMerchandisableArea.VaultMAIDxxxklbsuvshvbsljkn = dbo.tblVaultAisleMerchandisableAreaInstance.VaultMAIDxxxklbsuvshvbsljkn LEFT JOIN tblGlobalObjectLifeTime ON dbo.tblVaultPlanObject.GOLTIDxxxxlknkljbnjkvhhf = tblGlobalObjectLifeTime.GOLTIDxxxxlknkljbnjkvhhf LEFT JOIN tblStatus AS tblLTSxxxjhgcaycsjhskljhliygv ON dbo.tblGlobalObjectLifeTime.GlobalObjectStatusID = tblLTSxxxjhgcaycsjhskljhliygv.StatusID LEFT OUTER JOIN ( SELECT tblVaultPlanObjectFixtureSequence.VPOIDxxxxljhbsulhvsjksbls, COUNT(dbo.tblVaultPlanObjectFixtureSequence.VaultFixtureID) AS FixtureCount FROM dbo.tblVaultPlanObjectFixtureSequence WITH (NOLOCK) GROUP BY tblVaultPlanObjectFixtureSequence.VPOIDxxxxljhbsulhvsjksbls ) AS POGFixtures ON dbo.tblVaultPlanObject.VPOIDxxxxljhbsulhvsjksbls = POGFixtures.VPOIDxxxxljhbsulhvsjksbls INNER JOIN ( Select FPIDxxxxxxxxx From ( SELECT dbo.tblMasterFloorPlan.mfpidxxxxxxxx, dbo.tblFloorPlan.FPIDxxxxxxxxx, Row_Number () OVER(Partition By dbo.tblMasterFloorPlan.mfpidxxxxxxxx order by dbo.tblFloorPlan.FPIDxxxxxxxxx DESC) As RowNumber FROM dbo.tblFloorPlan WITH (NOLOCK) INNER JOIN dbo.tblFloorPlanAnalysisDetail WITH (NOLOCK) ON dbo.tblFloorPlan.FPIDxxxxxxxxx = dbo.tblFloorPlanAnalysisDetail.FloorPlanInstanceID INNER JOIN dbo.tblFloorPlanAnalysisHeader WITH (NOLOCK) ON dbo.tblFloorPlanAnalysisDetail.FloorPlanAnalysisID = dbo.tblFloorPlanAnalysisHeader.FloorPlanAnalysisID INNER JOIN dbo.tblMasterFloorPlan WITH (NOLOCK) ON dbo.tblFloorPlan.mfpidxxxxxxxx = dbo.tblMasterFloorPlan.mfpidxxxxxxxx WHERE (dbo.tblFloorPlanAnalysisHeader.IsVault = 1) AND (dbo.tblMasterFloorPlan.MFPSxxxxxxxxID = 38) AND (dbo.tblFloorPlan.FloorPlanStatusID = 38) ) As LIVEFloorPlans Where RowNumber = 1 ) As LIVEFloorPlans ON LIVEFloorPlans.FPIDxxxxxxxxx = tblFloorPlan.FPIDxxxxxxxxx LEFT OUTER JOIN ( SELECT dbo.tblVaultPlanObject.FPIDxxxxxxxxx,PlanObjectGlobalID,COUNT(DISTINCT POIGIDxxxxjlhvbsjlhvbsuhvsssss) As NOPxxxxjksvulhvsdlhvbs FROM dbo.tblVaultPlanObject WITH (NOLOCK) INNER JOIN dbo.tblFloorPlan WITH (NOLOCK) ON dbo.tblVaultPlanObject.FPIDxxxxxxxxx = dbo.tblFloorPlan.FPIDxxxxxxxxx WHERE (dbo.tblVaultPlanObject.PlanObjectGlobalID > 0) AND (dbo.tblFloorPlan.FloorPlanStatusID = 38) GROUP BY dbo.tblVaultPlanObject.FPIDxxxxxxxxx,PlanObjectGlobalID HAVING (COUNT(DISTINCT POIGIDxxxxjlhvbsjlhvbsuhvsssss) > 1) ) As View_NOPxxxxjksvulhvsdlhvbs ON dbo.tblVaultPlanObject.PlanObjectGlobalID = View_NOPxxxxjksvulhvsdlhvbs.PlanObjectGlobalID AND dbo.tblVaultPlanObject.FPIDxxxxxxxxx = View_NOPxxxxjksvulhvsdlhvbs.FPIDxxxxxxxxx LEFT OUTER JOIN ( SELECT POIGIDxxxxjlhvbsjlhvbsuhvsssss,1 AS ISxxxxjhasgvukgvsjhvbshk FROM dbo.tblVaultPlanObject WITH (NOLOCK) INNER JOIN dbo.tblFloorPlan WITH (NOLOCK) ON dbo.tblVaultPlanObject.FPIDxxxxxxxxx = dbo.tblFloorPlan.FPIDxxxxxxxxx WHERE (dbo.tblVaultPlanObject.PlanObjectGlobalID > 0) AND (dbo.tblFloorPlan.FloorPlanStatusID = 38) GROUP BY POIGIDxxxxjlhvbsjlhvbsuhvsssss HAVING (COUNT(DISTINCT VPOIDxxxxljhbsulhvsjksbls) > 1) ) As View_SplitPlanograms ON dbo.tblVaultPlanObject.POIGIDxxxxjlhvbsjlhvbsuhvsssss = View_SplitPlanograms.POIGIDxxxxjlhvbsjlhvbsuhvsssss INNER JOIN ( SELECT GPOIDxxxxxjhvasysykgcskgvsykgs,GOLTIDxxxxlknkljbnjkvhhf AS MissingGOLTIDxxxxlknkljbnjkvhhf,LTSxxxjhgcaycsjhskljhliygv AS MissingLTSxxxjhgcaycsjhskljhliygv FROM ( SELECT GPOIDxxxxxjhvasysykgcskgvsykgs,GOLTIDxxxxlknkljbnjkvhhf,LTSxxxjhgcaycsjhskljhliygv,ROW_NUMBER() OVER ( PARTITION BY GPOIDxxxxxjhvasysykgcskgvsykgs ORDER BY StatusPriority ASC) AS RowNumber FROM ( SELECT dbo.tblGlobalPlanObject.GPOIDxxxxxjhvasysykgcskgvsykgs, dbo.tblGlobalObjectLifeTime.GOLTIDxxxxlknkljbnjkvhhf,tblStatus.Status AS LTSxxxjhgcaycsjhskljhliygv , CASE dbo.tblGlobalObjectLifeTime.GlobalObjectStatusID WHEN 20 THEN 1 WHEN 25 THEN 2 WHEN 24 THEN 3 ELSE 4 END StatusPriority FROM dbo.tblGlobalPlanObject WITH (NOLOCK) LEFT OUTER JOIN dbo.tblGlobalObjectLifeTime WITH (NOLOCK) ON dbo.tblGlobalPlanObject.GPOIDxxxxxjhvasysykgcskgvsykgs = dbo.tblGlobalObjectLifeTime.GlobalObjectID INNER JOIN dbo.tblStatus WITH (NOLOCK) ON dbo.tblGlobalObjectLifeTime.GlobalObjectStatusID = dbo.tblStatus.StatusID ) AS SelSet ) AS FinalSet WHERE RowNumber = 1 ) AS MissingLifeTimes ON dbo.tblGlobalPlanObject.GPOIDxxxxxjhvasysykgcskgvsykgs = MissingLifeTimes.GPOIDxxxxxjhvasysykgcskgvsykgs WHERE (dbo.tblFloorPlanAnalysisHeader.IsVault = 1) AND (dbo.tblVaultMerchandisableAreaDetail.ChildObjectTypeID = 60) AND (dbo.tblMasterFloorPlan.MFPSxxxxxxxxID = 38) AND (UPPER(LTRIM(RTRIM(dbo.tblRetailer.RetailerDescription))) = 'BI-LO') AND (dbo.tblVaultPlanObject.PlanObjectGlobalID > 0) ) AS InnerSet Where SplitNumber = 1 ) As SelectionSet ) As FinalSet
2014-06-09 09:39:58
Hey richard101
As the query isn't shown I'd have to suggest starting with updating your statistics on the related tables as the estimates are way out against the actual – look at the query columns tab to see which are out the most.
From there take a look at the key lookups, index 15 and 20 could be altered to include the columns and help out a bit.
Start there and let me know
Mart
There's a lot to look at there, would you be able to run it from within plan explorer to get the actual plan then post that without anonymizing it please?
Thanks
Mart
P.S. if you note how long it takes to run and what performance improvement your looking for that will give us a target 🙂