July 20, 2010 at 11:17 pm
Comments posted to this topic are about the item SQL CLR Data Types and Performance
July 21, 2010 at 4:37 am
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
July 21, 2010 at 7:23 am
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
July 21, 2010 at 7:36 am
Wonderful article! I have just started using SQLCLR and the possibilities are endless. Thansk for the hint on the constants.
July 21, 2010 at 8:08 am
Thank you for clarifying , will be revisiting the only CLR i've written to see if i can benefit from this tip!
cheers
r
July 21, 2010 at 10:05 am
A good tip well presented - many thanks
July 21, 2010 at 10:38 am
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
July 21, 2010 at 11:27 am
Matt,
Could you post the T-SQL Function you ran against, please? Thanks.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 21, 2010 at 11:40 am
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
July 21, 2010 at 11:46 am
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.
July 21, 2010 at 11:49 am
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.
July 21, 2010 at 11:59 am
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
July 21, 2010 at 12:32 pm
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.
July 21, 2010 at 12:56 pm
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.
July 21, 2010 at 1:12 pm
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 33 total)
You must be logged in to reply to this topic. Login to reply