| Author |
 Topic  |
|
|
Page47
Flowing Fount of Yak Knowledge
USA
2878 Posts |
 Posted - 11/21/2006 : 10:25:14
|
I'd like to expose System.Diagnostics.Stopwatch to my T-SQL procs. Initially, I was thinking I could do this with a CLR UDT, so I could do something like:
declare @sw as MyStopwatch
set @sw = 'Start'
select .... --version 1 of some complicated select
select @sw.ElapsedTime
select .... --version 2 of some complicated select
select @sw.ElapsedTime
I'm stuck on implementing IBinarySerialize ...
Is my concept impossible? Should I look at a different approach?
Jay White
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
6997 Posts |
Posted - 11/21/2006 : 10:40:36
|
Why not something like this?
declare @st datetime
set @st = getdate()
-- Some query here
waitfor delay '000:00:01.200'
select [Elapsed Time 1] = getdate() - @st
set @st = getdate()
-- Another query here
waitfor delay '000:00:02.300'
select [Elapsed Time 2] = getdate() - @st
Results:
Elapsed Time 1
------------------------------------------------------
1900-01-01 00:00:01.203
(1 row(s) affected)
Elapsed Time 2
------------------------------------------------------
1900-01-01 00:00:02.313
(1 row(s) affected)
CODO ERGO SUM |
 |
|
|
Page47
Flowing Fount of Yak Knowledge
USA
2878 Posts |
Posted - 11/21/2006 : 10:51:14
|
Cause what I'm trying to do here is learn more about SQL CLR and identify new ways I can do old things. Trust me, I know how to use T-SQL to do statement timing ... I'm trying to figure out if there is a better way to do it with CLR ..
Jay White
|
|
|
|
spirit1
Cybernetic Yak Master
Slovenia
11741 Posts |
Posted - 11/21/2006 : 11:09:30
|
do you have this
[Microsoft.SqlServer.Server.SqlUserDefinedType(Format.Native)]
or this
[Microsoft.SqlServer.Server.SqlUserDefinedType(Format.UserDefined)]
it should be first.
Go with the flow & have fun! Else fight the flow 
blog thingie: http://weblogs.sqlteam.com/mladenp |
|
|
|
Page47
Flowing Fount of Yak Knowledge
USA
2878 Posts |
Posted - 11/21/2006 : 11:21:00
|
Format.Native will now allow a member of type Stopwatch since it is not natively serializable. In order to do this IBinarySerialize must be implemented to user-define the serialization scheme.
Jay White
|
|
|
|
spirit1
Cybernetic Yak Master
Slovenia
11741 Posts |
Posted - 11/21/2006 : 11:41:40
|
of course... stopwatch is a reference type... DOH!
so how are you trying to serialize it now?
Go with the flow & have fun! Else fight the flow
blog thingie: http://weblogs.sqlteam.com/mladenp |
|
|
|
Page47
Flowing Fount of Yak Knowledge
USA
2878 Posts |
Posted - 11/21/2006 : 12:00:33
|
Well, that is were I'm stuck ...
Jay White
|
|
|
|
spirit1
Cybernetic Yak Master
Slovenia
11741 Posts |
Posted - 11/21/2006 : 12:27:43
|
public class MyStopwatch: INullable, IBinarySerialize
{
// other stuff
public void Read(System.IO.BinaryReader r)
{
// do your reading
}
public void Write(System.IO.BinaryWriter w)
{
// do your writing
}
}
if you're not doing any reading/writung then just leave them be.
or am i missing something?
Go with the flow & have fun! Else fight the flow
blog thingie: http://weblogs.sqlteam.com/mladenp |
|
|
|
jezemine
Flowing Fount of Yak Knowledge
USA
2871 Posts |
Posted - 11/21/2006 : 12:51:02
|
couldn't you just wrap StopWatch in your own class that returns types that *are* natively serializable?
SqlSpec - a fast, cheap, and comprehensive data dictionary generator for
SQL Server 2000/2005 and Analysis Server 2005 - http://www.elsasoft.org |
|
|
|
Page47
Flowing Fount of Yak Knowledge
USA
2878 Posts |
Posted - 11/21/2006 : 13:12:54
|
I'm just not getting it ... if someone can author a Stopwatch UDT, based on System.Diagnostics.Stopwatch ... even if it only exposes start and elapsedtime ... please post here that you were able to get it to work ... you don't have to share the code ... until then, I'm giving up on it.
As best I can tell, if you don't serialize the stopwatch (not just its return types), it's state doesn't get persisted from one sql call to the next.
Jay White
|
|
|
|
snSQL
Flowing Fount of Yak Knowledge
USA
1837 Posts |
Posted - 11/21/2006 : 13:20:47
|
You're taking the wrong approach. You need to remember that although you're writing object oriented code, you can't use objected oriented code in SQL Server because it has no notion of objects.
So you have to create everything as a bunch of static methods that you then call as stored procs or functions. You won't create objects and call methods on them at all.
Here is a working example.
The C# code first
using System;
using System.Data;
using System.Diagnostics;
public class MyStopwatch
{
private static readonly Stopwatch sw = new Stopwatch();
public static int StopwatchReset()
{
sw.Reset();
return 0;
}
public static int StopwatchStart()
{
sw.Start();
return 0;
}
public static int StopwatchStop()
{
sw.Stop();
return 0;
}
public static long StopwatchElapsedSeconds()
{
return (long)sw.Elapsed.TotalSeconds;
}
public static long StopwatchElapsedMilliseconds()
{
return sw.ElapsedMilliseconds;
}
public static long StopwatchElapsedTicks()
{
return sw.ElapsedTicks;
}
}
Now the SQL declarations
sp_configure 'clr enabled', 1
RECONFIGURE
--DROP ASSEMBLY MyStopwatch
CREATE ASSEMBLY MyStopwatch
FROM 'E:\CLRCode\MyStopwatch.dll'
GO
--DROP FUNCTION dbo.StopwatchReset
--DROP FUNCTION dbo.StopwatchStart
--DROP FUNCTION dbo.StopwatchStop
--DROP FUNCTION dbo.StopwatchElapsedSeconds
--DROP FUNCTION dbo.StopwatchElapsedMilliseconds
--DROP FUNCTION dbo.StopwatchElapsedTicks
CREATE FUNCTION dbo.StopwatchReset()
RETURNS int
AS EXTERNAL NAME MyStopwatch.MyStopwatch.StopwatchReset
GO
CREATE FUNCTION dbo.StopwatchStart()
RETURNS int
AS EXTERNAL NAME MyStopwatch.MyStopwatch.StopwatchStart
GO
CREATE FUNCTION dbo.StopwatchStop()
RETURNS int
AS EXTERNAL NAME MyStopwatch.MyStopwatch.StopwatchStop
GO
CREATE FUNCTION dbo.StopwatchElapsedSeconds()
RETURNS bigint
AS EXTERNAL NAME MyStopwatch.MyStopwatch.StopwatchElapsedSeconds
GO
CREATE FUNCTION dbo.StopwatchElapsedMilliseconds()
RETURNS bigint
AS EXTERNAL NAME MyStopwatch.MyStopwatch.StopwatchElapsedMilliseconds
GO
CREATE FUNCTION dbo.StopwatchElapsedTicks()
RETURNS bigint
AS EXTERNAL NAME MyStopwatch.MyStopwatch.StopwatchElapsedTicks
And finally the SQL function calls
select dbo.StopwatchStart()
select dbo.StopwatchElapsedSeconds()
select dbo.StopwatchElapsedMilliseconds()
select dbo.StopwatchStop()
select dbo.StopwatchElapsedSeconds()
select dbo.StopwatchElapsedMilliseconds()
select dbo.StopwatchReset()
select dbo.StopwatchElapsedSeconds()
select dbo.StopwatchElapsedMilliseconds()
select dbo.StopwatchStart()
select dbo.StopwatchElapsedSeconds()
select dbo.StopwatchElapsedMilliseconds()
|
|
|
|
snSQL
Flowing Fount of Yak Knowledge
USA
1837 Posts |
Posted - 11/21/2006 : 13:25:00
|
| One note about this example - it will create a single static stopwatch that all connections will see. If you wanted multiple stopwatches you'd have to code a check for the connection id and keep them separately that way. Again, the SQL Server calls don't create instances, so you have to code things from an entirely static perspective. |
|
|
|
Page47
Flowing Fount of Yak Knowledge
USA
2878 Posts |
Posted - 11/21/2006 : 14:01:29
|
That makes sense ... thanks a lot.
Jay White
|
|
|
|
spirit1
Cybernetic Yak Master
Slovenia
11741 Posts |
|
|
Page47
Flowing Fount of Yak Knowledge
USA
2878 Posts |
Posted - 11/22/2006 : 07:10:34
|
I don't think you can use QueryPerformanceCounter() and have it be a safe assembly ... I haven't yet played with signing and stuff ...
Jay White
|
|
|
|
spirit1
Cybernetic Yak Master
Slovenia
11741 Posts |
|
|
Page47
Flowing Fount of Yak Knowledge
USA
2878 Posts |
Posted - 11/29/2006 : 07:10:40
|
Awesome. One question. Before I play with what you've done ... is this still a "one timer running per server" scenerio or with the QueryPerformanceCounter solution, can you have a timer run in every proc?
The catalyst for this post is that I have a "DebugMode" param on each of my procs that when set prints debugging and timing information. I have it written using the good old getdate and datediff(ms, @DebugTimerStart, getdate()) method. I was looking to replace this with something a bit more advanced.
Thanks.
Jay White
|
|
|
|
spirit1
Cybernetic Yak Master
Slovenia
11741 Posts |
Posted - 11/29/2006 : 07:34:18
|
it's still a "one timer running per server"
i didn't have time to go more in depth... when i do i'll play with that.
Go with the flow & have fun! Else fight the flow
blog thingie: http://weblogs.sqlteam.com/mladenp |
|
|
|
spirit1
Cybernetic Yak Master
Slovenia
11741 Posts |
|
| |
Topic |
|
System.Diagnostics.Stopwatch
