Why is function so slow?

[djj55]

Hello, SQL 2008 R2
Because the person who created the database table used Microsoft conversion from Access to SQL 2000 our zip codes are now of data type FLOAT. We are living with this but we have one process that pulls over 2 million records into a table used for reports but it takes a long time to run.
So breaking down the query one thing I found I needed an index which I created. But when I went a little further testing a few columns at a time I found that before the function call database.dbo.udf2_CnvtZip5(Zip) the query took 1:38 after 4:04. So I code the conversion in the query

CASE 
    WHEN ISNULL(Zip, 0) = 0 THEN NULL 
    WHEN Zip > 99999.0 THEN RIGHT('00000' + CAST(CAST(Zip/10000 AS INT) AS VARCHAR), 5) 
    ELSE RIGHT('00000' + CAST(CAST(Zip AS INT) AS VARCHAR), 5) 
END AS MyZip

which only took 1:48.

Since we have about six differenct zip codes we wanted a function, however the time to run the query is getting prohibitive. Note that the test code I was working with only had the one zip.

The function

ALTER FUNCTION [dbo].[udf2_CnvtZip5]
(
    @ZipIn float
)
RETURNS varchar(5)
AS
-- Outputs a five digit zip from Float
BEGIN
    DECLARE @ZipOut VARCHAR(25) 
    IF @ZipIn IS NULL OR @ZipIn = 0
        SET @ZipOut = NULL
    ELSE
        IF @ZipIn > 99999.0 
            SET @ZipOut = RIGHT('00000' + CAST(CAST(@ZipIn/10000 AS INT) AS VARCHAR), 5) 
        ELSE
            SET @ZipOut = RIGHT('00000' + CAST(CAST(@ZipIn AS INT) AS VARCHAR), 5) 

    RETURN @ZipOut
END

So any comments or suggestions? Converting the table is not an option at this time or I would do that in a hear beat.
Thanks

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[MarkSweetland]

For the most part, character manipulations slow things down however you implement. Not sure it would be any faster, but may try this:

declare @ZipIn float 
set @ZipIn = 32542.00023233

select @ZipIn = case when @zipIn = 0 then NULL else isNull(CONVERT(int, @zipIn) % 100000, NULL) end
select right('0000'+convert(varchar, @ZipIn), 5)

Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach

 

[gmmastros]

There are 2 different ways I would approach this.

1. Create a computed column.

You could add a computed column to the table, index the computed column, and then use the computed column in the query. By placing an index on the computed column, the values get stored in an index. There is the added overhead of another index on the table, but with only a couple million rows, this should not be a problem.

This, of course, assumes that you have the appropriate permissions to add the computed column.

2. When optimizing functions, it's important to understand what "takes the time". The slowest thing you can do in a function is to access data stored in a table. You're not doing this, which is good. The next thing to realize is that the more code you have, the slower the function will be. A function that only operates on the input variables, and only has 1 line that is returned immediately, will operate faster than the same function that has multiple lines of code to accomplish the same thing.

Alter Function dbo.gmmastros_ConvertZip(@Zip Float)
Returns VarChar(5)
AS
Begin
    Return (
      Select Case When @Zip = 0 Then NULL
                  When @Zip > 99999.0 Then Convert(Int, @Zip / 10000)
                  Else Convert(Int, @Zip)
                  End
           )
End

Optimizing the function will cause the query to run faster, but I wouldn't expect any miracles here. The computed column really is the better way to go, if you can.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

I just realized that the function I posted doesn't do the zero padding. Here is a revised version.

Alter Function dbo.gmmastros_ConvertZip(@Zip Float)
Returns VarChar(5)
AS
Begin
	Return Right('00000' + Convert(VarChar(5), (
	     Select Case When @Zip = 0 Then NULL
                     When @Zip > 99999.0 Then Convert(Int, @Zip / 10000)
                     Else Convert(Int, @Zip)
                     End
	        )), 5)
End

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[djj55]

Thanks for the replies. Got busy yesterday and was unable to test this.

Mark, thanks for the information. I still need to try yours.

George, yours did speed things up but now I am getting quick times on the old function. 2:08 old, 1:53 new, 1:52 inline code which was the first run.

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[gmmastros]

djj,

Just curious....

By "old", do you mean your original function, "new" = my suggested function, and inline = no function at all?



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[djj55]

Yes. My web interface was/is acting strange so I did not go into detail but you have it correct.
Since this is a low priority I keep getting pulled off and losing my train of thought. Hope to have some time this afternoon to look into it more.

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!