If you can't see the picture: stop reading, you are wasting your time.
img src=xksd.com
I used to do "custom bi", creating my own import programs (in a multitude of languages with more technologies than I care to recall), using Excel and print outs for analysis.
I now do Microsoft BI.
Loose analysis of Gartners quadrant should place custom BI close to coconuts (bellow to the left of the visible range) and MS BI close to the seeded grapes (moving towards peaches).
Gorm Braarvig
Hi!
I write scripts to generate my database, I want my scripts to run from command line using SQLCMD and (at the same time) use SSMS to edit the scripts and some times run them there. This creates a problem when dropping databases. Databases will not be dropped while they have open connections.
The solution is easy; here goes:
ALTER DATABASE x SET SINGLE_USER WITH ROLLBACK IMMEDIATE
In a sample:
PRINT 'Recreate database...'
IF EXISTS (SELECT name FROM sys.databases WHERE name='
Some_DB')
BEGIN
ALTER DATABASE Some_DB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE Some_DB
END
GO
CREATE DATABASE Some_DB
GO
Gorm Braarvig
Timing with extreme precision
I) Preface
I played with writing a generic metadata repository (prototyping) to prepare for ETL at Nortura. I'm not doing the meta data design, but want to understand what we talk about, and to get some experience it's one of two ways I could think of doing it:
I want to do both.
a) Brute force
I started with the hands-on and created a generic meta data table with support for versioning (of the model) and hierarchy:
(
GenMetumId
,GenMetumType
,GenGrupperendeMetumId
,GenMetumForrigeId -- GenMetumPreviousId
,GenMetumGyldigFra -- GenMetumValidFrom
,Navn -- Name
,EkstraXml
,StartTid -- StartTime
,SluttTid -- EndTime
,Suksess -- Success
,Nyeste -- Newest
)
then I created some stored procedures and tried to insert some rows and update them with status etc. surprise surprise:
The timestamps were mostly equal!
BOL reveals that datetime has a resolution of some 3 ms, but this does not account for the results I got. My resolution was 10-15 ms, which to me is an etarnity (I used to do low-level stuff)
This needs to be fixed. By me. Off hours. (as in hobby).
See solution in point II).
b) Adaption
I am so lucky that I am to implement ETL on one of Europes largest BI initiatives, so I'll get to be good with operational or technical meta data (I'm not versed in the jargon, yet)
II) Implementation (my favourite)
(I know design preceedes implementation in real life, but we're playing here)
a) A better .NET "DateTime.Now"
I know DateTime.Now is sufficient (10M ticks per sec). but we are playing, so let's do it properly.
WINAPI QueryPerformanceCounter
I've written some timing routines far back for Windows CE, because the .NET timer routines does not work very well on Pocket PC and Smartphone (resolution is not good). QueryPerformanceCounter and QueryPerformanceFrequency are the WINAPI methods of reading the OEM high resolution counter, which is the lowest level of time-taking on modern computers.
#region "Timer routines"
[DllImport("kernel32.dll")] static extern public bool QueryPerformanceCounter(out UInt64 i64);
[DllImport("kernel32.dll")] static extern public bool QueryPerformanceFrequency(out UInt64 i64);
static protected UInt64 i64Freq = 0;
public static UInt64 GetNowI64()
{
UInt64 i64;
if (!QueryPerformanceCounter(out i64))
i64 = 0;
return (i64 * 10000000) / GetFreq();
}
public static UInt64 GetFreq()
{
if (0 == i64Freq)
if (!QueryPerformanceFrequency(out i64Freq))
i64Freq = 1;
return i64Freq;
}
protected static DateTime dtBaseline = DateTime.MinValue;
protected static UInt64 i64Baseline = 0;
public static DateTime GetNowDt()
{
UInt64 i64Now = GetNowI64();
DateTime dt;
if (dtBaseline == DateTime.MinValue i64Baseline == 0 i64Baseline > i64Now)
{ // We have no reference point, or reference point is no longer sufficient: create one.
dtBaseline = DateTime.Now;
i64Baseline = i64Now;
dt = dtBaseline;
}
else
{ // We have a sufficient baseline, return baseline plus timespan for diff
dt = new DateTime(dtBaseline.Ticks + (Int64)(i64Now - i64Baseline));
}
return dt;
}
#endregion "Timer routines"
Converting these routines to SQL CLR is a non-brainer.
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlDateTime GetTime()
{
//return (SqlDateTime)DateTime.Now;
return (SqlDateTime)GetNowDt();
}
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString GetTimeAsString()
{
return (SqlString)(GetNowDt().ToString("O"));
}
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlInt64 GetFrequency()
{
return (SqlInt64)(Int64)GetFreq();
}
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlInt64 GetTickCount()
{
UInt64 i64;
if (!QueryPerformanceCounter(out i64))
i64 = 0;
return (SqlInt64)(Int64)i64;
}
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlInt64 GetMs()
{
return (SqlInt64)(Int64)GetNowI64();
}
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlInt64 GetTicksPast(Int64 zero)
{
UInt64 uiRes = GetNowI64() - (UInt64)zero;
return (SqlInt64)(Int64)uiRes;
}
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlInt64 GetMicrosPast(Int64 zero)
{
UInt64 uiRes = GetNowI64() / 10;
uiRes -= (UInt64)zero;
return (SqlInt64)(Int64)uiRes;
}
SQL Server import:
CREATE ASSEMBLY MeasureTime AUTHORIZATION [dbo] FROM ... WITH PERMISSION_SET = UNSAFE
CREATE FUNCTION Meta.GetFrequency() RETURNS bigint AS EXTERNAL NAME MeasureTime.UserDefinedFunctions.GetFrequency
CREATE FUNCTION Meta.GetMicrosPast(@zero bigint) RETURNS bigint AS EXTERNAL NAME MeasureTime.UserDefinedFunctions.GetMicrosPast
CREATE FUNCTION Meta.GetMs() RETURNS bigint AS EXTERNAL NAME MeasureTime.UserDefinedFunctions.GetMs
CREATE FUNCTION Meta.GetTickCount() RETURNS bigint AS EXTERNAL NAME MeasureTime.UserDefinedFunctions.GetTickCount
CREATE FUNCTION Meta.GetTicksPast(@zero bigint) RETURNS bigint AS EXTERNAL NAME MeasureTime.UserDefinedFunctions.GetTicksPast
CREATE FUNCTION Meta.GetTime() RETURNS datetime AS EXTERNAL NAME MeasureTime.UserDefinedFunctions.GetTime
CREATE FUNCTION Meta.GetTimeAsString() RETURNS nvarchar(MAX) AS EXTERNAL NAME MeasureTime.UserDefinedFunctions.GetTimeAsString
Testing with some simple queries shows me that it worked. (hope link to image is not broken)
Green arrows
These demonstrates that you can get a higher granularity on timestamps than using GetDate() with Meta.GetTime(). GetDate jumps from ...573 to ...587, while Meta.GetTime() jumps 2, 4 and 6 ms. Two other facts stands out: times differ, so .NET-time seams not equal to T-SQL time, update of 4ms means that the granularity of datetime can't be exactly 3ms.
Blue arrows
GetTicksPast() (10 000 000 pr sec) and GetMicrosPast() (1 000 000 pr sec) works as expected. Calling CLR, getting the time and putting it into a cell in temp looks to take less than 50 microseconds or less than 1/20000 second, which is faster than I expected (but we have very desent laptops).
Red arrows
Putting the same functions multiple times on the same SELECT ensured me that they are called every time (without caching). I particulary liked GetTimeAsString() which measured time in readable format exact with seven digits after secounds... higher granularity than GetDate(), no?
III) What's the business case
You tell me, I just needed to write som code and wanted to share the result.
IV) Where is the code
http://www.codeplex.com/SqlClrMeasureTime
this got kind of nerdy, next one will be better
have a nice week end
Gorm Braarvig
