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

27 Oct 2007 4:24 AM

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

29 Oct 2007 2:30 PM

Cowboy (Gregory A. Beamer)

Is this true everytime you call the routine, or only the first. There will
be some JIT overhead on the first call. This is why websites now have the
ability to run through all of the pages and pre-JIT compile them.

Other than that, I would have to look at the SQL docs.

--
Gregory A. Beamer
MVP, MCP: +I, SE, SD, DBA

*************************************************

Show quote

| Think outside the box!
|
*************************************************
"DR" <softwareengineer98***@yahoo.com> wrote in message
news:evqLAFFGIHA.3400@TK2MSFTNGP03.phx.gbl...
> 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
>