Sql-server – Slow SQL Server Query

execution-planperformancequery-performancesql-server-2012

First let me start off by saying I am in no way a DB expert. I know enough to be dangerous, but I am primarily a c# developer. I have a query off a view that is slow, but I am having problems figuring out the root cause. I have attached the query plan, hoping I can get some insight here.

https://www.brentozar.com/pastetheplan/?id=H1GT43J8e

Here is the view info as well:

CREATE VIEW [dbo].[ReSyncAdwords]
WITH schemabinding
AS
  SELECT dbo.currentadwordsmaxbid.maxcpc - dervtable.Bid AS Expr1,
         dbo.currentadwordsmaxbid.webid,
         dbo.currentadwordsmaxbid.maxcpc,
         dbo.currentadwordsmaxbid.adgroupid,
         dbo.currentadwordsmaxbid.keywordid,
         dervtable.SKU,
         dervtable.Bid,
         dbo.currentadwordsmaxbid.campaginid
  FROM   dbo.currentadwordsmaxbid
         LEFT OUTER JOIN dbo.WebSkuLookup
                      ON dbo.currentadwordsmaxbid.webid = dbo.WebSkuLookup.webid
         LEFT OUTER JOIN (SELECT SKU,
                                 Bid,
                                 Channel,
                                 DTStamp,
                                 campaginid
                          FROM   dbo.BidHistory AS ph1
                          WHERE  ( DTStamp = (SELECT MAX(DTStamp) AS Expr1
                                              FROM   dbo.BidHistory AS ph2
                                              WHERE  ( campaginid = ph1.campaginid )
                                                     AND ( ph1.SKU = SKU )) )) AS dervtable
                      ON dbo.WebSkuLookup.sku = dervtable.SKU
                         AND dbo.currentadwordsmaxbid.campaginid = dervtable.campaginid
  WHERE  ( dbo.currentadwordsmaxbid.maxcpc - dervtable.Bid <> 0 )

GO

Thanks.

Best Answer

Why all the ( ) and long names?
Why mixed lowerecase camel case on table names?

Just basic cleanup

  SELECT dbo.currentadwordsmaxbid.maxcpc - dervtable.Bid AS Expr1,
         dbo.currentadwordsmaxbid.webid,
         dbo.currentadwordsmaxbid.maxcpc,
         dbo.currentadwordsmaxbid.adgroupid,
         dbo.currentadwordsmaxbid.keywordid,
         dbo.currentadwordsmaxbid.campaginid,
         dervtable.SKU,
         dervtable.Bid
  FROM   dbo.currentadwordsmaxbid
         LEFT OUTER JOIN dbo.WebSkuLookup
                      ON dbo.currentadwordsmaxbid.webid = dbo.WebSkuLookup.webid
         LEFT OUTER JOIN (SELECT SKU, campaginid, Bid
                          FROM   dbo.BidHistory AS ph1
                          WHERE  DTStamp = ( SELECT MAX(DTStamp) AS Expr1
                                             FROM   dbo.BidHistory AS ph2
                                             WHERE  ph1.campaginid = ph2.campaginid
                                             AND    ph1.SKU = ph2.SKU )
                        ) AS dervtable
                      ON dbo.WebSkuLookup.sku = dervtable.SKU
                     AND dbo.currentadwordsmaxbid.campaginid = dervtable.campaginid
                     AND dbo.currentadwordsmaxbid.maxcpc <> dervtable.Bid

row_number() 

  SELECT dbo.currentadwordsmaxbid.maxcpc - dervtable.Bid AS Expr1,
         dbo.currentadwordsmaxbid.webid,
         dbo.currentadwordsmaxbid.maxcpc,
         dbo.currentadwordsmaxbid.adgroupid,
         dbo.currentadwordsmaxbid.keywordid,
         dbo.currentadwordsmaxbid.campaginid,
         dervtable.SKU,
         dervtable.Bid
  FROM   dbo.currentadwordsmaxbid
         LEFT OUTER JOIN dbo.WebSkuLookup
                      ON dbo.currentadwordsmaxbid.webid = dbo.WebSkuLookup.webid
         LEFT OUTER JOIN (SELECT SKU, campaginid, Bid 
                               , row_number over (partition by SKU, campaginid order by DTStamp desc) as rn
                          FROM   dbo.BidHistory AS ph1
                        ) AS dervtable
                      ON dbo.WebSkuLookup.sku = dervtable.SKU 
                     AND dervtable.rn = 1
                     AND dbo.currentadwordsmaxbid.campaginid = dervtable.campaginid
                     AND dbo.currentadwordsmaxbid.maxcpc <> dervtable.Bid
Related Question