SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL CLR Data Types and Performance


SQL CLR Data Types and Performance

Author
Message
mzak
mzak
Valued Member
Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)

Group: General Forum Members
Points: 72 Visits: 177
Comments posted to this topic are about the item SQL CLR Data Types and Performance
r5d4
r5d4
Old Hand
Old Hand (395 reputation)Old Hand (395 reputation)Old Hand (395 reputation)Old Hand (395 reputation)Old Hand (395 reputation)Old Hand (395 reputation)Old Hand (395 reputation)Old Hand (395 reputation)

Group: General Forum Members
Points: 395 Visits: 820
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
mzak
Valued Member
Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)

Group: General Forum Members
Points: 72 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 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
eric.cardinal
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 32
Wonderful article! I have just started using SQLCLR and the possibilities are endless. Thansk for the hint on the constants.
r5d4
r5d4
Old Hand
Old Hand (395 reputation)Old Hand (395 reputation)Old Hand (395 reputation)Old Hand (395 reputation)Old Hand (395 reputation)Old Hand (395 reputation)Old Hand (395 reputation)Old Hand (395 reputation)

Group: General Forum Members
Points: 395 Visits: 820
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
pete callaghan
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 Visits: 82
A good tip well presented - many thanks
SQLRNNR
SQLRNNR
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40327 Visits: 18565
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

Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)

Group: General Forum Members
Points: 114777 Visits: 41394
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

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
mzak
mzak
Valued Member
Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)

Group: General Forum Members
Points: 72 Visits: 177
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
wbrianwhite
SSC-Enthusiastic
SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)

Group: General Forum Members
Points: 160 Visits: 152
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search