Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

functions Expand / Collapse
Author
Message
Posted Thursday, September 11, 2008 8:25 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, March 29, 2011 12:26 PM
Points: 127, 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.




Post #567766
Posted Thursday, September 11, 2008 8:35 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 6:39 PM
Points: 2,153, Visits: 644
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
Post #567788
Posted Thursday, September 11, 2008 8:47 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, March 29, 2011 12:26 PM
Points: 127, 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).



Post #567808
Posted Thursday, September 11, 2008 8:50 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:16 PM
Points: 7,115, Visits: 14,984
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?
Post #567812
Posted Thursday, September 11, 2008 9:13 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, March 29, 2011 12:26 PM
Points: 127, 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.




Post #567836
Posted Thursday, September 11, 2008 9:25 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 5:18 PM
Points: 42,437, Visits: 35,492
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 2008, MVP
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

Post #567847
Posted Thursday, September 11, 2008 11:16 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, March 29, 2011 12:26 PM
Points: 127, 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










Post #567965
Posted Thursday, September 11, 2008 11:52 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:16 PM
Points: 7,115, Visits: 14,984
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?
Post #567998
Posted Thursday, September 11, 2008 10:19 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Sunday, July 20, 2014 5:23 PM
Points: 4,576, Visits: 8,341

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.
Post #568247
Posted Friday, September 12, 2008 7:15 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, March 29, 2011 12:26 PM
Points: 127, 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?



Post #568462
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse