At the moment I have the following code that converts the number of seconds into hh:mm:ss.
Messy but it works!
If I converted that to the following which is neater but then run it for thousands of rows will I see a performance hit?
The code I would use to create my function could be like so.
asked May 14 '10 at 04:56 AM in Default
Similar to what Scot said... The answer is yes.
However, you have to weigh the positive aspect of encapsulating code that you do not have to duplicate all over your codebase. If you are dealing with a low volume of data, the encapsulation is good so that future changes only need to be made in one place (if you find a bug in your code, with it encapsulated in your function, you only have to change it once).
The negative aspect, which usually is not noticed until you have an extremely large volume of data, is the hit on performance. This is what happened at my current employer and we have slowly started moving away from functions for some of the simpler tasks (like date/time conversions similar to yours) while remaining with functions for more complicated tasks.
We feel the benefit of keeping complicated code encapsulated instead of attempting to duplicate it in many areas of our code base is worthwhile. (And yes, copy and paste works until you fat finger it one day or until a developer attempts to improve performance and introduces a bug in 1 of the 20 locations that the complicated functionality is used and you spend hours trying to track down what/where things went wrong).
answered May 14 '10 at 09:11 AM
+1 to John - good answer.
Personally, I wouldn't sit on the side of encapsulation though - I would usually go for the more performant option on anything that had to deal with more than a few rows. Bear in mind I come from a large database background (think 6 billion rows and 750GB).
One thing though, there is a 'best of both worlds' solution - it just doesn't happen to exist yet. See here on connect.
The reason it is slow is because there is nothing in the meta-data which says to the query optimizer 'this scalar function just does a simple calculation' - so it effectively turns it into a hidden RBAR in case you are doing data lookups in your scalar function (which is a hugely bad practice anyway). I did blog about it on my old blog here. I will be moving that article to my new blog sometime in the future.
Dave B did point out to me that in that blog article I was experiencing the observer effect - in that by measuring the performance difference, I was actually making the performance difference worse. However, the performance penalty is still definitely present.
answered May 14 '10 at 09:40 AM
Matt Whitfield ♦♦