Post reply

UDF Issue

  • February 18, 2008 at 12:45 pm

    #182189

    Hi,

    I am facing a situation where in the UDF takes more time when compared to the base query itself.

    UDF Definition

    CREATE FUNCTION [fn_GetStateByZip] (@ZipCode varchar(5))

    RETURNS varchar(2)

    AS

    BEGIN

    RETURN(SELECT DISTINCT State FROM zipcodes WHERE Zipcode = @ZipCode)

    END

    DECLARE @START datetime

    SELECT @START = getdate()

    SELECT fn_GetStateByZip('37814')

    PRINT 'Time taken in ms: ' + CAST(DATEDIFF(ms,@START,getdate()) as char(10));

    Output is Time taken in ms: 16

    DECLARE @START datetime

    SELECT @START = getdate()

    SELECT DISTINCT STATE FROM zipcodes where zipcode ='37814'

    PRINT 'Time taken in ms: ' + CAST(DATEDIFF(ms,@START,getdate()) as char(10));

    Output is Time taken in ms: 0

    I ran this test for a couple of times and still get the same kind of result

    The zipcodes table have the following indexes

    CREATE CLUSTERED INDEX [IX_ZIPCODE] ON [Zipcodes]

    (

    [ZipCode] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    CREATE NONCLUSTERED INDEX [IX_STATE] ON [Zipcodes]

    (

    [State] ASC

    )

    INCLUDE ( [ZipCode]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    Any help is appreciated

    Thanks

    Vinoj

  • February 18, 2008 at 12:49 pm

    #779435

    Cross post...

    http://www.sqlservercentral.com/Forums/Topic457027-8-1.aspx?Update=1

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • February 18, 2008 at 1:11 pm

    #779444

    I would make this a stored procedure, unless you are calling this proceedure from select statements and where clauses.


    My blog: http://jahaines.blogspot.com

Viewing 3 posts - 1 through 2 (of 2 total)

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