1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Trying to speed up a SQL function at work

Discussion in 'HTML, Graphics & Programming' started by B@Th*nG, May 2, 2018.

  1. B@Th*nG

    Soldato

    Joined: Oct 18, 2002

    Posts: 6,107

    Hi all,

    i'm looking to speed up a process LENGTHY at work and i don't know the best way to go about it. It's just to produce some reports for our call logging software, this scalar function brings in the hours remaining on a job. Does anyone have any advice at all? Would limiting the character size of the variables have a noticeable effect?

    Code:
    --get the time left on an issue
    =============================================
    ALTER FUNCTION [dbo].[GetHoursLeftFULL]
    (
        @IssueID int
    )
    RETURNS DECIMAL(20,10)
    AS
    BEGIN
        -- Declare the return variable here
        DECLARE @HoursLeftFULL DECIMAL(20,10)
      
        declare @HoursElapsed decimal(20,10)
    declare @startdate varchar(20)
    declare @enddate varchar(20)
    declare @motion varchar(10) = 'Halt' --or 'Moving'
    declare @engine varchar(10) = 'Stopped' --or 'Running'
    declare @brakes varchar(10) = 'Applied' --or 'Released'
    declare @mark varchar (20)
    declare @seq smallint = 1
    declare @entryline smallint = 1
    declare @time datetime
    declare @CompSLAHrs DECIMAL(20,10)
    
    set @startdate = (select CAST(min(created) as date) from [VS019-GEMINI].[GEMINI].[dbo].gemini_issuehistory where issueid = @issueid)
    set @enddate = (select CAST(GETDATE() as date))
    
    declare @IssueEventsTemp table
    (
        seq int,
        issueid int,
        history nvarchar(250),
        [time] datetime,
        mark nvarchar(30),
        entryline int,
        motion nvarchar(30),
        timeaccruedsecs int
    );
    
    
    
    with mycte as
         (
             select cast(@startdate as datetime) DateValue
             union all
             select DateValue + 1
             from    mycte 
             where   DateValue < @enddate
         )
      
    Insert into @IssueEventsTemp
    SELECT ROW_NUMBER() over (ORDER BY time asc) as seq, *
    FROM
    (
        select @issueid issueid, 'BRAKES OFF' as history, dateadd(minute, 540, datevalue) as 'Time', 'BRAKES OFF' as 'mark', NULL as entryline, '          ' as motion, 0 as TimeAccruedSecs
        from mycte
        where LEFT(datename(weekday, DateValue), 3) not in ('Sat', 'Sun')
        and CAST(DateValue as date) not in (select holiday from [VS019-GEMINI].[GEMINI].[dbo].ListofHolidays)--prevents brakes off/on for holidays
        UNION ALL
        select @issueid, 'BRAKES ON' as history, dateadd(minute, 1050, datevalue) as 'Time', 'BRAKES ON' as 'mark', NULL as entryline, '          ' as motion, 0 as timeaccruedsecs
        from mycte
        where LEFT(datename(weekday, DateValue), 3) not in ('Sat', 'Sun')
        and CAST(DateValue as date) not in (select holiday from [VS019-GEMINI].[GEMINI].[dbo].ListofHolidays)--prevents brakes off/on for holidays
        UNION ALL
        select issueid, history, created as 'Time', case when history in ('Created','Issue status changed to Acknowledged', 'Issue status changed to Assigned', 'Issue status changed to Development Investigation','Issue status changed to Reopened', 'Issue status changed to Escalated', 'Issue status changed to In Progress') then 'ENGINE ON' ELSE 'ENGINE OFF' end as 'mark', NULL as entryline, '          ' as motion, 0 as timeaccruedsecs
         from [VS019-GEMINI].[GEMINI].[dbo].gemini_issuehistory
        where issueid = @issueid
        and (history = 'created' or history like 'Issue status%')
        UNION ALL
        select @issueid, 'ENGINE OFF', GETDATE(), 'ENGINE OFF', NULL, '', 0 --this line adds an engine off at the time of calling this function
    ) as data
    WHERE EXISTS (select issueid from KPI2015 where timetostart = 0)
    order by Time
    OPTION (MAXRECURSION 0)
    
    
    
    --adding in the marks
    while (@seq <= (select MAX(seq) from @IssueEventsTemp))
    begin
    set @mark = (select mark from @IssueEventsTemp where seq = @seq)
    set @time = (select Time from @IssueEventsTemp where seq = @seq)
    
    if @mark = 'ENGINE ON'
    begin
    set @engine = 'Running'
    end
    
    
    if @mark = 'ENGINE OFF'
    begin
    set @engine = 'Stopped'
    end
    
    
    if @mark = 'BRAKES OFF'
    begin
    set @brakes = 'Released'
    end
    
    
    if @mark = 'BRAKES ON'
    begin
    set @brakes = 'Applied'
    end
    
    if (@brakes = 'Released' and @engine = 'Running')
    BEGIN
    set @motion = 'Moving'
    END
    
    if NOT (@brakes = 'Released' and @engine = 'Running')
    BEGIN
    set @motion = 'Halt'
    END
    
    --first time we get a Moving
    IF (@motion = 'Moving' and @entryline = 1)
    BEGIN
    Update @issueeventstemp
    set motion = 'Moving', entryline = @entryline
    where seq = @seq
    set @entryline +=1
    goto wayout --exit the function
    END
    
    --other times we get a moving only write log if previous entry was a halt
    IF (@motion = 'Moving' and @entryline <> 1)
    BEGIN
        IF (select motion from @IssueEventsTemp where entryline = @entryline -1) = 'Halt'
            BEGIN
                Update @issueeventstemp
                set motion = 'Moving', entryline = @entryline
                where seq = @seq
                set @entryline +=1     
            END
    goto wayout --exit the function
    END
    
    --if we get a halt, only write if previous entry was a moving
    IF @motion = 'Halt'
    BEGIN
        IF (select motion from @IssueEventsTemp where entryline = @entryline -1) = 'Moving'
            BEGIN
                Update @issueeventstemp
                set motion = 'Halt', entryline = @entryline
                where seq = @seq
                set @entryline +=1     
            END
    END
    
    wayout:
    set @seq +=1
    END
    
    --now add in the time accrued
    update @issueeventstemp
    set [@issueeventstemp].[timeaccruedsecs] = zxc.timeaccruedsecs
    from @issueeventstemp
    join
    (select entryline, DATEDIFF(SECOND, (select time from @IssueEventsTemp where entryline = it.entryline-1), time) as timeaccruedsecs
    from @IssueEventsTemp it
    where motion = 'Halt') as zxc
    on [@issueeventstemp].[entryline] = zxc.entryline
    
    
    set @HoursElapsed =  (select SUM(TimeAccruedSecs)/3600.00 from @IssueEventsTemp)
    
    
    
    --get the time to complete SLA time
    
    set @CompSLAHrs = (select --gi.projectid, RIGHT(gip.prioritydesc, 2) as 'priority', gp.projectcode,
    case when RIGHT(gip.prioritydesc, 2)='P1' then sla.P1Comp when RIGHT(gip.prioritydesc, 2)='P2' then sla.P2Comp when RIGHT(gip.prioritydesc, 2)='P3' then sla.P3Comp when RIGHT(gip.prioritydesc, 2)='P4' then sla.P4Comp end as SLAHours
     from
    [VS019-GEMINI].[GEMINI].[dbo].gemini_issues gi
    join [VS019-GEMINI].[GEMINI].[dbo].gemini_projects gp
    on gi.projectid = gp.projectid
    join [VS019-GEMINI].[GEMINI].[dbo].gemini_issuepriorities gip
    on gi.issuepriorityid = gip.priorityid
    join [VS019-GEMINI].[GEMINI].[dbo].zzsla sla
    on gi.projectid = sla.projectid
    where issueid = @issueid)
      
    
        -- Add the T-SQL statements to compute the return value here
        SELECT @HoursLeftFULL = @CompSLAHrs-@HoursElapsed
    
        -- Return the result of the function
        RETURN @HoursLeftFULL
    
    END
    B@
     
  2. AHarvey

    Sgarrista

    Joined: Mar 6, 2008

    Posts: 8,598

    Location: Stoke area

    Last edited: May 2, 2018
  3. Spunkey

    Capodecina

    Joined: Oct 18, 2002

    Posts: 13,027

    Location: The land of milk & beans

    Have you profiled the query to see where the choke points are? You can then start optimising those areas specifically.

    Next to none I'd expect.
     
  4. Hades

    Capodecina

    Joined: Oct 19, 2002

    Posts: 19,583

    Location: Surrey and London

    Have you looked at the indexes on the table?
     
  5. billysielu

    Sgarrista

    Joined: Aug 9, 2009

    Posts: 9,195

    Location: Oxfordshire

    1. Look at the execution plan, see what part of it is taking up the time. This may suggest missing indexes.
    2. Run the database engine tuning advisor on your workload. This may suggest missing indexes or missing statistics.

    My gut feeling is you need to reduce the data on a daily/regular basis. Then report on this reduced data instead.
     
  6. Gornall

    Wise Guy

    Joined: Jan 2, 2007

    Posts: 1,873

    Feels more like this should be a procedure than a in-line/scalar function?

    How often is this run?

    Take a look at lead and lag? That's usually an effective way of building up cumulative totals from a series of transactions/records.
     
  7. Shoeyuk

    Gangster

    Joined: Jan 31, 2018

    Posts: 161

    Location: Bury St Edmunds

    Run with the actual execution plan, as you can see where the expensive parts of your queries are and suggested indexes that can be added.
     
  8. skyripper

    Wise Guy

    Joined: Jul 19, 2011

    Posts: 1,495

    Update the calculated durations whenever a save is made, rather than whenever the page/data report is retrieved.
    Then your page report can work out the time remaining itself, rather than asking the database to do it.

    If you can't do that, then you'll need to look at optimising the retrieval of the data as quickly as possible (reducing the amount of data needed to look thru, optimising tablespaces, in-memory processing etc.)
     
    Last edited: May 2, 2018
  9. B@Th*nG

    Soldato

    Joined: Oct 18, 2002

    Posts: 6,107

    ok, thanks guys, i'll give this a go next week, when the data isn't required by the boss, and report back with findings!

    B@
     
  10. touch

    Capodecina

    Joined: Oct 28, 2006

    Posts: 10,278

    Location: Sufferlandria

    I'd say you need to have a look at the logic before doing any profiling/optimising of the code you have. I can't tell without knowing your data, but surely there's a more efficient way of writing that?