System.Diagnostics.Stopwatch - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

System.Diagnostics.Stopwatch

Author

Topic

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2006-11-21 : 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)

7020 Posts

Posted - 2006-11-21 : 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
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2006-11-21 : 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

11752 Posts

Posted - 2006-11-21 : 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
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2006-11-21 : 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

11752 Posts

Posted - 2006-11-21 : 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
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2006-11-21 : 12:00:33

Well, that is were I'm stuck ...

Jay White

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-11-21 : 12:27:43

[code]
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
}
}
[/code]

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
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2006-11-21 : 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
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2006-11-21 : 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
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-21 : 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
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-21 : 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
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2006-11-21 : 14:01:29

That makes sense ... thanks a lot.

Jay White

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-11-22 : 04:48:48

this might be of interest to you jay
http://www.codeproject.com/csharp/highperformancetimercshar.asp

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2006-11-22 : 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

11752 Posts

Posted - 2006-11-29 : 06:27:57

i played a bit

http://weblogs.sqlteam.com/mladenp/archive/2006/11/29/35688.aspx

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2006-11-29 : 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

11752 Posts

Posted - 2006-11-29 : 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

11752 Posts

Posted - 2006-12-02 : 15:51:33

here you go:
http://weblogs.sqlteam.com/mladenp/archive/2006/12/02/39124.aspx

a full blown ready to use multi connection Hi Perf Timer.

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources