Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2003
    Posts
    30

    Lightbulb Unanswered: Need help optizing a user defined function

    Hi,
    I posted a question sometime back about any one has written a function which converts a given date to a a new timezone.

    As it turns out I have actually written the function and it is functionally sound.

    the issue I am not facing is the performace.

    The Algorithm is pretty simple.
    step 1
    I have create a table called TZ1 which has the TImezoneCODE (PST,EST..), daylightsavingsstart (DATETIME on which the daylight savings start), daylightsavingsend ( end of DLS) and offset (EG -8 for PST, -5 for EST etc)

    step 2
    I check the timezone and pick out corresponding start and enddate for daylight savings.

    Step 3
    Depending on where the date lies I create a new date by adding offset to the original datetime (+1) for the new date and then return this new datetime.

    As simple as this looks the response time is 16 times than that of a select statement without the function.

    Do you see something that I can tune to get a better response time.

    Heres my code


    ================================================== ================================================== ================================================== ================================================== ==================================

    CREATE FUNCTION TC3
    -- drop function tc3
    (@dt datetime, @Src varchar(10), @Dest varchar(10))
    Returns datetime
    begin
    declare @v_src varchar(10)
    Declare @v_dest varchar(10)
    declare @V_dt datetime
    declare @v_newdt datetime
    declare @v_year int
    declare @v_offset float
    declare @v_sd datetime
    declare @v_ed datetime

    -- select * into TZ1 from TZ1
    -- create table #tz1 (fn int)

    select @v_year = datepart (year, @dt)

    if (@v_year between 2000 and 2004)
    begin
    -- -------------------
    -- PST BEgin
    -- -------------------
    if (@dest = 'PST')
    begin
    -- select * from TZ1
    select @V_year = datepart (year, @dt)

    select @v_offset = offset,
    @v_sd = daylightsavingsstart,
    @v_ed = daylightsavingsend
    from TZ1
    where
    @V_YEAR = YEAR
    and timezonecode = @dest
    -- AND @DT between daylightsavingsstart and daylightsavingsend

    if (@dt between @v_sd and @v_ed )
    begin
    -- ( select @v_newdt = dateadd (hh, -9, @dt) )
    select @v_newdt = dateadd (hh, CAST( @v_offset as int)-1 , @dt)
    end
    else
    begin
    -- ( select @v_newdt = dateadd (hh, -8, @dt) )
    select @v_newdt = dateadd (hh, @v_offset, @dt)
    end -- if dt between daylight savings

    return @v_newdt
    end -- @dest=pst end
    -- -------------------
    -- PST END
    -- -------------------
    -- -------------------
    -- EST BEGIN
    -- -------------------

    ELSE

    if (@dest = 'EST')
    begin

    select @V_year = datepart (year, @dt)

    select @v_offset = offset,
    @v_sd = daylightsavingsstart,
    @v_ed = daylightsavingsend
    from TZ1
    where
    @V_YEAR = YEAR
    and timezonecode = @dest

    if (@dt between @v_sd and @v_ed )
    begin
    -- ( select @v_newdt = dateadd (hh, -9, @dt) )
    select @v_newdt = dateadd (hh, CAST( @v_offset as int)-1 , @dt)
    end
    else
    begin
    -- ( select @v_newdt = dateadd (hh, -8, @dt) )
    select @v_newdt = dateadd (hh, @v_offset, @dt)
    end -- if dt between daylight savings


    return @v_newdt

    end -- @dest=Est end

    -- -------------------
    -- EST END
    -- -------------------

    -- -------------------
    -- HST BEgin
    -- select * from timezones
    -- -------------------
    else
    if (@dest = 'HST')
    begin

    select @V_year = datepart (year, @dt)

    select @v_offset = offset,
    @v_sd = daylightsavingsstart,
    @v_ed = daylightsavingsend
    from TZ1
    where
    @V_YEAR = YEAR
    and timezonecode = @dest

    if (@dt between @v_sd and @v_ed )
    begin
    -- ( select @v_newdt = dateadd (hh, -9, @dt) )
    select @v_newdt = dateadd (hh, CAST( @v_offset as int) , @dt)
    end
    else
    begin
    -- ( select @v_newdt = dateadd (hh, -8, @dt) )
    select @v_newdt = dateadd (hh, @v_offset, @dt)
    end -- if dt between daylight savings


    return @v_newdt

    end -- @dest=Ast end

    -- -------------------
    -- HST END
    -- -------------------

    -- -------------------
    -- MST BEgin
    -- select * from timezones
    -- -------------------
    else
    if (@dest = 'MST')
    begin

    select @V_year = datepart (year, @dt)

    select @v_offset = offset,
    @v_sd = daylightsavingsstart,
    @v_ed = daylightsavingsend
    from TZ1
    where
    @V_YEAR = YEAR
    and timezonecode = @dest

    if (@dt between @v_sd and @v_ed )
    begin
    -- ( select @v_newdt = dateadd (hh, -9, @dt) )
    select @v_newdt = dateadd (hh, CAST( @v_offset as int)-1 , @dt)
    end
    else
    begin
    -- ( select @v_newdt = dateadd (hh, -8, @dt) )
    select @v_newdt = dateadd (hh, @v_offset, @dt)
    end -- if dt between daylight savings

    return @v_newdt

    end -- @dest=Mst end

    -- -------------------
    -- MST END
    -- -------------------

    else
    return '19990909'
    end -- year end

    return @v_newdt


    end





    ================================================== ================================================== ================================================== =============================================

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    The problem is that if you include this function in the select list then it is called once for every record processed. If you process a 10,000 records, you are executing these queries 10,000 times.

    Try to take the same logic and implement it in a set-based solution by joining your data table directly to your time zone table.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •