June 28, 2012 at 8:27 am
I have a scalar function that used to work fine and now doesnt complete.
the weird thing is that if I cut and paste the query inside the function it works fine.
it's not exactly that simple the scalar function is recursive...
functionFoo @param1 char(1))
RETURNS INT
AS
Begin
declare @foo as int
if (@param1 = 'A') -- recursive part
Begin
select @foo = dbo.functionFoo('B') + dbo.functionFoo('C') + dbo.functionFoo('D')
End
if (@param1 <> 'A')
Begin
select @foo = count(*) from tblFoo where cField = @param1
End
Return @foo
End
Again the above code is far simpler than the real program I'm working with but If I run dbo.functionFoo('A') it sits there for half an hour. likewise if I run dbo.functionFoo('B') or C or D it just does not complete.
If I copy and paste the contents of the <> 'A' if block into ssms query editor 3 times and hard code the @param1 values to 'B', 'C' and 'D' then run it, it will complete in approx 5 seconds and I can add up the 3 values with calc to get to the end result I need to find.
Has anyone come accross strange behaviour like this before? Used to work fine and I haven't changed the code at all. My colleague changed the Maxdop and the parallelism settings at instance level the other day to 25 threshold and 2 maxdop which I have now changed back to 5 thresh and 0 maxdop but this appears to have made no difference. my colleague is on annual leave at the moment so I can't find out if he changed anything else.
Any advice/pointers would be greatly appreciated.
Ben
^ Thats me!
----------------------------------------
01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
----------------------------------------
June 28, 2012 at 8:58 am
Since it's both recursive and dependent on data in a table, it probably just takes a duplicate value in the table in order to end up in an infinite loop. You won't catch that by hard-coding calls to the script, only by running against actual table values. That's the most likely explanation, and it takes into account no changes to code, since data changes would trigger the behavior change.
Also, depending on the math involved in the actual code, you should check for specific call values that might end up looping. For example, I've seen 0 end up causing infinite loops in functions that were accidentally invalid for any non-positive input.
But without the actual code and actual data, there's little we can do to help here other than suggest general principles. Can't get very specific for you. I understand you can't post the actual data, so you may be stuck fiddling with it till you find the specific cause.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 28, 2012 at 9:42 am
To add to the above you might even consider adding some sort of sanity check inside your recursion. Make an iteration count or something along those lines that gets passed in an incremented for each recursion. That way you can bailout if the recursion exceeds some predetermined point. That would at least keep you from an infinite loop.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 28, 2012 at 9:52 am
Thanks Both
The recursive element is as simple as the example code, if the parameter = 'A' it calls itself a few times and adds the results together, the parameter it passes in place of 'A' in this part is hard coded in the function and it will never call itself with another 'A' so it wont get stuck in a loop there.
I thought it might be encountering a lock issue as the actual count statement is run over a cte that agregates another cte and has 3 subqueries all from the same source table so its essentially joining the same 8 million row table into the same query 6 or 7 times. however when running the query no processes listed in activity monitor have a 'blocked by' or 'blocking' pid on them.
I could anonymise the data but I don't suppose you'd appreciate getting 8 million rows on here 😀
I'm now investigating the query execution plans etc to see if they differ between scalar function and straight query.
Ben
^ Thats me!
----------------------------------------
01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
----------------------------------------
June 28, 2012 at 9:55 am
HOLY COW!!! This thing must take forever to run. Any chance you can pull it out of a scalar function? They are notoriously bad for performance and with this one being nested and hitting tables with millions of rows...
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 28, 2012 at 9:59 am
lol if I copy and paste the query out of the udf it completes in just 5 seconds.
I just can't get my head around why exactly the same code works lightening fast outside the udf but slower than a donkey with piles inside the udf...
Ben
^ Thats me!
----------------------------------------
01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
----------------------------------------
June 28, 2012 at 10:03 am
BenWard (6/28/2012)
lol if I copy and paste the query out of the udf it completes in just 5 seconds.I just can't get my head around why exactly the same code works lightening fast outside the udf but slower than a donkey with piles inside the udf...
Because a scalar UDF is slow!!! I have a meeting in about 1 minute ago but you should try changing to an iTVF instead of a scalar. I suspect you will find some performance boost.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 28, 2012 at 10:09 am
hmm I shall give that a go thanks.
a udf is slow... still defies logic to me - a query is a query, a table is a table. unless of course M$ have imposed resource limits on queries executed from within UDFs. If they have I'd like to go 'talk' to whoever came up with that idea... I use scalar UDFs all over this database and most of them are shuffling numbers in massive data warehouse format tables in excess of 2 million rows. This is the only one I'm having big issues with though.
have a fun meeting!
Ben
^ Thats me!
----------------------------------------
01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
----------------------------------------
June 28, 2012 at 10:28 am
FIXED IT!!!!!!!!!!!!!!!!!!
I am calling the function with literals which was causing the execution plan to fall back on out-of-date statistics. I updated statistics and got the thing running in 5 seconds again 🙂
had I declared a variable and passed this to the function it would have been OK.
so yea, recursive, nested, agregated, subqueried count statements over tables 3.6GB in size CAN run fast in a UDF 🙂
i found this resolution searching for info on itvf
thanks sean, wouldn't have found that without your advice on itvf.
//edit - maybe it's about time I spoke to my DBA colleague about maintenance of statistics!
Ben
^ Thats me!
----------------------------------------
01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
----------------------------------------
June 28, 2012 at 10:40 am
Short meeting, the kind I like best!!!! Glad you were able to get it working.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 28, 2012 at 10:48 am
in case any-one else stumbles accross this thread having had similar issues, theres a great explanation of the issues with out-of-date statistics here:
essentially, the way I have understood this, if the db stats don't know that there are any rows in the table with a date of '01 Jun 2012' and above the query will be optimised for a small row count (expects 0 or 1 row(s)) and might nest a loop in a join which would be fine on a couple of rows but when you start to run a nested loop on a join of 600,000 rows you're in a whole world of hidden RBAR pain.
Cheers guys. Happy SQLing!
Ben
^ Thats me!
----------------------------------------
01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
----------------------------------------
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply