July 3, 2012 at 5:26 am
Gullimeel (7/3/2012)
@JeffI would add following.This will be more helpful if most of my rows are not guid.But if most of them are GUID the I might stick with what you have posted..
Did you try to use the convert(uniqueidentifier) with begin try etc.? I know you wont be able to use that as in line function..
SELECT IsGuid = CASE
when (len(@guid) <> 36 or LEN(replace(@guid,'-','')) <> 32 or PATINDEX('%[G-Z]%',@guid) <> 0) then 0
WHEN @GUID LIKE '[0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F]-[0-9A-F][0-9A-F][0-9A-F][0-9A-F]-[0-9A-F][0-9A-F][0-9A-F][0-9A-F]-[0-9A-F][0-9A-F][0-9A-F][0-9A-F]-[0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F]'
THEN 1
ELSE 0
END
How would you like this to be tested? As a stored proc, a function, or direct code?
--Jeff Moden
Change is inevitable... Change for the better is not.
July 3, 2012 at 9:09 am
How would you like this to be tested? As a stored proc, a function, or direct code?
--Jeff Moden
Which one to test? Using the unqiueidentifier or the code snip i have given to you?
GulliMeel
Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]
July 3, 2012 at 9:47 am
Gullimeel (7/3/2012)
How would you like this to be tested? As a stored proc, a function, or direct code?
--Jeff Moden
Which one to test? Using the unqiueidentifier or the code snip i have given to you?
The one that had attached to the same post I asked the question in.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 3, 2012 at 11:50 am
I used your IVF and added my statement before your like statement as I did in the code snip...
drop FUNCTION dbo.IsGUIDGulliMeel
go
CREATE FUNCTION dbo.IsGUIDGulliMeel
(@pSomeString VARCHAR(100))
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
SELECT IsGuid = CASE
when (len(@pSomeString) <> 36 or LEN(replace(@pSomeString,'-','')) <> 32 or charindex('-',@psomestring) <> 9 or PATINDEX('%[G-Z]%',@pSomeString) <> 0 )
Then 0
WHEN @pSomeString LIKE '[0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F]-[0-9A-F][0-9A-F][0-9A-F][0-9A-F]-[0-9A-F][0-9A-F][0-9A-F][0-9A-F]-[0-9A-F][0-9A-F][0-9A-F][0-9A-F]-[0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F]'
THEN 1
ELSE 0
END
;
I ran it for 4 diff scenarios.. Results are below..
drop table myguid
go
create table myguid(myguid varchar(100) not null)
go
insert into myguid
select convert(varchar(100),NEWID())
from Sales.SalesOrderDetail
go
alter table myguid add constraint pk_myguid primary key(myguid)
go
/*
as expected
Jeff Method 2059ms
GulliMeel Method 3401 ms
*/
--len is not 36
drop table myguid
go
create table myguid(myguid varchar(100) not null)
go
insert into myguid
select substring(convert(varchar(100),NEWID()),1,30)
from Sales.SalesOrderDetail
go
alter table myguid add constraint pk_myguid primary key(myguid)
go
/* as expected
Jeff Method 1857ms
GulliMeel Method 47 ms
*/
--there are 3 '-'
drop table myguid
go
create table myguid(myguid varchar(100) not null)
go
insert into myguid
select stuff(convert(varchar(100),NEWID()),24,1,'0')
from Sales.SalesOrderDetail
go
alter table myguid add constraint pk_myguid primary key(myguid)
go
/* as expected
Jeff Method 1372ms
GulliMeel Method 671 ms
*/
--there are no '-'
drop table myguid
go
create table myguid(myguid varchar(100) not null)
go
insert into myguid
select replace(convert(varchar(100),NEWID()),'-','0')
from Sales.SalesOrderDetail
go
alter table myguid add constraint pk_myguid primary key(myguid)
go
/* This i did not expect but after looking at the code realized both are almost similar in terms
--that your method will stop after reading first 9 characters and 9th character is not a '-' so it wont progress
--further and return 0
Jeff Method 593ms
GulliMeel Method 572 ms
*/
GulliMeel
Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]
July 3, 2012 at 12:04 pm
I see test setup code. Where is the test code so I can verify the measurement methods?
--Jeff Moden
Change is inevitable... Change for the better is not.
July 3, 2012 at 12:15 pm
set statistics io,time on
go
select SUM(ca.IsGuid) from myguid mg
cross apply
IsGuid(mg.myguid) ca
go
select SUM(ca.IsGuid) from myguid mg
cross apply
IsGuidGulliMeel(mg.myguid) ca
go
Sorry forgot to add that... I took the time for second run to make sure that data was in cache...I have used sum to make sure that I do not get too many rows..Out of this CPU time some must be used by sum function as well..
GulliMeel
Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]
July 3, 2012 at 2:54 pm
How many rows are you testing with? I.e are in myguid? (I couldn't see it mentioned in the previous posts but apologises if I've missed it)
regards
david
July 3, 2012 at 8:03 pm
Gullimeel (7/3/2012)
set statistics io,time on
go
select SUM(ca.IsGuid) from myguid mg
cross apply
IsGuid(mg.myguid) ca
go
select SUM(ca.IsGuid) from myguid mg
cross apply
IsGuidGulliMeel(mg.myguid) ca
go
Sorry forgot to add that... I took the time for second run to make sure that data was in cache...I have used sum to make sure that I do not get too many rows..Out of this CPU time some must be used by sum function as well..
Oddly enough, I submitted an article just two days ago on the possible problems of using SET STATISTICS for performance evaluations like this. It may not be a problem in this particular instance but I'll double check.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 3, 2012 at 9:37 pm
How many rows are you testing with? I.e are in myguid? (I couldn't see it mentioned in the previous posts but apologises if I've missed it)
regards
david
I tried with 121317 rows..same as Sales.SaleOrderdetail table...
GulliMeel
Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]
July 3, 2012 at 9:39 pm
Gullimeel (7/3/2012)
--------------------------------------------------------------------------------
set statistics io,time on
go
select SUM(ca.IsGuid) from myguid mg
cross apply
IsGuid(mg.myguid) ca
go
select SUM(ca.IsGuid) from myguid mg
cross apply
IsGuidGulliMeel(mg.myguid) ca
go
Sorry forgot to add that... I took the time for second run to make sure that data was in cache...I have used sum to make sure that I do not get too many rows..Out of this CPU time some must be used by sum function as well..
Oddly enough, I submitted an article just two days ago on the possible problems of using SET STATISTICS for performance evaluations like this. It may not be a problem in this particular instance but I'll double check.
--Jeff Moden
I would like to read the article,Could you please provide the link? I should not have used at least IO as that was not the concern..
GulliMeel
Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]
July 3, 2012 at 10:05 pm
I just submitted it for review 2 days ago. It probably won't be published for at least 3 weeks.
As a side bar, I'm almost done testing and will post the test harness and results in about a half hour or so.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 3, 2012 at 11:38 pm
Ok, here we go.
I tested using Gullimeel's "SUM" method with the PK he used and I tested using loops. Each run type starts with no non-GUID values and then increases the number of non-GUID values by 10% for each iteration until everything is a non-GUID value. The problem with the SUM method is that it rather unfairly taxes the CLR because the CLR returns a BIT datatype which must be converted to an INT before it can be summed. Of course, this is one more reason to NOT use a BIT datatype as a return from a function, if at all possible.
Then, because I object to the idea that you can safely put a PK on an unknown column of data that can contain virtually anything including some GUIDs, I ran the whole shootin' match over again using a "throw away" variable to take the display and disk I/O times out of the picture very much like the use of SUM did.
In most all cases (index or not), the simple "Jeff" function held it's own against the CLR (even after removing the "bit" penalty where it finally passed about half the time) and the "Gullimeel" function didn't catch up and then pass until more than 50% of the data was non-GUID data.
I used the same number of rows that Gullimeel used (121,317) for each test. I'm not sure where the "47ms" time came from in his tests unless he had some massive parallelism going on. I'm limited to only 2 - 2.8GHz processors on my laptop for testing. It's also why I like to see complete test harnesses so that folks don't have to try to recreate what actually happened.
Here are the functions I used... I didn't use any of the stored proc methods, just the functions. Someone else can test the stored procs.
CREATE FUNCTION dbo.IsGUID_Jeff
/*******************************************************************************
Purpose:
If the given string parameter matches the format of GUID, return a 1.
Otherwise, return a 0.
Notes:
1. This is what Microsoft refers to as an "Inline Scalar Function" and it's
MUCH faster than a regular or classic Scalar Function.
Example Batch Usage:
SELECT st.SomeVarcharColumn,
ig.IsGUID
FROM dbo.SomeTable st
CROSS APPLY dbo.IsGUID(st.SomeVarcharColume) ig
Example Single Variable Usage:
SELECT IsGUID
FROM dbo.IsGUID(@SomeString)
Revision History:
Rev 00 - 2 Jul 2012 - Jeff Moden - Initial creation
Ref: http://www.sqlservercentral.com/Forums/Topic1323353-392-1.aspx
*******************************************************************************/
(@pSomeString VARCHAR(100))
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
SELECT IsGuid = CASE
WHEN @pSomeString LIKE '[0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F]-[0-9A-F][0-9A-F][0-9A-F][0-9A-F]-[0-9A-F][0-9A-F][0-9A-F][0-9A-F]-[0-9A-F][0-9A-F][0-9A-F][0-9A-F]-[0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F]'
THEN 1
ELSE 0
END
;
GO
/*******************************************************************************
Create David's CLR
*******************************************************************************/
CREATE ASSEMBLY [IsGUID]
AUTHORIZATION [dbo]
FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C0103002321F24F0000000000000000E00002210B010800000A000000060000000000000E290000002000000040000000004000002000000002000004000000000000000400000000000000008000000002000000000000030040850000100000100000000010000010000000000000100000000000000000000000B428000057000000004000004003000000000000000000000000000000000000006000000C000000402800001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E746578740000001409000000200000000A000000020000000000000000000000000000200000602E72737263000000400300000040000000040000000C0000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000001000000000000000000000000000004000004200000000000000000000000000000000F028000000000000480000000200050074210000CC060000010000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000013300200180100000100001100170A0F00FE16030000016F1100000A0B0772010000706F1200000A130511052D0C720100007007281300000A0B0772050000706F1400000A130511052D0C077205000070281300000A0B076F1500000A1F26FE01130511052D0900160A0038A700000000160C38900000000007086F1600000A0D08130611061F0E30131106162E2211061F092E2B11061F0E2E252B4111061F132E1D11061F182E1711061F252E202B2D091F7BFE01130511052D02160A2B43091F2DFE01130511052D02160A2B34091F7DFE01130511052D02160A2B2509281700000A2D12091F41320A091F46FE0216FE012B01162B0117130511052D02160A2B00000817580C081F26FE04130511053A62FFFFFF0006281800000A13042B0011042A42534A4201000100000000000C00000076322E302E35303732370000000005006C00000024020000237E0000900200000C03000023537472696E6773000000009C0500000C00000023555300A8050000100000002347554944000000B80500001401000023426C6F620000000000000002000001471502000900000000FA0133001600000100000016000000020000000100000001000000180000000E00000001000000010000000200000000000A00010000000000060033002C000A005B0046000A00660046000600960084000600B30084000600D00084000600EF00840006000801840006002101840006003C01840006005701840006008F0170010600A30170010600B10184000600CA0184000600FA01E70143000E02000006003D021D0206005D021D020A009D0282020600BB022C000600F2022C0000000000010000000000010001008101100015000000050001000100502000000000960070000A0001000000010077002100AD0011002900AD0011003100AD0011003900AD0011004100AD0011004900AD0011005100AD0011005900AD0011006100AD0016006900AD0011007100AD0011007900AD0011008100AD001B009100AD0021009900AD002600A100AD0026000900B2022F00A900C2023300A900CD023800A900D4023300A900DD023E00A900E8024200B100F7024700110000034C00200083002A002E00330094002E0013006E002E001B006E002E00230074002E002B008A002E000B005D002E003B006E002E004B006E002E005300AC002E006300D6002E006B00E3002E007300EC002E007B00F50052000480000001000000000000000000000000007B020000020000000000000000000000010023000000000002000000000000000000000001003A00000000000000003C4D6F64756C653E004973475549442E646C6C004775696446756E6374696F6E73006D73636F726C69620053797374656D004F626A6563740053797374656D2E446174610053797374656D2E446174612E53716C54797065730053716C426F6F6C65616E0053716C537472696E670049734775696400706F737369626C65475549440053797374656D2E5265666C656374696F6E00417373656D626C795469746C65417474726962757465002E63746F7200417373656D626C794465736372697074696F6E41747472696275746500417373656D626C79436F6E66696775726174696F6E41747472696275746500417373656D626C79436F6D70616E7941747472696275746500417373656D626C7950726F6475637441747472696275746500417373656D626C79436F7079726967687441747472696275746500417373656D626C7954726164656D61726B41747472696275746500417373656D626C7943756C747572654174747269627574650053797374656D2E52756E74696D652E496E7465726F70536572766963657300436F6D56697369626C65417474726962757465004775696441747472696275746500417373656D626C7956657273696F6E41747472696275746500417373656D626C7946696C6556657273696F6E4174747269627574650053797374656D2E446961676E6F73746963730044656275676761626C6541747472696275746500446562756767696E674D6F6465730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C69747941747472696275746500497347554944004D6963726F736F66742E53716C5365727665722E5365727665720053716C46756E6374696F6E41747472696275746500546F537472696E6700537472696E67005374617274735769746800436F6E63617400456E647357697468006765745F4C656E677468006765745F436861727300436861720049734E756D626572006F705F496D706C696369740000037B0000037D0000000000752A02430862FA4783ABD7085DD16F3F0008B77A5C561934E0890600011109110D042001010E042001010205200101114504200101080320000104010000000320000E042001020E0500020E0E0E03200008042001030804000102030500011109020A0707020E0803110902081001000B49734755494420546573740000050100000000150100104461766964204265747465726964676500000901000454657374000017010012436F7079726967687420C2A920203230313200002901002462633835333634662D373034322D343534302D396330372D61616632376330323264333600000C010007312E302E302E3000000801000701000000000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F777301000000002321F24F0000000002000000580000005C2800005C0A00005253445315F7FB80F10E0D4CA9E5A61F15E1F11202000000433A5C506572736F6E616C5C446176696420426574746572696467655C4973475549445C4973475549445C6F626A5C44656275675C4973475549442E70646200DC2800000000000000000000FE280000002000000000000000000000000000000000000000000000F02800000000000000000000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF2500204000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000100100000001800008000000000000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058400000E80200000000000000000000E80234000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000001000000000000000100000000003F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B00448020000010053007400720069006E006700460069006C00650049006E0066006F00000024020000010030003000300030003000340062003000000044001100010043006F006D00700061006E0079004E0061006D0065000000000044006100760069006400200042006500740074006500720069006400670065000000000040000C000100460069006C0065004400650073006300720069007000740069006F006E0000000000490073004700550049004400200054006500730074000000300008000100460069006C006500560065007200730069006F006E000000000031002E0030002E0030002E003000000038000B00010049006E007400650072006E0061006C004E0061006D00650000004900730047005500490044002E0064006C006C00000000004800120001004C006500670061006C0043006F007000790072006900670068007400000043006F0070007900720069006700680074002000A900200020003200300031003200000040000B0001004F0072006900670069006E0061006C00460069006C0065006E0061006D00650000004900730047005500490044002E0064006C006C00000000002C0005000100500072006F0064007500630074004E0061006D00650000000000540065007300740000000000340008000100500072006F006400750063007400560065007200730069006F006E00000031002E0030002E0030002E003000000038000800010041007300730065006D0062006C0079002000560065007200730069006F006E00000031002E0030002E0030002E0030000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000C000000103900000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
WITH PERMISSION_SET = SAFE
GO
CREATE FUNCTION IsGUID(@possibleGUID nvarchar(4000)) RETURNS bit
AS EXTERNAL NAME IsGUID.GuidFunctions.IsGuid;
GO
/*******************************************************************************
Create Gullimeel's function
*******************************************************************************/
CREATE FUNCTION dbo.IsGUIDGulliMeel
(@pSomeString VARCHAR(100))
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
SELECT IsGuid = CASE
when (len(@pSomeString) <> 36 or LEN(replace(@pSomeString,'-','')) <> 32 or charindex('-',@psomestring) <> 9 or PATINDEX('%[G-Z]%',@pSomeString) <> 0 )
Then 0
WHEN @pSomeString LIKE '[0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F]-[0-9A-F][0-9A-F][0-9A-F][0-9A-F]-[0-9A-F][0-9A-F][0-9A-F][0-9A-F]-[0-9A-F][0-9A-F][0-9A-F][0-9A-F]-[0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F]'
THEN 1
ELSE 0
END
;
GO
Here's the complete test harness and it includes ALL the runs I ran.
/***********************************************************************************************************************
Create and populate the test table (100% GUID) to start the tests with
***********************************************************************************************************************/
SET STATISTICS TIME OFF;
RAISERROR ('==========================================================================================',0,1) WITH NOWAIT;
RAISERROR ('Building the test data (Length Test)',0,1) WITH NOWAIT;
RAISERROR ('==========================================================================================',0,1) WITH NOWAIT;
--===== Conditionally drop the test table to make reruns in SSMS easier
IF OBJECT_ID('tempdb..#TestTable','U') IS NOT NULL
DROP TABLE #TestTable
;
GO
--===== Create and populate a million row test table on-the-fly.
SELECT TOP 121317
RowNum = IDENTITY(INT,1,1),
SomeVarcharColumn = ISNULL(CAST(NEWID() AS VARCHAR(100)),'') --ISNULL makes the column NOT NULL
INTO #TestTable
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2
;
--===== Add a PK
ALTER TABLE #TestTable ADD CONSTRAINT PK_#TestTable PRIMARY KEY(SomeVarcharColumn);
--===== Just some obviously name support variables
DECLARE @Counter INT,
--@Trash BIT,
@Percent INT;
SELECT @Counter = 0
WHILE @Counter < 11 --Each count will modify 10% of the table with first loop being 100% GUID and last being 100% non-GUID
BEGIN
--===== Calculate the % of non-GUID info
SELECT @Percent = @Counter*10;
--===== Mark each iteration of the test
RAISERROR ('==========================================================================================',0,1) WITH NOWAIT;
RAISERROR ('Working on %u percent non-GUID info (Length Test)',0,1,@Percent) WITH NOWAIT;
RAISERROR ('==========================================================================================',0,1) WITH NOWAIT;
RAISERROR ('---------- IsGUID_Jeff -------------------------------------------------------------------',0,1) WITH NOWAIT;
DBCC FREEPROCCACHE;
SET STATISTICS TIME ON;
SELECT SUM(ig.IsGuid)
FROM #TestTable test
CROSS APPLY dbo.IsGUID_Jeff(SomeVarcharColumn) ig;
SET STATISTICS TIME OFF;
RAISERROR ('---------- IsGUID David ------------------------------------------------------------------',0,1) WITH NOWAIT;
DBCC FREEPROCCACHE;
SET STATISTICS TIME ON;
SELECT SUM(CAST(dbo.IsGuid(SomeVarcharColumn) AS INT))
FROM #TestTable test;
SET STATISTICS TIME OFF;
RAISERROR ('---------- IsGUIDGulliMeel ---------------------------------------------------------------',0,1) WITH NOWAIT;
DBCC FREEPROCCACHE;
SET STATISTICS TIME ON;
SELECT SUM(ig.IsGuid)
FROM #TestTable test
CROSS APPLY dbo.IsGUIDGulliMeel(SomeVarcharColumn) ig;
SET STATISTICS TIME OFF;
RAISERROR ('---------- Updating table for next percent -----------------------------------------------',0,1) WITH NOWAIT;
--===== Increment the counter
SELECT @Counter = @Counter +1;
--===== Change another 10% to non-GUID data
UPDATE #TestTable
SET SomeVarcharColumn = SUBSTRING(SomeVarcharColumn,1,30)
WHERE RowNum%10 = @Counter;
END
;
RAISERROR ('==========================================================================================',0,1) WITH NOWAIT;
RAISERROR ('Length Test RUN COMPLETE.',0,1) WITH NOWAIT;
GO
/***********************************************************************************************************************
Create and populate the test table (100% GUID) to start the tests with
***********************************************************************************************************************/
SET STATISTICS TIME OFF;
RAISERROR ('==========================================================================================',0,1) WITH NOWAIT;
RAISERROR ('Building the test data (Missing dash test)',0,1) WITH NOWAIT;
RAISERROR ('==========================================================================================',0,1) WITH NOWAIT;
--===== Conditionally drop the test table to make reruns in SSMS easier
IF OBJECT_ID('tempdb..#TestTable','U') IS NOT NULL
DROP TABLE #TestTable
;
GO
--===== Create and populate a million row test table on-the-fly.
SELECT TOP 121317
RowNum = IDENTITY(INT,1,1),
SomeVarcharColumn = ISNULL(CAST(NEWID() AS VARCHAR(100)),'') --ISNULL makes the column NOT NULL
INTO #TestTable
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2
;
--===== Add a PK
ALTER TABLE #TestTable ADD CONSTRAINT PK_#TestTable PRIMARY KEY(SomeVarcharColumn);
--===== Just some obviously name support variables
DECLARE @Counter INT,
@Trash BIT,
@Percent INT;
SELECT @Counter = 0
WHILE @Counter < 11
BEGIN
--===== Calculate the % of non-GUID info
SELECT @Percent = @Counter*10;
--===== Mark each iteration of the test
RAISERROR ('==========================================================================================',0,1) WITH NOWAIT;
RAISERROR ('Working on %u percent non-GUID info (Missing dash test)',0,1,@Percent) WITH NOWAIT;
RAISERROR ('==========================================================================================',0,1) WITH NOWAIT;
RAISERROR ('---------- IsGUID_Jeff -------------------------------------------------------------------',0,1) WITH NOWAIT;
DBCC FREEPROCCACHE;
SET STATISTICS TIME ON;
SELECT SUM(ig.IsGuid)
FROM #TestTable test
CROSS APPLY dbo.IsGUID_Jeff(SomeVarcharColumn) ig;
SET STATISTICS TIME OFF;
RAISERROR ('---------- IsGUID David ------------------------------------------------------------------',0,1) WITH NOWAIT;
DBCC FREEPROCCACHE;
SET STATISTICS TIME ON;
SELECT SUM(CAST(dbo.IsGuid(SomeVarcharColumn) AS INT))
FROM #TestTable test;
SET STATISTICS TIME OFF;
RAISERROR ('---------- IsGUIDGulliMeel ---------------------------------------------------------------',0,1) WITH NOWAIT;
DBCC FREEPROCCACHE;
SET STATISTICS TIME ON;
SELECT SUM(ig.IsGuid)
FROM #TestTable test
CROSS APPLY dbo.IsGUIDGulliMeel(SomeVarcharColumn) ig;
SET STATISTICS TIME OFF;
RAISERROR ('---------- Updating table for next percent -----------------------------------------------',0,1) WITH NOWAIT;
--===== Increment the counter
SELECT @Counter = @Counter +1;
--===== Change another 10% to non-GUID data (Dash at character 24 changed)
UPDATE #TestTable
SET SomeVarcharColumn = STUFF(SomeVarcharColumn,24,1,'0')
WHERE RowNum%10 = @Counter;
END
;
RAISERROR ('==========================================================================================',0,1) WITH NOWAIT;
RAISERROR ('Length Test RUN COMPLETE.',0,1) WITH NOWAIT;
GO
PRINT '====================================================================================================================================';
PRINT 'Changed from SUM to trash variable and no index (like a staging table would be)'
PRINT '====================================================================================================================================';
/***********************************************************************************************************************
Create and populate the test table (100% GUID) to start the tests with
***********************************************************************************************************************/
SET STATISTICS TIME OFF;
RAISERROR ('==========================================================================================',0,1) WITH NOWAIT;
RAISERROR ('Building the test data (Length Test)',0,1) WITH NOWAIT;
RAISERROR ('==========================================================================================',0,1) WITH NOWAIT;
--===== Conditionally drop the test table to make reruns in SSMS easier
IF OBJECT_ID('tempdb..#TestTable','U') IS NOT NULL
DROP TABLE #TestTable
;
GO
--===== Create and populate a million row test table on-the-fly.
SELECT TOP 121317
RowNum = IDENTITY(INT,1,1),
SomeVarcharColumn = ISNULL(CAST(NEWID() AS VARCHAR(100)),'') --ISNULL makes the column NOT NULL
INTO #TestTable
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2
;
--===== Just some obviously name support variables
DECLARE @Counter INT,
@Trash BIT,
@Percent INT;
SELECT @Counter = 0
WHILE @Counter < 11 --Each count will modify 10% of the table with first loop being 100% GUID and last being 100% non-GUID
BEGIN
--===== Calculate the % of non-GUID info
SELECT @Percent = @Counter*10;
--===== Mark each iteration of the test
RAISERROR ('==========================================================================================',0,1) WITH NOWAIT;
RAISERROR ('Working on %u percent non-GUID info (Length Test)',0,1,@Percent) WITH NOWAIT;
RAISERROR ('==========================================================================================',0,1) WITH NOWAIT;
RAISERROR ('---------- IsGUID_Jeff -------------------------------------------------------------------',0,1) WITH NOWAIT;
DBCC FREEPROCCACHE;
SET STATISTICS TIME ON;
SELECT @Trash = ig.IsGuid
FROM #TestTable test
CROSS APPLY dbo.IsGUID_Jeff(SomeVarcharColumn) ig;
SET STATISTICS TIME OFF;
RAISERROR ('---------- IsGUID David ------------------------------------------------------------------',0,1) WITH NOWAIT;
DBCC FREEPROCCACHE;
SET STATISTICS TIME ON;
SELECT @Trash = dbo.IsGuid(SomeVarcharColumn)
FROM #TestTable test;
SET STATISTICS TIME OFF;
RAISERROR ('---------- IsGUIDGulliMeel ---------------------------------------------------------------',0,1) WITH NOWAIT;
DBCC FREEPROCCACHE;
SET STATISTICS TIME ON;
SELECT @Trash = ig.IsGuid
FROM #TestTable test
CROSS APPLY dbo.IsGUIDGulliMeel(SomeVarcharColumn) ig;
SET STATISTICS TIME OFF;
RAISERROR ('---------- Updating table for next percent -----------------------------------------------',0,1) WITH NOWAIT;
--===== Increment the counter
SELECT @Counter = @Counter +1;
--===== Change another 10% to non-GUID data
UPDATE #TestTable
SET SomeVarcharColumn = SUBSTRING(SomeVarcharColumn,1,30)
WHERE RowNum%10 = @Counter;
END
;
RAISERROR ('==========================================================================================',0,1) WITH NOWAIT;
RAISERROR ('Length Test RUN COMPLETE.',0,1) WITH NOWAIT;
GO
/***********************************************************************************************************************
Create and populate the test table (100% GUID) to start the tests with
***********************************************************************************************************************/
SET STATISTICS TIME OFF;
RAISERROR ('==========================================================================================',0,1) WITH NOWAIT;
RAISERROR ('Building the test data (Missing dash test)',0,1) WITH NOWAIT;
RAISERROR ('==========================================================================================',0,1) WITH NOWAIT;
--===== Conditionally drop the test table to make reruns in SSMS easier
IF OBJECT_ID('tempdb..#TestTable','U') IS NOT NULL
DROP TABLE #TestTable
;
GO
--===== Create and populate a million row test table on-the-fly.
SELECT TOP 121317
RowNum = IDENTITY(INT,1,1),
SomeVarcharColumn = ISNULL(CAST(NEWID() AS VARCHAR(100)),'') --ISNULL makes the column NOT NULL
INTO #TestTable
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2
;
--===== Just some obviously name support variables
DECLARE @Counter INT,
@Trash BIT,
@Percent INT;
SELECT @Counter = 0
WHILE @Counter < 11
BEGIN
--===== Calculate the % of non-GUID info
SELECT @Percent = @Counter*10;
--===== Mark each iteration of the test
RAISERROR ('==========================================================================================',0,1) WITH NOWAIT;
RAISERROR ('Working on %u percent non-GUID info (Missing dash test)',0,1,@Percent) WITH NOWAIT;
RAISERROR ('==========================================================================================',0,1) WITH NOWAIT;
RAISERROR ('---------- IsGUID_Jeff -------------------------------------------------------------------',0,1) WITH NOWAIT;
DBCC FREEPROCCACHE;
SET STATISTICS TIME ON;
SELECT @Trash = ig.IsGuid
FROM #TestTable test
CROSS APPLY dbo.IsGUID_Jeff(SomeVarcharColumn) ig;
SET STATISTICS TIME OFF;
RAISERROR ('---------- IsGUID David ------------------------------------------------------------------',0,1) WITH NOWAIT;
DBCC FREEPROCCACHE;
SET STATISTICS TIME ON;
SELECT @Trash = dbo.IsGuid(SomeVarcharColumn)
FROM #TestTable test;
SET STATISTICS TIME OFF;
RAISERROR ('---------- IsGUIDGulliMeel ---------------------------------------------------------------',0,1) WITH NOWAIT;
DBCC FREEPROCCACHE;
SET STATISTICS TIME ON;
SELECT @Trash = ig.IsGuid
FROM #TestTable test
CROSS APPLY dbo.IsGUIDGulliMeel(SomeVarcharColumn) ig;
SET STATISTICS TIME OFF;
RAISERROR ('---------- Updating table for next percent -----------------------------------------------',0,1) WITH NOWAIT;
--===== Increment the counter
SELECT @Counter = @Counter +1;
--===== Change another 10% to non-GUID data (Dash at character 24 changed)
UPDATE #TestTable
SET SomeVarcharColumn = STUFF(SomeVarcharColumn,24,1,'0')
WHERE RowNum%10 = @Counter;
END
;
RAISERROR ('==========================================================================================',0,1) WITH NOWAIT;
RAISERROR ('Length Test RUN COMPLETE.',0,1) WITH NOWAIT;
GO
If someone is interested in the actual output from my machine (does not include the SUMs because I was in GRID mode and all of the information below comes from the MESSAGES tab).
==========================================================================================
Building the test data (Length Test)
==========================================================================================
(121317 row(s) affected)
==========================================================================================
Working on 0 percent non-GUID info (Length Test)
==========================================================================================
---------- IsGUID_Jeff -------------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 499 ms, elapsed time = 502 ms.
---------- IsGUID David ------------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 858 ms, elapsed time = 861 ms.
---------- IsGUIDGulliMeel ---------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 1014 ms, elapsed time = 1021 ms.
---------- Updating table for next percent -----------------------------------------------
(12132 row(s) affected)
==========================================================================================
Working on 10 percent non-GUID info (Length Test)
==========================================================================================
---------- IsGUID_Jeff -------------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 499 ms, elapsed time = 489 ms.
---------- IsGUID David ------------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 827 ms, elapsed time = 826 ms.
---------- IsGUIDGulliMeel ---------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 905 ms, elapsed time = 951 ms.
---------- Updating table for next percent -----------------------------------------------
(12132 row(s) affected)
==========================================================================================
Working on 20 percent non-GUID info (Length Test)
==========================================================================================
---------- IsGUID_Jeff -------------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 484 ms, elapsed time = 483 ms.
---------- IsGUID David ------------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 811 ms, elapsed time = 835 ms.
---------- IsGUIDGulliMeel ---------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 827 ms, elapsed time = 831 ms.
---------- Updating table for next percent -----------------------------------------------
(12132 row(s) affected)
==========================================================================================
Working on 30 percent non-GUID info (Length Test)
==========================================================================================
---------- IsGUID_Jeff -------------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 452 ms, elapsed time = 483 ms.
---------- IsGUID David ------------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 764 ms, elapsed time = 804 ms.
---------- IsGUIDGulliMeel ---------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 734 ms, elapsed time = 738 ms.
---------- Updating table for next percent -----------------------------------------------
(12132 row(s) affected)
==========================================================================================
Working on 40 percent non-GUID info (Length Test)
==========================================================================================
---------- IsGUID_Jeff -------------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 468 ms, elapsed time = 473 ms.
---------- IsGUID David ------------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 749 ms, elapsed time = 787 ms.
---------- IsGUIDGulliMeel ---------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 640 ms, elapsed time = 644 ms.
---------- Updating table for next percent -----------------------------------------------
(12132 row(s) affected)
==========================================================================================
Working on 50 percent non-GUID info (Length Test)
==========================================================================================
---------- IsGUID_Jeff -------------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 468 ms, elapsed time = 469 ms.
---------- IsGUID David ------------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 718 ms, elapsed time = 773 ms.
---------- IsGUIDGulliMeel ---------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 515 ms, elapsed time = 544 ms.
---------- Updating table for next percent -----------------------------------------------
(12132 row(s) affected)
==========================================================================================
Working on 60 percent non-GUID info (Length Test)
==========================================================================================
---------- IsGUID_Jeff -------------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 436 ms, elapsed time = 465 ms.
---------- IsGUID David ------------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 734 ms, elapsed time = 718 ms.
---------- IsGUIDGulliMeel ---------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 421 ms, elapsed time = 443 ms.
---------- Updating table for next percent -----------------------------------------------
(12132 row(s) affected)
==========================================================================================
Working on 70 percent non-GUID info (Length Test)
==========================================================================================
---------- IsGUID_Jeff -------------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 453 ms, elapsed time = 456 ms.
---------- IsGUID David ------------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 687 ms, elapsed time = 715 ms.
---------- IsGUIDGulliMeel ---------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 359 ms, elapsed time = 345 ms.
---------- Updating table for next percent -----------------------------------------------
(12131 row(s) affected)
==========================================================================================
Working on 80 percent non-GUID info (Length Test)
==========================================================================================
---------- IsGUID_Jeff -------------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 437 ms, elapsed time = 441 ms.
---------- IsGUID David ------------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 655 ms, elapsed time = 672 ms.
---------- IsGUIDGulliMeel ---------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 250 ms, elapsed time = 247 ms.
---------- Updating table for next percent -----------------------------------------------
(12131 row(s) affected)
==========================================================================================
Working on 90 percent non-GUID info (Length Test)
==========================================================================================
---------- IsGUID_Jeff -------------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 437 ms, elapsed time = 429 ms.
---------- IsGUID David ------------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 655 ms, elapsed time = 650 ms.
---------- IsGUIDGulliMeel ---------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 125 ms, elapsed time = 153 ms.
---------- Updating table for next percent -----------------------------------------------
(0 row(s) affected)
==========================================================================================
Working on 100 percent non-GUID info (Length Test)
==========================================================================================
---------- IsGUID_Jeff -------------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 437 ms, elapsed time = 431 ms.
---------- IsGUID David ------------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 640 ms, elapsed time = 673 ms.
---------- IsGUIDGulliMeel ---------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 140 ms, elapsed time = 151 ms.
---------- Updating table for next percent -----------------------------------------------
(0 row(s) affected)
==========================================================================================
Length Test RUN COMPLETE.
==========================================================================================
Building the test data (Missing dash test)
==========================================================================================
(121317 row(s) affected)
==========================================================================================
Working on 0 percent non-GUID info (Missing dash test)
==========================================================================================
---------- IsGUID_Jeff -------------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 499 ms, elapsed time = 503 ms.
---------- IsGUID David ------------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 858 ms, elapsed time = 869 ms.
---------- IsGUIDGulliMeel ---------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 1029 ms, elapsed time = 1024 ms.
---------- Updating table for next percent -----------------------------------------------
(12132 row(s) affected)
==========================================================================================
Working on 10 percent non-GUID info (Missing dash test)
==========================================================================================
---------- IsGUID_Jeff -------------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 484 ms, elapsed time = 480 ms.
---------- IsGUID David ------------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 826 ms, elapsed time = 847 ms.
---------- IsGUIDGulliMeel ---------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 952 ms, elapsed time = 943 ms.
---------- Updating table for next percent -----------------------------------------------
(12132 row(s) affected)
==========================================================================================
Working on 20 percent non-GUID info (Missing dash test)
==========================================================================================
---------- IsGUID_Jeff -------------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 468 ms, elapsed time = 465 ms.
---------- IsGUID David ------------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 858 ms, elapsed time = 854 ms.
---------- IsGUIDGulliMeel ---------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 858 ms, elapsed time = 860 ms.
---------- Updating table for next percent -----------------------------------------------
(12132 row(s) affected)
==========================================================================================
Working on 30 percent non-GUID info (Missing dash test)
==========================================================================================
---------- IsGUID_Jeff -------------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 484 ms, elapsed time = 482 ms.
---------- IsGUID David ------------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 889 ms, elapsed time = 929 ms.
---------- IsGUIDGulliMeel ---------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 952 ms, elapsed time = 955 ms.
---------- Updating table for next percent -----------------------------------------------
(12132 row(s) affected)
==========================================================================================
Working on 40 percent non-GUID info (Missing dash test)
==========================================================================================
---------- IsGUID_Jeff -------------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 421 ms, elapsed time = 446 ms.
---------- IsGUID David ------------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 811 ms, elapsed time = 906 ms.
---------- IsGUIDGulliMeel ---------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 702 ms, elapsed time = 703 ms.
---------- Updating table for next percent -----------------------------------------------
(12132 row(s) affected)
==========================================================================================
Working on 50 percent non-GUID info (Missing dash test)
==========================================================================================
---------- IsGUID_Jeff -------------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 437 ms, elapsed time = 437 ms.
---------- IsGUID David ------------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 826 ms, elapsed time = 852 ms.
---------- IsGUIDGulliMeel ---------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 468 ms, elapsed time = 673 ms.
---------- Updating table for next percent -----------------------------------------------
(12132 row(s) affected)
==========================================================================================
Working on 60 percent non-GUID info (Missing dash test)
==========================================================================================
---------- IsGUID_Jeff -------------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 405 ms, elapsed time = 406 ms.
---------- IsGUID David ------------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 843 ms, elapsed time = 889 ms.
---------- IsGUIDGulliMeel ---------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 530 ms, elapsed time = 538 ms.
---------- Updating table for next percent -----------------------------------------------
(12132 row(s) affected)
==========================================================================================
Working on 70 percent non-GUID info (Missing dash test)
==========================================================================================
---------- IsGUID_Jeff -------------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 390 ms, elapsed time = 386 ms.
---------- IsGUID David ------------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 843 ms, elapsed time = 885 ms.
---------- IsGUIDGulliMeel ---------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 421 ms, elapsed time = 441 ms.
---------- Updating table for next percent -----------------------------------------------
(12131 row(s) affected)
==========================================================================================
Working on 80 percent non-GUID info (Missing dash test)
==========================================================================================
---------- IsGUID_Jeff -------------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 359 ms, elapsed time = 369 ms.
---------- IsGUID David ------------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 842 ms, elapsed time = 868 ms.
---------- IsGUIDGulliMeel ---------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 375 ms, elapsed time = 367 ms.
---------- Updating table for next percent -----------------------------------------------
(12131 row(s) affected)
==========================================================================================
Working on 90 percent non-GUID info (Missing dash test)
==========================================================================================
---------- IsGUID_Jeff -------------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 343 ms, elapsed time = 369 ms.
---------- IsGUID David ------------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 889 ms, elapsed time = 956 ms.
---------- IsGUIDGulliMeel ---------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 281 ms, elapsed time = 282 ms.
---------- Updating table for next percent -----------------------------------------------
(0 row(s) affected)
==========================================================================================
Working on 100 percent non-GUID info (Missing dash test)
==========================================================================================
---------- IsGUID_Jeff -------------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 359 ms, elapsed time = 363 ms.
---------- IsGUID David ------------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 827 ms, elapsed time = 869 ms.
---------- IsGUIDGulliMeel ---------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 281 ms, elapsed time = 275 ms.
---------- Updating table for next percent -----------------------------------------------
(0 row(s) affected)
==========================================================================================
Length Test RUN COMPLETE.
====================================================================================================================================
Changed from SUM to trash variable and no index (like a staging table would be)
====================================================================================================================================
==========================================================================================
Building the test data (Length Test)
==========================================================================================
(121317 row(s) affected)
==========================================================================================
Working on 0 percent non-GUID info (Length Test)
==========================================================================================
---------- IsGUID_Jeff -------------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
SQL Server Execution Times:
CPU time = 483 ms, elapsed time = 500 ms.
---------- IsGUID David ------------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
SQL Server Execution Times:
CPU time = 484 ms, elapsed time = 546 ms.
---------- IsGUIDGulliMeel ---------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
SQL Server Execution Times:
CPU time = 1029 ms, elapsed time = 1030 ms.
---------- Updating table for next percent -----------------------------------------------
(12132 row(s) affected)
==========================================================================================
Working on 10 percent non-GUID info (Length Test)
==========================================================================================
---------- IsGUID_Jeff -------------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
SQL Server Execution Times:
CPU time = 468 ms, elapsed time = 486 ms.
---------- IsGUID David ------------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
SQL Server Execution Times:
CPU time = 483 ms, elapsed time = 480 ms.
---------- IsGUIDGulliMeel ---------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
SQL Server Execution Times:
CPU time = 921 ms, elapsed time = 925 ms.
---------- Updating table for next percent -----------------------------------------------
(12132 row(s) affected)
==========================================================================================
Working on 20 percent non-GUID info (Length Test)
==========================================================================================
---------- IsGUID_Jeff -------------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
SQL Server Execution Times:
CPU time = 468 ms, elapsed time = 475 ms.
---------- IsGUID David ------------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
SQL Server Execution Times:
CPU time = 468 ms, elapsed time = 469 ms.
---------- IsGUIDGulliMeel ---------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
SQL Server Execution Times:
CPU time = 826 ms, elapsed time = 826 ms.
---------- Updating table for next percent -----------------------------------------------
(12132 row(s) affected)
==========================================================================================
Working on 30 percent non-GUID info (Length Test)
==========================================================================================
---------- IsGUID_Jeff -------------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
SQL Server Execution Times:
CPU time = 468 ms, elapsed time = 468 ms.
---------- IsGUID David ------------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
SQL Server Execution Times:
CPU time = 453 ms, elapsed time = 462 ms.
---------- IsGUIDGulliMeel ---------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
SQL Server Execution Times:
CPU time = 733 ms, elapsed time = 735 ms.
---------- Updating table for next percent -----------------------------------------------
(12132 row(s) affected)
==========================================================================================
Working on 40 percent non-GUID info (Length Test)
==========================================================================================
---------- IsGUID_Jeff -------------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
SQL Server Execution Times:
CPU time = 468 ms, elapsed time = 460 ms.
---------- IsGUID David ------------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
SQL Server Execution Times:
CPU time = 437 ms, elapsed time = 449 ms.
---------- IsGUIDGulliMeel ---------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
SQL Server Execution Times:
CPU time = 624 ms, elapsed time = 630 ms.
---------- Updating table for next percent -----------------------------------------------
(12132 row(s) affected)
==========================================================================================
Working on 50 percent non-GUID info (Length Test)
==========================================================================================
---------- IsGUID_Jeff -------------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
SQL Server Execution Times:
CPU time = 452 ms, elapsed time = 455 ms.
---------- IsGUID David ------------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
SQL Server Execution Times:
CPU time = 437 ms, elapsed time = 436 ms.
---------- IsGUIDGulliMeel ---------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
SQL Server Execution Times:
CPU time = 530 ms, elapsed time = 536 ms.
---------- Updating table for next percent -----------------------------------------------
(12132 row(s) affected)
==========================================================================================
Working on 60 percent non-GUID info (Length Test)
==========================================================================================
---------- IsGUID_Jeff -------------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
SQL Server Execution Times:
CPU time = 452 ms, elapsed time = 447 ms.
---------- IsGUID David ------------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
SQL Server Execution Times:
CPU time = 421 ms, elapsed time = 426 ms.
---------- IsGUIDGulliMeel ---------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
SQL Server Execution Times:
CPU time = 437 ms, elapsed time = 446 ms.
---------- Updating table for next percent -----------------------------------------------
(12132 row(s) affected)
==========================================================================================
Working on 70 percent non-GUID info (Length Test)
==========================================================================================
---------- IsGUID_Jeff -------------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
SQL Server Execution Times:
CPU time = 437 ms, elapsed time = 440 ms.
---------- IsGUID David ------------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
SQL Server Execution Times:
CPU time = 421 ms, elapsed time = 416 ms.
---------- IsGUIDGulliMeel ---------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
SQL Server Execution Times:
CPU time = 344 ms, elapsed time = 342 ms.
---------- Updating table for next percent -----------------------------------------------
(12131 row(s) affected)
==========================================================================================
Working on 80 percent non-GUID info (Length Test)
==========================================================================================
---------- IsGUID_Jeff -------------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
SQL Server Execution Times:
CPU time = 421 ms, elapsed time = 429 ms.
---------- IsGUID David ------------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
SQL Server Execution Times:
CPU time = 406 ms, elapsed time = 417 ms.
---------- IsGUIDGulliMeel ---------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
SQL Server Execution Times:
CPU time = 250 ms, elapsed time = 246 ms.
---------- Updating table for next percent -----------------------------------------------
(12131 row(s) affected)
==========================================================================================
Working on 90 percent non-GUID info (Length Test)
==========================================================================================
---------- IsGUID_Jeff -------------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
SQL Server Execution Times:
CPU time = 421 ms, elapsed time = 422 ms.
---------- IsGUID David ------------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
SQL Server Execution Times:
CPU time = 390 ms, elapsed time = 390 ms.
---------- IsGUIDGulliMeel ---------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
SQL Server Execution Times:
CPU time = 141 ms, elapsed time = 150 ms.
---------- Updating table for next percent -----------------------------------------------
(0 row(s) affected)
==========================================================================================
Working on 100 percent non-GUID info (Length Test)
==========================================================================================
---------- IsGUID_Jeff -------------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
SQL Server Execution Times:
CPU time = 421 ms, elapsed time = 421 ms.
---------- IsGUID David ------------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
SQL Server Execution Times:
CPU time = 390 ms, elapsed time = 392 ms.
---------- IsGUIDGulliMeel ---------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
SQL Server Execution Times:
CPU time = 141 ms, elapsed time = 148 ms.
---------- Updating table for next percent -----------------------------------------------
(0 row(s) affected)
==========================================================================================
Length Test RUN COMPLETE.
==========================================================================================
Building the test data (Missing dash test)
==========================================================================================
(121317 row(s) affected)
==========================================================================================
Working on 0 percent non-GUID info (Missing dash test)
==========================================================================================
---------- IsGUID_Jeff -------------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
SQL Server Execution Times:
CPU time = 499 ms, elapsed time = 493 ms.
---------- IsGUID David ------------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
SQL Server Execution Times:
CPU time = 499 ms, elapsed time = 499 ms.
---------- IsGUIDGulliMeel ---------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
SQL Server Execution Times:
CPU time = 1030 ms, elapsed time = 1029 ms.
---------- Updating table for next percent -----------------------------------------------
(12132 row(s) affected)
==========================================================================================
Working on 10 percent non-GUID info (Missing dash test)
==========================================================================================
---------- IsGUID_Jeff -------------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
SQL Server Execution Times:
CPU time = 468 ms, elapsed time = 511 ms.
---------- IsGUID David ------------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
SQL Server Execution Times:
CPU time = 483 ms, elapsed time = 495 ms.
---------- IsGUIDGulliMeel ---------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
SQL Server Execution Times:
CPU time = 936 ms, elapsed time = 946 ms.
---------- Updating table for next percent -----------------------------------------------
(12132 row(s) affected)
==========================================================================================
Working on 20 percent non-GUID info (Missing dash test)
==========================================================================================
---------- IsGUID_Jeff -------------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
SQL Server Execution Times:
CPU time = 468 ms, elapsed time = 461 ms.
---------- IsGUID David ------------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
SQL Server Execution Times:
CPU time = 500 ms, elapsed time = 493 ms.
---------- IsGUIDGulliMeel ---------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
SQL Server Execution Times:
CPU time = 858 ms, elapsed time = 857 ms.
---------- Updating table for next percent -----------------------------------------------
(12132 row(s) affected)
==========================================================================================
Working on 30 percent non-GUID info (Missing dash test)
==========================================================================================
---------- IsGUID_Jeff -------------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
SQL Server Execution Times:
CPU time = 437 ms, elapsed time = 446 ms.
---------- IsGUID David ------------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
SQL Server Execution Times:
CPU time = 499 ms, elapsed time = 502 ms.
---------- IsGUIDGulliMeel ---------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
SQL Server Execution Times:
CPU time = 764 ms, elapsed time = 775 ms.
---------- Updating table for next percent -----------------------------------------------
(12132 row(s) affected)
==========================================================================================
Working on 40 percent non-GUID info (Missing dash test)
==========================================================================================
---------- IsGUID_Jeff -------------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
SQL Server Execution Times:
CPU time = 437 ms, elapsed time = 431 ms.
---------- IsGUID David ------------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
SQL Server Execution Times:
CPU time = 484 ms, elapsed time = 493 ms.
---------- IsGUIDGulliMeel ---------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
SQL Server Execution Times:
CPU time = 686 ms, elapsed time = 691 ms.
---------- Updating table for next percent -----------------------------------------------
(12132 row(s) affected)
==========================================================================================
Working on 50 percent non-GUID info (Missing dash test)
==========================================================================================
---------- IsGUID_Jeff -------------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
SQL Server Execution Times:
CPU time = 421 ms, elapsed time = 415 ms.
---------- IsGUID David ------------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
SQL Server Execution Times:
CPU time = 484 ms, elapsed time = 492 ms.
---------- IsGUIDGulliMeel ---------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
SQL Server Execution Times:
CPU time = 608 ms, elapsed time = 609 ms.
---------- Updating table for next percent -----------------------------------------------
(12132 row(s) affected)
==========================================================================================
Working on 60 percent non-GUID info (Missing dash test)
==========================================================================================
---------- IsGUID_Jeff -------------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
SQL Server Execution Times:
CPU time = 406 ms, elapsed time = 397 ms.
---------- IsGUID David ------------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
SQL Server Execution Times:
CPU time = 484 ms, elapsed time = 497 ms.
---------- IsGUIDGulliMeel ---------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
SQL Server Execution Times:
CPU time = 530 ms, elapsed time = 526 ms.
---------- Updating table for next percent -----------------------------------------------
(12132 row(s) affected)
==========================================================================================
Working on 70 percent non-GUID info (Missing dash test)
==========================================================================================
---------- IsGUID_Jeff -------------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
SQL Server Execution Times:
CPU time = 375 ms, elapsed time = 385 ms.
---------- IsGUID David ------------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
SQL Server Execution Times:
CPU time = 499 ms, elapsed time = 496 ms.
---------- IsGUIDGulliMeel ---------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
SQL Server Execution Times:
CPU time = 437 ms, elapsed time = 438 ms.
---------- Updating table for next percent -----------------------------------------------
(12131 row(s) affected)
==========================================================================================
Working on 80 percent non-GUID info (Missing dash test)
==========================================================================================
---------- IsGUID_Jeff -------------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
SQL Server Execution Times:
CPU time = 375 ms, elapsed time = 367 ms.
---------- IsGUID David ------------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
SQL Server Execution Times:
CPU time = 468 ms, elapsed time = 494 ms.
---------- IsGUIDGulliMeel ---------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
SQL Server Execution Times:
CPU time = 343 ms, elapsed time = 354 ms.
---------- Updating table for next percent -----------------------------------------------
(12131 row(s) affected)
==========================================================================================
Working on 90 percent non-GUID info (Missing dash test)
==========================================================================================
---------- IsGUID_Jeff -------------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
SQL Server Execution Times:
CPU time = 343 ms, elapsed time = 352 ms.
---------- IsGUID David ------------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
SQL Server Execution Times:
CPU time = 499 ms, elapsed time = 491 ms.
---------- IsGUIDGulliMeel ---------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
SQL Server Execution Times:
CPU time = 265 ms, elapsed time = 267 ms.
---------- Updating table for next percent -----------------------------------------------
(0 row(s) affected)
==========================================================================================
Working on 100 percent non-GUID info (Missing dash test)
==========================================================================================
---------- IsGUID_Jeff -------------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
SQL Server Execution Times:
CPU time = 343 ms, elapsed time = 352 ms.
---------- IsGUID David ------------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
SQL Server Execution Times:
CPU time = 484 ms, elapsed time = 493 ms.
---------- IsGUIDGulliMeel ---------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
SQL Server Execution Times:
CPU time = 266 ms, elapsed time = 270 ms.
---------- Updating table for next percent -----------------------------------------------
(0 row(s) affected)
==========================================================================================
Length Test RUN COMPLETE.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 3, 2012 at 11:49 pm
Of course, you know what all of that testing really means, don't you?
[font="Arial Black"]IT DEPENDS!!![/font] 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
July 4, 2012 at 12:27 am
just tried another clr version which multiple functions which return different data types (as below). and used Jeff's test harness (attached).
there was no clear winner!
CREATE ASSEMBLY [IsGUID]
AUTHORIZATION [dbo]
FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C01030046DDF34F0000000000000000E00002210B010800000C000000060000000000009E2A0000002000000040000000004000002000000002000004000000000000000400000000000000008000000002000000000000030040850000100000100000000010000010000000000000100000000000000000000000482A000053000000004000004003000000000000000000000000000000000000006000000C000000D42900001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000A40A000000200000000C000000020000000000000000000000000000200000602E72737263000000400300000040000000040000000E0000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000001200000000000000000000000000004000004200000000000000000000000000000000802A000000000000480000000200050014220000C0070000010000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000013300200180100000100001100170A0F00FE16030000016F1100000A0B0772010000706F1200000A130511052D0C720100007007281300000A0B0772050000706F1400000A130511052D0C077205000070281300000A0B076F1500000A1F26FE01130511052D0900160A0038A700000000160C38900000000007086F1600000A0D08130611061F0E30131106162E2211061F092E2B11061F0E2E252B4111061F132E1D11061F182E1711061F252E202B2D091F7BFE01130511052D02160A2B43091F2DFE01130511052D02160A2B34091F7DFE01130511052D02160A2B2509281700000A2D12091F41320A091F46FE0216FE012B01162B0117130511052D02160A2B00000817580C081F26FE04130511053A62FFFFFF0006281800000A13042B0011042A13300200270000000200001100022801000006281900000A16FE010B072D0917281A00000A0A2B0916281A00000A0A2B00062A0013300200270000000300001100022801000006281900000A16FE010B072D0917281B00000A0A2B0916281B00000A0A2B00062A0013300200290000000400001100022801000006281900000A16FE010B072D0A176A281C00000A0A2B0A166A281C00000A0A2B00062A00000042534A4201000100000000000C00000076322E302E35303732370000000005006C00000090020000237E0000FC0200005403000023537472696E677300000000500600000C000000235553005C0600001000000023475549440000006C0600005401000023426C6F620000000000000002000001471502000900000000FA01330016000001000000190000000200000004000000040000001C0000000E00000004000000010000000200000000000A00010000000000060033002C000A005B0046000A00660046000A00770046000A008C0046000A00A10046000600D500C3000600F200C30006000F01C30006002E01C30006004701C30006006001C30006007B01C30006009601C3000600CE01AF010600E201AF010600F001C30006000902C3000600390226024F004D02000006007C025C0206009C025C020A00DC02C1020600FA022C00060031032C0000000000010000000000010001008101100015000000050001000100502000000000960070000A0001007421000000009600800011000200A821000000009600950018000300DC21000000009600AA001F00040000000100B60000000100B60000000100B60000000100B6003900EC0026004100EC0026004900EC0026005100EC0026005900EC0026006100EC0026006900EC0026007100EC0026007900EC002B008100EC0026008900EC0026009100EC0026009900EC003000A900EC003600B100EC003B00B900EC003B000900F1024400C10001034800C1000C034D00C10013034800C1001C035300C10027035700C90036035C0011003F03610011004B03720021003F03780029003F03840031003F039000200083003F002E003300D3002E001300AD002E001B00AD002E002300B3002E002B00C9002E000B009C002E003B00AD002E004B00AD002E005300EB002E00630015012E006B0022012E0073002B012E007B00340167007E008A009600048000000100000000000000000000000000BA020000020000000000000000000000010023000000000002000000000000000000000001003A00000000000000003C4D6F64756C653E004973475549442E646C6C004775696446756E6374696F6E73006D73636F726C69620053797374656D004F626A6563740053797374656D2E446174610053797374656D2E446174612E53716C54797065730053716C426F6F6C65616E0053716C537472696E67004973477569640053716C496E74313600497347756964496E7431360053716C496E74333200497347756964496E7433320053716C496E74363400497347756964496E74363400706F737369626C65475549440053797374656D2E5265666C656374696F6E00417373656D626C795469746C65417474726962757465002E63746F7200417373656D626C794465736372697074696F6E41747472696275746500417373656D626C79436F6E66696775726174696F6E41747472696275746500417373656D626C79436F6D70616E7941747472696275746500417373656D626C7950726F6475637441747472696275746500417373656D626C79436F7079726967687441747472696275746500417373656D626C7954726164656D61726B41747472696275746500417373656D626C7943756C747572654174747269627574650053797374656D2E52756E74696D652E496E7465726F70536572766963657300436F6D56697369626C65417474726962757465004775696441747472696275746500417373656D626C7956657273696F6E41747472696275746500417373656D626C7946696C6556657273696F6E4174747269627574650053797374656D2E446961676E6F73746963730044656275676761626C6541747472696275746500446562756767696E674D6F6465730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C69747941747472696275746500497347554944004D6963726F736F66742E53716C5365727665722E5365727665720053716C46756E6374696F6E41747472696275746500546F537472696E6700537472696E67005374617274735769746800436F6E63617400456E647357697468006765745F4C656E677468006765745F436861727300436861720049734E756D626572006F705F496D706C69636974006F705F54727565000000037B0000037D000000000071D3EE146FA03F409E0E1359ECBB98600008B77A5C561934E0890600011109110D0600011111110D0600011115110D0600011119110D042001010E042001010205200101115104200101080320000104010000000320000E042001020E0500020E0E0E03200008042001030804000102030500011109020A0707020E08031109020805000102110905000111110605070211110205000111150805070211150205000111190A0507021119021001000B49734755494420546573740000050100000000150100104461766964204265747465726964676500000901000454657374000017010012436F7079726967687420C2A920203230313200002901002462633835333634662D373034322D343534302D396330372D61616632376330323264333600000C010007312E302E302E3000000801000701000000000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F777301000000000046DDF34F000000000200000058000000F0290000F00B00005253445315F7FB80F10E0D4CA9E5A61F15E1F11203000000433A5C506572736F6E616C5C446176696420426574746572696467655C4973475549445C4973475549445C6F626A5C44656275675C4973475549442E70646200702A000000000000000000008E2A0000002000000000000000000000000000000000000000000000802A000000000000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF250020400000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000100100000001800008000000000000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058400000E80200000000000000000000E80234000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000001000000000000000100000000003F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B00448020000010053007400720069006E006700460069006C00650049006E0066006F00000024020000010030003000300030003000340062003000000044001100010043006F006D00700061006E0079004E0061006D0065000000000044006100760069006400200042006500740074006500720069006400670065000000000040000C000100460069006C0065004400650073006300720069007000740069006F006E0000000000490073004700550049004400200054006500730074000000300008000100460069006C006500560065007200730069006F006E000000000031002E0030002E0030002E003000000038000B00010049006E007400650072006E0061006C004E0061006D00650000004900730047005500490044002E0064006C006C00000000004800120001004C006500670061006C0043006F007000790072006900670068007400000043006F0070007900720069006700680074002000A900200020003200300031003200000040000B0001004F0072006900670069006E0061006C00460069006C0065006E0061006D00650000004900730047005500490044002E0064006C006C00000000002C0005000100500072006F0064007500630074004E0061006D00650000000000540065007300740000000000340008000100500072006F006400750063007400560065007200730069006F006E00000031002E0030002E0030002E003000000038000800010041007300730065006D0062006C0079002000560065007200730069006F006E00000031002E0030002E0030002E0030000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000C000000A03A00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
WITH PERMISSION_SET = SAFE
GO
CREATE FUNCTION IsGuid(@possibleGUID nvarchar(4000)) RETURNS bit
AS EXTERNAL NAME IsGUID.GuidFunctions.IsGuid;
GO
CREATE FUNCTION IsGuidInt16(@possibleGUID nvarchar(4000)) RETURNS smallint
AS EXTERNAL NAME IsGUID.GuidFunctions.IsGuidInt16;
GO
CREATE FUNCTION IsGuidInt32(@possibleGUID nvarchar(4000)) RETURNS int
AS EXTERNAL NAME IsGUID.GuidFunctions.IsGuidInt32;
GO
CREATE FUNCTION IsGuidInt64(@possibleGUID nvarchar(4000)) RETURNS bigint
AS EXTERNAL NAME IsGUID.GuidFunctions.IsGuidInt64;
GO
July 4, 2012 at 12:46 am
In most all cases (index or not), the simple "Jeff" function held it's own against the CLR (even after removing the "bit" penalty where it finally passed about half the time) and the "Gullimeel" function didn't catch up and then pass until more than 50% of the data was non-GUID data.
I already mentioned that my method wil be better when most of the data is non guid..
I used the same number of rows that Gullimeel used (121,317) for each test. I'm not sure where the "47ms" time came from in his tests unless he had some massive parallelism going on. I'm limited to only 2 - 2.8GHz processors on my laptop for testing. It's also why I like to see complete test harnesses so that folks don't have to try to recreate what actually happened.
Your machine is much powerful than mine for 100% GUID data I was getting 2059ms for your method and some 3400ms for mine wheras in your case it is taking much less.
One reason why you did not see 47ms is because you are using the dbcc freeproccache and I mentioned that I ran the test two times and used the values for 2nd run.
Also, these timings sometime are not perfect because there are other things running on machines. But this is just to get an idea on when to use which ,off course after doing through testing.
GulliMeel
Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]
Viewing 15 posts - 16 through 30 (of 35 total)
You must be logged in to reply to this topic. Login to reply