reusing functions
-
Hi All.
Here is my situation. I've inherited a database that was created by a 3rd party. The application that uses this database runs extremely slow. When I took a look at it I noticed that most of the code is in form of functions. Stored procedures call those functions that call other functions that call other functions.
When I looked at the execution plan for one of the sps it turned out that some tables are accessed multiple times through different functions. I re-wrote it by removing the function calls and optimizing the code. The SP runs about 10 times faster now. It seems that the idea behind the design was to only write code for certain functionality once and then re-use it. My question is whether this is a good practice for database programming. The fact that it makes things run slower makes me think otherwise.
I would appreciate any input.
Veronika
-
The short answer is: it depends. There is a time and place to use functions, especially when you need a scalar value, but ultimately it has to be balanced with performance. Then again, this is the same consideration on whether or not to denormalize: additional management to ensure data integrity versus speed. Based on what you've written, if code is accessing tables multiple times because of the way the code is written, it does need to be rewritten, functions or no. The same thing could happen through the use of stored procedure or SQL batches, so it's not functions per se that was the issue here.
K. Brian Kelley
@kbriankelley -
Thank you for your input. That's what I thought. But I just wanted to make sure that I wasn't missing something.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply