Slow Join on Composite Primary Key

Question

I have the following query, its runtime is ~2 seconds until I join ProductStores on StoreID which increases it to ~3 minutes, joining only on ProductID keeps it at ~2 seconds.

SELECT
    Enabled = pp.PspEnabled
    , StockStatusID = ss.ID
    , WebSellable = pp.PspWebSellable
    , CSSellable = pp.PspCsSellable
FROM 
    #ExternalProducts pp 
JOIN 
    Product p ON p.ExternalCode = pp.code
JOIN 
    Stores s ON s.Name = pp.p_externalStore
JOIN 
    StockStatus ss ON ss.Name = pp.PspStockStatus
JOIN 
    ProductStores ps ON (/* Store join increases time only */ ps.StoreID = s.ID AND ps.ProductID = p.ID)

Rows:

Stores: 108
Product: 136'598
ProductStores: 609'963

Keys:

CONSTRAINT [PK_dbo.Stores] 
    PRIMARY KEY CLUSTERED ([ID] ASC)
CONSTRAINT [PK_dbo.Product] 
    PRIMARY KEY CLUSTERED ([ID] ASC)   
CONSTRAINT [PK_dbo.ProductStores] 
    PRIMARY KEY CLUSTERED ([ProductID] ASC, [StoreID] ASC)

CONSTRAINT [FK_dbo.ProductStores_dbo.Stores_SiteID] 
    FOREIGN KEY([StoreID]) REFERENCES [dbo].[Stores] ([ID])
CONSTRAINT [FK_dbo.ProductStores_dbo.Product_ProductID] 
    FOREIGN KEY([ProductID]) REFERENCES [dbo].[Product] ([ID])

Execution Plan:

The execution plan shows the bulk cost is coming from a Hash Match (Inner Join) with a Hash Keys Probe [dbo].[Stores].Name and Hash Keys Build [#ExternalProducts].p_externalstore which I assume is the problem but I'm not sure how to interpret this?

Any help is greatly appreciated!

I don't believe that you actually have the index on `ProductStores`. — Gordon Linoff, Feb 08 '17 at 12:12
Doing a `SELECT * FROM sys.indexes ...` shows me it has `PX_dbo.ProductStores CLUSTERED`, `IX_ProductID NONCLUSTERED`, `IX_StoreID NONCLUSTERED`. — Nick Spicer, Feb 08 '17 at 12:28
Can you post the execution plan? Also did you run that query in the tuning adviser? What is in the temp table? — Joe C, Feb 08 '17 at 12:37
I've added them as pictures, wasn't sure what the preferred format was. I have not I'll look into that thanks. — Nick Spicer, Feb 08 '17 at 13:13
Which columns from `ProductStores` are you using? You can [include](https://msdn.microsoft.com/en-us/library/ms188783.aspx) them in your index to improve performance. Either way, your reported query time (~3 minutes) is too long. Try to create an index on the `#ExternalProducts` table - it won't help directly, but may help query optimizer to generate a better plan. — Serge, Feb 08 '17 at 14:02
"Estimated Number of Rows" very different from "Actual Number of Rows". I think that you need to update statistics for your tables — Denis Rubashkin, Feb 08 '17 at 14:30

Nick Spicer · Accepted Answer · 2017-02-08T16:15:45.467

Denis Rubashkin noted that the estimated and actual row numbers are actually very different. After the UPDATE STATISTICS failed to modify the execution plan I reorganised the query to pull from ProductStores and filter from #ExternalProducts instead which allowed me to force a Nested Loop Join (which I assumed would be preferable on smaller result sets).

SELECT
    Enabled = pp.PspEnabled
    , StockStatusID = ss.ID
    , WebSellable = pp.PspWebSellable
    , CSSellable = pp.PspCsSellable
FROM ProductStores ps
JOIN Product p ON p.ID = ps.ProductID
JOIN Stores s ON s.ID = ps.StoreID
INNER LOOP JOIN #ExternalProducts pp ON (p.Code = pp.Code AND s.Name = pp.p_externalstore)
JOIN StockStatus ss ON ss.Name = pp.PspStockStatus

This reduced the query time to ~7 seconds from ~3 minutes which is acceptable for this purpose!

Slow Join on Composite Primary Key

1 Answers1