How can i take this recursive CTE and convert it to a inline CTE for better performance?

How can i take this recursive  CTE and convert it to a inline CTE for better performance?


			
	;WITH cte (StartDateUtc, EndDateUtc, OffhireDaysPerCalendarDay) AS (

					SELECT 
						@StartDateUtc,	
						CONVERT(DATETIME, DATEADD(DAY, 1, CONVERT(DATE, @StartDateUtc))),			
						CONVERT(NUMERIC(28,17), CONVERT(NUMERIC(28,17), (DATEDIFF(SECOND, @StartDateUtc , DATEADD(DAY, 1, CONVERT(DATE, @StartDateUtc)))) / CONVERT(NUMERIC(28,17), 86400)) * @OffhireDaysPerCalendarDay)

					UNION ALL

					SELECT 
						CONVERT(DATETIME, DATEADD(DAY, 1, CONVERT(DATE, StartDateUtc))),
						CASE WHEN DATEADD(DAY, 1, CONVERT(DATE, StartDateUtc)) = CONVERT(DATE, @EndDateUtc) THEN @EndDateUtc ELSE CONVERT(DATETIME, DATEADD(DAY, 2, CONVERT(DATE, StartDateUtc))) END,
						CASE WHEN DATEADD(DAY, 1, CONVERT(DATE, StartDateUtc)) = CONVERT(DATE, @EndDateUtc) THEN CONVERT(NUMERIC(28,17), CONVERT(NUMERIC(28,17), (DATEDIFF(SECOND, DATEADD(DAY, 1, CONVERT(DATE, StartDateUtc)), @EndDateUtc)) / CONVERT(NUMERIC(28,17), 86400)) * @OffhireDaysPerCalendarDay) ELSE @OffhireDaysPerCalendarDay END						
					FROM cte
					WHERE DATEADD(DAY, 1, CONVERT(DATE, StartDateUtc)) <= CONVERT(DATE, @EndDateUtc)
				)

Open in new window

Paul MaurielloSoftware Programmer Developer Analyst EngineerAsked:
Who is Participating?
 
Scott PletcherConnect With a Mentor Senior DBACommented:
I'm certain this is a follow-up to an earlier q:
https://www.experts-exchange.com/questions/29093677/How-can-I-improve-the-performance-of-this-SQL-Table-function-by-eliminating-the-While-Loop.html

Here's the general format of the improved function (as in the original q).  As I noted before, it's very straightforward, nothing really "slick".  
I couldn't test it because I don't have any sample data or the table type definition, but hopefully it's close enough you can debug any issues.  If not, provide sample data or specific details and I can help.


ALTER FUNCTION [dbo].[udtfTcVoyageOffhireDaily](
      @paramTcVoyageOffhire typeTcVoyageOffhire READONLY
)
RETURNS TABLE
AS
RETURN (
WITH
cteTally10 AS (
    SELECT * FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS numbers(number)
),
cteTally100 AS (
    SELECT 0 AS number
    FROM cteTally10 c1
    CROSS JOIN cteTally10 c2
),
cteTally10K AS (
    SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1 AS number
    FROM cteTally100 c1
    CROSS JOIN cteTally100 c2
)
SELECT p.VoyageID, p.TcInID, p.TcOutID,
    CalcStartDate AS StartDateUtc,
    CalcEndDate AS EndDateUct,
      (DATEDIFF(SECOND, CalcStartDate , CalcEndDate) / CONVERT(NUMERIC(28,17), 86400)) *
          p.OffhireDaysPerCalendarDay AS OffhireDaysPerCalendarDay
FROM @paramTcVoyageOffhire p
INNER JOIN cteTally10K t ON t.number BETWEEN 0 AND DATEDIFF(DAY, p.StartDateUtc, p.EndDateUtc)
CROSS APPLY (
    SELECT
    CASE WHEN t.number = 0 THEN StartDateUtc
          ELSE DATEADD(DAY, t.number, CAST(p.StartDateUtc AS date)) END AS CalcStartDate,
      CASE WHEN t.number = DATEDIFF(DAY, p.StartDateUtc, p.EndDateUtc) THEN EndDateUtc
          ELSE DATEADD(DAY, t.number + 1, CAST(p.StartDateUtc AS date)) END AS CalcEndDate
) AS aliases1
)
GO
0
 
Paul MaurielloSoftware Programmer Developer Analyst EngineerAuthor Commented:
Thank you Scott You rock!
0
 
Scott PletcherSenior DBACommented:
Thanks!

This should definitely be mucho-faster than a multi-line function version.

Sorry about earlier, I was really swamped then at work (as now!), so I figured I'd just let Mark do it, since he started the q with generalities, I figured he'd follow up with specifics, but that didn't happen.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.