SQL CLR Data Types and Performance

Question

Post reply

SQL CLR Data Types and Performance

mzak

Mr or Mrs. 500

Points: 562
More actions
July 20, 2010 at 11:17 pm

#235520

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

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

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

r5d4 SSCrazy Points: 2519 More actions · Answer 1

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 More actions · Answer 2

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 More actions · Answer 3

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

r5d4 SSCrazy Points: 2519 More actions · Answer 4

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 More actions · Answer 5

pete callaghan

SSC Veteran

Points: 222

July 21, 2010 at 10:05 am

#1196277

A good tip well presented - many thanks

SQLRNNR SSC Guru Points: 281334 More actions · Answer 6

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: 1003853 More actions · Answer 7

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.

Change is inevitable... Change for the better is not.

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

mzak Mr or Mrs. 500 Points: 562 More actions · Answer 8

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 More actions · Answer 9

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 More actions · Answer 10

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 More actions · Answer 11

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 More actions · Answer 12

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 More actions · Answer 13

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 More actions · Answer 14

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.