I need some help with a test, please.

Question

I need some help with a test, please.

Viewing 15 posts - 16 through 30 (of 214 total)

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

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 1

Jeff Moden (9/18/2010)
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.

My friend is doing OK, Jeff.. Thanks for your words 🙂

And i have attached the "Text Mode" Results .. I promise atleast 5 distinct environment's results tomorrow morning once i reach office..Please tel me if i have to provide any further information...

Paul White SSC Guru Points: 150467 More actions · Answer 2

Darn time zones. Jeff, the script has a comment in it to say that Profiler should be running:

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

-- Run the functions (Profiler turned on for this given SPID)

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

I'm going to assume that isn't in fact required, but if it is, can you provide a server-side trace definition so we're all running the same thing? As you know, traces can have a huge impact on scalar and multi-statement TVFs (e.g. dbo.Split8KXML1) so that would tend to favour the in-line TVFs unfairly.

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 3

Hi Jeff

Happy to help.

Desktop

SQL Server 2008 R2 Dev ed. 10.50.1600.1

OS NameMicrosoft® Windows Vista Business

Version6.0.6002 Service Pack 2 Build 6002

System Manufacturer Dell Inc.

System ModelPrecision WorkStation 390

System Typex64-based PC

ProcessorIntel(R) Core(TM)2 CPU 6300 @ 1.86GHz, 1862 Mhz, 2 Core(s), 2 Logical Processor(s)

Installed Physical Memory (RAM)2.00 GB.

Good luck

Regards Graham

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 4

Hi Jeff,

Results attached for my creaky Fujitsu Amilo laptop running Vista Home Premium, 2GB RAM, Intel Core 2 Duo

with SQL Server 2008 Express R2.

Query took 27 mins 49 secs to complete.

Cheers

Mark

____________________________________________________

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

Paul White SSC Guru Points: 150467 More actions · Answer 5

My results attached. Total run time 5 min 04 sec.

(Pentium 4m Processor @2GHz - SQL Server 2008 x86 Dev)

Sorry I've done this slightly backwards: this run includes one of Brad's optimizations missing from the original rig:

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).query('.')) X(N)

CROSS APPLY N.nodes('//r') R(Item)

;

The difference is the ".query('.')" after the CAST...AS XML.

I'll post the results from the original rig in a bit.

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Paul White SSC Guru Points: 150467 More actions · Answer 6

Ok, these are the results for the unmodified script. Total execution time 19 min 11 sec.

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Paul White SSC Guru Points: 150467 More actions · Answer 7

For anyone wanting to see how a SQLCLR implementation compares to the other tested methods on this dataset, here's my slightly-tweaked version of Adam Machanic's string splitter: (source code attached)

CREATE ASSEMBLY Utility

AUTHORIZATION dbo

FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C010300B3C57F4C0000000000000000E00002210B010800000E00000006000000000000FE2C0000002000000040000000004000002000000002000004000000000000000400000000000000008000000002000000000000030040850000100000100000000010000010000000000000100000000000000000000000A82C000053000000004000000803000000000000000000000000000000000000006000000C000000082C00001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000040D000000200000000E000000020000000000000000000000000000200000602E7273726300000008030000004000000004000000100000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000001400000000000000000000000000004000004200000000000000000000000000000000E02C000000000000480000000200050054220000B40900000100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000D2026F1300000A2D1A036F1300000A2D12026F1400000A036F1400000A73050000062A168D1A000001168D1A00000173050000062A000000133003002C0000000100001102A5030000020A0312007B01000004731500000A81050000010412007B02000004731600000A81060000012A1E02281700000A2A3E02037D0100000402047D020000042A03300300530000000000000002281700000A02037D0600000402038E697D0800000402047D0700000402048E69D27D0900000402027B0900000417FE017D0A00000402167D0500000402167D0300000402027B09000004155A7D040000042ABE027B05000004027B06000004027B03000004027B04000004027B0300000459731900000A73040000068C030000022A0013300300C90000000200001102257B0500000417587D05000004027B04000004027B080000043202162A02027B04000004027B09000004587D03000004027B030000040A2B65170B027B0A0000042C16027B060000040693027B0700000416932E39160B2B35160C2B28060858027B080000042F14027B0600000406085893027B0700000408932E04160B2B0E081758D20C08027B0900000432CF072C0902067D04000004172A0617580A06027B08000004329202027B04000004027B09000004587D0300000402027B080000047D04000004172A5A02167D0300000402027B09000004155A7D040000042A42534A4201000100000000000C00000076322E302E35303732370000000005006C00000080030000237E0000EC0300002804000023537472696E6773000000001408000008000000235553001C0800001000000023475549440000002C0800008801000023426C6F6200000000000000020000015717A2010902000000FA253300160000010000001D000000040000000A00000008000000090000000100000019000000100000000200000001000000010000000100000001000000020000000200000000000A0001000000000006005D005600060064005600060081006E000A00AE0099000A00C90099000A00D20099000A008F0174010600CE01AF0106000502F30106001C02F30106003902F30106005802F30106007102F30106008A02F3010600A502F3010600C002F3010600D902AF010600ED02AF010600FB02F30106001403F3010600440331035700580300000600870367030600A70367030A00D10374010600FB03560006000004AF0106001604AF010600210456000000000001000000000001000100010010001A0000000500010001000B0110002F000000090001000400020010003C0000000500030005000600F00022000600F90025000100190122000100210122000100F0002200210029013E00210033013E0021003D0122002100470142002100540145005020000000009600B7000A0001008820000000009600DC0013000300C020000000008618EA001E000600C820000000008618EA0028000600D820000000008618EA002E000800372100000000E609FE0036000A00682100000000E6010A013A000A003D2200000000E60113011E000A00000001006E0100000200A10100000100AB0102000200F00002000300F90000000100DB0100000200E40100000100E90100000200A10104000D003900EA001E004100EA001E004900EA0072005100EA0072005900EA0072006100EA0072006900EA0072007100EA0072007900EA0072008100EA0072008900EA0077009100EA0072009900EA007200A100EA007200A900EA007C00B900EA008200C100EA001E00C900EA001E002100E6033A002100F103D4002900EA0082003100EA0072000900EA001E00D900EA00DE00E900EA00E40020009300870024000B004C002E003B00F2002E001B00F2002E002300FF002E002B00FF002E003300FF002E0063001D012E008B0066012E004B00FF002E005B00FF002E00430005012E00730047012E007B0054012E0083005D0144000B005F00D900EC0004000100000066014800020006000300048000000100000000000000000000000000C503000002000000000000000000000001004D000000000002000000000000000000000001008D000000000003000200040002000000003C4D6F64756C653E00537472696E675574696C732E646C6C0055736572446566696E656446756E6374696F6E73004F75747075745265636F72640053706C6974537472696E674D756C7469006D73636F726C69620053797374656D004F626A6563740056616C7565547970650053797374656D2E436F6C6C656374696F6E730049456E756D657261746F720053797374656D2E446174610053797374656D2E446174612E53716C54797065730053716C43686172730053706C6974537472696E675F4D756C74690053716C496E7433320053716C537472696E670046696C6C526F775F4D756C7469002E63746F720073657175656E6365006974656D006765745F43757272656E74004D6F76654E657874005265736574006C617374506F73006E657874506F7300746865537472696E670064656C696D6974657200737472696E674C656E0064656C696D697465724C656E00697353696E676C654368617244656C696D0043757272656E7400496E707574004D6963726F736F66742E53716C5365727665722E5365727665720053716C46616365744174747269627574650044656C696D69746572006F626A0053797374656D2E52756E74696D652E496E7465726F705365727669636573004F75744174747269627574650053657175656E6365004974656D00546865537472696E670053797374656D2E5265666C656374696F6E00417373656D626C795469746C6541747472696275746500417373656D626C794465736372697074696F6E41747472696275746500417373656D626C79436F6E66696775726174696F6E41747472696275746500417373656D626C79436F6D70616E7941747472696275746500417373656D626C7950726F6475637441747472696275746500417373656D626C79436F7079726967687441747472696275746500417373656D626C7954726164656D61726B41747472696275746500417373656D626C7943756C7475726541747472696275746500436F6D56697369626C65417474726962757465004775696441747472696275746500417373656D626C7956657273696F6E41747472696275746500417373656D626C7946696C6556657273696F6E4174747269627574650053797374656D2E446961676E6F73746963730044656275676761626C6541747472696275746500446562756767696E674D6F6465730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C69747941747472696275746500537472696E675574696C730053716C46756E6374696F6E417474726962757465006765745F49734E756C6C006765745F56616C75650043686172005374727563744C61796F7574417474726962757465004C61796F75744B696E6400537472696E67000003200000000000C84BC32985DEE5489E1FE2D7A168EC610008B77A5C561934E089080002120D121112110A0003011C1011151011190320000102060802060E05200201080E072002011D031D030320001C0320000203061D030206050206020328001C12010001005408074D617853697A65FFFFFFFF12010001005408074D617853697A65FF000000042001010E042001010205200101115904200101084C01000200540E1146696C6C526F774D6574686F644E616D650D46696C6C526F775F4D756C7469540E0F5461626C65446566696E6974696F6E136974656D206E766172636861722834303030290420001D03040701110C052001011171072003011D0308080507030802050C0100075574696C697479000005010000000017010012436F7079726967687420C2A920203230313000002901002436616266323165302D393063382D343865392D626165362D36666636363966336562653200000C010007312E302E302E3000000801000200000000000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F77730100000000000000B3C57F4C000000000200000084000000242C0000240E00005253445326D0FEDE5ECCC3408D78EA0B0EDDDEE90E000000433A5C446F63756D656E747320616E642053657474696E67735C5061756C5C4D7920446F63756D656E74735C56697375616C2053747564696F20323031305C50726F6A656374735C5574696C6974795C6F626A5C52656C656173655C537472696E675574696C732E70646200D02C00000000000000000000EE2C0000002000000000000000000000000000000000000000000000E02C000000000000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF250020400000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000100100000001800008000000000000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058400000B00200000000000000000000B00234000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000001000000000000000100000000003F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B00410020000010053007400720069006E006700460069006C00650049006E0066006F000000EC0100000100300030003000300030003400620030000000380008000100460069006C0065004400650073006300720069007000740069006F006E00000000005500740069006C006900740079000000300008000100460069006C006500560065007200730069006F006E000000000031002E0030002E0030002E003000000040001000010049006E007400650072006E0061006C004E0061006D006500000053007400720069006E0067005500740069006C0073002E0064006C006C0000004800120001004C006500670061006C0043006F007000790072006900670068007400000043006F0070007900720069006700680074002000A90020002000320030003100300000004800100001004F0072006900670069006E0061006C00460069006C0065006E0061006D006500000053007400720069006E0067005500740069006C0073002E0064006C006C000000300008000100500072006F0064007500630074004E0061006D006500000000005500740069006C006900740079000000340008000100500072006F006400750063007400560065007200730069006F006E00000031002E0030002E0030002E003000000038000800010041007300730065006D0062006C0079002000560065007200730069006F006E00000031002E0030002E0030002E00300000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000C000000003D00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000

WITH PERMISSION_SET = SAFE;

GO

CREATE FUNCTION dbo.SplitString_Multi

(

@Input NVARCHAR(MAX),

@Delimiter NVARCHAR(255)

)

RETURNS TABLE

(

sequence INTEGER NULL,

item NVARCHAR(4000) NULL

)

WITH EXECUTE AS CALLER

AS

EXTERNAL NAME

Utility.UserDefinedFunctions.SplitString_Multi;

GO

This function handles input strings up to 2GB and multi-character delimiters (notice also that the test dataset is not Unicode, so this function has to convert to and from Unicode on every row.) The test script is:

DECLARE @RowNum INTEGER,

@ItemNumber INTEGER,

@ItemValue INTEGER;

SET STATISTICS IO, TIME ON;

SELECT @RowNum = CSV.RowNum,

@ItemNumber = iTVF.sequence,

@ItemValue = iTVF.item

FROM dbo.CsvTest CSV

CROSS

APPLY dbo.SplitString_Multi(CSV.CsvParameter, N',') iTVF

SET STATISTICS IO, TIME OFF;

On my machine, these are the results:

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 = 9578 ms, elapsed time = 10013 ms.

SQLCLR:

Table 'CsvTest'. Scan count 1, logical reads 774, physical reads 0

CPU time = 1953 ms, elapsed time = 2039 ms.

That's a factor of five :smooooth:

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

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

Paul White NZ (9/19/2010)
On my machine, these are the results:
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 = 9578 ms, elapsed time = 10013 ms.
SQLCLR:
Table 'CsvTest'. Scan count 1, logical reads 774, physical reads 0
CPU time = 1953 ms, elapsed time = 2039 ms.
That's a factor of five :smooooth:
Paul

Yes, but if you compare the two to shift data either with a SELECT INTO or INSERT query you won't see that sort of difference

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 9

Paul White NZ (9/19/2010)
For anyone wanting to see how a SQLCLR implementation compares to the other tested methods on this dataset, here's my slightly-tweaked version of Adam Machanic's string splitter: (source code attached)
CREATE ASSEMBLY Utility
AUTHORIZATION dbo
FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C010300B3C57F4C0000000000000000E00002210B010800000E00000006000000000000FE2C0000002000000040000000004000002000000002000004000000000000000400000000000000008000000002000000000000030040850000100000100000000010000010000000000000100000000000000000000000A82C000053000000004000000803000000000000000000000000000000000000006000000C000000082C00001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000040D000000200000000E000000020000000000000000000000000000200000602E7273726300000008030000004000000004000000100000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000001400000000000000000000000000004000004200000000000000000000000000000000E02C000000000000480000000200050054220000B40900000100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000D2026F1300000A2D1A036F1300000A2D12026F1400000A036F1400000A73050000062A168D1A000001168D1A00000173050000062A000000133003002C0000000100001102A5030000020A0312007B01000004731500000A81050000010412007B02000004731600000A81060000012A1E02281700000A2A3E02037D0100000402047D020000042A03300300530000000000000002281700000A02037D0600000402038E697D0800000402047D0700000402048E69D27D0900000402027B0900000417FE017D0A00000402167D0500000402167D0300000402027B09000004155A7D040000042ABE027B05000004027B06000004027B03000004027B04000004027B0300000459731900000A73040000068C030000022A0013300300C90000000200001102257B0500000417587D05000004027B04000004027B080000043202162A02027B04000004027B09000004587D03000004027B030000040A2B65170B027B0A0000042C16027B060000040693027B0700000416932E39160B2B35160C2B28060858027B080000042F14027B0600000406085893027B0700000408932E04160B2B0E081758D20C08027B0900000432CF072C0902067D04000004172A0617580A06027B08000004329202027B04000004027B09000004587D0300000402027B080000047D04000004172A5A02167D0300000402027B09000004155A7D040000042A42534A4201000100000000000C00000076322E302E35303732370000000005006C00000080030000237E0000EC0300002804000023537472696E6773000000001408000008000000235553001C0800001000000023475549440000002C0800008801000023426C6F6200000000000000020000015717A2010902000000FA253300160000010000001D000000040000000A00000008000000090000000100000019000000100000000200000001000000010000000100000001000000020000000200000000000A0001000000000006005D005600060064005600060081006E000A00AE0099000A00C90099000A00D20099000A008F0174010600CE01AF0106000502F30106001C02F30106003902F30106005802F30106007102F30106008A02F3010600A502F3010600C002F3010600D902AF010600ED02AF010600FB02F30106001403F3010600440331035700580300000600870367030600A70367030A00D10374010600FB03560006000004AF0106001604AF010600210456000000000001000000000001000100010010001A0000000500010001000B0110002F000000090001000400020010003C0000000500030005000600F00022000600F90025000100190122000100210122000100F0002200210029013E00210033013E0021003D0122002100470142002100540145005020000000009600B7000A0001008820000000009600DC0013000300C020000000008618EA001E000600C820000000008618EA0028000600D820000000008618EA002E000800372100000000E609FE0036000A00682100000000E6010A013A000A003D2200000000E60113011E000A00000001006E0100000200A10100000100AB0102000200F00002000300F90000000100DB0100000200E40100000100E90100000200A10104000D003900EA001E004100EA001E004900EA0072005100EA0072005900EA0072006100EA0072006900EA0072007100EA0072007900EA0072008100EA0072008900EA0077009100EA0072009900EA007200A100EA007200A900EA007C00B900EA008200C100EA001E00C900EA001E002100E6033A002100F103D4002900EA0082003100EA0072000900EA001E00D900EA00DE00E900EA00E40020009300870024000B004C002E003B00F2002E001B00F2002E002300FF002E002B00FF002E003300FF002E0063001D012E008B0066012E004B00FF002E005B00FF002E00430005012E00730047012E007B0054012E0083005D0144000B005F00D900EC0004000100000066014800020006000300048000000100000000000000000000000000C503000002000000000000000000000001004D000000000002000000000000000000000001008D000000000003000200040002000000003C4D6F64756C653E00537472696E675574696C732E646C6C0055736572446566696E656446756E6374696F6E73004F75747075745265636F72640053706C6974537472696E674D756C7469006D73636F726C69620053797374656D004F626A6563740056616C7565547970650053797374656D2E436F6C6C656374696F6E730049456E756D657261746F720053797374656D2E446174610053797374656D2E446174612E53716C54797065730053716C43686172730053706C6974537472696E675F4D756C74690053716C496E7433320053716C537472696E670046696C6C526F775F4D756C7469002E63746F720073657175656E6365006974656D006765745F43757272656E74004D6F76654E657874005265736574006C617374506F73006E657874506F7300746865537472696E670064656C696D6974657200737472696E674C656E0064656C696D697465724C656E00697353696E676C654368617244656C696D0043757272656E7400496E707574004D6963726F736F66742E53716C5365727665722E5365727665720053716C46616365744174747269627574650044656C696D69746572006F626A0053797374656D2E52756E74696D652E496E7465726F705365727669636573004F75744174747269627574650053657175656E6365004974656D00546865537472696E670053797374656D2E5265666C656374696F6E00417373656D626C795469746C6541747472696275746500417373656D626C794465736372697074696F6E41747472696275746500417373656D626C79436F6E66696775726174696F6E41747472696275746500417373656D626C79436F6D70616E7941747472696275746500417373656D626C7950726F6475637441747472696275746500417373656D626C79436F7079726967687441747472696275746500417373656D626C7954726164656D61726B41747472696275746500417373656D626C7943756C7475726541747472696275746500436F6D56697369626C65417474726962757465004775696441747472696275746500417373656D626C7956657273696F6E41747472696275746500417373656D626C7946696C6556657273696F6E4174747269627574650053797374656D2E446961676E6F73746963730044656275676761626C6541747472696275746500446562756767696E674D6F6465730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C69747941747472696275746500537472696E675574696C730053716C46756E6374696F6E417474726962757465006765745F49734E756C6C006765745F56616C75650043686172005374727563744C61796F7574417474726962757465004C61796F75744B696E6400537472696E67000003200000000000C84BC32985DEE5489E1FE2D7A168EC610008B77A5C561934E089080002120D121112110A0003011C1011151011190320000102060802060E05200201080E072002011D031D030320001C0320000203061D030206050206020328001C12010001005408074D617853697A65FFFFFFFF12010001005408074D617853697A65FF000000042001010E042001010205200101115904200101084C01000200540E1146696C6C526F774D6574686F644E616D650D46696C6C526F775F4D756C7469540E0F5461626C65446566696E6974696F6E136974656D206E766172636861722834303030290420001D03040701110C052001011171072003011D0308080507030802050C0100075574696C697479000005010000000017010012436F7079726967687420C2A920203230313000002901002436616266323165302D393063382D343865392D626165362D36666636363966336562653200000C010007312E302E302E3000000801000200000000000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F77730100000000000000B3C57F4C000000000200000084000000242C0000240E00005253445326D0FEDE5ECCC3408D78EA0B0EDDDEE90E000000433A5C446F63756D656E747320616E642053657474696E67735C5061756C5C4D7920446F63756D656E74735C56697375616C2053747564696F20323031305C50726F6A656374735C5574696C6974795C6F626A5C52656C656173655C537472696E675574696C732E70646200D02C00000000000000000000EE2C0000002000000000000000000000000000000000000000000000E02C000000000000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF250020400000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000100100000001800008000000000000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058400000B00200000000000000000000B00234000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000001000000000000000100000000003F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B00410020000010053007400720069006E006700460069006C00650049006E0066006F000000EC0100000100300030003000300030003400620030000000380008000100460069006C0065004400650073006300720069007000740069006F006E00000000005500740069006C006900740079000000300008000100460069006C006500560065007200730069006F006E000000000031002E0030002E0030002E003000000040001000010049006E007400650072006E0061006C004E0061006D006500000053007400720069006E0067005500740069006C0073002E0064006C006C0000004800120001004C006500670061006C0043006F007000790072006900670068007400000043006F0070007900720069006700680074002000A90020002000320030003100300000004800100001004F0072006900670069006E0061006C00460069006C0065006E0061006D006500000053007400720069006E0067005500740069006C0073002E0064006C006C000000300008000100500072006F0064007500630074004E0061006D006500000000005500740069006C006900740079000000340008000100500072006F006400750063007400560065007200730069006F006E00000031002E0030002E0030002E003000000038000800010041007300730065006D0062006C0079002000560065007200730069006F006E00000031002E0030002E0030002E00300000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000C000000003D00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
WITH PERMISSION_SET = SAFE;
GO
CREATE FUNCTION dbo.SplitString_Multi
(
@Input NVARCHAR(MAX),
@Delimiter NVARCHAR(255)
)
RETURNS TABLE
(
sequence INTEGER NULL,
item NVARCHAR(4000) NULL
)
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME
Utility.UserDefinedFunctions.SplitString_Multi;
GO
This function handles input strings up to 2GB and multi-character delimiters (notice also that the test dataset is not Unicode, so this function has to convert to and from Unicode on every row.) The test script is:
DECLARE @RowNum INTEGER,
@ItemNumber INTEGER,
@ItemValue INTEGER;
SET STATISTICS IO, TIME ON;
SELECT @RowNum = CSV.RowNum,
@ItemNumber = iTVF.sequence,
@ItemValue = iTVF.item
FROM dbo.CsvTest CSV
CROSS
APPLY dbo.SplitString_Multi(CSV.CsvParameter, N',') iTVF
SET STATISTICS IO, TIME OFF;
On my machine, these are the results:
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 = 9578 ms, elapsed time = 10013 ms.
SQLCLR:
Table 'CsvTest'. Scan count 1, logical reads 774, physical reads 0
CPU time = 1953 ms, elapsed time = 2039 ms.
That's a factor of five :smooooth:
Paul

The difference on mine is far less pronounced

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 = 7719 ms, elapsed time = 7791 ms.

SQLCLR:

Table 'CsvTest'. Scan count 1, logical reads 774, physical reads 0

CPU time = 5610 ms, elapsed time = 5815 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 SSC Guru Points: 1004318 More actions · Answer 10

Paul White NZ (9/19/2010)
My results attached. Total run time 5 min 04 sec.
(Pentium 4m Processor @2GHz - SQL Server 2008 x86 Dev)
Sorry I've done this slightly backwards: this run includes one of Brad's optimizations missing from the original rig:
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).query('.')) X(N)
CROSS APPLY N.nodes('//r') R(Item)
;
The difference is the ".query('.')" after the CAST...AS XML.
I'll post the results from the original rig in a bit.
Paul

Thanks Paul... Yes, I found Brad's and I'll add it to the test rig. Thanks for posting your 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: 1004318 More actions · Answer 11

Paul White NZ (9/19/2010)
For anyone wanting to see how a SQLCLR implementation compares to the other tested methods on this dataset, here's my slightly-tweaked version of Adam Machanic's string splitter: (source code attached)

Heh... alright... guess it's finally time for me to give that bad boy a try. Thanks, Paul. 😉

--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: 1004318 More actions · Answer 12

Paul White NZ (9/19/2010)
Darn time zones. Jeff, the script has a comment in it to say that Profiler should be running:
--=====================================================================================================================
-- Run the functions (Profiler turned on for this given SPID)
--=====================================================================================================================
I'm going to assume that isn't in fact required, but if it is, can you provide a server-side trace definition so we're all running the same thing? As you know, traces can have a huge impact on scalar and multi-statement TVFs (e.g. dbo.Split8KXML1) so that would tend to favour the in-line TVFs unfairly.
Paul

Nah... my apologies, Paul. I wouldn't make anyone read the whole script to figure out what they need to do to do ME a favor. That's an artifact from my previous test code and I'll remove it so it doesn't confuse anyone. Thanks for reading the code, though! I love a good peer review.

Also, I'm going to add two of Brad's... the one you posted will be commented as "XML-Brad1 (Split8KXMLBrad1 iTVF)". The other one is what I believe Brad meant to be the fastest and will be commented as XML-Brad (Split8KXMLBrad iTVF). Almost done with that. Just running a sanity check before I update the code.

And thanks for jumping in on this thread. I always appreciate your comments and your code.

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

Paul White SSC Guru Points: 150467 More actions · Answer 13

Mark-101232 (9/19/2010)
The difference on mine is far less pronounced
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 = 7719 ms, elapsed time = 7791 ms.
SQLCLR:
Table 'CsvTest'. Scan count 1, logical reads 774, physical reads 0
CPU time = 5610 ms, elapsed time = 5815 ms.

1. The logical reads difference seems about the same 😛

2. CLR uses JIT (just-in-time) compilation, so the first few times you (ever) use the SQLCLR function it won't have been fully compiled to native machine code. Run the test several times to ensure the code is fully optimized.

3. If running on a laptop, ensure that your CPU is running at full clock speed (not on battery/ check power plan settings etc.) If in doubt, run something like CPU-Z to check.

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

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

Alright... both of Brad's good XML examples have been added. I left the others in simply because they were there before and I'll continue to collect data on those points. 🙂

Thanks again for everyone who participates/participated in this test. I don't know if you have the gumption to do so but for those folks that already ran it, it would be an additional huge help to me if you reran the code and posted the results now that we have Brad's more performant XML functions in the code.

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

Paul White SSC Guru Points: 150467 More actions · Answer 15

steve-893342 (9/19/2010)
Yes, but if you compare the two to shift data either with a SELECT INTO or INSERT query you won't see that sort of difference

I think I see what you mean, and the answer is very much "It Depends" 😉

For example, let's use a bcp export to a file (this removes a lot of variable factors):

Tally:

bcp "SELECT csv.RowNum, split.ItemNumber, item = CONVERT(INTEGER, split.ItemValue) FROM tempdb.dbo.CsvTest csv CROSS APPLY tempdb.dbo.Split8KTally(csv.CsvParameter,',') AS split" queryout tally.bcp -n -S .\SQL2008 -T

Results:

1000000 rows copied.

Clock Time (ms.) Total : 11406 Average : (87673.15 rows per sec.)

SQLCLR:

bcp "SELECT CSV.RowNum, iTVF.sequence, item = CONVERT(INTEGER, iTVF.item) FROM tempdb.dbo.CsvTest CSV CROSS APPLY tempdb.dbo.SplitString_Multi(CSV.CsvParameter, N',') iTVF" queryout sqlclr.bcp -n -S .\SQL2008 -T

Results:

1000000 rows copied.

Clock Time (ms.) Total : 3610 Average : (277008.31 rows per sec.)

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi