Witam,

Potrzebuję pomocy przy optymalizacji zapytania SQL, które wykonuje się zdecydowanie za wolno. Próbowałem sam się uporać z tym problemem jednak brakuje mi już pomysłów jak to rozwiązać.

Kod zapytania w poniższym linku.
http://wklej.org/id/1087334/

[SQL] pobierz, plaintext 
SELECT        dbo.MetaOffer.MetaOfferId, dbo.MetaOffer.OfferName, dbo.MetaOffer.CheckOutDate, dbo.MetaOffer.CreateDate, dbo.MetaOffer.CoordinatorDate, 
                         dbo.MetaOffer.DeploymentDate, ohii.UserId, dbo.Customer.CustomerNip, dbo.Customer.DisplayName AS CustomerFullName, dbo.ObjectType.ObjectTypeName, 
                         creator.FirstName + ' ' + creator.LastName AS CreatorFullName, Kozu.ProcessStatus.STATUS, owner.FirstName + ' ' + owner.LastName AS OwnerFullName, 
                         owner.Sfid AS OwnerSfid, creator.Sfid AS CreatorSfid, analyst.FirstName + ' ' + analyst.LastName AS AnalystFullName, 
                         coord.FirstName + ' ' + coord.LastName AS CoordFullName, coord.Sfid AS CoordSfid, analyst.Sfid AS AnalystSfid, ownerGroup.Name AS NazwaGrupy, 
                         deployer.FirstName + ' ' + deployer.LastName AS DeployerFullName, deployer.Sfid AS DeployerSfid, dbo.Customer.S2KNumber, dbo.MetaOffer.ObjectTypeId, 
                         dbo.MetaOffer.LastStatusChangeDate, dbo.MetaOffer.LastProcessStatusId, dbo.MetaOffer.SourceMetaOfferId, CAST(SUBSTRING
                             ((SELECT        ' ,' + CAST(Nip AS nvarchar(50))
                                 FROM            CustomerNip cn
                                 WHERE        cn.CustomerId = dbo.Customer.CustomerId FOR XML PATH('')), 3, 100) AS nvarchar(200)) AS NIPs, dbo.MetaOffer.IsArchive, dbo.MetaOffer.TemplateName, 
                         CAST(LastHistory.ActionComment AS nvarchar(400)) AS ActionComment, dbo.Customer.City, dbo.Trade.TradeName, 
                         checkout.FirstName + ' ' + checkout.LastName AS CheckOutFullName
FROM            dbo.MetaOffer WITH (nolock) INNER JOIN
                         dbo.OfferHistoryItem AS LastHistory WITH (nolock) ON LastHistory.OfferHistoryItemId = dbo.MetaOffer.LastOfferHistoryItem INNER JOIN
                            (SELECT        dbo.MetaOffer.MetaOfferId,ohi.UserId
							FROM            dbo.MetaOffer WITH (nolock) INNER JOIN
													 dbo.OfferHistoryItem AS LastHistory WITH (nolock) ON LastHistory.OfferHistoryItemId = dbo.MetaOffer.LastOfferHistoryItem INNER JOIN
														 (SELECT        UserId, MetaOfferId
														   FROM            dbo.OfferHistoryItem WITH (nolock)
														   WHERE        (UserId IS NOT NULL)
														   UNION
														   SELECT        DestinationUserId, MetaOfferId
														   FROM            dbo.OfferHistoryItem AS OfferHistoryItem_2 WITH (nolock)
														   WHERE        (DestinationUserId IS NOT NULL)
														   UNION
														   SELECT        Substitute4UserId, MetaOfferId
														   FROM            dbo.OfferHistoryItem AS OfferHistoryItem_1 WITH (nolock)
														   WHERE        (Substitute4UserId IS NOT NULL)) AS ohi ON dbo.MetaOffer.MetaOfferId = ohi.MetaOfferId						 
							WHERE        (dbo.MetaOffer.ObjectTypeId != 3)
 
							UNION
 
							SELECT        dbo.MetaOffer.MetaOfferId, Kozu.UserUserGroup.UserId
							FROM            dbo.MetaOffer WITH (nolock) INNER JOIN
													 dbo.OfferHistoryItem AS LastHistory WITH (nolock) ON LastHistory.OfferHistoryItemId = dbo.MetaOffer.LastOfferHistoryItem INNER JOIN
														 (SELECT        UserGroupId, MetaOfferId
														   FROM            dbo.OfferHistoryItem WITH (nolock)
														   WHERE        (UserGroupId IS NOT NULL)
														   UNION
														   SELECT        DestinationUserGroupId, MetaOfferId
														   FROM            dbo.OfferHistoryItem AS OfferHistoryItem_2 WITH (nolock)
														   WHERE        (DestinationUserGroupId IS NOT NULL)) AS ohi2 ON dbo.MetaOffer.MetaOfferId = ohi2.MetaOfferId  INNER JOIN 
 
													 Kozu.UserUserGroup WITH (nolock) ON Kozu.UserUserGroup.UserGroupId = ohi2.UserGroupId
							WHERE        (dbo.MetaOffer.ObjectTypeId != 3)) AS ohii ON dbo.MetaOffer.MetaOfferId = ohii.MetaOfferId  INNER JOIN 
						 dbo.Customer WITH (nolock) ON dbo.MetaOffer.CustomerId = dbo.Customer.CustomerId INNER JOIN
                         dbo.Trade WITH (nolock) ON dbo.Trade.TradeId = dbo.Customer.TradeId INNER JOIN
                         dbo.ObjectType WITH (nolock) ON dbo.MetaOffer.ObjectTypeId = dbo.ObjectType.ObjectTypeId INNER JOIN
                         Kozu.ProcessStatus WITH (nolock) ON dbo.MetaOffer.LastProcessStatusId = Kozu.ProcessStatus.ProcessStatusId LEFT OUTER JOIN
                         Kozu.[User] AS deployer WITH (nolock) ON dbo.MetaOffer.DeployUserId = deployer.UserId LEFT OUTER JOIN
                         Kozu.UserGroup AS ownerGroup WITH (nolock) ON dbo.MetaOffer.OwnerGroupId = ownerGroup.UserGroupId LEFT OUTER JOIN
					     Kozu.[User] AS coord WITH (nolock) ON dbo.MetaOffer.CoordinatorId = coord.UserId LEFT OUTER JOIN
                         Kozu.[User] AS analyst WITH (nolock) ON dbo.MetaOffer.CentralAnalystId = analyst.UserId LEFT OUTER JOIN
                         Kozu.[User] AS owner WITH (nolock) ON dbo.MetaOffer.OwnerId = owner.UserId LEFT OUTER JOIN
                         Kozu.[User] AS creator WITH (nolock) ON dbo.MetaOffer.CreatedById = creator.UserId LEFT OUTER JOIN
                         Kozu.[User] AS checkout WITH (nolock) ON dbo.MetaOffer.CheckOutById = checkout.UserId
[SQL] pobierz, plaintext 

Pokaz lepiej plan zapytania i indeksy na tabelach uzytych w zapytaniu.

Zapytanie jest ogromne, nie da sie czegoś wydzielić i przygotować danych wkładając do tebeli tymczasowej?

[SQL] pobierz, plaintext 
SELECT        ' ,' + CAST(Nip AS nvarchar(50))
                                 FROM            CustomerNip cn
                                 WHERE        cn.CustomerId = dbo.Customer.......................
[SQL] pobierz, plaintext 

Każde zapytanie 'inner query' wykonuje sie dla kazdego rekordu z 'outer query'


Pozwoliłem sobie zrobic refactore zapytania, jest bardziej czytelne...

[SQL] pobierz, plaintext 
SELECT dbo.metaoffer.metaofferid,
       dbo.metaoffer.offername,
       dbo.metaoffer.checkoutdate,
       dbo.metaoffer.createdate,
       dbo.metaoffer.coordinatordate,
       dbo.metaoffer.deploymentdate,
       ohii.userid,
       dbo.customer.customernip,
       dbo.customer.displayname AS customerfullname,
       dbo.objecttype.objecttypename,
       creator.firstname + ' ' + creator.lastname AS creatorfullname,
       kozu.processstatus.STATUS,
       owner.firstname + ' ' + owner.lastname AS ownerfullname,
       owner.sfid AS ownersfid,
       creator.sfid AS creatorsfid,
       analyst.firstname + ' ' + analyst.lastname AS analystfullname,
       coord.firstname + ' ' + coord.lastname AS coordfullname,
       coord.sfid AS coordsfid,
       analyst.sfid AS analystsfid,
       ownergroup.name AS nazwagrupy,
       deployer.firstname + ' ' + deployer.lastname AS deployerfullname,
       deployer.sfid AS deployersfid,
       dbo.customer.s2knumber,
       dbo.metaoffer.objecttypeid,
       dbo.metaoffer.laststatuschangedate,
       dbo.metaoffer.lastprocessstatusid,
       dbo.metaoffer.sourcemetaofferid,
       CAST(substring((SELECT ' ,' + CAST(nip AS nvarchar(50))
                        FROM customernip cn
                       WHERE cn.customerid = dbo.customer.customerid
                         FOR xml path('')),
                      3,
                      100) AS nvarchar(200)) AS nips,
       dbo.metaoffer.isarchive,
       dbo.metaoffer.templatename,
       CAST(lasthistory.actioncomment AS nvarchar(400)) AS actioncomment,
       dbo.customer.city,
       dbo.trade.tradename,
       checkout.firstname + ' ' + checkout.lastname AS checkoutfullname
  FROM dbo.metaoffer WITH(nolock)
 INNER JOIN dbo.offerhistoryitem AS lasthistory WITH(nolock) ON lasthistory.offerhistoryitemid = dbo.metaoffer.lastofferhistoryitem
 INNER JOIN (SELECT dbo.metaoffer.metaofferid,
                    ohi.userid
               FROM dbo.metaoffer WITH(nolock)
              INNER JOIN dbo.offerhistoryitem AS lasthistory WITH(nolock) ON lasthistory.offerhistoryitemid = dbo.metaoffer.lastofferhistoryitem
              INNER JOIN (SELECT userid,
                                metaofferid
                           FROM dbo.offerhistoryitem WITH(nolock)
                          WHERE (userid IS NOT NULL)
                         UNION
                         SELECT destinationuserid,
                                metaofferid
                           FROM dbo.offerhistoryitem AS offerhistoryitem_2 WITH(nolock)
                          WHERE (destinationuserid IS NOT NULL)
                         UNION
                         SELECT substitute4userid,
                                metaofferid
                           FROM dbo.offerhistoryitem AS offerhistoryitem_1 WITH(nolock)
                          WHERE (substitute4userid IS NOT NULL)) AS ohi ON dbo.metaoffer.metaofferid = ohi.metaofferid
              WHERE (dbo.metaoffer.objecttypeid != 3)
             UNION
             SELECT dbo.metaoffer.metaofferid,
                    kozu.userusergroup.userid
               FROM dbo.metaoffer WITH(nolock)
              INNER JOIN dbo.offerhistoryitem AS lasthistory WITH(nolock) ON lasthistory.offerhistoryitemid = dbo.metaoffer.lastofferhistoryitem
              INNER JOIN (SELECT usergroupid,
                                metaofferid
                           FROM dbo.offerhistoryitem WITH(nolock)
                          WHERE (usergroupid IS NOT NULL)
                         UNION
                         SELECT destinationusergroupid,
                                metaofferid
                           FROM dbo.offerhistoryitem AS offerhistoryitem_2 WITH(nolock)
                          WHERE (destinationusergroupid IS NOT NULL)) AS ohi2 ON dbo.metaoffer.metaofferid = ohi2.metaofferid
              INNER JOIN kozu.userusergroup WITH(nolock) ON kozu.userusergroup.usergroupid = ohi2.usergroupid
              WHERE (dbo.metaoffer.objecttypeid != 3)) AS ohii ON dbo.metaoffer.metaofferid = ohii.metaofferid
 INNER JOIN dbo.customer WITH(nolock) ON dbo.metaoffer.customerid = dbo.customer.customerid
 INNER JOIN dbo.trade WITH(nolock) ON dbo.trade.tradeid = dbo.customer.tradeid
 INNER JOIN dbo.objecttype WITH(nolock) ON dbo.metaoffer.objecttypeid = dbo.objecttype.objecttypeid
 INNER JOIN kozu.processstatus WITH(nolock) ON dbo.metaoffer.lastprocessstatusid = kozu.processstatus.processstatusid
  LEFT OUTER JOIN kozu. [ USER ] AS deployer WITH(nolock) ON dbo.metaoffer.deployuserid = deployer.userid
  LEFT OUTER JOIN kozu.usergroup AS ownergroup WITH(nolock) ON dbo.metaoffer.ownergroupid = ownergroup.usergroupid
  LEFT OUTER JOIN kozu. [ USER ] AS coord WITH(nolock) ON dbo.metaoffer.coordinatorid = coord.userid
  LEFT OUTER JOIN kozu. [ USER ] AS analyst WITH(nolock) ON dbo.metaoffer.centralanalystid = analyst.userid
  LEFT OUTER JOIN kozu. [ USER ] AS owner WITH(nolock) ON dbo.metaoffer.ownerid = owner.userid
  LEFT OUTER JOIN kozu. [ USER ] AS creator WITH(nolock) ON dbo.metaoffer.createdbyid = creator.userid
  LEFT OUTER JOIN kozu. [ USER ] AS checkout WITH(nolock) ON dbo.metaoffer.checkoutbyid = checkout.userid
 
[SQL] pobierz, plaintext