Increase speed - Scalar Function

  • 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

  • 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


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

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

  • 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


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Never used ITVF before, so how do i replace my function by the ITVF? 😀

  • 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

  • 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


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • 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... :Whistling:

    The all procedure now takes 3 seconds 🙂

    Thanks a lot.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply