Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

SQL CLR Data Types and Performance Expand / Collapse
Author
Message
Posted Tuesday, July 20, 2010 11:17 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, April 14, 2011 7:04 AM
Points: 20, Visits: 177
Comments posted to this topic are about the item SQL CLR Data Types and Performance
Post #956088
Posted Wednesday, July 21, 2010 4:37 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 9:10 AM
Points: 267, Visits: 686
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
Post #956229
Posted Wednesday, July 21, 2010 7:23 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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 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
Post #956337
Posted Wednesday, July 21, 2010 7:36 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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.
Post #956345
Posted Wednesday, July 21, 2010 8:08 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 9:10 AM
Points: 267, Visits: 686
Thank you for clarifying , will be revisiting the only CLR i've written to see if i can benefit from this tip!

cheers

r
Post #956369
Posted Wednesday, July 21, 2010 10:05 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, March 4, 2014 2:50 AM
Points: 10, Visits: 80
A good tip well presented - many thanks
Post #956495
Posted Wednesday, July 21, 2010 10:37 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:59 PM
Points: 21,297, Visits: 14,988
Thanks for sharing.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #956517
Posted Wednesday, July 21, 2010 11:27 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:06 PM
Points: 36,786, Visits: 31,243
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #956570
Posted Wednesday, July 21, 2010 11:40 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, April 14, 2011 7:04 AM
Points: 20, 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

Post #956580
Posted Wednesday, July 21, 2010 11:46 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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/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.
Post #956588
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse