Trying to speed up a SQL function at work

B@Th*nG · 2 May 2018 at 10:25

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@

AHarvey · 2 May 2018 at 11:05

Normally the best ways are reducing table size that you are referring too, maybe pull the data into a working table or to check your joins.

We often start by printing the time between each section of the code, this will help narrow down where it's taking longest.

A couple of useful links but you can get whole books on this subject.

https://community.modeanalytics.com/sql/tutorial/sql-performance-tuning/
http://geekswithblogs.net/Martinez/...sql-top-may-slow-down-your-query-and-how.aspx

GravyMonster · 2 May 2018 at 11:12

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

Would limiting the character size of the variables have a noticeable effect?

Next to none I'd expect.

Hades · 2 May 2018 at 13:47

Have you looked at the indexes on the table?

Randomface74 · 2 May 2018 at 14:12

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.

Gornall · 2 May 2018 at 16:25

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.

Shoeyuk · 2 May 2018 at 16:27

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.

skyripper · 2 May 2018 at 16:32

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.)

B@Th*nG · 3 May 2018 at 09:43

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@

touch · 3 May 2018 at 10:35

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?

dasyad · 12 Jul 2018 at 23:12

Is this for SQL Server? Scalar functions are notoriously slow and looking at this one I don’t think it’s the right tool for the job at all. Table variables can be problematic as well, again when they’re being misused.

I think what you actually want here is a stored procedure to return not only the SLA but other details regarding the issue or any other reporting details. If you really do need re-usable logic for the SLA, to use it in other queries, then look into re-writing it as a table valued function as they can be inlined by the optimiser.

Tee Hee Johnson · 14 Jul 2018 at 11:07

How is your tempdb configured, correctly set up it can make a huge difference!