I need some help with a test, please.

Question

I need some help with a test, please.

Viewing 15 posts - 61 through 75 (of 214 total)

You must be logged in to reply to this topic. Login to reply

steve-893342 SSCarpal Tunnel Points: 4439 More actions · Answer 1

Jeff Moden (9/19/2010)[hr
Hi Steve. Thanks for your involvement on this thread. Any chance of your running the code and returning the results for some extra data points for me

Yes absolutely Jeff

Please find enclosed the results run a 2008 virtual machine

BTW, not to worry in the context of this test, but I think there may be a slight typo in the function Split8KXML1

I believe the line

SELECT @XML = '<r>'+REPLACE(@Parameter, ',', '</r><r>')+'</r>';

should be

SELECT @XML = '<r>'+REPLACE(@Parameter, @Delimiter, '</r><r>')+'</r>';

to accommodate delimiters other than a comma

prvmine SSCertifiable Points: 7007 More actions · Answer 2

prvmine

SSCertifiable

Points: 7007

September 19, 2010 at 4:24 pm

#1223718

Jeff,

attached results --

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 3

steve-893342 (9/19/2010)
Jeff Moden (9/19/2010)[hr
Hi Steve. Thanks for your involvement on this thread. Any chance of your running the code and returning the results for some extra data points for me
Yes absolutely Jeff
Please find enclosed the results run a 2008 virtual machine
BTW, not to worry in the context of this test, but I think there may be a slight typo in the function Split8KXML1
I believe the line
SELECT @XML = '<r>'+REPLACE(@Parameter, ',', '</r><r>')+'</r>';
should be
SELECT @XML = '<r>'+REPLACE(@Parameter, @Delimiter, '</r><r>')+'</r>';
to accommodate delimiters other than a comma

Wow! People really do read the code. Glad I comment it.

Thanks for the catch, Steve. You're correct... probably no problem in the context of this test but I'll run a couple of tests and make sure there's no unfair advantage do to a variable NOT being used there. And thanks for the run results.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 4

bitbucket-25253 (9/19/2010)
Jeff I said I would attempt to run the code on SQL2008 R2. Been attempting to install the %&@ thing since 10 AM this morning. Things are not proceeding well or rapidly. Things are going so #$%@ bad that I feel like taking the DVD out of the machine and sailing it across the back yard never to be seen again.

Oddly enough, that's not the first time I've heard such a thing about R2. The folks that told me about it expressed it just about the same way you did, too! 😛

If you ever get it figured out, you should write an article on it. At least folks will all be able to cuss the thing together. :hehe:

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 5

Craig Farrell (9/19/2010)
SQL Express 2k5 9.00.3042.00 Desktop machine.
Sorry it took a bit, I went out and partied last night. 🙂

Heh... how could I ask for more on a Sunday? Thanks Craig.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 6

prvmine (9/19/2010)
Jeff,
attached results --

Very cool. Another 2008 2 processor box. Thanks prvmine!

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 7

Yowch... I just tried XML-3 in a 1000 row by 1000 element test rig... not good. I stopped the run after two hours. Needless to say, I probably won't include the XML-3 test in any of the big tests I'm running.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 8

As a side bar, I'm seeing that most people don't make any or many changes from the default configuration. I expected to see a much wider variance on the default collation (shows up in the sp_help on the dbo.CsvTest table) but most people use the default (which, as Paul White will attest to, is a pretty good thing for performance reasons).

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Brad Schulz Old Hand Points: 395 More actions · Answer 9

Okay, as requested, I'm attaching my results... 2 sets... one for SQL2005 (32-bit) and one for SQL2008 (64-bit).

I used a Laptop... Windows 7 Professional, 4-processor Intel Core i5 CPU M430 @ 2.27Ghz with 4GB RAM.

But the results don't really surprise me... There is no doubt when doing these kinds of time tests that the CLR is going to be the fastest (thought that's not part of this test), and the Tally table is going to be the fastest non-CLR approach.

But how often in the "real world" are you going to split 10,000 rows of comma-delimited lists consisting of 100 items... and that's all you're going to do?

Isn't list-splitting used most in the "real world" to acquire a set of ID's from a table? This is why I wrote my tongue-in-cheek blog post last month:

http://bradsruminations.blogspot.com/2010/08/integer-list-splitting-sql-fable.html

It was written tongue-in-cheek, but I was trying to demonstrate a real-world scenario. The Tally table approach is sure fast when you're just stripping a list, but once you actually USE that list to do something (i.e. lookup the ID's in a table), then it becomes a pig, because the optimizer is going to try to be smart at incorporating its inline function into the query, and it does a really lousy job of that.

If you do the following code to construct a SINGLE varchar(8000) variable with a 1000-item list of integers and use it to JOIN a couple tables from AdventureWorks...

--Build a list of 1000 items from the first 10 rows of 100 items each:

declare @CSVList varchar(8000)

select @CSVList=coalesce(@CSVList+',','')+csvparameter

from (select top 10 csvparameter from csvtest) x

--And use it in a query:

declare @ProductID int, @ProductName varchar(50), @SalesOrderID int, @OrderDate datetime, @TerritoryName varchar(50);

select @ProductID=d.ProductID

,@ProductName=p.Name

,@SalesOrderID=h.SalesOrderID

,@OrderDate=h.OrderDate

,@TerritoryName=t.Name

from dbo.Split8KTally(@CSVList,',') a

join AdventureWorks.Sales.SalesOrderDetail d on cast(a.ItemValue as int)=d.ProductID

join AdventureWorks.Production.Product p on d.ProductID=p.ProductID

join AdventureWorks.Sales.SalesOrderHeader h on d.SalesOrderID=h.SalesOrderID

join AdventureWorks.Sales.SalesTerritory t on h.TerritoryID=t.TerritoryID

order by d.ProductID,h.SalesOrderID ;

...then everything changes.

If I try the above with the SPlit8KTally function and the Split8KXMLBrad function and the Split8KL1 function, I get the following results from the SET STATISTICS TIME,IO ON (using SQL2008):

Split8KTally: CPU time = 239665 ms, elapsed time = 111526 ms.

Split8KXMLBrad: CPU time = 1248 ms, elapsed time = 1323 ms.

Split8KL1: CPU time = 141 ms, elapsed time = 136 ms.

Now which is fastest in a real world scenario? The Tally Table is the big-time loser and the multi-line loop function is the clear winner by a mile.

I'm just sayin'...

--Brad

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 10

Brad Schulz (9/19/2010)
but once you actually USE that list to do something (i.e. lookup the ID's in a table), then it becomes a pig,

First, thanks for the test runs, Brad. Much appreciated.

As I pointed out on your blog, I wasn't getting the "pig" like performance you speak of using your good code even with DBCC FREEPROCCACHE. I'll double check what you just posted. Thanks for your time.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 11

Brad Schulz (9/19/2010)
...then everything changes.
If I try the above with the SPlit8KTally function and the Split8KXMLBrad function and the Split8KL1 function, I get the following results from the SET STATISTICS TIME,IO ON (using SQL2008):
Split8KTally: CPU time = 239665 ms, elapsed time = 111526 ms.
Split8KXMLBrad: CPU time = 1248 ms, elapsed time = 1323 ms.
Split8KL1: CPU time = 141 ms, elapsed time = 136 ms.
Now which is fastest in a real world scenario? The Tally Table is the big-time loser and the multi-line loop function is the clear winner by a mile.
I'm just sayin'...
--Brad

So tell me... what's am I doing so wrong here at 98 ms for the Tally table using your code? Why is it that the Tally table is beating both in this scenario on my machine?

Would some of you other good folks run this code and post the results please? We need both 2k5 and 2k8 runs for anyone willing to participate Again, the code is all there including a fresh copy of the Tally table because this all runs in TempDB. And, again, many thanks to all of you.

Here's the code...

--===== Do this in a nice safe place that everyone has

USE tempdb;

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

--===== 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

--=====================================================================================================================

-- Build some test data from the table that is actually using in the tests

--=====================================================================================================================

--===== Build a thousand item CSV row without it being dependent on other test tables

DECLARE @CSVList VARCHAR(8000);

SELECT @CSVList = STUFF(

(

SELECT TOP 1000 ','+ CAST(SalesOrderDetailID AS VARCHAR(10))

FROM AdventureWorks.Sales.SalesOrderDetail

ORDER BY NEWID()

FOR XML PATH('')

)

,1,1,'');

--===== Show what we have for a parameter now...

PRINT @CSVList;

--=====================================================================================================================

-- Run the tests using Brad's code

--=====================================================================================================================

--===== Commence testing ...

SET STATISTICS TIME,IO ON;

--And use it in a query:

PRINT '--===== Split8KTally ==========================================================================================='

declare @ProductID int, @ProductName varchar(50), @SalesOrderID int, @OrderDate datetime, @TerritoryName varchar(50);

select @ProductID=d.ProductID

,@ProductName=p.Name

,@SalesOrderID=h.SalesOrderID

,@OrderDate=h.OrderDate

,@TerritoryName=t.Name

from dbo.Split8KTally(@CSVList,',') a

join AdventureWorks.Sales.SalesOrderDetail d on cast(a.ItemValue as int)=d.ProductID

join AdventureWorks.Production.Product p on d.ProductID=p.ProductID

join AdventureWorks.Sales.SalesOrderHeader h on d.SalesOrderID=h.SalesOrderID

join AdventureWorks.Sales.SalesTerritory t on h.TerritoryID=t.TerritoryID

order by d.ProductID,h.SalesOrderID ;

--declare @ProductID int, @ProductName varchar(50), @SalesOrderID int, @OrderDate datetime, @TerritoryName varchar(50);

PRINT '--===== Split8KXMLBrad ==========================================================================================='

select @ProductID=d.ProductID

,@ProductName=p.Name

,@SalesOrderID=h.SalesOrderID

,@OrderDate=h.OrderDate

,@TerritoryName=t.Name

from dbo.Split8KXMLBrad(@CSVList,',') a

join AdventureWorks.Sales.SalesOrderDetail d on cast(a.ItemValue as int)=d.ProductID

join AdventureWorks.Production.Product p on d.ProductID=p.ProductID

join AdventureWorks.Sales.SalesOrderHeader h on d.SalesOrderID=h.SalesOrderID

join AdventureWorks.Sales.SalesTerritory t on h.TerritoryID=t.TerritoryID

order by d.ProductID,h.SalesOrderID ;

--declare @ProductID int, @ProductName varchar(50), @SalesOrderID int, @OrderDate datetime, @TerritoryName varchar(50);

PRINT '--===== Split8KL1 ==========================================================================================='

select @ProductID=d.ProductID

,@ProductName=p.Name

,@SalesOrderID=h.SalesOrderID

,@OrderDate=h.OrderDate

,@TerritoryName=t.Name

from dbo.Split8KL1(@CSVList,',') a

join AdventureWorks.Sales.SalesOrderDetail d on cast(a.ItemValue as int)=d.ProductID

join AdventureWorks.Production.Product p on d.ProductID=p.ProductID

join AdventureWorks.Sales.SalesOrderHeader h on d.SalesOrderID=h.SalesOrderID

join AdventureWorks.Sales.SalesTerritory t on h.TerritoryID=t.TerritoryID

order by d.ProductID,h.SalesOrderID ;

SET STATISTICS TIME,IO OFF;

--=====================================================================================================================

-- 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.Split8KL1, dbo.Split8KXMLBrad;

DROP TABLE dbo.Tally;

GO

And here are the results... the Tally table smokes the other methods and we need to find out why Brads machine is running this so badly. Brad, if you could please, please post the CREATE TABLE code for your Tally Table so we can make sure we're using similar things. Thanks.

(11000 row(s) affected)

70203,5112,104684,95518,57999,103090,63816,54912,63872,110328,46477,95031,11830,4457,99257,43105,7426,111344,5159,27080,89872,19899,103733,50590,110926,37263,3559,84024,87361,99650,93060,30088,76076,93719,96407,85936,43305,54924,112545,5341,99516,13402,20828,48145,40947,13219,87736,41015,56310,71885,75711,29942,100271,89413,25471,69423,54968,91767,102635,57085,79908,52159,83533,100121,64250,87842,90732,81996,79532,93136,29892,70295,63139,89658,49187,90823,120797,102277,18172,62535,26048,30574,83238,286,61855,51761,28134,87070,118619,23797,113413,80942,24975,22363,62169,33863,32963,41398,94736,31256,83782,28692,10926,29243,116727,829,61278,28826,86376,92339,56065,97531,73002,106095,53746,48978,94186,90760,107647,35361,92554,82989,46948,9703,82228,39041,91828,80626,91776,39754,73317,75992,64636,8528,78821,43876,23301,62496,43300,113224,38705,642,106920,89587,53824,22781,49484,83680,22472,46611,67197,32276,56194,109813,70247,27472,33719,43,38602,1417,26655,53488,107542,14950,119719,31252,104806,41716,89454,31663,82153,32955,4029,96468,104907,43833,45720,58530,45144,62605,101352,69020,44631,93074,70972,77440,81698,74665,101878,1128,73465,42592,53555,84166,4615,87001,57273,7743,59336,7423,51021,59154,86974,112245,35574,103850,100985,19180,95843,79259,29496,94948,77336,32421,118714,41194,100672,97835,68743,8683,4753,21036,41476,105061,21294,34003,90022,70442,107632,6340,279,106714,14278,10835,77760,17901,9905,80269,94673,3111,18895,69638,82076,13998,65640,111374,95718,111582,63884,86908,57567,42027,24905,102931,90729,72456,4553,89976,70685,93381,81763,4365,43600,92357,63676,33265,89273,119994,3286,21671,112805,20920,15718,80960,5578,69273,297,107405,24249,64497,99065,85799,22933,111362,70364,88208,53222,28961,24272,77825,14367,8188,77328,53828,33078,53385,39468,41439,11337,109860,56750,24152,20305,1274,115706,67285,61105,25729,38307,26807,104938,18584,14811,3566,31374,99216,90121,55974,120034,71418,46267,64957,80120,65797,39161,24306,19179,115711,62186,103780,70820,91802,105296,53044,35117,117186,97089,36968,22502,53036,85431,94101,93769,29444,65914,87971,2389,37410,63128,435,109053,57737,118830,35763,3736,11001,36062,107123,63635,69712,78903,109775,65022,4050,28884,94875,35458,37248,80040,2271,19936,39853,9373,84069,80910,110989,59024,60516,117793,114429,55005,42115,109869,109444,95967,64054,26392,49992,35192,90534,63932,4957,95432,40319,115839,39875,110571,68814,102080,92975,113573,118836,84424,46881,70373,25952,63987,49540,12451,87242,90568,14582,61605,23269,114364,1671,61565,24131,3527,40477,17727,109526,9001,12773,99985,121089,63878,117788,32062,82267,43847,35647,41436,114065,107029,9660,66310,15771,30024,35245,119557,87907,77444,8366,50046,51927,66045,111655,53734,32121,29708,89722,81858,31117,109515,97319,49008,67030,62612,112083,5698,68824,72170,96423,34344,31797,91160,70919,45627,112968,23528,76364,46366,4649,90399,93084,44629,10304,113871,42128,10480,72253,96691,114089,70524,73732,91797,75247,54566,29931,106879,67525,80070,4489,66957,31135,46901,39099,114881,109493,19934,107190,63059,77354,111217,16088,75665,3469,120816,79943,110162,47677,10212,35427,116600,108843,40240,52494,67393,119608,116720,85451,57244,66311,95218,11077,64090,75738,43015,95358,92958,33716,31558,32019,13871,120950,39639,102783,24723,34222,50767,52439,60657,11209,71678,85209,31546,91071,49749,13196,29148,16904,13308,89504,42410,71177,24727,115143,23621,92341,27055,22020,59825,2797,40757,117343,78849,43097,60019,56431,89663,45076,38138,16304,114325,63680,37416,26894,17884,109197,107657,100463,22147,61866,5130,6617,4430,117385,20183,36665,23940,100051,11599,9864,115444,56433,9532,41420,75165,57734,2973,37475,58562,13445,81303,20002,107275,52097,28206,69182,74298,77160,9531,19969,66845,41393,11984,63815,112075,45940,115113,36055,2457,61766,116524,94828,28657,55911,114462,13514,97006,24176,60619,105260,38395,72543,88004,21343,24804,29635,38710,25828,29260,98365,30811,97455,44744,101650,46904,2940,7910,99609,55598,23689,67180,72806,114037,42481,73659,70422,95796,67716,77728,20144,43572,63195,11363,109269,89420,42717,113518,22062,104524,112754,80406,40604,119250,83177,26603,89695,117009,51657,17019,47793,95648,86248,91516,43126,84574,12156,113154,75737,115996,114519,106735,8056,14648,30834,64597,66219,22382,38988,78905,106532,47495,15123,77489,64459,1518,111117,53288,41453,68165,9526,42367,109236,79850,97909,103506,55299,89926,50775,46092,13992,44229,88357,119929,43273,25521,116338,46729,18754,88887,52877,23217,6904,60144,79412,81744,58261,101726,24981,79413,54317,24080,106197,117370,51496,88065,109442,40764,24365,98477,102381,45718,38921,119134,51912,12944,112650,19644,71304,65586,38241,55817,3421,56249,38114,39205,17233,80349,106244,6895,58972,30631,29416,31313,15898,100083,77692,58572,52372,79188,49161,27181,105266,77435,85304,4757,108925,46062,71189,33495,113273,17305,6420,19904,75544,56631,114255,88458,56121,82798,96511,93532,71545,118368,5796,74645,37453,7512,98761,108562,58229,89365,98356,80647,43751,2961,12034,86928,65459,47222,62352,34667,27562,64486,115387,97877,26831,53377,73241,113098,90045,91721,12058,59634,8911,114113,115037,42442,102083,94690,100521,55337,95787,64668,34438,14574,92578,61099,83253,117228,39674,115647,104047,65222,71383,87692,42667,20776,92816,59144,79595,24515,834,100098,97992,14353,74055,107572,117073,120014,117851,116778,40646,109038,94907,85733,103614,73111,1410,95423,2930,93065,79860,32870,96737,92676,46529,116669,107825,26093,26895,73463,104258,34885,104686,48998,103315,108773,59038,117927,24871,51116,71184,78612,27772,107804,106699,74880,41336,2963,93433,52058,23394,115146,65035,44930,2697,8550,92804,89073,78441,86755,101600,87531,111717,88847,18951,82503,54471,29157,120267,103058,4686,28595,50608,72975,96008,79795,28539,76531,26354,27091,118590,76113,70472,92348,76020,84486,51253,93322,81185,25711,83579,101176,28943,95191,100114,11598,37500,67219,9007,343,6310,41463,104110,73419,80193,62094,48860,104245,75479,119536,72741,44283,204,69914,91711,29368,55399,19226,82432,15336,98324,107629,18120,87364,109288,110803,92613,28396,114188,32230,1789,37139,18032,29192,42457

--===== Split8KTally ===========================================================================================

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 1 ms.

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'SalesOrderDetail'. Scan count 1000, logical reads 3142, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Tally'. Scan count 1, logical reads 12, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Product'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'SalesOrderHeader'. Scan count 1, logical reads 703, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'SalesTerritory'. Scan count 1, logical reads 2, 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 = 94 ms, elapsed time = 98 ms.

--===== Split8KXMLBrad ===========================================================================================

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 0 ms.

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'SalesOrderDetail'. Scan count 1000, logical reads 2142, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Product'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'SalesOrderHeader'. Scan count 1, logical reads 703, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'SalesTerritory'. Scan count 1, logical reads 2, 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 = 125 ms, elapsed time = 161 ms.

--===== Split8KL1 ===========================================================================================

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 0 ms.

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'SalesOrderDetail'. Scan count 4, logical reads 8, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Product'. Scan count 0, logical reads 2000, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table '#6EC0713C'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'SalesTerritory'. Scan count 1, logical reads 2, 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 = 312 ms, elapsed time = 403 ms.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 12

Brad Schulz (9/19/2010)
It was written tongue-in-cheek, but I was trying to demonstrate a real-world scenario. The Tally table approach is sure fast when you're just stripping a list, but once you actually USE that list to do something (i.e. lookup the ID's in a table), then it becomes a pig, because the optimizer is going to try to be smart at incorporating its inline function into the query, and it does a really lousy job of that.

I believe I've just demo'd the same real world scenario and my findings using your code are quite different. Now we need to figure out why. Lets start with you posting the CREATE TABLE statement and the indexes for the Tally table you used. Hopefully by then, a couple of other people using 2k5 and 2k8 will have also run the test (which contains a known Tally table of its own) and we'll maybe we can figure out why the Tally table isn't fairing so well on your machine for your test.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 13

By the way, Brad... that's one heck of a nice 4 x 4 you have for a laptop. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 14

Ah... one more thing, Brad. Would you run that latest code in both your 2k5 and your 2k8 environments so we have exactly apples-to-apples to compare with? Much appreciated. (heh... still can't talk much... mouth is watering over your machine). 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 15

Crud... I did it again. I've got to get some shuteye. I'll see you folks in about 8 hours or so. Thanks again for everyone's help. We'll get to the bottom of this, yet.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)