Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Increase speed - Scalar Function Expand / Collapse
Author
Message
Posted Thursday, January 17, 2013 9:25 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, October 31, 2014 5:44 AM
Points: 110, Visits: 450
Hello
I have a procedure that uses a cursor and inside the cursor i have an update statement like this:

-- Update the dates with the value for the day
UPDATE @TableDates
SET
qt = @qtDay + qt,
ids = ids + ',' + CAST(@id AS NVARCHAR(15))
WHERE pac = @pac AND
dt IN ( SELECT DATEADD(DAY,N-1,@st)
FROM dbo.Numbers
WHERE N <= @diff AND dbo.CheckDayType(DATEADD(DAY,N-1,@st)) = 0)

The column dt is a datetime column and i'm using the numbers table to increase the performance, but i'm also using a scalar function that checks the day type (CheckDayType):
CREATE FUNCTION [dbo].[CheckDayType]
(@Data SMALLDATETIME)
RETURNS TINYINT
AS
BEGIN
DECLARE
@Type TINYINT,
@Holiday BIT,
@WeekDay BIT

SELECT
@Holiday = Feriado,
@WeekDay = DiaSemana
FROM dbo.Calendar
WHERE Data = @Data


IF @Holiday = 0 AND @WeekDay = 1
SET @Type = 0 -- Normal WorkDay
ELSE IF @Holiday = 0 AND @WeekDay = 0
SET @Type = 1 -- Weekend
ELSE IF @Holiday = 1 AND @WeekDay = 1
SET @Type = 2 -- Holiday
ELSE IF @Holiday = 1 AND @WeekDay = 0
SET @Type = 3 -- Holiday in the WeekEnd

RETURN @Type
END

This function it's used to check if the day it's a normal working day.
If in the procedure i remove this function, the procedure runs in 5 seconds, with the function it takes 1:45...

How can i speed up this code? It's possible? Remove if's from function?

This code it's used to retrieve a table to a client app, to show a chart with the quantities for each day...

Thanks
Post #1408496
Posted Thursday, January 17, 2013 9:42 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:09 AM
Points: 12,924, Visits: 32,323
well, the obvious fix is to use an Inline Table Valued function instead of a scalar function;
i was able to convert it easily, but the example usage you pasted is a subset of the code...i have no idea where the @st value came from, but we'd want to use a column name instead of that variable to speed things up.

CREATE FUNCTION [dbo].[CheckDayType_ITVF]
(@Data SMALLDATETIME)
RETURNS TABLE
AS
RETURN
SELECT
CASE
WHEN Feriado = 0 AND DiaSemana = 1
THEN 0
WHEN Feriado = 0 AND DiaSemana = 0
THEN 1
WHEN Feriado = 1 AND DiaSemana = 1
THEN 2
WHEN Feriado = 1 AND DiaSemana = 0
THEN 3
END AS DayType
FROM dbo.Calendar
WHERE Data = @Data
GO



if you can show the fuller code that defines the @st variable, we could help change the code to do a single set based fix


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1408506
Posted Thursday, January 17, 2013 9:59 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, October 31, 2014 5:44 AM
Points: 110, Visits: 450
Thanks for the quick reply.

I'll give it a try with the ITVF, and see if it solves my problem!?

BTW, the @st it a datetime var used by the cursor, the update statement it's inside this cursor. It gets all info for all the pac's (kind off products), the production start time @st, the production end time @ed, and the quantity @qt, and the @id.

The cursor runs through all the pac's, gets the difference (@diff), between the start date and the end date in working days, divide the quantity for the number of days (@qtDay), then i update every date for the @pac with the @qtDay and with the @Id, the ids, it's just to know what pac are summing in the date.

I could put here all the code, but it has several lines before this phase and everything it's connected and it will be complicated to explain every line...
Post #1408521
Posted Thursday, January 17, 2013 10:11 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:09 AM
Points: 12,924, Visits: 32,323
the cursor is your # 1 performanceproblem then.
i'm sure you can replace it with a set based function:
if you are doing the same logic to each row in the cursor, you can replace it.

the only decent reason to use a cursor is if you are fiddling with metadata to build commands agaisnt tables and stuff,, or if you are feeding row results to a procedure (like sp_send_DbMail, for example)

post it if you want to speed things up by a few orders of magnitude or more. i've seen things that took 12 hours replaced by a one second set based operation doing the same thing, which is what SQl server is all about...using SETS of data.


i liken it to moving apples from one spot to another: a cursor moves the apples one by one, but the set based moves the BOX of apples;
the time to move one apple is almost the same as moving the whole box...the more apples you were moving one by one, the more time you'd save.


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1408531
Posted Thursday, January 17, 2013 10:11 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, October 31, 2014 5:44 AM
Points: 110, Visits: 450
Never used ITVF before, so how do i replace my function by the ITVF?
Post #1408532
Posted Thursday, January 17, 2013 10:17 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, October 31, 2014 5:44 AM
Points: 110, Visits: 450
Hehhehe
The cursor code:
DECLARE curD CURSOR FOR
SELECT pac,st,ed,qt,idpac
FROM @TabelaPACS
ORDER BY pac

OPEN curD
FETCH NEXT FROM curD INTO @PAC,@st,@ed,@Qt,@id
WHILE @@FETCH_STATUS = 0
BEGIN

-- Retrieve workingdays
SELECT @Diff = dbo.workingDays(@st,@ed,0,0)
SET @qtDay = @Qt/(CASE WHEN @qtDay = 0 THEN 1 ELSE @qtDay END)

UPDATE @TabelaDates
SET
ids = ids + ',' + CAST(@id AS NVARCHAR(10)),
qt = @qtDay + qt
WHERE pac = @pac AND data IN (
SELECT DATEADD(DAY,N-1,@st)
FROM dbo.Numbers
WHERE N <= @Diff AND dbo.VerificaTipoDia(DATEADD(DAY,N-1,@st)) = 0)


FETCH NEXT FROM curD INTO @PAC,@st,@ed,@Qt,@id
END
CLOSE curD
DEALLOCATE curD

Post #1408536
Posted Thursday, January 17, 2013 10:19 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:09 AM
Points: 12,924, Visits: 32,323
rootfixxxer (1/17/2013)
Never used ITVF before, so how do i replace my function by the ITVF?


without the code i mentioned previously, it's really hard to guess;
since i cannot test this, it might give you an idea;
you use a CROSS APPLY with an ITVF:
UPDATE MyTable
SET
MyTable.qt = @qtDay + qt,
MyTable.ids = ids + ',' + CAST(@id AS NVARCHAR(15))
FROM @TableDates MyTable
CROSS APPLY dbo.CheckDayType_ITVF(DATEADD(DAY,N-1,dt)) myfn
WHERE pac = @pac
AND myfn = 0



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1408537
Posted Thursday, January 17, 2013 11:04 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, October 31, 2014 5:44 AM
Points: 110, Visits: 450
Thanks
I was using the CROSS APPLY, but to test i was using the GETDATE that returns date and time so the ITVF doesn't returned anything...

The all procedure now takes 3 seconds :)
Thanks a lot.
Post #1408551
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse