I need some help with a test, please.
-
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...
-
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
-
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 -
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
-
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
-
Ok, these are the results for the unmodified script. Total execution time 19 min 11 sec.
Paul
-
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 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
-
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 -
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) -
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) -
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) -
Mark-101232 (9/19/2010)
The difference on mine is far less pronouncedTally:
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
-
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) -
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 differenceI 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
Viewing 15 posts - 16 through 30 (of 214 total)
You must be logged in to reply to this topic. Login to reply