| Author |
Topic |
|
morleyhill
Starting Member
19 Posts |
 Posted - 2004-10-18 : 06:50:58
|
| Hi allCan anyone explain why I'm getting a serious performance problem when I pass a variable to a user defined function.Here is the scenario-if I execute the following: declare @ShortName nvarchar(40) set @ShortName = 'my agency' select * from dbo.MyFunction(@ShortName)it takes 60-seconds to run!but if I execute this: select * from dbo.MyFunction('my agency')it takes 1-second to run.All other things being the same, surely these two statements should execute the same, or am I missing something? Any suggestions please! |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-10-18 : 06:56:36
|
| Have a look at the query plans==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
morleyhill
Starting Member
19 Posts |
Posted - 2004-10-18 : 07:06:42
|
| OK- when I pass the parameter it uses a 'Table Scan' which takes up 99% of the 60-second run time, whereas when I pass the hard coded value it performs an 'Index Seek' which takes no time at all. So, now I know this difference, what can I do about it? |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2004-10-18 : 09:11:00
|
| what is the datatype declared of the parameters the UDF expect?how doesselect * from dbo.MyFunction(N'my agency')perform?- Jeff |
|
|
|
morleyhill
Starting Member
19 Posts |
Posted - 2004-10-18 : 09:16:15
|
| Q. what is the datatype declared of the parameters the UDF expect?A. The parameter is declared as nvarchar(40)Q. how doesselect * from dbo.MyFunction(N'my agency')perform?A. The same as select * from dbo.MyFunction('my agency'), i.e. it runs in about 1-second |
|
|
|
morleyhill
Starting Member
19 Posts |
Posted - 2004-10-18 : 10:49:13
|
| For anyone that's interested, here's the fix-The UDF is an inline table value function that uses a SQL select to return statistical data. One of the tables in the SQL select has over 2,000,000 records. Tests have shown that the SQL join on this table uses Index Seek when I pass the hard coded value but a Table Scan when I pass the parameter. However, if I delete some records (leaving around 1,400,000 records) then the select uses the preferable Index Seek in both scenarios.I've got around the query using a Table Scan by specifying an 'inner loop join' hint, to force the function to use the index.This does raise a more general issue of functions executing quickly, by using an available index, upto a point. Then slow down dramatically when an apparently arbitrary record number threshold has been met. This may be something peculiar to my database, or has anyone come across this before?I can see me having a long week while I check the execution plan of all 1000+ UDFs and SPs!Thanks to everyone who took the time to ponder my dilemma! |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-10-18 : 11:04:06
|
| Why are you using unicode?And I don't believe for a second that deleting records is the "fix".And maybe you could actually post the Code for the function, and the sample of what you actually passed.And why use a function at all?And what's the cardinality of that column?Brett8-) |
|
|
|
morleyhill
Starting Member
19 Posts |
Posted - 2004-10-18 : 12:10:44
|
| Brett-Q. Why are you using unicode?A. I can't answer that as there aren't any of the original dba's left on the project. I've been tasked with a number of enhancements and didn't design the original db. It might possibly have something to do with this databse being live in over 20-countries and not all of them speak English.Q. And I don't believe for a second that deleting records is the "fix".A. I've not said that deleting records was the 'fix', that was just an observation that I made while testing. The fix was to add the 'inner loop join' hint. I mentioned deleting records because of the concerns that I went on to express.Q. And maybe you could actually post the Code for the function, and the sample of what you actually passed.A. The 'production' function is quite involved, so for testing I used the following which produced the similar performance issue. (dbo.tblPaymentAccount is the table containing over 2,000,000 rows). Note also that this has the hint fix. create function dbo.fnPaymentAccount_Get_Payments_Agency_Period ( @AgencyShortName nvarchar(40) )returns tableasreturn ( select e.RespondedTo, pa.PaidIn, pa.FirstPayment from dbo.Enquirers e join dbo.clients c on c.EnqId = e.EnqId inner loop join dbo.tblPaymentAccount pa on pa.ClientId = c.ClientId where e.Source = @AgencyShortName )The calling code is as posted above.Q. And why use a function at all?A. For the scenario that I have, it is the best solution. I'm not going to justify that because that isn't the issue. I've now observed this performance issue in UDF's, SP's and executing SQL straight from Query Analyzer.Q. And what's the cardinality of that column?A. If you mean unique values in Enquirers.Source then 48,500. |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2004-10-19 : 04:49:07
|
| I'm no dba so I really don't know too much about this but could it be that the delete forces a statistics update? I know that for query plans to be worked out properly statistics must be recomputed every now and the and index defrag/reindex could also do you some good. If this is not taken care of allready, look up UPDATE STATISTICS, DBCC INDEXDEFRAG, DBCC DBREINDEX and DBCC UPDATEUSAGE in BOL--Lumbago"Real programmers don't document, if it was hard to write it should be hard to understand" |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-10-19 : 14:39:49
|
| Hey, cool : What's an "inner loop join"??I ain't got one of those in my code, and I so **WANT** one for my collection ...Kristen |
|
|
|
morleyhill
Starting Member
19 Posts |
Posted - 2004-10-20 : 06:18:17
|
| Hi Lumbago-Thanks for the tip, I'll check that out.However, the problem was repeatable- so by adding records back into the table the query used an 'Index Seek' upto a certain row count threshold, over which it then used a 'Table Scan'. When I then removed the records again below the row count threshold the query would revert to using the 'Index Seek'.The row count threshold was about 1,400,000. |
|
|
|
morleyhill
Starting Member
19 Posts |
Posted - 2004-10-20 : 06:29:07
|
| Kristen-An 'inner loop join' forces the query to use a loop join rather that a hash or merge join.I've since found this article by Michael Balloni that describes this issue very well-http://www.sql-server-performance.com/mb_sql_server_joins.aspI feel a lot happier that this isn't something peculiar to my db, but it still doesn't explain why the original UDF call behaved differently when passing a variable to when passing a hard-coded value. |
|
|
|
|
Parameter vs Hard Coded performance problem