

Grasshopper
Group: General Forum Members
Last Login: Thursday, April 14, 2011 7:04 AM
Points: 20,
Visits: 177





SSC Veteran
Group: General Forum Members
Last Login: Sunday, September 20, 2015 4:08 PM
Points: 268,
Visits: 733


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




Grasshopper
Group: General Forum Members
Last Login: Thursday, April 14, 2011 7:04 AM
Points: 20,
Visits: 177


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 userdefined 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




Forum Newbie
Group: General Forum Members
Last Login: Thursday, September 27, 2012 12:35 PM
Points: 7,
Visits: 32


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




SSC Veteran
Group: General Forum Members
Last Login: Sunday, September 20, 2015 4:08 PM
Points: 268,
Visits: 733


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




Grasshopper
Group: General Forum Members
Last Login: Tuesday, June 23, 2015 10:07 AM
Points: 10,
Visits: 81


A good tip well presented  many thanks




SSCoach
Group: General Forum Members
Last Login: Yesterday @ 11:54 PM
Points: 19,281,
Visits: 17,634





SSCDedicated
Group: General Forum Members
Last Login: Yesterday @ 4:22 PM
Points: 38,553,
Visits: 35,560


Matt,
Could you post the TSQL Function you ran against, please? Thanks.
Jeff Moden "RBAR is pronounced "reebar" and is a "Modenism" for "RowByAgonizingRow".
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."
(play on words) "Just because you CAN do something in TSQL, doesn't mean you SHOULDN'T." 22 Aug 2013
Favorite Quotes: "Has anyone ever told you that a query you have written runs too fast?"  Dwain Camps  6 Mar 2014
Helpful Links: How to post code problems How to post performance problems




Grasshopper
Group: General Forum Members
Last Login: Thursday, April 14, 2011 7:04 AM
Points: 20,
Visits: 177


Hi Jeff. Here's the requested tsql 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




SSC Rookie
Group: General Forum Members
Last Login: Thursday, January 24, 2013 12:02 PM
Points: 42,
Visits: 152


Since you were using lat/long datatypes  did you look into the geometry data type? http://msdn.microsoft.com/enus/library/bb964711.aspx http://social.msdn.microsoft.com/Forums/enUS/sqlspatial/thread/c557032a038e480c84e87f0ad72e1af7
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.



