Smart questions
Smart answers
Smart people
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login
Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

LINK TO THIS FORUM!

Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

Tek-Tips LinkedIn® Group

Partner With Us!

"Best Of Breed" Forums Add Stickiness To Your Site
Partner Button
(Download This Button Today!)

Feedback

"...I signed up to your site to get help with a problem and I am so glad I did. I found the help I needed immediately. Thanks to all who contribute to your site..."
More...

Geography

Where in the world do Tek-Tips members come from?
Click Here To Find Out!

Tek-Tips Shirts!

Get Tek-Tips Swag
Get your
Tek-Tips Forums
SWAG here!
Home > Forums > Programmers > DBMS Packages > Microsoft SQL Server: Programming Forum

Why is function so slow?

thread183-1688427
Read More Threads Like This One
djj55 (Programmer)
17 Jul 12 8:23
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

CODE

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

CODE

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 (MIS)
17 Jul 12 8:46

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

CODE

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 (Programmer)
17 Jul 12 9:07
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.

CODE

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 (Programmer)
17 Jul 12 9:11
I just realized that the function I posted doesn't do the zero padding. Here is a revised version.

CODE

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 (Programmer)
18 Jul 12 9:00
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 (Programmer)
18 Jul 12 9:25
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 (Programmer)
18 Jul 12 9:29
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!

Read More Threads Like This One

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Back To Forum

Back To Microsoft SQL Server: Programming

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close