Wednesday, August 11, 2010

Loops Vs Set Based Approach...

Recently I had a requirement where given a range of dates, I had to perform the counts for each date within the date range. Initially I had started off approaching the problem by using a While Loop construct and running the select statement for each date in the date range. The solution worked but the performance was bad, the user had the choice of giving the date range, so the performance would get a hit if the date range is very wide. After researching for a while I came across a query in ORACLE which used the CONNECT and LEVEL clauses to handle the problem which i had. This was a set based approach with no looping involved and apparently performed very well on oracle. I decided to use this query as a starting point and created a CTE to be populated with the range of dates within the date range. I use the following query to create the CTE:

WITH t_dates(rptday) AS  
(SELECT CONVERT(VARCHAR(10),@ldt_failstartdate,121)

      UNION ALL
SELECT CONVERT(VARCHAR(10),DATEADD(d,+1,rptday),121)
FROM t_dates
WHERE rptday < CONVERT(VARCHAR(10),@ldt_failenddate,121)
)
Once this was done, immedietly followed it up with the main SELECT statement which calculated the counts. The performance of this setup was much better than the Looping construct. This was an instance were I was able to replace the looping construct with a SET Based solution to achieve better results.

No comments:

Post a Comment