I need some help with a test, please.
-
Jeff, here are the other 3 environment's results (Note : This result set is not the results of the AdventureWorks JOIN code test rig ; this is of the first post of this thread)
All of the environments are desktops with Env5 using SQL 2008 RTM) and Env3 & Env4 using SQL 2005 SP2..
-
More results attached, this time on a server.
Also SQLCLR figures are this
Tally:
Table 'Tally'. Scan count 10000, logical reads 30000, physical reads 0
Table 'CsvTest'. Scan count 1, logical reads 774, physical reads 0
CPU time = 4531 ms, elapsed time = 4523 ms.
SQLCLR:
Table 'CsvTest'. Scan count 1, logical reads 774, physical reads 0
CPU time = 2562 ms, elapsed time = 3452 ms.
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Jeff Moden (9/19/2010)
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.I can confirm Brad's results on my (2K8) instance:
116868,36286,6828,39319,1631,112932,6602,31247,35366,23110,49229,61040,15453,11,30952,62765,31788,33386,89922,31867,88301,48823,115051,14778,99044,60315,11354,82368,9999,90142,43485,63035,26761,4175,8875,97662,66726,111437,116108,117068,34159,104802,52725,78269,50186,113969,101296,5510,31866,6301,79689,11948,29538,109308,88559,63525,17836,81417,85874,64395,6924,109219,74067,56730,33098,27945,17577,97142,89434,84901,13325,106137,69298,31017,118087,26674,49383,90955,36489,43383,967,82859,86301,72167,110468,96183,75115,109963,22454,44367,101399,53877,6992,530,108095,64564,4131,7552,62910,53055,106011,117404,93043,117744,22769,97248,112950,102191,32283,21162,27206,85992,24090,46291,85908,20197,16553,50164,29397,17950,117210,108062,28142,39041,9394,91680,82186,32553,6877,7348,96899,115409,37314,73066,11301,59225,97553,43243,52854,97396,91959,30173,106913,64559,34539,33606,81802,113011,95517,10096,42727,11807,58880,58279,92303,114233,55349,109259,21641,83093,48403,108134,103145,17485,79841,57895,116646,8623,73579,94961,76796,40617,26389,29283,45379,5227,46760,37955,6661,88068,28341,120690,1723,7359,27484,13692,7686,113735,46920,84059,80302,109258,90028,22357,33108,20316,45692,67891,119512,30967,3409,75197,46206,34274,119420,116339,8911,67821,113361,13904,93105,90630,121304,16061,71898,110182,106055,112890,11316,30040,90307,56510,58490,60350,26055,14496,103356,114870,109356,41157,67961,69721,29913,597,4935,76974,61009,46567,47225,67458,51971,62708,38352,73603,112922,16239,57123,28262,149,44055,77328,35016,62544,13943,98090,114473,106931,85173,77655,27760,90252,11994,6664,97770,51688,110641,73262,106133,50095,81159,54193,22370,8044,95430,81804,75110,15296,28442,4310,50648,34395,92855,25447,45073,100992,99148,70300,76953,78491,78175,114912,40346,59298,14566,41845,70835,17213,73604,104548,110532,41949,74373,31820,61193,97247,67062,97069,6470,17654,74938,65525,100358,44925,61335,89994,67649,84234,47144,31952,51677,32968,55366,100492,105432,30734,40338,87346,73936,78591,106565,86566,56435,56292,81617,102646,16175,87031,102356,22815,96894,56418,2313,80763,55418,25922,120219,105668,100293,28684,53450,95831,68306,101783,22175,56035,109637,77101,47663,75826,42068,49183,8558,51851,20565,98999,43704,34628,262,91207,52337,101929,32854,23957,79201,34213,105805,47583,86790,59975,46405,2857,117338,24900,13209,26238,110579,58273,92648,4270,66477,18767,75628,91598,110142,39424,60934,44015,85703,66287,52681,72815,97439,43820,28560,15638,63960,62623,23868,106043,44511,99742,5763,95156,120720,82988,91449,6280,1876,18683,56177,66010,54691,72752,28169,54147,9091,63190,49487,89205,89645,72738,37613,3765,56555,7151,5145,117868,7526,32441,4167,106705,104611,49248,118974,50820,87196,11816,87258,15509,30985,117409,77449,3822,94639,53315,60213,61444,4358,44153,86324,9851,61986,66238,28185,56266,31321,55449,117193,21602,108318,16388,29073,77194,43345,10195,30391,67141,24162,59370,1153,110922,17229,62194,4897,100993,31050,50841,99353,60509,25440,47526,11964,6316,19784,92427,9416,85434,74928,50378,4170,21480,18015,81031,117775,63436,72010,13159,61745,50800,119,907,84603,58981,11439,108614,93111,110231,119407,76749,27836,83289,59816,47788,820,104562,54277,70036,43220,4063,5913,8885,26814,23655,60907,41037,48099,33580,84381,43369,102899,88829,98047,61431,78838,56544,5311,23829,86715,106783,93653,50060,68176,62649,94667,22741,35822,114019,40813,51919,68584,52826,36326,30615,49272,48851,65228,94205,17858,43838,86467,780,30801,82922,12987,102707,21367,69694,33601,16280,8052,38227,16023,115023,31130,109013,116860,55992,45890,42843,98135,21523,52374,114666,94098,54007,110640,17639,121215,63141,23459,107320,34076,49460,35839,84452,31049,25476,88485,42206,67880,50458,89039,29751,63380,37157,46880,38621,20438,40489,24914,46383,17978,5504,97205,42774,120082,109066,80865,59775,48572,24096,52919,53896,14022,58363,31988,15994,42446,19438,57242,98264,39636,28858,32840,2993,44579,44661,98165,7823,116984,102332,102864,104670,46499,64556,49754,25323,102951,37353,7715,114083,14521,70813,11793,23677,80956,89613,117701,93968,43856,120074,10854,91769,76873,90719,117815,86923,72929,81150,50126,49165,16681,68530,94774,10184,10773,65786,74727,77294,64108,31509,26919,73637,12751,22543,11980,9924,80521,9317,36451,80704,75221,26198,30179,59029,29388,46194,16824,112158,86541,98290,59597,20223,39139,105011,106278,108402,119696,71827,35322,120875,28359,59956,28211,31223,54073,79057,74890,81445,94281,105407,17367,55506,108659,89465,48119,101730,35288,75114,16989,93162,110737,17784,71701,113783,19031,106481,76916,63828,58482,120671,68529,42061,93590,12941,29726,20899,44057,5098,81548,55429,69632,50895,22008,64293,104332,69591,2449,65010,105149,31714,119647,25920,63230,63203,42131,39919,38702,104765,58265,111951,71262,79395,106087,65858,19509,112665,1113,83387,90285,67611,105233,57813,105753,13845,70008,4733,69182,21832,69827,49323,86536,29157,50147,50678,89001,35848,65968,21796,114463,76751,87048,86836,70737,99905,97110,8999,91251,74699,48471,109884,115156,5160,90609,105565,70966,31573,72217,61332,104893,92708,13241,105119,51962,5223,86134,44927,59308,93008,59488,91521,52609,49287,56254,4252,43155,104144,46709,29724,58834,110661,29483,116149,96220,39374,79072,75098,12542,84560,115826,79596,78008,96057,1131,109927,104250,46059,119919,67913,22661,93042,99615,96718,66501,98544,81924,65469,119134,70111,20209,41195,101468,100117,113920,27117,11681,55567,103539,116495,23109,96085,62080,32780,62117,59213,86802,38757,24830,36306,80308,111253,3135,25627,87439,97290,99820,104871,114859,16714,92873,100377,44458,53175,6375,8722,34376,106379,30560,57858,32566,110506,118106,91183,72850,59028,40869,89319,58532,107963,106871,116016,114431,116158,82223,55726,118200,46533,44945,54554,102579,977,74719,70798,80771,73251,44971,104080,83372,53743,17742,59498,6085,82762,52891,53732,29958,52987,115513,80745,117810,8145,68888,99266,113003,107652,94960,33278,70113,73173,101716,16482,46937,60671,56891,72371,31326,44741,2520,91267,31537,112762,64784,36618,25910,119010,64041,26818,41392,35125,112966,115545,58526,86033,26310,76065,89120,12877,49792,14191,10207
--===== Split8KTally ===========================================================================================
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 38 ms.
Table 'Worktable'. Scan count 1, logical reads 487274, 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 'SalesOrderDetail'. Scan count 1, logical reads 228, 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 27, 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 = 868172 ms, elapsed time = 959781 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 2147, physical reads 0, read-ahead reads 6, 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 = 62 ms, elapsed time = 174 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 '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 'SalesOrderDetail'. Scan count 6, logical reads 17, 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 '#07C12930'. 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 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 156 ms, elapsed time = 627 ms.
Paul
-
Similar results here (on Quad Core laptop SS2K8 SP1):
(11000 row(s) affected)
93505,119866,70015,71143,24626,62589,40184,59632,38481,118972,96168,118056,1795,109516,83755,2966,13657,56092,34704,45238,36859,65379,45753,16887,34112,120845,119138,22041,74279,83754,34796,6808,57448,99588,57252,116251,3459,57143,111840,27789,9306,43152,69984,86956,103090,20651,78984,103994,49750,4216,99706,108684,104970,7768,48837,22219,36379,18951,87704,119377,38495,120803,13227,58167,103388,61873,109852,12315,17756,31004,81302,54261,22279,34523,43652,67043,2656,14668,7523,89039,1819,15905,92662,55380,61642,35440,69001,88761,109026,100653,83910,78138,56175,39300,115153,73287,58867,103401,30146,22929,111148,78761,89283,45205,28267,88940,39738,70838,34181,72573,74317,53090,103229,68089,120804,70965,63660,58484,62077,40720,78515,49156,12166,108634,58902,84864,48113,74813,17686,26688,45200,13848,71295,110993,108735,39060,100429,34773,49710,514,50029,99672,87908,95850,69608,20017,47768,66719,34255,80490,76102,113501,114636,45012,73598,40816,101330,23720,8585,29180,101994,111028,117842,13643,17374,70470,43040,61723,71675,26311,59288,118734,107933,9291,29532,65992,41254,38194,88399,103982,75317,112826,104399,109436,9674,1744,77216,43980,28337,74047,29505,78592,92615,63417,46301,8348,26793,44853,35322,106834,77870,15084,82373,9743,16755,11046,22871,39033,108282,71022,38614,41937,92730,57515,93913,33194,24815,94997,65920,26764,58900,120109,113374,81338,88301,73308,85284,104272,42101,117046,113409,58940,2183,73455,48917,62185,26033,51183,32359,51301,83936,6697,90039,93948,16533,15432,4366,16093,70918,330,63036,46041,1970,16165,17083,119929,61799,10509,63384,44498,103265,29786,45782,16313,110622,107622,50402,5417,101783,73682,26066,58114,11869,108378,24573,78397,96209,58942,84767,119284,98252,61683,84690,64959,35649,62914,103553,82067,110531,116935,7086,109757,1956,63063,33163,98632,30236,35203,74341,51552,35485,49080,17190,114811,54193,72382,9446,47631,93415,36195,115965,29750,102307,21760,44295,56725,36201,66518,41917,49412,14373,76560,46946,114449,49261,67914,75163,25036,82033,30037,65455,10039,1233,111266,6510,112531,63593,51449,9384,46049,106440,90308,97497,83817,94695,90893,58800,112045,106194,73321,115483,39791,30526,91720,114369,35278,54289,85914,43453,3064,82524,16947,34101,35472,76253,24736,46471,32012,27314,23925,98604,101638,67464,85997,110012,11713,101618,96727,11835,120466,91405,104954,62934,884,43690,43407,76061,346,101846,93251,84265,83075,23557,69713,7386,85889,39087,12320,105535,112058,87007,12963,21319,94335,61282,15347,119909,49439,56106,118954,87497,45384,28606,78721,102117,6681,119153,14486,51953,98518,6189,33062,43400,118509,100285,14467,110037,37860,24916,61870,13886,117234,102306,62286,32109,114870,8786,21167,101111,119204,48573,27023,50601,39210,117846,67323,25030,117147,47312,68204,91539,9656,93402,30173,43122,54541,63792,90684,112169,96694,87446,120256,29667,71773,64341,110558,3195,96714,102923,45596,72423,59622,29896,121259,80528,28203,16312,50253,54531,29562,65904,97937,98450,46821,100872,27534,76221,51932,83293,40676,84609,111564,794,31750,83511,93267,94154,23603,51973,41449,8798,60876,59537,36127,18211,89544,66920,12297,107993,65069,1706,2505,118718,114613,58863,97763,60335,62611,111389,18709,64420,68987,13491,5591,112705,12133,117678,32802,14496,61079,83931,29218,26041,118141,50752,107019,99586,23476,9299,16806,84492,5257,4228,82458,107236,85656,43148,59979,31297,69676,28672,12572,10286,64731,70745,90813,91101,13808,60446,75437,120439,20500,7895,107878,72589,77358,111325,42589,74568,97577,119083,17835,94852,67572,85161,63734,59970,8869,70552,116627,113715,102425,42207,8503,113537,21018,30197,118457,45371,93838,58873,7620,51406,6590,103706,35968,57457,12115,53612,54238,68016,74256,27475,23853,9714,9451,6289,20647,15833,98085,111900,25469,42652,75314,26072,78051,13607,89758,10612,38206,12101,100973,15170,63748,10532,19489,77975,34928,47571,91317,41054,22750,45063,17839,10410,68183,105837,35435,42458,628,69953,104133,57656,35952,54059,38842,80009,52446,61432,40012,94369,80747,77156,14141,49703,44767,46028,6721,47619,54067,13798,114736,64623,20397,104077,13761,89908,4392,8978,54731,47106,69093,3786,89423,18828,77620,3128,93826,38244,29688,57893,46510,80180,34487,84514,43104,31906,77212,54613,65099,70988,116575,64382,69007,2839,20602,14159,98603,118221,58547,55375,20293,107771,115030,11022,112892,45178,33380,39253,75835,64427,89392,19046,62207,71163,65627,108476,101911,93218,47005,61664,7900,40367,24433,25760,64035,70329,27643,42913,107356,36597,82055,46543,4033,107785,21729,23963,99060,62425,108430,38485,43592,36599,74737,78012,18977,62276,36641,88675,77660,58052,36748,102724,9030,52866,40752,44828,107321,64782,95809,115245,2909,35462,108263,56893,49604,114832,95136,70802,55331,33367,96104,23691,6802,101965,96915,44265,47327,26801,119449,86035,99022,98780,99225,53718,11090,18779,51891,86373,79982,54845,102756,93602,99802,101164,95680,119070,18094,79160,27817,25813,84565,16106,27655,7872,41644,94034,60755,83194,5630,101628,42801,81484,23145,16817,83727,74144,100330,59809,55280,115858,3228,14130,112121,101565,60074,1974,30838,9876,109789,82057,6813,51657,112661,19657,98841,58094,53050,83232,6454,12742,58141,59795,1055,21333,59894,85560,77254,64118,83835,49068,4412,51750,31830,79478,12378,51453,101436,12702,17217,83578,10006,73547,75159,92789,29885,43054,25958,39982,83381,22437,43999,22135,11056,65149,106475,110608,89883,3209,15695,32040,109352,115460,115734,45234,92382,22782,24696,105235,55832,95729,64468,57078,84430,101249,69068,14170,63969,96779,49254,85806,73789,74029,604,118728,63138,18213,98613,94544,101615,108844,41894,15539,36430,41648,110579,16785,53472,33544,102221,56781,43354,69222,62922,98790,70250,15650,21510,43468,59402,105138,109294,95289,114552,6625,77897,31162,112465,93283,43253,7376,119714,3358,16431,7186,53409,17062,77060,90387,72479,3861,113316,97346,109368,13528,49636,35833,83512,45510,31151,14828,103530,3407,11951,1978,43464,72435,48504,84413,25687,97059,46820,117747,101542,116146,206,76909,17759,110042,38433,4742,51523,82801,14555,83437,52012,53558,66184,54163,64476,93243,57701,25016,44480,104059,12212,74491,78837,34298,72628
--===== Split8KTally ===========================================================================================
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 4 ms.
Table 'Product'. Scan count 5, logical reads 10, 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.
Table 'Worktable'. Scan count 4, logical reads 493292, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SalesOrderHeader'. Scan count 5, logical reads 775, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SalesOrderDetail'. Scan count 5, logical reads 682, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Tally'. Scan count 4, logical reads 48, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
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.
SQL Server Execution Times:
CPU time = 860908 ms, elapsed time = 246577 ms.
--===== Split8KXMLBrad ===========================================================================================
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
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.
Table 'SalesOrderHeader'. Scan count 5, logical reads 775, physical reads 0, read-ahead reads 2, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Product'. Scan count 5, logical reads 10, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
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 '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 2144, 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 = 623 ms, elapsed time = 575 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 'SalesOrderHeader'. Scan count 1, logical reads 706, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SalesOrderDetail'. Scan count 5, 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 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 '#5165187F'. 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 = 78 ms, elapsed time = 88 ms.
Edit: all three exec plans attached
-
Attached (zipped) actual execution plan when running Brad's code:
declare @CSVList varchar(8000)
select @CSVList=coalesce(@CSVList+',','')+csvparameter
from (select top 10 csvparameter from csvtest) x
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;
Paul
-
Anyone else a little concerned about the 2k8 results for the inline Tally?
Sorry, I would obviously try this myself but I don't have a 2k8 instance to hand, could someone try a multi-line tally version on 2k8?
Below is Jeff's original just with the ML Tally inserted
Don't worry Jeff, I didn't forget to attach the results of the query in the original post.
--===== 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
--===== 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
--===== Tally Table (Split8KTally mlTVF) ===============================================================================
IF OBJECT_ID('tempdb.dbo.Split8KTally_mlTVF') IS NOT NULL
DROP FUNCTION dbo.Split8KTally_mlTVF
GO
Create FUNCTION [dbo].[Split8KTally_mlTVF]
(@Parameter varchar(8000), @Delimiter varchar(5))
RETURNS @RtnValue table (Id int identity(1,1), [Value] varchar(8000))
AS BEGIN
SET @Parameter = @Delimiter + @Parameter + @Delimiter
DECLARE @SplitCharLength int;
SET @SplitCharLength = len(@Delimiter);
INSERT INTO @RtnValue (Value)
SELECT SUBSTRING(@Parameter, (N + @SplitCharLength), CHARINDEX(@Delimiter, @Parameter, N+1) - (N + @SplitCharLength))
FROM dbo.Tally
WHERE N < (LEN(@Parameter) - @SplitCharLength)
AND SUBSTRING(@Parameter, N, @SplitCharLength) = @Delimiter
RETURN
END
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 ;
PRINT '--===== Split8KTally mlTVF ==========================================================================================='
select @ProductID=d.ProductID
,@ProductName=p.Name
,@SalesOrderID=h.SalesOrderID
,@OrderDate=h.OrderDate
,@TerritoryName=t.Name
from dbo.Split8KTally_mlTVF(@CSVList,',') a
join AdventureWorks.Sales.SalesOrderDetail d on cast(a.Value 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
-
There you go (Same SS2k8 SP1 Quad-Core as previous post):
--===== Split8KTally mlTVF ===========================================================================================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 'SalesOrderHeader'. Scan count 1, logical reads 706, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SalesOrderDetail'. Scan count 6, logical reads 15, 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 2, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#619B8048'. 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.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 62 ms, elapsed time = 99 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
-
mazzz (9/20/2010)
I would like to help - is the latest code that Jeff would like us to run in the original post?My understanding is that Jeff welcomes runs from the code in the original post, and/or from the XML-vs-Tally script in this post.
-
Paul White NZ (9/20/2010)
mazzz (9/20/2010)
I would like to help - is the latest code that Jeff would like us to run in the original post?My understanding is that Jeff welcomes runs from the code in the original post, and/or from the XML-vs-Tally script in this post.
That's correct, Paul. Mazzz, if you have the time, I'd love to have returns from both. Thanks a bunch.
--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 (9/19/2010)
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.Only 1000 X 1000, you were lucky.;-) I ran it on 10,000 Row X 1,000 Element and let it go to completion :crazy: although I wouldn't recommend it. Full results available on request.
-
Jeff Moden (9/20/2010)
Paul White NZ (9/20/2010)
mazzz (9/20/2010)
I would like to help - is the latest code that Jeff would like us to run in the original post?My understanding is that Jeff welcomes runs from the code in the original post, and/or from the XML-vs-Tally script in this post.
That's correct, Paul. Mazzz, if you have the time, I'd love to have returns from both. Thanks a bunch.
Thanks Paul
Jeff - I'm on the case 🙂
-
ColdCoffee (9/19/2010)
Jeff, here are the other 3 environment's results (Note : This result set is not the results of the AdventureWorks JOIN code test rig ; this is of the first post of this thread)All of the environments are desktops with Env5 using SQL 2008 RTM) and Env3 & Env4 using SQL 2005 SP2..
Outstanding! And thanks for the notes on your envirionment!
--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) -
steve-893342 (9/20/2010)
Jeff Moden (9/19/2010)
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.Only 1000 X 1000, you were lucky.;-) I ran it on 10,000 Row X 1,000 Element and let it go to completion :crazy: although I wouldn't recommend it. Full results available on request.
Hell yeah! That would indeed be an interesting data point to have, Steve. Thanks. 🙂
--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) -
Mark-101232 (9/20/2010)
More results attached, this time on a server.Also SQLCLR figures are this
Tally:
Table 'Tally'. Scan count 10000, logical reads 30000, physical reads 0
Table 'CsvTest'. Scan count 1, logical reads 774, physical reads 0
CPU time = 4531 ms, elapsed time = 4523 ms.
SQLCLR:
Table 'CsvTest'. Scan count 1, logical reads 774, physical reads 0
CPU time = 2562 ms, elapsed time = 3452 ms.
Thanks Mark. And a special thanks for the SQLCLR 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)
Viewing 15 posts - 76 through 90 (of 214 total)
You must be logged in to reply to this topic. Login to reply