How can I improve the performance of this SQL Table function by eliminating the While Loop?

I'm attempting to improve the performance of the this SQL Table Function to remove the while loop. Its part of an ETL and this part in particular is slowing it down.

Is there some fancy Update/ Cross Apply method or something so I can eliminate this loop?

ALTER FUNCTION [dbo].[udtfTcVoyageOffhireDaily](
      @paramTcVoyageOffhire typeTcVoyageOffhire READONLY
)
RETURNS @TcVoyageOffhireDaily TABLE
(
      VoyageID INT,
      TcInID INT,
      TcOutID INT,
      StartDateUtc DATETIME,
      EndDateUtc DATETIME,
      OffhireDaysPerCalendarDay numeric(28,17)
)
AS

BEGIN

DECLARE @TcVoyageOffhire dbo.typeTcVoyageOffhire;

INSERT INTO @TcVoyageOffhire
(
      VoyageOffhireConsolidatedID,
      VoyageID,
      TcInID,
      TcOutID,
      StartDateUtc,
      EndDateUtc,
      OffhireDaysPerCalendarDay
)
SELECT
      VoyageOffhireConsolidatedID,
      VoyageID,
      TcInID,
      TcOutID,
      StartDateUtc,
      EndDateUtc,
      OffhireDaysPerCalendarDay
FROM
      @paramTcVoyageOffhire;


DECLARE @VoyageOffhireConsolidatedID INT, @VoyageID INT, @TcInID INT, @TcOutID INT, @StartDateUtc DATETIME, @EndDateUtc DATETIME, @OffhireDaysPerCalendarDay NUMERIC(28,17);

SELECT TOP 1
      @VoyageOffhireConsolidatedID = VoyageOffhireConsolidatedID,
      @VoyageID = VoyageID,
      @TcInID = TcInID,
      @TcOutID = TcOutID,
      @StartDateUtc = StartDateUtc,
      @EndDateUtc = EndDateUtc,      
      @OffhireDaysPerCalendarDay = OffhireDaysPerCalendarDay
FROM
      @TcVoyageOffhire
ORDER BY
      StartDateUtc,
      EndDateUtc;


      WHILE @VoyageOffhireConsolidatedID IS NOT NULL
      BEGIN

            IF CONVERT(DATE, @StartDateUtc) = CONVERT(DATE, @EndDateUtc)
                  BEGIN

                        INSERT INTO @TcVoyageOffhireDaily (VoyageID, TcInID, TcOutID, StartDateUtc, EndDateUtc, OffhireDaysPerCalendarDay)
                        SELECT @VoyageID, @TcInID, @TcOutID, @StartDateUtc, @EndDateUtc, (DATEDIFF(SECOND, @StartDateUtc , @EndDateUtc) / CONVERT(NUMERIC(28,17), 86400)) * @OffhireDaysPerCalendarDay

                  END
            ELSE
                  BEGIN
                        
                        ;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)
                        )

                        INSERT INTO @TcVoyageOffhireDaily (VoyageID, TcInID, TcOutID, StartDateUtc, EndDateUtc, OffhireDaysPerCalendarDay)
                        SELECT @VoyageID, @TcInID, @TcOutID, StartDateUtc, EndDateUtc, OffhireDaysPerCalendarDay
                        FROM cte
                        WHERE StartDateUtc != EndDateUtc
                        OPTION (MAXRECURSION 0)

                  END


            DELETE FROM @TcVoyageOffhire WHERE VoyageOffhireConsolidatedID = @VoyageOffhireConsolidatedID;

            SET @VoyageOffhireConsolidatedID = NULL;

            SELECT TOP 1
                  @VoyageOffhireConsolidatedID = VoyageOffhireConsolidatedID,
                  @VoyageID = VoyageID,
                  @TcInID = TcInID,
                  @TcOutID = TcOutID,
                  @StartDateUtc = StartDateUtc,
                  @EndDateUtc = EndDateUtc,      
                  @OffhireDaysPerCalendarDay = OffhireDaysPerCalendarDay
            FROM
                  @TcVoyageOffhire
            ORDER BY
                  StartDateUtc,
                  EndDateUtc;

      END

RETURN;

END
Paul MaurielloSoftware Programmer Developer Analyst EngineerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mark WillsTopic AdvisorCommented:
might be better served with a calendar table - or make the CTE do more work in establishing virtual dates from @StartDateUtc to @EndDateUtc by using 'numbers' and not a recursive CTE.

Also try to make it a inline - rather than define a table, try to RETURN ( a result set)

It would take a bit of unravelling - but think it could be done using embedded CTE's  without having to select top 1 each time.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott PletcherSenior DBACommented:
Since Mark's started this, I'll let him finish.  Yes, for performance reasons, this must be turned into an inline function.  That won't be difficult, but it will result in huge performance gains.
0
Paul MaurielloSoftware Programmer Developer Analyst EngineerAuthor Commented:
Thank you
0
Scott PletcherSenior DBACommented:
It looked straightforward enough to me to simplify it and make it into an inline function, but obviously Mark didn't follow up on that.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.