• Kevin Rathgeber (7/21/2010)


    mzak (7/21/2010)


    Kevin Rathgeber (7/21/2010)


    I have a comment regarding your T-SQL

    In trying to keep all things equal, Shouldn't you declare a variable at the beginning of your function to hold the value of PI() / 180 instead of calculating it 4 times.

    That was the first item I noticed.

    Yep!

    The point of the article wasn't to pit SQL CLR against T-SQL implementations (I debated whether to include the T-SQL results with it's current implementation in the article). Rather, it was to show that you don't have to use a sql data type in your SQL CLR (you can use native .net data types) and that doing so may result in performance gains - in my particular example, rather significant performance gains.

    I figured Jeff would mention this shortly after I posted the requested T-SQL so I was anticipating it :-).

    Good catch Kevin.

    It should also be stated that this wouldn't make 'all things equal' per se. The SQL CLR implementation doesn't perform the calculation once, let alone 4 times with each call. By declaring a variable to eliminate the T-SQL redundancy, it still must be performed once with each call.

    Oops....posted without saying anything.

    Good point that it wouldn't be equal, just a little more equal 🙂

    Sorry to detract from your article, but in case you were interested, I just did some testing creating two functions. The first function calculates "PI() / 180" 4 times (your T-SQL example). The 2nd function assigns "PI() / 180" to a variable and then uses that variable 4 times (my suggestion). Surpisingly the 2nd one (my suggestion) was slower. The amount it was slower is pretty much insignificant.....by about 4/10ths of a second over 1million iterations. Long story short, ignore my comment 🙂

    That's interesting. Although I wonder what the "cost" of declaring a variable is with respect to this scenario? If I get some time, I'll rewrite the T-SQL udf based on your suggestion and see what results.

    Based on your test, I'd also be curious if changing it to eliminate some variable declarations would have much impact (remove @dDistance, @EarthRadius, etc). Also, there's no reason I couldn't assign a reasonable constant for pi() / 180.0 which essentially would get us to equality :-).