Trace Shows Statement Executing Thousands of Times 2

[fuzzyocelot]

System: SQL Server 2005 SP2

We recently discovered a stored procedure containing a large complicated select statement. When the stored procedure is executed by one person, the select statement shows up in an SQL Profiler trace about 500 times. The EventClass I used is "SP:Completed". The stored proc returns 50 records eventually or it times out.

I did not write this procedure. The person who wrote it is no longer here so we're stuck trying to figure it out on our own. The select statement contains 21 table joins alone. We believe the problem is that there is a left join on a user-defined function. When we comment out the function, we have no problems. Now we're not sure how to proceed from here.

Here is the left join containing the function.

LEFT JOIN tblPLP plp ON plp.inyRcdId = dbo.fnPLGP(c.intInc,c.inyHouseRcdId,getdate())

Here is the function:

ALTER FUNCTION [dbo].[fnPLGP]
              (@intInc   INT
               ,@inyHouseSize TINYINT  = 1
               ,@dtsRDate SMALLDATETIME  = GetDate)
RETURNS TINYINT
AS
  BEGIN
    DECLARE  @inyRcdId TINYINT
      
    SET @inyRcdId = (SELECT MIN(plr.inyRcdId)
                     FROM   (SELECT *
                             FROM   dbo.tblPLR
                             WHERE  inyRcdId NOT IN (6,7)) plr
                     JOIN dbo.tblPLP pld
                     ON plr.insRcdId = pld.insRcdId
                     WHERE  plr.inyHouseSize = @inyHouseSize
                        AND pld.dtsStartDate < @dtsRDate
                        AND ISNULL(pld.dtsEndDate,'12/12/2078') >= @dtsRDate
                        AND plr.intMinInc <= @intInc
                        AND ISNULL(plr.intMaxInc,@intInc) >= @intInc)
      RETURN (@inyRcdId)
  END

Any ideas how to fix this? Do I need to include the entire select statement from the stored procedure? I'm thinking that someone might need to rewrite this thing without using the user-defined function.

Thanks!

 

[gmmastros]

THIS IS EXACTLY WHY FUNCTIONS ARE HORRID.

Used properly, UFD's can be great, but they really need to be used sparingly because this sort of thing can really kill your performance.

Unfortunately, fixing this problem is not going to be fun. Not even a little.

You could try replacing the code from the function directly in to the query that calls it. This will probably help a little, but you should not expect a miracle either.

Unfortunately, the best course of action would be to understand the entire query and re-write it in such a way that this function isn't used at all.

Good luck.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[fuzzyocelot]

That's basically what I told the developer who is stuck with fixing it. She took a stab at it yesterday and managed to rewrite the select statement without the function. It appeared to work great! I know we have more of these to fix but it's a start.

Thank you!

 

[gmmastros]

I've said it before, and I'll say it again.

I have 2 criteria that must be met in order for me to create a function. These are my personal requirements, and may not apply to everyone.

1. The function must be re-usable by multiple queries.
2. The 'guts' of the function must be complicated enough that you want to hide the implementation details.

If BOTH of these requirements are not met, then I don't let it be a function.

I almost want to add a 3rd criteria... functions cannot access data in a table. I hesitate on this one because it severely limits the use of function, but that's not a bad thing. For example, I have a function in my database that calculates the distance between a pair of latitude/longitude coordinates. This function performs a lot of math operations (involving trig functions). I could put this function in all the queries where it is used, but I run the risk of implementing it wrong (it really is quite complicated). So, I made this a function. This function doesn't use any data from any tables, and it really is rather quick, so I don't mind having it be a function.

You see, functions are evaluated for EVERY row returned by a query. In your case, you were using it for a join condition, which means the function was evaluated for every COMBINATION of rows between the 2 tables to see if that combination of rows satisfies the join condition.

Just to give you an idea... I have 2067 stored procedures in my database and only 117 functions (many of which are not used). This is because functions can be real performance killers. As such, you need to use them sparingly.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[fuzzyocelot]

What you said makes sense to me. In this particular case, this function was used in only one stored procedure. So it probably didn't need to be a function in the first place.

Thanks again for your help!