1

My procedure is as follows:

    
set [tb1].[SLD_VAR] =               case when [CalendarDate] = [tb1].[FileDate] and ([tb2].[SLD] - [tb2].[LAG_SLD])/([tb2].[SLD]+1) >= 0.15     then 1 else 0 end, 
    [tb1].[StartingDate_VAR] =      case when [CalendarDate] = [tb1].[FileDate] and [tb2].[StartingDate] <> [tb2].[LAG_StartingDate]            then 1 else 0 end,
    [tb1].[ClosingDate_VAR] =       case when [CalendarDate] = [tb1].[FileDate] and [tb2].[ClosingDate] <> [tb2].[LAG_ClosingDate]              then 1 else 0 end,
    [tb1].[CurrentStatus_VAR] =     case when [CalendarDate] = [tb1].[FileDate] and [tb2].[CurrentStatus] <> [tb2].[LAG_CurrentStatus]          then 1 else 0 end,
    [tb1].[Rate_VAR] =              case when [CalendarDate] = [tb1].[FileDate] and [tb2].[Rate] <> [tb2].[LAG_Rate]                            then 1 else 0 end,
    [tb1].[COF_VAR] =               case when [CalendarDate] = [tb1].[FileDate] and [tb2].[COF] <> [tb2].[LAG_COF]                              then 1 else 0 end,
    [tb1].[Term_VAR] =              case when [CalendarDate] = [tb1].[FileDate] and [tb2].[Term] <> [tb2].[LAG_Term]                            then 1 else 0 end,
    [tb1].[Term_GT_VAR] =           case when [CalendarDate] = [tb1].[FileDate] and [tb2].[Term_GT] <> [tb2].[LAG_Term_GT]                      then 1 else 0 end

from [DataTable]     as [tb1]

left join (
    
    select       [Filedate]
                ,cast([Deal] as inT) as [Deal]
                ,[SLD]
                ,[CurrentStatus]
                ,[StartingDate]
                ,[ClosingDate]
                ,[Rate]
                ,[COF]
                ,[Term],
                DATEDIFF(dd,[ApplicationDate],[ClosingDate])                                                       as [Term_GT],
                LAG([StartingDate],1)                               over (partition BY [Deal] order by [FileDate]) as [LAG_StartingDate],
                LAG([ClosingDate],1)                                over (partition BY [Deal] order by [FileDate]) as [LAG_ClosingDate],
                LAG([CurrentStatus],1)                              over (partition BY [Deal] order by [FileDate]) as [LAG_CurrentStatus],
                LAG([Rate],1)                                       over (partition BY [Deal] order by [FileDate]) as [LAG_Rate],
                LAG([COF],1)                                        over (partition BY [Deal] order by [FileDate]) as [LAG_COF],
                LAG([SLD],1)                                        over (partition BY [Deal] order by [FileDate]) as [LAG_SLD],
                LAG([Term],1)                                       over (partition BY [Deal] order by [FileDate]) as [LAG_Term],
                LAG(DATEDIFF(dd,[ApplicationDate],[ClosingDate]),1) over (partition BY [Deal] order by [FileDate]) as [LAG_Term_GT]

    from        [DataTable]
                    
    where       [FileDate] >= DATEADD( day, -14, @Date)
    
    )  as [tb2]

on          [tb1].[Filedate]        = [tb2].[Filedate] 
and         [tb1].[Deal]            = [tb2].[Deal]

where       [tb1].[CalendarDate]    >= @DateProd

end 

Having CalendarDate and Dealtogether as PrimaryKey, I had created some indexes but it still took quite a long time to execute and I need to run this procedure on a weekly basis. Please help me with some strategy to optimize this.

0

1 Answer 1

3

The first thing I would do is split this up a bit so you know which part is the slowest. To do that, you'll wanna grab actual execution plans for both. You can follow the instructions here to do that:

Once you have those, you can use Paste The Plan to share them if you still need help.

You're using SQL Server 2014, which sucks, because there are performance features and query plan diagnostics in newer versions that would really help to figure out where and why things are slow.

First, dump the contents of the join to a #temp table. I'm not sure why you need a left join here, but feel free to remove the EXISTS subquery at the end if there's a good reason to evaluate rows that aren't there.

SELECT
    Filedate,
    Deal = CAST(Deal AS int),
    SLD,
    CurrentStatus,
    StartingDate,
    ClosingDate,
    Rate,
    COF,
    Term,
    Term_GT = 
        DATEDIFF(dd, ApplicationDate, ClosingDate),
    LAG_StartingDate = 
        LAG(StartingDate, 1) OVER 
        (
            PARTITION BY
                Deal
            ORDER BY
                FileDate
        ),
    LAG_ClosingDate = 
        LAG(ClosingDate, 1) OVER 
        (
            PARTITION BY
                Deal
            ORDER BY
                FileDate
        ),
    LAG_CurrentStatus = 
        LAG(CurrentStatus, 1) OVER 
        (
            PARTITION BY
                Deal
            ORDER BY
                FileDate
         ),
    LAG_Rate = 
        LAG(Rate, 1) OVER 
        (
            PARTITION BY
                Deal
            ORDER BY
                FileDate
        ),
    LAG_COF = 
        LAG(COF, 1) OVER 
        (
            PARTITION BY
                Deal
            ORDER BY
                FileDate
        ),
    LAG_SLD = 
        LAG(SLD, 1) OVER 
        (
            PARTITION BY
                Deal
            ORDER BY
                FileDate
        ),
    LAG_Term = 
        LAG(Term, 1) OVER 
        (
            PARTITION BY
                Deal
            ORDER BY
                FileDate
        ),
    LAG_Term_GT = 
        LAG
        (
            DATEDIFF
            (
                DAY,
                ApplicationDate,
                ClosingDate
            ),
            1
        ) OVER 
        (
           PARTITION BY
               Deal
           ORDER BY
                FileDate
        )
INTO #DataTable
FROM DataTable
WHERE FileDate >= DATEADD(DAY, -14, @Date)
AND   EXISTS
(
    SELECT
        1/0
    FROM DataTable AS tb1
    WHERE tb1.Filedate = DataTable.Filedate 
    AND   tb1.Deal = DataTable.Deal
)
OPTION(RECOMPILE) /*If @Date is a local variable and not a parameter*/

Part two, doing the actual update.

UPDATE
    tb1
SET
    tb1.SLD_VAR = 
        CASE
             WHEN CalendarDate = tb1.FileDate
             AND (tb2.SLD - tb2.LAG_SLD) / (tb2.SLD + 1) >= 0.15
             THEN 1
             ELSE 0
        END,
    tb1.StartingDate_VAR = 
        CASE
             WHEN CalendarDate = tb1.FileDate
             AND  tb2.StartingDate <> tb2.LAG_StartingDate
             THEN 1
             ELSE 0
        END,
    tb1.ClosingDate_VAR = 
        CASE
             WHEN CalendarDate = tb1.FileDate
             AND  tb2.ClosingDate <> tb2.LAG_ClosingDate
             THEN 1
             ELSE 0
        END,
    tb1.CurrentStatus_VAR = 
        CASE
             WHEN CalendarDate = tb1.FileDate
             AND  tb2.CurrentStatus <> tb2.LAG_CurrentStatus
             THEN 1
             ELSE 0
        END,
    tb1.Rate_VAR = 
        CASE
             WHEN CalendarDate = tb1.FileDate
             AND  tb2.Rate <> tb2.LAG_Rate
             THEN 1
             ELSE 0
        END,
    tb1.COF_VAR = 
        CASE
             WHEN CalendarDate = tb1.FileDate
             AND  tb2.COF <> tb2.LAG_COF
             THEN 1
             ELSE 0
        END,
    tb1.Term_VAR = 
        CASE
             WHEN CalendarDate = tb1.FileDate
             AND  tb2.Term <> tb2.LAG_Term
             THEN 1
             ELSE 0
        END,
    tb1.Term_GT_VAR = 
        CASE
             WHEN CalendarDate = tb1.FileDate
             AND  tb2.Term_GT <> tb2.LAG_Term_GT
             THEN 1
             ELSE 0
        END
FROM DataTable AS tb1
JOIN #DataTable AS tb2
  ON  tb1.Filedate = tb2.Filedate
  AND tb1.Deal = tb2.Deal;
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.