CODEWITHSUNDEEP
sql-servert-sqlsql-server-2014
Roland Andrag
Roland Andrag

Reputation: 462

Getting SQL server to apply where clause with cross join to query in a view

My first post to stackoverflow (which has helped me hugely over time):

I have a query with a cross join that works fine when I run it with a where clause, but takes forever when I place it in a view and apply the where clause to the view.

I think the problem is that SQL is not applying the where clause to the cross join when the code is encapsulated in a view, and thus ending up with millions of rows (instead of 180 in this case).

The code is below - it is a query which forecasts the future on-hand stock of an item in a warehouse using an average expected monthly usage and a list of incoming orders.

CREATE VIEW [dbo].[ItemWarehouseStockForecastDaily2]
AS    
SELECT
    fd.AsafterDate
    , iw.idItem
    , iw.idWarehouse
    , iw.OnHandQuantity  
        + SUM(ISNULL(iwio.PurchaseOrderInboundQuantity, 0)
                - iws.AverageMonthlyDemandQuantity / (365.25/12)
            ) OVER (ORDER BY fd.AsafterDate) AS OnHandQuantity
FROM
    (
        ( SELECT CalendarDate Asafterdate 
          FROM Calendar c 
          WHERE c.CalendarDate > GETDATE() 
            AND c.CalendarDate < DATEADD(d, 180, GETDATE())
        ) fd -- This table has 180 rows
        -- This table has 10 million rows - one per item per warehouse
        CROSS JOIN ItemWarehouse iw 
    )
    LEFT JOIN ItemWarehouseDemandFromStockStatisticsMonthly iws
           ON iws.idItem = iw.idItem 
          AND iws.idWarehouse = iw.idWarehouse
    LEFT JOIN ItemWarehouseInboundAndOutboundQuantitiesWithDueDate iwio 
           ON iwio.idItem = iw.idItem 
          AND iwio.idWarehouse = iw.idWarehouse 
          AND iwio.DueDate = fd.Asafterdate
/*
WHERE iw.idItem = 12345
  AND iw.idWarehouse = 67
ORDER BY AsafterDate
*/

The commented-out where clause makes the query run fast (sub-second) when not in a view (tables cluster by idwarehouse, iditem)

Any/all help and advice will be greatly appreciated.

Upvotes: 1

Views: 792

Answers (1)

Arne Klein
Arne Klein

Reputation: 882

I don't have the reputation to comment yet. However, checking on technet it says that a Cross Join should behave the same as a inner join if you add a where statement (at least for SQL 2008 R2). https://technet.microsoft.com/en-us/library/ms190690(v=sql.105).aspx

Did you check wether the same issue occurs if you use an inner join?

As another alternativ, can you add the constraint directly to the where clause as follows?

CREATE VIEW [dbo].[ItemWarehouseStockForecastDaily2]
AS    
SELECT
fd.AsafterDate
, iw.idItem
, iw.idWarehouse
, iw.OnHandQuantity  
    + SUM(ISNULL(iwio.PurchaseOrderInboundQuantity, 0)
            - iws.AverageMonthlyDemandQuantity / (365.25/12)
        ) OVER (ORDER BY fd.AsafterDate) AS OnHandQuantity
FROM
(
    ( SELECT CalendarDate Asafterdate 
      FROM Calendar c 
      WHERE c.CalendarDate > GETDATE() 
        AND c.CalendarDate < DATEADD(d, 180, GETDATE())
    ) fd -- This table has 180 rows
    -- Filter right here instead of later:
    INNER JOIN ItemWarehouse iw ON iw.idItem = 12345 AND iw.idWarehouse = 67
)
LEFT JOIN ItemWarehouseDemandFromStockStatisticsMonthly iws
       ON iws.idItem = iw.idItem 
      AND iws.idWarehouse = iw.idWarehouse
LEFT JOIN ItemWarehouseInboundAndOutboundQuantitiesWithDueDate iwio 
       ON iwio.idItem = iw.idItem 
      AND iwio.idWarehouse = iw.idWarehouse 
      AND iwio.DueDate = fd.Asafterdate

Upvotes: 0

Related Questions