SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 System.Diagnostics.Stopwatch
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Page47
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 11/21/2006 :  10:25:14  Show Profile  Reply with Quote
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
7020 Posts

Posted - 11/21/2006 :  10:40:36  Show Profile  Reply with Quote
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
Go to Top of Page

Page47
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 11/21/2006 :  10:51:14  Show Profile  Reply with Quote
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
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11750 Posts

Posted - 11/21/2006 :  11:09:30  Show Profile  Visit spirit1's Homepage  Reply with Quote
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
Go to Top of Page

Page47
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 11/21/2006 :  11:21:00  Show Profile  Reply with Quote
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
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11750 Posts

Posted - 11/21/2006 :  11:41:40  Show Profile  Visit spirit1's Homepage  Reply with Quote
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
Go to Top of Page

Page47
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 11/21/2006 :  12:00:33  Show Profile  Reply with Quote
Well, that is were I'm stuck ...

Jay White
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11750 Posts

Posted - 11/21/2006 :  12:27:43  Show Profile  Visit spirit1's Homepage  Reply with Quote

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
Go to Top of Page

jezemine
Flowing Fount of Yak Knowledge

USA
2885 Posts

Posted - 11/21/2006 :  12:51:02  Show Profile  Visit jezemine's Homepage  Reply with Quote
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
Go to Top of Page

Page47
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 11/21/2006 :  13:12:54  Show Profile  Reply with Quote
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
Go to Top of Page

snSQL
Flowing Fount of Yak Knowledge

USA
1837 Posts

Posted - 11/21/2006 :  13:20:47  Show Profile  Reply with Quote
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()
Go to Top of Page

snSQL
Flowing Fount of Yak Knowledge

USA
1837 Posts

Posted - 11/21/2006 :  13:25:00  Show Profile  Reply with Quote
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.
Go to Top of Page

Page47
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 11/21/2006 :  14:01:29  Show Profile  Reply with Quote
That makes sense ... thanks a lot.

Jay White
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11750 Posts

Posted - 11/22/2006 :  04:48:48  Show Profile  Visit spirit1's Homepage  Reply with Quote
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
Go to Top of Page

Page47
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 11/22/2006 :  07:10:34  Show Profile  Reply with Quote
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
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11750 Posts

Posted - 11/29/2006 :  06:27:57  Show Profile  Visit spirit1's Homepage  Reply with Quote
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
Go to Top of Page

Page47
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 11/29/2006 :  07:10:40  Show Profile  Reply with Quote
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
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11750 Posts

Posted - 11/29/2006 :  07:34:18  Show Profile  Visit spirit1's Homepage  Reply with Quote
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
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11750 Posts

Posted - 12/02/2006 :  15:51:33  Show Profile  Visit spirit1's Homepage  Reply with Quote
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

Edited by - spirit1 on 12/04/2006 06:09:35
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000