> 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()
>...
> 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#?

"Erland Sommarskog" <esquel@sommarskog.se> wrote in message
news:Xns99D6AD63D4F4BYazorman@127.0.0.1...
> DR (softwareengineer98037@yahoo.com) writes:
>> 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()
>>...
>> 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#?
>
> No. T-SQL and CLR are if not different universes or galaxies at least
> different solar systems, and the overhead for crossing that boundary
> is higher than calling a C# function from another C# function.
>
> It's also a truth that measuring the performance of something can affect
> that performance, and this amplified even more the smaller the numbers
> are. Heisenbergs principle on uncertainty is applicable here.
>
> I ran some tests, and I did not see any apparent flaw. Of course, I did
> try not to measure in nanoseconds, rather I looked at numbers in the
> millisecond range. And compared to getdate() which has a resolution
> 3.33 ms, Diagnostics.Stopwatch seems to be a lot better.
>
> Do you have any feeling for big in microseconds this overhead would be?
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

> is there something like gettimestamp for nanosecond time test in TSQL?