

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: Friday, March 14, 2014 8:26 AM
Points: 267,
Visits: 674


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: Friday, March 14, 2014 8:26 AM
Points: 267,
Visits: 674


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, March 04, 2014 2:50 AM
Points: 10,
Visits: 80


A good tip well presented  many thanks




SSCInsane
Group: General Forum Members
Last Login: Today @ 1:10 PM
Points: 20,485,
Visits: 14,141





SSCDedicated
Group: General Forum Members
Last Login: Today @ 8:11 AM
Points: 36,015,
Visits: 30,302


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."
"Change is inevitable. Change for the better is not."  04 August 2013 (play on words) "Just because you CAN do something in TSQL, doesn't mean you SHOULDN'T." 22 Aug 2013
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.



