there seems to be some overhead between SQL Server 2005 and a CLR function written in C#. Why is thi

[DR]

there seems to be some overhead between SQL Server 2005 and a CLR function
written in C#. Why is this?

I have a simple wraper around System.Diagnostics.Stopwatch.GetTimestamp() :

public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static long GetTimestampF()
{
return System.Diagnostics.Stopwatch.GetTimestamp();
}
};


but if i run this it is hardly accurate. the time of getting from sql server
to a CLR function seems to be relatively slow for nanosecond calculations:

DECLARE @before bigint
DECLARE @after bigint
SET @before = dbo.GetTimestampF()
-- do something
SET @after = dbo.GetTimestampF()
SELECT @after - @before as nanoseconds

is there nyway to elimitate this overhead so that dbo.GetTimestampF()
executes as fast for TSQL as it is to call GetTimestampF() in C#?

If i call my GetTimestampF() on c# it executes fast enough for the
nanosecond results to be accurate

 

[Willy Denoyette [MVP]]

there seems to be some overhead between SQL Server 2005 and a CLR function
written in C#. Why is this?

I have a simple wraper around System.Diagnostics.Stopwatch.GetTimestamp()
:

public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static long GetTimestampF()
{
return System.Diagnostics.Stopwatch.GetTimestamp();
}
};


but if i run this it is hardly accurate. the time of getting from sql
server to a CLR function seems to be relatively slow for nanosecond
calculations:

DECLARE @before bigint
DECLARE @after bigint
SET @before = dbo.GetTimestampF()
-- do something
SET @after = dbo.GetTimestampF()
SELECT @after - @before as nanoseconds

is there nyway to elimitate this overhead so that dbo.GetTimestampF()
executes as fast for TSQL as it is to call GetTimestampF() in C#?

If i call my GetTimestampF() on c# it executes fast enough for the
nanosecond results to be accurate

Note that what you are doing makes little sense, what exactly are you after?
The overhead is due to the transitioning from unmanaged code (SQL SP) into
managed code SQL/CLR and back.
Each time you cross this barrier, a security stack walk is performed, this
can take quite some time especially when you run in a safe context.

Note also that you assume that GetTimestamp returns a number of nanoseconds
which is wrong, it returns a number of ticks.
The interval between each tick depends on the clock frequency,
(Stopwatch.Frequency) so you first have to calculate the number of nano
seconds per tick, and multiply the value returned by GetTimestamp with this
value.


Willy.

 

[Rad [Visual C# MVP]]

there seems to be some overhead between SQL Server 2005 and a CLR function
written in C#. Why is this?

I have a simple wraper around System.Diagnostics.Stopwatch.GetTimestamp() :

public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static long GetTimestampF()
{
return System.Diagnostics.Stopwatch.GetTimestamp();
}
};


but if i run this it is hardly accurate. the time of getting from sql server
to a CLR function seems to be relatively slow for nanosecond calculations:

DECLARE @before bigint
DECLARE @after bigint
SET @before = dbo.GetTimestampF()
-- do something
SET @after = dbo.GetTimestampF()
SELECT @after - @before as nanoseconds

is there nyway to elimitate this overhead so that dbo.GetTimestampF()
executes as fast for TSQL as it is to call GetTimestampF() in C#?

If i call my GetTimestampF() on c# it executes fast enough for the
nanosecond results to be accurate

I wonder if it is necessary to use this particular technique.
Personally I'd take one of the following options
1) Let the application calculate the difference between the start and
the finish
2) Use SQL Server's getdate() function to get the start and the finish
and the datediff() function to determine the difference