SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


functions


functions

Author
Message
Brookstone
Brookstone
SSC-Addicted
SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)

Group: General Forum Members
Points: 425 Visits: 211
Hello,

We have a select into query that has 5 functions called numerous times. The functions are merely different ways of calculating values such as extended cost, very simple stuff.

For the past several years, this query has executed in approximately 20 minutes against 3 million rows. Now it is taking 11+ hours. As far as we can tell, nothing has changed. If we remove the functions from the query, it runs in under a minute. Add one function back and the run time increases dramatically.

Also, When we move the data and code to a development server (much smaller), it runs in 20 minutes. The execution plans are identical between production and development. At this point, we are not sure where to look or how to further troubleshoot.

We are running SQL2000 Ent. Ed. SP3a AWE enabled, 32GB ram on Windows 2003 Enterprise Ed.

Has anyone experienced a problem similar to this one? Any help would be appreciated.



Big Data Man
Big Data Man
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4456 Visits: 750
hey,

Well, haven't experienced this type of behavior, but very interesting and would like to help.

First question, how much data is being processed now? How often to you update your indexes?


Thanks,
Phillip Cox
Brookstone
Brookstone
SSC-Addicted
SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)

Group: General Forum Members
Points: 425 Visits: 211
Thanks for the reply,

The source table has 3 million rows. We perform a select into a temporary table and calculate various cost columns using functions. Functions are used beacuse many columns have the came calculation, just different column values.

Indexes are rebuilt weekly.

The problem does not appear to be in the joins. If we remove the functions, it runs in under a minute. We are not CPU bound either (4 dual core processors).



Matt Miller (4)
Matt Miller (4)
SSC-Forever
SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)

Group: General Forum Members
Points: 46744 Visits: 19274
Are these multi-line or inline? scalar? are they deterministic (and flagged as such)?

Unfortunately, user defined functions in SQL Server aren't known for their performance. There are lots of instances where they force scans or row by row processing, essentially causing a perf nosedive...

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Brookstone
Brookstone
SSC-Addicted
SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)

Group: General Forum Members
Points: 425 Visits: 211
They are simple calculations, nothing complex. For example, we pass a couple of parms like cost, qty, and unit of measure then return the extended cost as money.

In some cases, we may read a lookup table (10 rows) to determine a value based on a code.

Hope this helps.



GilaMonster
GilaMonster
SSC Guru
SSC Guru (370K reputation)SSC Guru (370K reputation)SSC Guru (370K reputation)SSC Guru (370K reputation)SSC Guru (370K reputation)SSC Guru (370K reputation)SSC Guru (370K reputation)SSC Guru (370K reputation)

Group: General Forum Members
Points: 370936 Visits: 46965
Can you post code?

There are a number of constructs within functions that look good and perform terribly. As an example, if you have a scalar udf (user-defined function) that reads 10 rows and you use that function in the select clause of a query that returns 10 000 rows, then the function runs 10000 times (in most cases) and those 10 rows that it reads are each read 10000 times.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Brookstone
Brookstone
SSC-Addicted
SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)

Group: General Forum Members
Points: 425 Visits: 211
Sorry, can't post the code but I will try to explain further using an example.


Select ...
column1,
column2,
column3,
dbo.fnFunction (a.Cost1, b.COST2, b.COST3, b.XDate,@CurrentDt) as Cost4,
column5
.
.
.
into #temp from tblsource1 a (3 million rows) left join tblsource2 b (700,000 rows) on a.key=b.key


CREATE function fnFunction
(
@Cost1 money,
@Cost2 money,
@Cost3 money,
@XDate datetime,
@Today datetime
)
Returns money
as
begin
return
isnull(case
when @Cost1 > 0 then @Cost1
else
case when @XDate > @Today then @Cost2
else
@Cost3
end
end,0.00)
end



Matt Miller (4)
Matt Miller (4)
SSC-Forever
SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)

Group: General Forum Members
Points: 46744 Visits: 19274
If the core of the function involves no data access, you might care to try turning it into a CLR function (deterministic and precise if applicable). Since CLR functions are compiled, scalar CLR functions will tend to execute faster than the interpreted T-SQL ones, assuming they don't get slowed down by things like data access. Especially on things that CLR is "good" at and T-SQL is only somewhat good at (like string manipulation/pattern-matching, formatting, some type of validations etc...)

Still - it's not at all unusual to see that simply incorporating the "guts" of the function into the outer select is faster than calling the function. That approach might even be faster than using the CLR version of the function (and won't have the resource issues you sometimes run into with CLR objects in SQL Server).

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Sergiy
Sergiy
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40239 Visits: 12605

In some cases, we may read a lookup table (10 rows) to determine a value based on a code.


This is your problem.

Replace those functions with views and join your tables to them.
Brookstone
Brookstone
SSC-Addicted
SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)

Group: General Forum Members
Points: 425 Visits: 211
Thanks for all the replies. They are very much appreciated!

What continues to be a mystery is why a procedure that has been running since 2005 for 20 minutes would increase to 11+ hours. (It appears to me that the optimizer is forcing a cursor operation.)

Even more confusing, is that the extended run time appeared on our development system whithout warning yesterday. Previously, the process was running 20 minutes, now 11+ hours.

What would cause this kind of behavior?



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search