SQL CLR Data Types and Performance

  • mzak

    Mr or Mrs. 500

    Points: 562

    Comments posted to this topic are about the item SQL CLR Data Types and Performance

  • r5d4

    SSCrazy

    Points: 2499

    Hiya,

    Can you clarify how you declared the variables for each function (for a .NET amateur)?

    Which variables did you declare scope differently for?

    Can you give an example of each?

    * DistanceSqlTypesNoGlobalConsts

    * DistanceSqlTypesGlobalConsts

    * DistanceNoGlobalConsts

    * DistanceGlobalConsts

    thank you

    r

  • mzak

    Mr or Mrs. 500

    Points: 562

    Absolutely.

    If you look at my first attempt block of code in the article you will notice multiple calculations with

    ... * Math.PI / 180.0

    In my second attempt, I scoped some constants such as the "Math.PI / 180.0" to the user-defined function assembly (when the assembly is loaded into memory these values are available constants) by placing them in my class.

    Public Const RadianConversionConst As Decimal = Math.PI / 180.0

    The calculations in the function still have to perform some operations, but they don't have to perform as many operations with each call.

    The result has 1 operation (other than dimensioning the variable) - 1 multiplication operation.

    Dim Latitude1Radian As Decimal = Latitude1 * RadianConversionConst

    Instead of of 2 operations - a multiplication operation and a divide operation.

    Dim Latitude1Radian As SqlDecimal = Latitude1 * Math.PI / 180.0

  • eric.cardinal

    SSC Journeyman

    Points: 93

    Wonderful article! I have just started using SQLCLR and the possibilities are endless. Thansk for the hint on the constants.

  • r5d4

    SSCrazy

    Points: 2499

    Thank you for clarifying , will be revisiting the only CLR i've written to see if i can benefit from this tip!

    cheers

    r

  • pete callaghan

    SSC Veteran

    Points: 222

    A good tip well presented - many thanks

  • SQLRNNR

    SSC Guru

    Points: 281210

    Thanks for sharing.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Jeff Moden

    SSC Guru

    Points: 994289

    Matt,

    Could you post the T-SQL Function you ran against, please? Thanks.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • mzak

    Mr or Mrs. 500

    Points: 562

    Hi Jeff. Here's the requested t-sql implementation.

    SET ANSI_NULLS OFF

    GO

    SET QUOTED_IDENTIFIER OFF

    GO

    CREATE Function [dbo].[Distance] (

    @Lat1 as decimal(18, 6),

    @Long1 as decimal(18, 6),

    @Lat2 as decimal(18, 6),

    @Long2 as decimal(18, 6))

    Returns decimal(18, 6) With SchemaBinding

    As

    Begin

    Declare @dLat1InRad as float(53)

    Set @dLat1InRad = @Lat1 * ( PI() / 180.0 )

    Declare @dLong1InRad as float(53)

    Set @dLong1InRad = @Long1 * ( PI() / 180.0 )

    Declare @dLat2InRad as float(53)

    Set @dLat2InRad = @Lat2 * ( PI() / 180.0 )

    Declare @dLong2InRad as float(53)

    Set @dLong2InRad = @Long2 * ( PI() / 180.0 )

    Declare @dLongitude as float(53)

    Set @dLongitude = @dLong2InRad - @dLong1InRad

    Declare @dLatitude as float(53)

    Set @dLatitude = @dLat2InRad - @dLat1InRad

    /* Intermediate result a. */

    Declare @a as float(53)

    Set @a = SQUARE( SIN( @dLatitude / 2.0 ) ) + COS( @dLat1InRad ) * COS( @dLat2InRad ) * SQUARE( SIN( @dLongitude / 2.0 ) )

    /* Intermediate result c (great circle distance in Radians). */

    Declare @C as real

    Set @C = 2.0 * ATN2( SQRT( @a ), SQRT( 1.0 - @a ) )

    Declare @EarthRadius as decimal(18, 6)

    Set @EarthRadius = 3956.0 /* miles */

    Declare @dDistance as decimal(18, 6)

    Set @dDistance = @EarthRadius * @C

    Return (@dDistance)

    End

  • wbrianwhite

    SSC Eights!

    Points: 954

    Since you were using lat/long datatypes - did you look into the geometry data type?

    http://msdn.microsoft.com/en-us/library/bb964711.aspx

    http://social.msdn.microsoft.com/Forums/en-US/sqlspatial/thread/c557032a-038e-480c-84e8-7f0ad72e1af7

    I've read about it a little, but haven't used it yet. Seems to be the way to go if you're going CLR.

  • wbrianwhite

    SSC Eights!

    Points: 954

    wbrianwhite (7/21/2010)


    Since you were using lat/long datatypes - did you look into the geometry data type?

    http://msdn.microsoft.com/en-us/library/bb964711.aspx

    http://social.msdn.microsoft.com/Forums/en-US/sqlspatial/thread/c557032a-038e-480c-84e8-7f0ad72e1af7

    I've read about it a little, but haven't used it yet. Seems to be the way to go if you're going CLR.

    Sorry, should have recommended geography, not geometry.

  • Charles Kincaid

    SSChampion

    Points: 13593

    Hmm. Yes avoiding data type conversions is a good thing. I think that you did some other good things as well. Not repeating "[font="Courier New"]Math.PI / 180.0[/font]" but using a precalculated constant is great. Plus you did that with some other non-changing elements as well. I'm then curious if your speed gains were due mostly to precalculation or data type conversion avoidance.

    The CLR is based on the Dot Net framework and I have had quite a bit of experience with that. I have not gotten into the CLR yet as it is not available on the Compact Edition on the mobile side. Yet there are some things that us old procedural coders can pass along to help. I had an issue where I was looping though quite a set of items. For each one I had to call some functions that took a Long as a parameter. I saved a bit by using a Long as my loop counter and not having to convert for every function call. There was no way I was ever going to process that many items per run but using the right type saved the loop time overall.

    It's good to point out this sort of thing as it's not obvious to new folk what is going on. The "less code is better code" is great but if your code calls stuff under the hood your small source can actually turn out tho be more code at run time. I suggest that you turn on both Option Explicit and Option Strict in your Visual Studio projects.

    Good article.

    ATBCharles Kincaid

  • mzak

    Mr or Mrs. 500

    Points: 562

    Hi wbrianwhite,

    I have looked at the geometry and geography data types / support in the later versions of SQL Server.

    I don't feel that I'm familiar enough yet to comment conclusively one way or the other.

    My initial tests with geography data types did not seem to perform near as well as the basic udf function outlined in the article. There may be a lot more than what "meets the eye" here though so further investigation will continue when time permits.

  • mzak

    Mr or Mrs. 500

    Points: 562

    Charles,

    Great suggestions and my compliments to a 'procedural coder' pursuing 'set-based' material!

    I think it's important to note that the constants not only provided a pre-calculated constant as you indicated but, taking it further, it also eliminated the recalculation efforts with each function call. It's really cool that the ability exists to define these constants with SQL CLR.

    I think I can say quite confidently that the majority of performance improvement between the various flavors of the SQL CLR functions tested was due to implicit data type conversion avoidance. (Note the performance difference between DistanceNoGlobalConsts and DistanceGlobalConsts.) That said, both aspects obviously helped to make the function faster.

  • Kevin Rathgeber

    SSC Veteran

    Points: 270

    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.

Viewing 15 posts - 1 through 15 (of 34 total)

You must be logged in to reply to this topic. Login to reply