Simplify a complex query

richard101 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
Mart 2014-06-09 10:44:00
Hi richard101

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 🙂

Mart 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