CODEWITHSUNDEEP
sqlsubqueryquery-optimizationteradata
Chirag Bogra
Chirag Bogra

Reputation: 11

Teradata spool space issue on running a sub query with Count

I am using below query to calculate business days between two dates for all the order numbers. Business days are already available in the teradata table Common_WorkingCalendar. But, i'm also facing spool space issue while i execute the query. I have ample space available in my data lab. Need to optimize the query. Appreciate any inputs.

SELECT 
tx."OrderNumber",
(SELECT COUNT(1) FROM Common_WorkingCalendar
WHERE CalDate between Cast(tx."TimeStamp" as date) and Cast(mf.ShipDate as  date)) as BusDays
from StoreFulfillment ff
inner join StoreTransmission tx 
            on tx.OrderNumber = ff.OrderNumber
        inner join StoreMerchandiseFulfillment mf 
            on mf.OrderNumber = ff.OrderNumber

Upvotes: 0

Views: 574

Answers (1)

dnoeth
dnoeth

Reputation: 60482

This is a very inefficient way to get this count which results in a product join.

The recommended approach is adding a sequential number to your calendar which increases only on business days (calculated using SUM(CASE WHEN businessDay THEN 1 ELSE 0 END) OVER (ORDER BY CalDate ROWS UNBOUNDED PRECEDING)), then it's two joins, for the start date and the end date.

If this calculation is needed a lot you better add a new column, otherwise you can do it on the fly:

WITH cte AS 
 (
  SELECT CalDate,
     -- as this table only contains business days you can use this instead
     row_number(*) Over (ORDER BY CalDate) AS DayNo
  FROM Common_WorkingCalendar
 )
SELECT
  tx."OrderNumber",
  to_dt.DayNo - from_dt.DayNo AS BusDays
FROM StoreFulfillment ff
INNER JOIN StoreTransmission tx 
            ON tx.OrderNumber = ff.OrderNumber
        INNER JOIN StoreMerchandiseFulfillment mf 
            ON mf.OrderNumber = ff.OrderNumber
JOIN cte AS from_dt
  ON from_dt.CalDate = Cast(tx."TimeStamp" AS DATE)
JOIN cte AS to_dt
  ON to_dt.CalDate = Cast(mf.ShipDate AS  DATE)

Upvotes: 2

Related Questions