?
Solved

using function to process string makes query very slow

Posted on 2011-05-02
18
Medium Priority
?
151 Views
Last Modified: 2012-05-11
I'm using SQL 2005. I have a query that returns a string from a table that is usually the name of the County. But a few of the entries have something added to the County field that I want to strip out. So I made a function to process the string. If one of a couple of patterns are matched, an altered string is returned from the function, ELSE the passed in string is returned unaltered. This lets me use the function in several queries and have only one point of maintenance.

But the query takes 100 times longer to run with the function call in the query. Seems like the query is processing all the strings through the function first, then running applying the where clause to restrint the result set. This is the case whether or not the function processed string is included in the where clause.

Any ideas how I can either make this function perform well or otherwise create reusable code to alter these County names, other than more data (another column to the original table with the corrected County name in it, or a lookup table with the wrong name and the corrected name in two columns, etc.)?
0
Comment
Question by:GordonPrince
  • 11
  • 7
18 Comments
 
LVL 11

Expert Comment

by:David Kroll
ID: 35504608
Can you show us the query and the code of your function?
0
 
LVL 4

Author Comment

by:GordonPrince
ID: 35504684
I've attached both in the CODE window.
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER OFF
GO

CREATE FUNCTION [dbo].[FixedCountyName] (@strDesc varchar(50))  
RETURNS varchar(50)
AS  
BEGIN
  DECLARE @Desc varchar(50)
	IF left(@strDesc, 7) = 'SHELBY-' set @Desc='SHELBY'
	ELSE IF left(@strDesc, 10) ='ANDERSON -' OR left(@strDesc, 9) ='ANDERSON-' set @Desc='ANDERSON'
	ELSE IF @strDesc = 'HAMBLENINMO' set @Desc='HAMBLEN'
	ELSE IF left(@strDesc, 8) ='OBION-IN' OR left(@strDesc, 9) ='OBION NOT'  set @Desc='OBION'
	ELSE IF left(@strDesc, 11) ='WILLIAMSON ' OR left(@strDesc, 11) ='WILLIAMSON-' set @Desc='WILLIAMSON'
	ELSE set @Desc=@strDesc
	RETURN(@Desc)
END
/*
SELECT JurisID, jurCounty, dbo.FixedCountyName(jurCounty) FixedCounty, JurDesc, NoTaxes, jurState, jurCity, jurOth
FROM  Jurisdiction
WHERE (jurCounty LIKE 'WILLIAMSON%')

select top 9 * from AllOutOfTown2011 where jurState = 'TN' and jurCounty like 'SHELBY%'
alter view AllOutOfTown2011 SCHEMABINDING 
create index ix_All2011_County on AllOutOfTown2011(State, County)

SELECT JurisID, JurDesc, jurState, jurCounty
FROM  Jurisdiction where JurCounty like 'WILLIAMSON%' 

If strState = "TN" Then
    If Left(strCounty, 6) = "SHELBY" Then
        FixCountyName = "SHELBY"
    ElseIf Left(strCounty, Len("ANDERSON")) = "ANDERSON" Then
        FixCountyName = "ANDERSON"
    ElseIf Left(strCounty, Len("OBION")) = "OBION" Then
        FixCountyName = "OBION"
    ElseIf Left(strCounty, Len("WILLIAMSON")) = "WILLIAMSON" Then
        FixCountyName = "WILLIAMSON"
    Else
        FixCountyName = strCounty
    End If
Else
    FixCountyName = strCounty
End If
*/
GO

Open in new window

0
 
LVL 4

Author Comment

by:GordonPrince
ID: 35504692
Sorry, too much code. The function is at the top of the code window, the first select statement in the comments is all the query you need. The rest is notes to myself while working on using the function, so you can ignore that.
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 11

Expert Comment

by:David Kroll
ID: 35504699
Nothing to do with your code Gordon, but are you in the property tax business?
0
 
LVL 4

Author Comment

by:GordonPrince
ID: 35504710
My client is, yes. Why. Are you?
0
 
LVL 11

Expert Comment

by:David Kroll
ID: 35504717
Yes I am.
0
 
LVL 11

Expert Comment

by:David Kroll
ID: 35504728
In the case of Williamson, it seems that you know the first 10 characters are "WILLIAMSON", so instead of using your function, just return the 10 left characters.


SELECT JurisID, jurCounty, left(jurCounty, 10) as FixedCounty, JurDesc, NoTaxes, jurState, jurCity, jurOth
FROM  Jurisdiction
WHERE (jurCounty LIKE 'WILLIAMSON%')

Open in new window

0
 
LVL 11

Expert Comment

by:David Kroll
ID: 35504748
It looks like your counties have no uniformity, some having dashes or spaces and including extra characters.  Instead of having to use a function full of IF statements for every query, can you run an update once to clean up all of the county names?
0
 
LVL 4

Author Comment

by:GordonPrince
ID: 35504784
The problem is the names of the counties can't be changed. Users came up with the strategy of using this "enhanced" county name for some other business reasons. I'm trying to come up with a way of making clean queries without making another data column somewhere -- one with the clean county name, one with the "enhanced" county name. The function seemed easy enough. But the performance was so bad.
0
 
LVL 11

Expert Comment

by:David Kroll
ID: 35504808
Try changing those IF statements in the function to a CASE statement and see if that has any effect on the speed.
0
 
LVL 11

Expert Comment

by:David Kroll
ID: 35504815
Also, do you have an index on jurCounty?  Also, instead of using like 'Williiamson%', try left(jurcounty, 10) = 'Williamson'
0
 
LVL 4

Author Comment

by:GordonPrince
ID: 35504842
The thing is I don't know which county I'm going to query on. I've created a view that users run queries on. It's not practical to have them select which of the multiple queries to use based on which county they are interested in. Aso, sometimes they want a list with multiple counties in it, and each should be cleaned up. So it needs to be in one place.

I suppose I could put the set of IF statements from the function into the definition of the view as a CASE statement. But then I have to keep copying the CASE statement into multiple views, defeating my strategy of only putting each set of logic in one place.
0
 
LVL 11

Expert Comment

by:David Kroll
ID: 35504880
It seems to me that the non-uniformity of this column is going to cause you major headaches unless you create a new field with the cleaned up county name.  What's going to happen when a new county gets added?  You're going to have to change the code in your function and that doesn't seem very productive to me.  Is there a reason you don't want to add a new column?
0
 
LVL 4

Author Comment

by:GordonPrince
ID: 35504918
Only that the list doesn't change much -- it's a list of Counties. I mean, County names don't change very often. So a few are added every few months, and so far out of 5000 of them, only 6 have funny names that I want to correct. I was just holding off on making another data column since 99.99% of the data is the same in each of the columns. It seemed the call to the function to clean up the 6 or so out of 5,000 items was a low maintenance way of doing this. But I was surprised that it had such a negative impact on the performance.

I would have thought a CASE statement in a SQL statement would have processed about the same as a set of IF statements inside a function. But apparently that's not the case.
0
 
LVL 11

Accepted Solution

by:
David Kroll earned 2000 total points
ID: 35504951
How many records are in the Jurisdiction table?  Have you run the query without the function call, just to see how fast it runs?  I also like to use (NOLOCK), helps speed up queries quite a bit.
SELECT JurisID, jurCounty, JurDesc, NoTaxes, jurState, jurCity, jurOth
FROM  Jurisdiction (NOLOCK)
WHERE (jurCounty LIKE 'WILLIAMSON%')

Open in new window

0
 
LVL 4

Author Comment

by:GordonPrince
ID: 35505037
There are 5212 rows in the Jurisdiction table.

Checking the indexes, turns out there's not one of the County field. I added one. Now the query performs great and the view (with 7 tables in the definition) performs great. So I was overlooking the basics. Good idea, dkrollCTN.
0
 
LVL 11

Expert Comment

by:David Kroll
ID: 35505047
No problem Gordon, glad I could help.
0
 
LVL 11

Expert Comment

by:David Kroll
ID: 35505878
Gordon, please make sure to accept my solution and award points. :)
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question