alivemedia
asked on
method in assembly is not static
I am trying to use an assembly called from a function in SQL Server 2005,
Here is the assembly code:
Public Class Distance
Public Function getDistance(ByVal lat1 As Double, ByVal lon1 As Double, ByVal lat2 As Double, ByVal lon2 As Double) As Double
Dim theta As Double
Dim dist As Double
Dim distance As Double
theta = lon1 - lon2
dist = Math.Sin(CDbl(deg2rad(lat1 ))) * Math.Sin(CDbl(deg2rad(lat2 ))) + Math.Cos(CDbl(deg2rad(lat1 ))) * Math.Cos(CDbl(deg2rad(lat2 ))) * Math.Cos(CDbl(deg2rad(thet a)))
dist = Math.Acos(dist)
dist = CDbl(rad2deg(dist))
distance = dist * 60 * 1.1515
Return distance
End Function
Private Function deg2rad(ByVal Deg As Double) As Double
Return CDbl(Deg * Math.PI / 180)
End Function
Private Function rad2deg(ByVal Rad As Double) As Double
Return CDbl(Rad * 180 / Math.PI)
End Function
End Class
Here is the code to register the assembly with sql server:
CREATE ASSEMBLY asmDistanceFunctions
FROM 'E:\web\clients\out2dayliv e\Bin\asmD istanceFun ctions.dll '
WITH PERMISSION_SET = SAFE
GO
Here is the code for the function calling the assembly - THIS IS WHERE I GET AN ERROR!
CREATE FUNCTION dbo.clrComputeDistance
(
@lat1 as float,
@lon1 as float,
@lat2 as float,
@lon2 as float
)
RETURNS float
AS EXTERNAL NAME [asmDistanceFunctions].[as mDistanceF unctions.D istance].[ getDistanc e]
GO
I get the following error:
Msg 6573, Level 16, State 1, Procedure clrComputeDistance, Line 1
Method, property or field 'getDistance' of class 'asmDistanceFunctions.Dist ance' in assembly 'asmDistanceFunctions' is not static.
How do I correct this?
Here is the assembly code:
Public Class Distance
Public Function getDistance(ByVal lat1 As Double, ByVal lon1 As Double, ByVal lat2 As Double, ByVal lon2 As Double) As Double
Dim theta As Double
Dim dist As Double
Dim distance As Double
theta = lon1 - lon2
dist = Math.Sin(CDbl(deg2rad(lat1
dist = Math.Acos(dist)
dist = CDbl(rad2deg(dist))
distance = dist * 60 * 1.1515
Return distance
End Function
Private Function deg2rad(ByVal Deg As Double) As Double
Return CDbl(Deg * Math.PI / 180)
End Function
Private Function rad2deg(ByVal Rad As Double) As Double
Return CDbl(Rad * 180 / Math.PI)
End Function
End Class
Here is the code to register the assembly with sql server:
CREATE ASSEMBLY asmDistanceFunctions
FROM 'E:\web\clients\out2dayliv
WITH PERMISSION_SET = SAFE
GO
Here is the code for the function calling the assembly - THIS IS WHERE I GET AN ERROR!
CREATE FUNCTION dbo.clrComputeDistance
(
@lat1 as float,
@lon1 as float,
@lat2 as float,
@lon2 as float
)
RETURNS float
AS EXTERNAL NAME [asmDistanceFunctions].[as
GO
I get the following error:
Msg 6573, Level 16, State 1, Procedure clrComputeDistance, Line 1
Method, property or field 'getDistance' of class 'asmDistanceFunctions.Dist
How do I correct this?
Of course, you could always also do this as a regular T-SQL function as well and not have the overhead of having to load an assembly (returns distance in meters):
CREATE FUNCTION DistanceBetween2PointsQ( @Lat1 float,@Long1 float, @Lat2 float, @Long2 float)
returns float as
return
((Abs(60 * (ACos((Sin(@Lat1 * (PI()/180)) * Sin(@Lat2 * (PI()/180)))
+ (Cos(@Lat1 * (PI()/180)) * Cos(@Lat2 * (PI()/180)) * Cos((@Long2 * (PI()/180) * -1)
- (@Long1 * (PI()/180) * -1)))) * (180/PI())))) * 1852)
GO
declare @Lat1 float
declare @Long1 float
declare @Lat2 float
declare @Long2 float
Set @Lat1 = 48.06361
Set @Long1 = 8.4251385
Set @Lat2 = 48.063526
Set @Long2 = 8.4250002
SELECT dbo.DistanceBetween2Points (@Lat1,@Lo ng1,@Lat2, @Long2)
CREATE FUNCTION DistanceBetween2PointsQ( @Lat1 float,@Long1 float, @Lat2 float, @Long2 float)
returns float as
return
((Abs(60 * (ACos((Sin(@Lat1 * (PI()/180)) * Sin(@Lat2 * (PI()/180)))
+ (Cos(@Lat1 * (PI()/180)) * Cos(@Lat2 * (PI()/180)) * Cos((@Long2 * (PI()/180) * -1)
- (@Long1 * (PI()/180) * -1)))) * (180/PI())))) * 1852)
GO
declare @Lat1 float
declare @Long1 float
declare @Lat2 float
declare @Long2 float
Set @Lat1 = 48.06361
Set @Long1 = 8.4251385
Set @Lat2 = 48.063526
Set @Long2 = 8.4250002
SELECT dbo.DistanceBetween2Points
ASKER
actually doing it the way I am trying to will be several times faster. right now it takes more than a second to run my query - that's just 1 user with no one else hitting the database. From what I have read this should reallypeed this up for me since I am using SQl Server 2005 has asp.net CLR hosted in it.
ASKER
AngelII that gives me the following error: Methods cannot be declared static
I am sure I am missing something small here
I am sure I am missing something small here
for visual basic you need to use Shared
ASKER
I figured it out, I needed to add shared to all of my functions:
Public Class Distance
Public Shared Function getDistance(ByVal lat1 As Double, ByVal lon1 As Double, ByVal lat2 As Double, ByVal lon2 As Double) As Double
Dim theta As Double
Dim dist As Double
Dim distance As Double
theta = lon1 - lon2
dist = Math.Sin(CDbl(deg2rad(lat1 ))) * Math.Sin(CDbl(deg2rad(lat2 ))) + Math.Cos(CDbl(deg2rad(lat1 ))) * Math.Cos(CDbl(deg2rad(lat2 ))) * Math.Cos(CDbl(deg2rad(thet a)))
dist = Math.Acos(dist)
dist = CDbl(rad2deg(dist))
distance = dist * 60 * 1.1515
Return distance
End Function
Private Shared Function deg2rad(ByVal Deg As Double) As Double
Return CDbl(Deg * Math.PI / 180)
End Function
Private Shared Function rad2deg(ByVal Rad As Double) As Double
Return CDbl(Rad * 180 / Math.PI)
End Function
End Class
Public Class Distance
Public Shared Function getDistance(ByVal lat1 As Double, ByVal lon1 As Double, ByVal lat2 As Double, ByVal lon2 As Double) As Double
Dim theta As Double
Dim dist As Double
Dim distance As Double
theta = lon1 - lon2
dist = Math.Sin(CDbl(deg2rad(lat1
dist = Math.Acos(dist)
dist = CDbl(rad2deg(dist))
distance = dist * 60 * 1.1515
Return distance
End Function
Private Shared Function deg2rad(ByVal Deg As Double) As Double
Return CDbl(Deg * Math.PI / 180)
End Function
Private Shared Function rad2deg(ByVal Rad As Double) As Double
Return CDbl(Rad * 180 / Math.PI)
End Function
End Class
that's what I said.
Also make sure that you have enabled clr
EXEC sp_configure 'clr enabled',1
RECONFIGURE WITH OVERRIDE
GO
Also make sure that you have enabled clr
EXEC sp_configure 'clr enabled',1
RECONFIGURE WITH OVERRIDE
GO
benchmarks on selecting variable long lat and a calculating distance from a defined position for 100,000 rows from a spatial database (OK, I'm doing a clustered index scan, with top 100,000 so this is pretty straightforward and provides a like for like comparison).
with CLR:
436 ms
with T-SQL UDF
548 ms
I eliminated the FIRST execution of the CLR process, as this had a small overhead in loading into memory for the first time (negligible, really) but wanted to make it accurate.
Then upped to 1,000,000 rows. CLR=8.2 seconds, T-SQL = 11.3 seconds.
In the long run the CLR does appear to be faster. But only significant at high volumes.
with CLR:
436 ms
with T-SQL UDF
548 ms
I eliminated the FIRST execution of the CLR process, as this had a small overhead in loading into memory for the first time (negligible, really) but wanted to make it accurate.
Then upped to 1,000,000 rows. CLR=8.2 seconds, T-SQL = 11.3 seconds.
In the long run the CLR does appear to be faster. But only significant at high volumes.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks for the great info - definitly something to keep in mind.
Public Static Function getDistance(ByVal lat1 As Double, ByVal lon1 As Double, ByVal lat2 As Double, ByVal lon2 As Double) As Double
Dim theta As Double
Dim dist As Double
Dim distance As Double
theta = lon1 - lon2
dist = Math.Sin(CDbl(deg2rad(lat1
dist = Math.Acos(dist)
dist = CDbl(rad2deg(dist))
distance = dist * 60 * 1.1515
Return distance
End Function
Private Function deg2rad(ByVal Deg As Double) As Double
Return CDbl(Deg * Math.PI / 180)
End Function
Private Function rad2deg(ByVal Rad As Double) As Double
Return CDbl(Rad * 180 / Math.PI)
End Function
End Class