September 18, 2010 at 3:08 pm
[font="Arial Black"]{Edit 2010-09-20 09:00 EDST}
First, thanks to everyone for running the test code. I believe I have enough data to post some decent results.
Thanks to Brad Schultz posting his code, we were able to isolate the original problem that prompted all the testing. I'm just waiting for some feedback from a couple of folks that also experienced the problem. As soon as I get that, I should be able to write up all that went wrong and how to avoid it.
Thanks again for everyone's help (especially on the weeked). You just can't ask for a better community than this one.
{/Edit}[/font]
I recently opened a post about XML splitter methods and also came across some other posts about XML splitter methods in the process. I'm currently involved in several of those types of posts and I've found an incredible oddity occuring. Many people are claiming that XML splitter methods are beating Tally table methods for splits of less than 8K.
These aren't lightweight SQL wanna-be's were talking about, here. Some of them are SQL Server MVP's and some of them are people that I have some honest respect for.
Of course, their tests are different than mine and, even if the code were the same, there are other factors to consider including things like collation, etc.
So! With all of that in mind, I wonder if you good folks would help me with an experiment to figure out what the heck is going on. I need to know as much as I can about your machine (obviously without getting into any of the security context) and I need you to run the exact same code that I used and provide me with the results. I tried to make it as easy as possible for you to do, as well. The following script does everything I need. Just load it into SSMS and run it. It all runs in TempDB so there's no chance of screwing up any code you may have. It tells me about your server (again, NO security info is revealed), builds a Tally table, creates 4 splitter functions, builds a test table, executes some test code, and then cleans up TempDB after itself.
I just need you to run it with the "Results to Text" mode turned on so that everything shows up in the "Messages" tab and post the result here. I'm not sure this will run on Case Sensitive machines but I did try my best.
Here's the code I'd like you to run. If you want to tweak any code, please do it after you run it once without tweaking it. I need the output from an unmodified run. Of course, I'm definitely interested in your modifications once you've executed the unmodified code.
Oh yeah... almost forgot. I believe that the only thing I won't be able to tell about your computer on all of this is whether or not it's a laptop or not. [font="Arial Black"]If it's a laptop, would you add the word "LAPTOP" to the top left corner of the output[/font] before you post it or send it? Thanks.
Depending on a number of factors, it could take about 45 minutes for this code to run or just 16 or so minutes.
--PLEASE RUN THIS IN THE "RESULTS TO TEXT" MODE
--PREFERABLY IN THE "COLUMN ALIGNED" OUTPUT FORMAT.
-- THANKS. --JEFF MODEN
--===== Presets
SET NOCOUNT ON;
SET STATISTICS IO, TIME OFF;
--===== Do this in a nice safe place that everyone has
USE tempdb;
--======================================================================
-- Gather some configuration (ONLY) information about the
-- the server we're running on. Note that NO SECURITY INFORMATION
-- is revealed here... not even an IP ADDRESS. This is just a
-- real easy way to get number of processors, Operating System info
-- etc, etc, etc.
--======================================================================
RAISERROR ('--===== System Configuration information ==============================================================================',10,1) WITH NOWAIT;
SELECT name AS Name,CAST(value_in_use AS BIGINT) AS CurrentValue FROM sys.configurations ORDER BY name;
SELECT @@VERSION;
EXEC xp_msver;
RAISERROR ('--=====================================================================================================================',10,1) WITH NOWAIT;
GO
--===================================================================
-- Create a Tally table from 1 to 11000
--===================================================================
--===== If the Tally table already exists here, drop it to make reruns easier.
IF OBJECT_ID('tempdb.dbo.Tally','U') IS NOT NULL
DROP TABLE dbo.Tally;
--===== Create and populate the Tally table on the fly.
-- This ISNULL function makes the column NOT NULL
-- so we can put a Primary Key on it
SELECT TOP 11000
IDENTITY(INT,1,1) AS N
INTO dbo.Tally
FROM master.sys.all_columns ac1
CROSS JOIN master.sys.all_columns ac2
;
--===== Add a CLUSTERED Primary Key to maximize performance
ALTER TABLE dbo.Tally
ADD CONSTRAINT PK_Tally_N
PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100
;
--===== Allow the general public to use it
GRANT SELECT ON dbo.Tally TO PUBLIC
;
GO
--=====================================================================================================================
-- Create the various functions to test
--=====================================================================================================================
--===== Do this in a nice safe place that everyone has
USE tempdb;
--===== Tally Table (Split8KTally iTVF) ===============================================================================
IF OBJECT_ID('tempdb.dbo.Split8KTally') IS NOT NULL
DROP FUNCTION dbo.Split8KTally
GO
CREATE FUNCTION dbo.Split8KTally
(@Parameter VARCHAR(8000), @Delimiter VARCHAR(1))
RETURNS TABLE
WITH SCHEMABINDING AS
RETURN
SELECT ROW_NUMBER() OVER (ORDER BY N) AS ItemNumber,
SUBSTRING(@Parameter,N,CHARINDEX(@Delimiter,@Parameter+@Delimiter,N)-N) AS ItemValue
FROM dbo.Tally
WHERE N BETWEEN 1 AND LEN(@Parameter)
AND SUBSTRING(@Delimiter+@Parameter,N,1) = @Delimiter; --Notice how we find the comma
GO
--===== XML-1 (Split8KXML1 mlTVF) =====================================================================================
IF OBJECT_ID('tempdb.dbo.Split8KXML1') IS NOT NULL
DROP FUNCTION dbo.Split8KXML1
GO
CREATE FUNCTION dbo.Split8KXML1
(@Parameter VARCHAR(MAX), @Delimiter VARCHAR(1))
RETURNS @Result TABLE
(ItemNumber INT, ItemValue VARCHAR(8000))
WITH SCHEMABINDING AS
BEGIN
DECLARE @XML XML;
SELECT @XML = '<r>'+REPLACE(@Parameter, ',', '</r><r>')+'</r>';
INSERT INTO @Result (ItemNumber, ItemValue)
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS ItemNumber,
Item.value('text()[1]', 'varchar(8000)') AS ItemValue
FROM @XML.nodes('//r') R(Item);
RETURN;
END;
GO
--===== XML-3 (Split8KXML3 iTVF) ======================================================================================
IF OBJECT_ID('tempdb.dbo.Split8KXML3') IS NOT NULL
DROP FUNCTION dbo.Split8KXML3
GO
CREATE FUNCTION dbo.Split8KXML3
(@Parameter VARCHAR(MAX), @Delimiter VARCHAR(1))
RETURNS TABLE
WITH SCHEMABINDING AS
RETURN
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS ItemNumber,
R.Item.value('text()[1]', 'varchar(8000)') AS ItemValue
FROM (SELECT CAST('<r>'+REPLACE(@Parameter, @Delimiter, '</r><r>')+'</r>' AS XML)) X(N)
CROSS APPLY N.nodes('//r') R(Item)
;
GO
--===== Loop Method 1 (Split8KL1 mlTVF) ===============================================================================
IF OBJECT_ID('tempdb.dbo.Split8KL1') IS NOT NULL
DROP FUNCTION dbo.Split8KL1
GO
CREATE FUNCTION dbo.Split8KL1
(@Parameter VARCHAR(8000), @Delimiter VARCHAR(1))
RETURNS @Result TABLE (ItemNumber INT IDENTITY(1,1), ItemValue VARCHAR(8000))
WITH SCHEMABINDING AS
BEGIN
--===== Declare a variable to remember the position of the current comma
DECLARE @N INT;
--===== Add start and end commas to the Parameter so we can handle
-- single elements
SELECT @Parameter = @Delimiter + @Parameter + @Delimiter,
--===== Preassign the current comma as the first character
@N = 1;
--===== Loop through and find each comma, then insert the string value
-- found between the current comma and the next comma. @N is
-- the position of the current comma.
WHILE @N < LEN(@Parameter) --Don't include the last comma
BEGIN
--==== Do the insert using the value between the commas
INSERT INTO @Result (ItemValue)
SELECT SUBSTRING(@Parameter, @N+1, CHARINDEX(@Delimiter, @Parameter, @N+1)-@N-1);
--==== Find the next comma
SELECT @N = CHARINDEX(@Delimiter, @Parameter, @N+1);
END; --END While
RETURN;
END; --END Function
GO
--===== XML-Brad (Split8KXMLBrad iTVF) ======================================================================================
IF OBJECT_ID('dbo.Split8KXMLBrad') IS NOT NULL
DROP FUNCTION dbo.Split8KXMLBrad
GO
CREATE FUNCTION dbo.Split8KXMLBrad
(@Parameter VARCHAR(MAX), @Delimiter VARCHAR(1))
RETURNS TABLE
WITH SCHEMABINDING AS
RETURN
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS ItemNumber,
x.i.value('(./text())[1]','varchar(8000)') AS ItemValue
from (select XMLList=cast('<i>'+replace(@Parameter,@Delimiter,'</i><i>')+'</i>' as xml).query('.')) a
cross apply XMLList.nodes('i') x(i)
;
GO
--===== XML-Brad1 (Split8KXMLBrad1 iTVF) ======================================================================================
IF OBJECT_ID('dbo.Split8KXMLBrad1') IS NOT NULL
DROP FUNCTION dbo.Split8KXMLBrad1
GO
CREATE FUNCTION dbo.Split8KXMLBrad1
(@Parameter VARCHAR(MAX), @Delimiter VARCHAR(1))
RETURNS TABLE
WITH SCHEMABINDING AS
RETURN
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS ItemNumber,
R.Item.value('text()[1]', 'varchar(8000)') AS ItemValue
FROM (SELECT CAST('<r>'+REPLACE(@Parameter, @Delimiter, '</r><r>')+'</r>' AS XML).query('.')) X(N)
CROSS APPLY N.nodes('//r') R(Item)
;
GO
-- ***** Build 10k Row x 100 Element ************************************************************************************
DECLARE @ElementCount INT;
SELECT @ElementCount = 100;
--=====================================================================================================================
-- Build the test data
--=====================================================================================================================
--===== Do this in a nice safe place that everyone has
USE tempdb;
--===== Conditionally drop the test table to make reruns easier
IF OBJECT_ID('tempdb.dbo.CsvTest','U') IS NOT NULL
DROP TABLE dbo.CsvTest;
--===== This creates and populates a test table on the fly containing a
-- sequential column and a randomly generated CSV Parameter column.
SELECT TOP (10000) --Controls the number of rows in the test table
ISNULL(CAST(ROW_NUMBER() OVER (ORDER BY(SELECT NULL)) AS INT),0) AS RowNum,
(
SELECT CAST(STUFF( --=== STUFF get's rid of the leading comma
( --=== This builds CSV row with a leading comma
SELECT TOP (@ElementCount) --Controls the number of CSV elements in each row
','+CAST(ABS(CHECKSUM(NEWID()))%100000 AS VARCHAR(10))
FROM dbo.Tally t3 --Classic cross join pseudo-cursor
CROSS JOIN dbo.Tally t4 --can produce row sets up 121 million.
WHERE t1.N <> t3.N --Without this line, all rows would be the same
FOR XML PATH('')
)
,1,1,'') AS VARCHAR(8000))
) AS CsvParameter
INTO CsvTest
FROM dbo.Tally t1 --Classic cross join pseudo-cursor
CROSS JOIN dbo.Tally t2; --can produce row sets up 121 million.
--===== Let's add a PK just for grins. Since it's a temp table, we won't name it.
ALTER TABLE dbo.CsvTest
ADD PRIMARY KEY CLUSTERED (RowNum) WITH FILLFACTOR = 100;
GO
--===== Show what we know about the test tables
EXEC sp_help 'Tally';
EXEC sp_help 'CsvTest';
--=====================================================================================================================
-- Run the functions
--=====================================================================================================================
--===== Do this in a nice safe place that everyone has
USE tempdb;
GO
SET STATISTICS TIME,IO ON;
RAISERROR (N'--===== Tally Table (Split8KTally iTVF) ===============================================================================',10,1) WITH NOWAIT;
DECLARE @RowNum INT, @ItemNumber INT, @ItemValue INT;
SELECT @RowNum = csv.RowNum, @ItemNumber = split.ItemNumber, @ItemValue = CAST(split.ItemValue AS INT)
FROM dbo.CsvTest csv
CROSS APPLY dbo.Split8KTally(csv.CsvParameter,',') AS split;
GO
RAISERROR (N'--===== XML-1 (Split8KXML1 mlTVF) =====================================================================================',10,1) WITH NOWAIT;
DECLARE @RowNum INT, @ItemNumber INT, @ItemValue INT;
SELECT @RowNum = csv.RowNum, @ItemNumber = split.ItemNumber, @ItemValue = CAST(split.ItemValue AS INT)
FROM dbo.CsvTest csv
CROSS APPLY dbo.Split8KXML1(csv.CsvParameter,',') AS split;
GO
RAISERROR (N'--===== XML-3 (Split8KXML3 iTVF) ======================================================================================',10,1) WITH NOWAIT;
DECLARE @RowNum INT, @ItemNumber INT, @ItemValue INT;
SELECT @RowNum = csv.RowNum, @ItemNumber = split.ItemNumber, @ItemValue = CAST(split.ItemValue AS INT)
FROM dbo.CsvTest csv
CROSS APPLY dbo.Split8KXML3(csv.CsvParameter,',') AS split;
GO
RAISERROR (N'--===== Loop Method 1 (Split8KL1 mlTVF) ===============================================================================',10,1) WITH NOWAIT;
DECLARE @RowNum INT, @ItemNumber INT, @ItemValue INT;
SELECT @RowNum = csv.RowNum, @ItemNumber = split.ItemNumber, @ItemValue = CAST(split.ItemValue AS INT)
FROM dbo.CsvTest csv
CROSS APPLY dbo.Split8KL1(csv.CsvParameter,',') AS split;
GO
RAISERROR (N'--===== XML-Brad (Split8KXMLBrad iTVF) ===============================================================================',10,1) WITH NOWAIT;
DECLARE @RowNum INT, @ItemNumber INT, @ItemValue INT;
SELECT @RowNum = csv.RowNum, @ItemNumber = split.ItemNumber, @ItemValue = CAST(split.ItemValue AS INT)
FROM dbo.CsvTest csv
CROSS APPLY dbo.Split8KXMLBrad(csv.CsvParameter,',') AS split;
GO
RAISERROR (N'--===== XML-Brad1 (Split8KXMLBrad1 iTVF) ===============================================================================',10,1) WITH NOWAIT;
DECLARE @RowNum INT, @ItemNumber INT, @ItemValue INT;
SELECT @RowNum = csv.RowNum, @ItemNumber = split.ItemNumber, @ItemValue = CAST(split.ItemValue AS INT)
FROM dbo.CsvTest csv
CROSS APPLY dbo.Split8KXMLBrad1(csv.CsvParameter,',') AS split;
GO
SET STATISTICS TIME,IO OFF;
PRINT REPLICATE('=',120)
--=====================================================================================================================
-- Houskeeping
--=====================================================================================================================
--===== Do this in a nice safe place that everyone has
USE tempdb;
GO
--===== Drop all the object that we just created
DROP FUNCTION dbo.Split8KTally, dbo.Split8KXML1, dbo.Split8KXML3, dbo.Split8KL1, dbo.Split8KXMLBrad, dbo.Split8KXMLBrad1;
DROP TABLE dbo.Tally, dbo.CsvTest;
GO
Just to share what I'm asking you to share and to show you what I'm going to get out of the run, here are the outputs from my Desktop "server" and a "real" server that I have access to.
--===== System Configuration information ==============================================================================
Name CurrentValue
----------------------------------- --------------------
Ad Hoc Distributed Queries 1
affinity I/O mask 0
affinity mask 0
Agent XPs 1
allow updates 0
awe enabled 0
blocked process threshold 0
c2 audit mode 0
clr enabled 1
common criteria compliance enabled 0
cost threshold for parallelism 5
cross db ownership chaining 0
cursor threshold -1
Database Mail XPs 0
default full-text language 1033
default language 0
default trace enabled 1
disallow results from triggers 0
fill factor (%) 0
ft crawl bandwidth (max) 100
ft crawl bandwidth (min) 0
ft notify bandwidth (max) 100
ft notify bandwidth (min) 0
index create memory (KB) 0
in-doubt xact resolution 0
lightweight pooling 0
locks 0
max degree of parallelism 0
max full-text crawl range 4
max server memory (MB) 2147483647
max text repl size (B) 65536
max worker threads 0
media retention 0
min memory per query (KB) 1024
min server memory (MB) 8
nested triggers 1
network packet size (B) 4096
Ole Automation Procedures 1
open objects 0
PH timeout (s) 60
precompute rank 0
priority boost 0
query governor cost limit 0
query wait (s) -1
recovery interval (min) 0
remote access 1
remote admin connections 0
remote login timeout (s) 20
remote proc trans 0
remote query timeout (s) 600
Replication XPs 0
scan for startup procs 0
server trigger recursion 1
set working set size 0
show advanced options 0
SMO and DMO XPs 1
SQL Mail XPs 0
transform noise words 0
two digit year cutoff 2049
user connections 0
user options 0
Web Assistant Procedures 0
xp_cmdshell 1
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
Nov 24 2008 13:01:59
Copyright (c) 1988-2005 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
Index Name Internal_Value Character_Value
------ -------------------------------- -------------- ------------------------------------------------------------------------------------------------------------------------
1 ProductName NULL Microsoft SQL Server
2 ProductVersion 589824 9.00.4035.00
3 Language 1033 English (United States)
4 Platform NULL NT INTEL X86
5 Comments NULL NT INTEL X86
6 CompanyName NULL Microsoft Corporation
7 FileDescription NULL SQL Server Windows NT
8 FileVersion NULL 2005.090.4035.00
9 InternalName NULL SQLSERVR
10 LegalCopyright NULL © Microsoft Corp. All rights reserved.
11 LegalTrademarks NULL Microsoft® is a registered trademark of Microsoft Corporation. Windows(TM) is a trademark of Microsoft Corporation
12 OriginalFilename NULL SQLSERVR.EXE
13 PrivateBuild NULL NULL
14 SpecialBuild 264437760 NULL
15 WindowsVersion 170393861 5.1 (2600)
16 ProcessorCount 1 1
17 ProcessorActiveMask 1 00000001
18 ProcessorType 586 PROCESSOR_INTEL_PENTIUM
19 PhysicalMemory 1024 1024 (1073250304)
20 Product ID NULL NULL
--=====================================================================================================================
Name Owner Type Created_datetime
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------------------------- -----------------------
Tally dbo user table 2010-09-18 15:45:11.120
Column_name Type Computed Length Prec Scale Nullable TrimTrailingBlanks FixedLenNullInSource Collation
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ----------------------------------- ----------- ----- ----- ----------------------------------- ----------------------------------- ----------------------------------- --------------------------------------------------------------------------------------------------------------------------------
N int no 4 10 0 no (n/a) (n/a) NULL
Identity Seed Increment Not For Replication
-------------------------------------------------------------------------------------------------------------------------------- --------------------------------------- --------------------------------------- -------------------
N 1 1 0
RowGuidCol
--------------------------------------------------------------------------------------------------------------------------------
No rowguidcol column defined.
Data_located_on_filegroup
--------------------------------------------------------------------------------------------------------------------------------
PRIMARY
index_name index_description index_keys
-------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
PK_Tally_N clustered, unique, primary key located on PRIMARY N
constraint_type constraint_name delete_action update_action status_enabled status_for_replication constraint_keys
-------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------- ------------- -------------- ---------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
PRIMARY KEY (clustered) PK_Tally_N (n/a) (n/a) (n/a) (n/a) N
No foreign keys reference table 'Tally', or you do not have permissions on referencing tables.
Table is referenced by views
--------------------------------------------------------------------------------------------------------------------------------
Name Owner Type Created_datetime
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------------------------- -----------------------
CsvTest dbo user table 2010-09-18 15:45:11.240
Column_name Type Computed Length Prec Scale Nullable TrimTrailingBlanks FixedLenNullInSource Collation
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ----------------------------------- ----------- ----- ----- ----------------------------------- ----------------------------------- ----------------------------------- --------------------------------------------------------------------------------------------------------------------------------
RowNum int no 4 10 0 no (n/a) (n/a) NULL
CsvParameter varchar no 8000 yes no yes SQL_Latin1_General_CP1_CI_AS
Identity Seed Increment Not For Replication
-------------------------------------------------------------------------------------------------------------------------------- --------------------------------------- --------------------------------------- -------------------
No identity column defined. NULL NULL NULL
RowGuidCol
--------------------------------------------------------------------------------------------------------------------------------
No rowguidcol column defined.
Data_located_on_filegroup
--------------------------------------------------------------------------------------------------------------------------------
PRIMARY
index_name index_description index_keys
-------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
PK__CsvTest__1D66518C clustered, unique, primary key located on PRIMARY RowNum
constraint_type constraint_name delete_action update_action status_enabled status_for_replication constraint_keys
-------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------- ------------- -------------- ---------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
PRIMARY KEY (clustered) PK__CsvTest__1D66518C (n/a) (n/a) (n/a) (n/a) RowNum
No foreign keys reference table 'CsvTest', or you do not have permissions on referencing tables.
No views with schema binding reference table 'CsvTest'.
--===== Tally Table (Split8KTally iTVF) ===============================================================================
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Table 'Tally'. Scan count 10000, logical reads 30000, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'CsvTest'. Scan count 1, logical reads 774, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 13000 ms, elapsed time = 13189 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
--===== XML-1 (Split8KXML1 mlTVF) =====================================================================================
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Table '#1E5A75C5'. Scan count 10000, logical reads 10000, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'CsvTest'. Scan count 1, logical reads 774, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 64922 ms, elapsed time = 69440 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
--===== XML-3 (Split8KXML3 iTVF) ======================================================================================
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Table 'CsvTest'. Scan count 1, logical reads 774, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 1744953 ms, elapsed time = 1795828 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.
--===== Loop Method 1 (Split8KL1 mlTVF) ===============================================================================
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Table '#1F4E99FE'. Scan count 10000, logical reads 10000, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'CsvTest'. Scan count 1, logical reads 774, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 313203 ms, elapsed time = 444067 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.
========================================================================================================================
--===== System Configuration information ==============================================================================
Name CurrentValue
----------------------------------- --------------------
Ad Hoc Distributed Queries 1
affinity I/O mask 0
affinity mask 0
Agent XPs 1
allow updates 0
awe enabled 0
blocked process threshold 0
c2 audit mode 0
clr enabled 1
cost threshold for parallelism 5
cross db ownership chaining 0
cursor threshold -1
Database Mail XPs 1
default full-text language 1033
default language 0
default trace enabled 1
disallow results from triggers 0
fill factor (%) 90
ft crawl bandwidth (max) 100
ft crawl bandwidth (min) 0
ft notify bandwidth (max) 100
ft notify bandwidth (min) 0
index create memory (KB) 0
in-doubt xact resolution 0
lightweight pooling 0
locks 0
max degree of parallelism 2
max full-text crawl range 4
max server memory (MB) 2147483647
max text repl size (B) 65536
max worker threads 0
media retention 0
min memory per query (KB) 1024
min server memory (MB) 0
nested triggers 1
network packet size (B) 4096
Ole Automation Procedures 1
open objects 0
PH timeout (s) 60
precompute rank 0
priority boost 1
query governor cost limit 0
query wait (s) -1
recovery interval (min) 5
remote access 1
remote admin connections 0
remote login timeout (s) 20
remote proc trans 0
remote query timeout (s) 600
Replication XPs 0
scan for startup procs 0
server trigger recursion 1
set working set size 0
show advanced options 1
SMO and DMO XPs 1
SQL Mail XPs 0
transform noise words 0
two digit year cutoff 2049
user connections 0
user options 0
Web Assistant Procedures 0
xp_cmdshell 1
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Microsoft SQL Server 2005 - 9.00.4053.00 (Intel X86)
May 26 2009 14:24:20
Copyright (c) 1988-2005 Microsoft Corporation
Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
Index Name Internal_Value Character_Value
------ -------------------------------- -------------- ------------------------------------------------------------------------------------------------------------------------
1 ProductName NULL Microsoft SQL Server
2 ProductVersion 589824 9.00.4053.00
3 Language 1033 English (United States)
4 Platform NULL NT INTEL X86
5 Comments NULL NT INTEL X86
6 CompanyName NULL Microsoft Corporation
7 FileDescription NULL SQL Server Windows NT
8 FileVersion NULL 2005.090.4053.00
9 InternalName NULL SQLSERVR
10 LegalCopyright NULL © Microsoft Corp. All rights reserved.
11 LegalTrademarks NULL Microsoft® is a registered trademark of Microsoft Corporation. Windows(TM) is a trademark of Microsoft Corporation
12 OriginalFilename NULL SQLSERVR.EXE
13 PrivateBuild NULL NULL
14 SpecialBuild 265617408 NULL
15 WindowsVersion 248381957 5.2 (3790)
16 ProcessorCount 4 4
17 ProcessorActiveMask 15 0000000f
18 ProcessorType 586 PROCESSOR_INTEL_PENTIUM
19 PhysicalMemory 4091 4091 (4289343488)
20 Product ID NULL NULL
--=====================================================================================================================
Name Owner Type Created_datetime
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------------------------- -----------------------
Tally dbo user table 2010-09-18 15:41:57.130
Column_name Type Computed Length Prec Scale Nullable TrimTrailingBlanks FixedLenNullInSource Collation
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ----------------------------------- ----------- ----- ----- ----------------------------------- ----------------------------------- ----------------------------------- --------------------------------------------------------------------------------------------------------------------------------
N int no 4 10 0 no (n/a) (n/a) NULL
Identity Seed Increment Not For Replication
-------------------------------------------------------------------------------------------------------------------------------- --------------------------------------- --------------------------------------- -------------------
N 1 1 0
RowGuidCol
--------------------------------------------------------------------------------------------------------------------------------
No rowguidcol column defined.
Data_located_on_filegroup
--------------------------------------------------------------------------------------------------------------------------------
PRIMARY
index_name index_description index_keys
-------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
PK_Tally_N clustered, unique, primary key located on PRIMARY N
constraint_type constraint_name delete_action update_action status_enabled status_for_replication constraint_keys
-------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------- ------------- -------------- ---------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
PRIMARY KEY (clustered) PK_Tally_N (n/a) (n/a) (n/a) (n/a) N
No foreign keys reference table 'Tally', or you do not have permissions on referencing tables.
Table is referenced by views
--------------------------------------------------------------------------------------------------------------------------------
Name Owner Type Created_datetime
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------------------------- -----------------------
CsvTest dbo user table 2010-09-18 15:41:57.230
Column_name Type Computed Length Prec Scale Nullable TrimTrailingBlanks FixedLenNullInSource Collation
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ----------------------------------- ----------- ----- ----- ----------------------------------- ----------------------------------- ----------------------------------- --------------------------------------------------------------------------------------------------------------------------------
RowNum int no 4 10 0 no (n/a) (n/a) NULL
CsvParameter varchar no 8000 yes no yes SQL_Latin1_General_CP1_CI_AS
Identity Seed Increment Not For Replication
-------------------------------------------------------------------------------------------------------------------------------- --------------------------------------- --------------------------------------- -------------------
No identity column defined. NULL NULL NULL
RowGuidCol
--------------------------------------------------------------------------------------------------------------------------------
No rowguidcol column defined.
Data_located_on_filegroup
--------------------------------------------------------------------------------------------------------------------------------
PRIMARY
index_name index_description index_keys
-------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
PK__CsvTest__58117423 clustered, unique, primary key located on PRIMARY RowNum
constraint_type constraint_name delete_action update_action status_enabled status_for_replication constraint_keys
-------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------- ------------- -------------- ---------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
PRIMARY KEY (clustered) PK__CsvTest__58117423 (n/a) (n/a) (n/a) (n/a) RowNum
No foreign keys reference table 'CsvTest', or you do not have permissions on referencing tables.
No views with schema binding reference table 'CsvTest'.
--===== Tally Table (Split8KTally iTVF) ===============================================================================
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
Table 'Tally'. Scan count 10000, logical reads 30000, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'CsvTest'. Scan count 1, logical reads 774, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 5187 ms, elapsed time = 5196 ms.
SQL Server parse and compile time:
CPU time = 13 ms, elapsed time = 13 ms.
--===== XML-1 (Split8KXML1 mlTVF) =====================================================================================
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Table '#5AEDE0CE'. Scan count 10000, logical reads 10000, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'CsvTest'. Scan count 1, logical reads 774, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 37218 ms, elapsed time = 37463 ms.
SQL Server parse and compile time:
CPU time = 13 ms, elapsed time = 13 ms.
--===== XML-3 (Split8KXML3 iTVF) ======================================================================================
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Table 'CsvTest'. Scan count 1, logical reads 774, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 953000 ms, elapsed time = 953224 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 6 ms.
--===== Loop Method 1 (Split8KL1 mlTVF) ===============================================================================
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Table '#489A2669'. Scan count 10000, logical reads 10000, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'CsvTest'. Scan count 1, logical reads 774, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 143672 ms, elapsed time = 147417 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
========================================================================================================================
--Jeff Moden
Change is inevitable... Change for the better is not.
September 18, 2010 at 5:04 pm
Hi Jeff
results attached. I have a HP laptop with win XP SP2, 3GB RAM and AMD Turion x2 1.9ghz
query took 23 mins 21 secs
Regards
Perry
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 18, 2010 at 5:36 pm
Perry Whittle (9/18/2010)
Hi Jeffresults attached. I have a HP laptop with win XP SP2, 3GB RAM and AMD Turion x2 1.9ghz
query took 23 mins 21 secs
Regards
Perry
Heh... I bought a brand new HP G71 laptop back in February. Dual 64's at 3.3GHZ w/4 GB of ram, Windows 7, 280GB HD, and enough CACHE to choke a mule. I finally powered it up for the first time last night and have started to load stuff on it. Seeing what your machine did compared to the server at work, I can't wait for it to be ready to rock and roll. I also built a nasty wireless network with a 300 yard range so I can take the laptop out to the garage if I need to.
Anyway, thank you for your good time and running the code. I really appreciate it. I hope some other folks jump in soon.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 18, 2010 at 6:39 pm
Hi Jeff,
results attached.
System: WIN XP SP3
Hardware: DELL Vostro, 2QuadCPU @ 2.4GHz, 3.25Gb RAM
Profiler trace available, if needed.
September 18, 2010 at 6:41 pm
Jeff ran test on the oldest, least amount of memory, slowest desktop, age - older than dirt. Running Windows XP
Result for SQL 2005 Express in attached word doc.
Will post results for SQL 2008 Developer edition run on a desk top with slightly more memory and slightly higher speed
September 18, 2010 at 6:46 pm
Results for SQL 2008 Developer edition run on a desk top with slightly more memory and slightly higher speed Running Windows XP
Between this and my prior post should give the base line for the worst possible results:
September 18, 2010 at 7:33 pm
bitbucket-25253 (9/18/2010)
Results for SQL 2008 Developer edition run on a desk top with slightly more memory and slightly higher speed Running Windows XPBetween this and my prior post should give the base line for the worst possible results:
That run is what I feared the most but just what I was hoping for. The XML-3 run only took about half the time as the While Loop. On all the other machines so far (including mine), it usually takes about the same time or longer. Still, the Tally table smoked everything.
I hope a couple more people jump in. I'll see if I can get ahold of Brad Schultz... he was one of the folks that came up with the absolutely contrary numbers in his testing.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 18, 2010 at 7:36 pm
LutzM (9/18/2010)
Hi Jeff,results attached.
System: WIN XP SP3
Hardware: DELL Vostro, 2QuadCPU @ 2.4GHz, 3.25Gb RAM
Profiler trace available, if needed.
Thanks Lutz! I appreciate it. Nope... no trace needed (at least not yet). Your run was about the same as my server run.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 18, 2010 at 9:12 pm
Results for SQL 2005 Developer edition run on a desk top with slightly more memory and slightly higher speed Running Windows XP
Now do you need one run on SQL 2008 R2? Will be installing that on the bigger / faster box tomorrow (Sunday) and will attempt it after the iinstal.
September 18, 2010 at 9:36 pm
Jeff, PFA the results my DESKTOP..
Mine runs on
OS : Windows 7 Ultimate,
SQL : SQL Server 2005 Developer Edition RTM (9.0.1399) ,
Processor: Intel(R) Core(TM)2 Duo CPU E7400 @ 2.80GHz, 2800 Mhz, 2 Core(s), 2 Logical Processor(s)
Total RAM : 2 GB
Please tell me if u need further info from my machine.. i will run the code on my office machine which is higher power machine, and publish the results...
September 18, 2010 at 9:47 pm
bitbucket-25253 (9/18/2010)
Results for SQL 2005 Developer edition run on a desk top with slightly more memory and slightly higher speed Running Windows XPNow do you need one run on SQL 2008 R2? Will be installing that on the bigger / faster box tomorrow (Sunday) and will attempt it after the iinstal.
That's awesome and I'd love to see a run on SQL 2008 R2. Thanks, Ron!
--Jeff Moden
Change is inevitable... Change for the better is not.
September 18, 2010 at 9:51 pm
ColdCoffee (9/18/2010)
Jeff, PFA the results my DESKTOP..Mine runs on
OS : Windows 7 Ultimate,
SQL : SQL Server 2005 Developer Edition RTM (9.0.1399) ,
Processor: Intel(R) Core(TM)2 Duo CPU E7400 @ 2.80GHz, 2800 Mhz, 2 Core(s), 2 Logical Processor(s)
Total RAM : 2 GB
Please tell me if u need further info from my machine.. i will run the code on my office machine which is higher power machine, and publish the results...
CC... could you do a rerun but in the TEXT mode, please? I'm all setup to read one big text output. Thanks.
And, yeah... I'd love to see the results from the other machine, as well. The more samples I can get, the more plot points I have. Thanks, CC.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 18, 2010 at 9:54 pm
Jeff Moden (9/18/2010)
ColdCoffee (9/18/2010)
Jeff, PFA the results my DESKTOP..Mine runs on
OS : Windows 7 Ultimate,
SQL : SQL Server 2005 Developer Edition RTM (9.0.1399) ,
Processor: Intel(R) Core(TM)2 Duo CPU E7400 @ 2.80GHz, 2800 Mhz, 2 Core(s), 2 Logical Processor(s)
Total RAM : 2 GB
Please tell me if u need further info from my machine.. i will run the code on my office machine which is higher power machine, and publish the results...
CC... could you do a rerun but in the TEXT mode, please? I'm all setup to read one big text output. Thanks.
Anything for u Jeff.. But do i have some 3 hrs ? got to meet my friend now who is in hospital.. so can your consolidation hold for another 3 hrs? Thanks in advance..
September 18, 2010 at 9:57 pm
ColdCoffee (9/18/2010)
Jeff Moden (9/18/2010)
ColdCoffee (9/18/2010)
Jeff, PFA the results my DESKTOP..Mine runs on
OS : Windows 7 Ultimate,
SQL : SQL Server 2005 Developer Edition RTM (9.0.1399) ,
Processor: Intel(R) Core(TM)2 Duo CPU E7400 @ 2.80GHz, 2800 Mhz, 2 Core(s), 2 Logical Processor(s)
Total RAM : 2 GB
Please tell me if u need further info from my machine.. i will run the code on my office machine which is higher power machine, and publish the results...
CC... could you do a rerun but in the TEXT mode, please? I'm all setup to read one big text output. Thanks.
Anything for u Jeff.. But do i have some 3 hrs ? got to meet my friend now who is in hospital.. so can your consolidation hold for another 3 hrs? Thanks in advance..
Anytime is a good time. Absolutely no rush, CC. I'm happy to have this much help, so far. I didn't think I'd have this much help until Monday or so. Thanks for your help and I hope your friend is OK.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 18, 2010 at 9:59 pm
It's midnight here. I've got to get some shuteye. Apologies for not answering anything on this post for about the next 8 hours or so (especially to those on the daylight side of the Earth right now).
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 214 total)
You must be logged in to reply to this topic. Login to reply