June 5, 2016 at 3:06 am
leehbi (6/5/2016)
Thanks again for sharing. Modern T-SQL gives us many ways to skin a cat.
Would be interesting to know which method you choose, there is up to 25 times difference in the execution time between these methods.
😎
June 16, 2016 at 2:06 am
Apologies for the delay, been very busy. I had to solve other measure problems with this fact table. I came back to this and used the dry run query. Query executes in 3 minutes with many other calculations 😀
Eirikur Eiriksson (6/5/2016)
leehbi (6/5/2016)
Thanks again for sharing. Modern T-SQL gives us many ways to skin a cat.Would be interesting to know which method you choose, there is up to 25 times difference in the execution time between these methods.
😎
June 16, 2016 at 9:00 am
This returns 3 rows from the base table when doing the except both directions.
;WITH CUSTOMERS_SALE AS
(
SELECT
SSF.CUST_ID
FROM dbo.TBL_SAMPLE_SALE_FLAG SSF
WHERE SSF.SALE_VAL IS NOT NULL
GROUP BY SSF.CUST_ID
)
SELECT
SF.CUST_ID
,SF.WIND_ID
,SF.PROD_ID
,SF.SALE_VAL
,SIGN(ISNULL(CSALE.CUST_ID,0)) AS SALE_FLAG
FROM dbo.TBL_SAMPLE_SALE_FLAG SF
LEFT OUTER JOIN CUSTOMERS_SALE CSALE
ON SF.CUST_ID = CSALE.CUST_ID
except
SELECT e.CUST_ID
,e.WIND_ID
,e.PROD_ID
,e.SALE_VAL, CASE WHEN s.sf = 1 THEN 1 ELSE 0 END --AS sales_flag
FROM dbo.TBL_SAMPLE_SALE_FLAG e
OUTER APPLY (SELECT TOP 1 1 AS sf
FROM dbo.TBL_SAMPLE_SALE_FLAG e2
WHERE e.CUST_ID = e2.CUST_ID
AND e2.SALE_VAL IS NOT NULL) AS s
This one has same results both ways, so we can compare their performance.
SELECT
SF.CUST_ID
,SF.WIND_ID
,SF.PROD_ID
,SF.SALE_VAL
,CASE WHEN COUNT(sf.SALE_VAL) OVER
(
PARTITION BY SF.CUST_ID
ORDER BY SF.CUST_ID
) > 0 THEN 1 ELSE 0 END --AS FLG
FROM dbo.TBL_SAMPLE_SALE_FLAG sf
except
SELECT e.CUST_ID
,e.WIND_ID
,e.PROD_ID
,e.SALE_VAL, CASE WHEN s.sf = 1 THEN 1 ELSE 0 END --AS sales_flag
FROM dbo.TBL_SAMPLE_SALE_FLAG e
OUTER APPLY (SELECT TOP 1 1 AS sf
FROM dbo.TBL_SAMPLE_SALE_FLAG e2
WHERE e.CUST_ID = e2.CUST_ID
AND e2.SALE_VAL IS NOT NULL) AS s
June 16, 2016 at 1:40 pm
Typically I've found that DISTINCT is faster than GROUP BY when SELECTing a single column, i.e. as below, but obviously that could vary:
SELECT DISTINCT
SSF.customerId
FROM #example SSF
WHERE SSF.sales IS NOT NULL
--vs.
SELECT
SSF.customerId
FROM #example SSF
WHERE SSF.sales IS NOT NULL
GROUP BY SSF.customerId
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply