September 9, 2009 at 9:38 am
Jack Corbett (9/9/2009)
Ah but what if I want a soft boiled egg? :w00t:
I assume if you take the egg out right when the water hits boiling you'll be pretty close... The egg should be relatively cooked even at that point, having heated along with the water. Maybe one or two minutes in the hot water instead of 10, if you like it slightly more set.
--
Adam Machanic
whoisactive
September 9, 2009 at 9:44 am
...o.O
I can't have been away more than ten minutes...:w00t:
Thanks for the egg recipes!
September 9, 2009 at 12:29 pm
Tune in same time next week for another episode of "Cooking with SQL."
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
September 9, 2009 at 1:06 pm
Eggsactly...
--Jeff Moden
Change is inevitable... Change for the better is not.
September 9, 2009 at 1:21 pm
That was an eggseptionally good pun, Jeff.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
September 24, 2011 at 8:52 pm
Just in case anyone is still following this thread and may have missed the article... the performance problem wasn't with the Tally Table itself. It was with the concatenation of delimiters.
Please see the article at the following link...
http://www.sqlservercentral.com/articles/Tally+Table/72993/
--Jeff Moden
Change is inevitable... Change for the better is not.
September 25, 2011 at 6:45 am
😀
Thanks Jeff. I've been following this thread for years. It's where I keep my egg recipe.
September 25, 2011 at 8:47 am
Now THAT's funny! 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
October 18, 2011 at 10:21 pm
So...I'm liking the DelimitedSplit8K, but I need to increase it to an unknown number between 25K and 150K rows. Any way to modify this so I can go that high without running into a performance wall?
November 24, 2011 at 8:22 am
John Nelson-327605 (10/18/2011)
So...I'm liking the DelimitedSplit8K, but I need to increase it to an unknown number between 25K and 150K rows. Any way to modify this so I can go that high without running into a performance wall?
My apologies... this got lost in all of the emails I get.
The DelimitedSplit8k function can be easily used with a CROSS APPLY as demonstrated in the "Tally OH" article previously cited to handle virtually any number of rows that need up to VARCHAR(8000) columns to be split. Of course, the CLR will outperform the DelimitedSplit8k function by about 2:1.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 4, 2012 at 4:04 pm
Alright kids, it's time for another round of fun...
Last week I started playing with a streaming CLR solution, hoping to come up with something that would be a bit more memory efficient. I was able to do just that, but the bad news is that better memory efficiency -- at least for my current code -- means somewhat worse performance. Not a whole lot worse, mind you, maybe 10-15%, but still, worse.
Interestingly, I ran some tests on a machine that has both SQL Server 2008 R2 and SQL Server 2012 installed, and all of my test queries (for either version) run around 50% faster in 2012. Kind of an interesting bonus there, and I have no idea what changed. But no complaints.
The code that follows is my crack at a more memory efficient solution. It works only for a single delimiter (I never did see the point of multi-character delimiters, to be honest), and also does a few other things that some people will hate, such as trimming the output and not returning empty results. I also bolted on a row number, which a number of people have requested for the last SQLCLR version that was posted.
What's more memory efficient about it? Instead of reading the whole string in at once it uses the .Read method on the SqlChars instance to read 7500 characters at a time (that number is configurable via a constant set at the top). I played around with other numbers but was unable to come up with much in terms of conclusive evidence that the choice makes a difference. Really low numbers -- like 10 -- and really high numbers -- like 75000 -- definitely impact the return times, but aside from that it doesn't seem to matter.
I am measuring memory utilization primarily using the handy total_allocated_memory_kb column that's been added to sys.dm_clr_appdomains in SQL Server 2012, and I can say that this code does use less memory overall, but not much less. What I haven't been able to measure yet is how much PEAK memory is being used. That will require perf counters or something else and to be honest I've been too lazy to bother quite yet 🙂
Anyway, here's the code. Anyone feel like optimizing, testing, or otherwise kicking the tires a bit?
using System;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class UserDefinedFunctions
{
//It's unclear what the penalty for setting this too high or too low is
//for the moment I'm setting it to a point where it will help to avoid LOB heap allocations
const int SUBSTRING_SIZE = 7500;
[Microsoft.SqlServer.Server.SqlFunction(
FillRowMethodName = "SplitString_Single_Fill",
TableDefinition = "OutParam nvarchar(4000), Row INT",
Name = "split_string_with_row")]
public static IEnumerator SplitString_Single(
[SqlFacet(MaxSize = -1)]
SqlChars Input,
[SqlFacet(MaxSize = 1)]
SqlChars Delimiter)
{
return (
(Input.IsNull || Delimiter.IsNull) ?
new stringSplit(new SqlChars(new char[0]), new char[0]) :
new stringSplit(Input, Delimiter.Value));
}
public static void SplitString_Single_Fill(object obj, out SqlString item, out SqlInt32 row)
{
var r = (stringSplit.results)obj;
item = r.OutputString;
row = r.RowNumber;
}
public class stringSplit : IEnumerator
{
public stringSplit(SqlChars TheString, char[] Delimiter)
{
theString = TheString;
delimiter = (Delimiter.Length == 0 ? ' ' : Delimiter[0]);
segment = new char[SUBSTRING_SIZE];
theResult = new results(this);
this.Reset();
}
#region IEnumerator Members
public object Current
{
get
{
return this.theResult;
}
}
public bool MoveNext()
{
result = String.Empty;
bool foundNext = false;
while (true)
{
while (nextPos < segmentLen)
{
if (segment[nextPos] == delimiter)
{
//ignore consecutive delimiters
if (nextPos > lastPos)
{
if (result.Length > 0)
result = String.Concat(result, new string(segment, lastPos, nextPos - lastPos)).Trim();
else
result = new string(segment, lastPos, nextPos - lastPos).Trim();
}
if (result.Length > 0)
foundNext = true;
lastPos = nextPos + 1;
}
nextPos++;
if (foundNext)
break;
}
if (foundNext)
break;
else if (nextPos == segmentLen)
{
//do not trim here -- we don't know if spaces here are trailing or if there's more content in the next segment
if (result.Length > 0)
result = String.Concat(result, new string(segment, lastPos, nextPos - lastPos));
else
result = new string(segment, lastPos, nextPos - lastPos);
nextPos = 0;
lastPos = 0;
//Is there anything left to read?
if (0 == (segmentLen = (int)theString.Read(nextSegment, segment, 0, SUBSTRING_SIZE)))
{
//final trim to remove trailing spaces at the end
result = result.Trim();
if (result.Length > 0)
foundNext = true;
break;
}
else
nextSegment += segmentLen;
}
}
rowNumber++;
return (foundNext);
}
public void Reset()
{
lastPos = SUBSTRING_SIZE;
nextPos = SUBSTRING_SIZE;
segmentLen = SUBSTRING_SIZE;
nextSegment = 0;
result = String.Empty;
}
#endregion
private int lastPos;
private int nextPos;
private int segmentLen;
private int nextSegment;
private int rowNumber = 0;
private string result;
private readonly SqlChars theString;
private readonly char delimiter;
private readonly char[] segment;
private readonly results theResult;
public class results
{
public results(stringSplit ss)
{
this.ss = ss;
}
private readonly stringSplit ss;
public int RowNumber
{
get
{
return (ss.rowNumber);
}
}
public string OutputString
{
get
{
return (ss.result);
}
}
}
}
};
--
Adam Machanic
whoisactive
September 4, 2012 at 6:07 pm
Adam Machanic (9/4/2012)
Interestingly, I ran some tests on a machine that has both SQL Server 2008 R2 and SQL Server 2012 installed, and all of my test queries (for either version) run around 50% faster in 2012.
I have found the same thing with streaming SQLCLR functions - another good reason to upgrade I guess. I have no idea why Microsoft don't make more of all the small improvements they work into every new major release.
The code that follows is my crack at a more memory efficient solution. It works only for a single delimiter (I never did see the point of multi-character delimiters, to be honest), and also does a few other things that some people will hate, such as trimming the output and not returning empty results. I also bolted on a row number, which a number of people have requested for the last SQLCLR version that was posted.
Not returning empty results is the only one that strikes me as odd.
Anyway, here's the code. Anyone feel like optimizing, testing, or otherwise kicking the tires a bit?
Some people will appreciate the bits as well as the source, so here's my release build targeting .NET 2.0 and tested on 2005, 2008, 2008 R2, and 2012:
CREATE ASSEMBLY SplitStringWithRow
FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C010300FA9646500000000000000000E00002210B010800000E00000006000000000000CE2D0000002000000040000000004000002000000002000004000000000000000400000000000000008000000002000000000000030040850000100000100000000010000010000000000000100000000000000000000000782D00005300000000400000D003000000000000000000000000000000000000006000000C00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000D40D000000200000000E000000020000000000000000000000000000200000602E72737263000000D0030000004000000004000000100000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000001400000000000000000000000000004000004200000000000000000000000000000000B02D00000000000048000000020005006C2300000C0A00000100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000D2026F1000000A2D15036F1000000A2D0D02036F1100000A73040000062A168D15000001731200000A168D1500000173040000062A000000133002002A000000010000110274040000020A03066F0A000006281300000A810400000104066F09000006281400000A81050000012A1E02281500000A2A000003300300420000000000000002281500000A02037D0800000402048E692C050416932B021F207D0900000402204C1D00008D150000017D0A000004020273080000067D0B0000040228070000062A1E027B0B0000042A000013300600DF01000002000011027E1600000A7D07000004160A38C2000000027B0A000004027B0300000493027B090000044099000000027B03000004027B02000004316D027B070000046F1700000A16313602027B07000004027B0A000004027B02000004027B03000004027B0200000459731800000A281900000A6F1A00000A7D070000042B2902027B0A000004027B02000004027B03000004027B0200000459731800000A281A00000A7D07000004027B070000046F1700000A163102170A02027B0300000417587D0200000402257B0300000417587D03000004062D11027B03000004027B040000043F2DFFFFFF063AE4000000027B03000004027B0400000433DB027B070000046F1700000A16313102027B07000004027B0A000004027B02000004027B03000004027B0200000459731800000A281900000A7D070000042B2402027B0A000004027B02000004027B03000004027B0200000459731800000A7D0700000402167D0300000402167D0200000402027B08000004027B050000046A027B0A00000416204C1D00006F1B00000A69250B7D04000004072D2302027B070000046F1A00000A7D07000004027B070000046F1700000A16311C170A2B1802257B05000004027B04000004587D050000043805FFFFFF02257B0600000417587D06000004062AD202204C1D00007D0200000402204C1D00007D0300000402204C1D00007D0400000402167D05000004027E1600000A7D070000042A3A02281500000A02037D0C0000042A32027B0C0000047B060000042A32027B0C0000047B070000042A00000042534A4201000100000000000C00000076322E302E35303732370000000005006C00000098030000237E0000040400002004000023537472696E6773000000002408000008000000235553002C0800001000000023475549440000003C080000D001000023426C6F620000000000000002000001571FA2010902000000FA2533001600000100000016000000040000000C0000000A00000008000000010000001B000000010000000D0000000200000002000000030000000300000001000000020000000200000000000A0001000000000006005A0053000600740061000A00B0009B000A00CC009B000A00D6009B000A00D801BD0106001C02FD0106004902370206006002370206007D02370206009C0237020600B50237020600CE0237020600E902370206000403370206001D03FD0106003103370206006A034A0306008A034A030A00BB03BD010600E50353000600F6035300000000000100000000000100010001001000210000000500010001000200100036000000050002000400020010004200000005000C000800518080000A00010018010A00010020010A00010028010A00010033010A0001003F010A00010049013A00210050013D0021005A01410021006401440021006C01480021007E0156005020000000009600B900120001008820000000009600DF001B000300BE20000000008618F70026000600C820000000008618F7002A000600162100000000E609FD0032000800202100000000E6010901360008000B2300000000E6011201260008004023000000008618F700500008004F2300000000860881015A0009005C230000000086088F015E00090000000100B70100000200EA0100000100F40102000200F801020003002902000001002D0200000200EA01000001007E01030009003100F70026003900F70026004100F70090004900F70090005100F70090005900F70090006100F70090006900F70090007100F70090007900F70090008100F70095008900F70090009100F7009A009900F7002600A100F70026001900D00336001900DB0321011900F70026012100EA032C012900EA0332010900F7002600B100FD033A00B10003045A00B100F7003D01B1000E044501B10015045E0019001A044B01080004000D0020007B009F0024000B006A002E00330077012E001B0059012E00230071012E002B0071012E004B0071012E007300B0012E00430086012E003B0059012E005B0071012E006B00A70144000B007D00380154010300010004000200000076014C000000A00162000000AA01660002000500030002000900050002000A00070004800000010000001712E55B000000000000A803000002000000000000000000000001004A000000000002000000000000000000000001008F000000000003000200040003000000003C4D6F64756C653E0053706C6974537472696E674368756E6B65642E646C6C0055736572446566696E656446756E6374696F6E7300737472696E6753706C697400726573756C7473006D73636F726C69620053797374656D004F626A6563740053797374656D2E436F6C6C656374696F6E730049456E756D657261746F7200535542535452494E475F53495A450053797374656D2E446174610053797374656D2E446174612E53716C54797065730053716C43686172730053706C6974537472696E675F53696E676C650053716C537472696E670053716C496E7433320053706C6974537472696E675F53696E676C655F46696C6C002E63746F72006765745F43757272656E74004D6F76654E657874005265736574006C617374506F73006E657874506F73007365676D656E744C656E006E6578745365676D656E7400726F774E756D62657200726573756C7400746865537472696E670064656C696D69746572007365676D656E7400746865526573756C740043757272656E74007373006765745F526F774E756D626572006765745F4F7574707574537472696E6700526F774E756D626572004F7574707574537472696E6700496E707574004D6963726F736F66742E53716C5365727665722E5365727665720053716C46616365744174747269627574650044656C696D69746572006F626A006974656D0053797374656D2E52756E74696D652E496E7465726F705365727669636573004F757441747472696275746500726F7700546865537472696E670053797374656D2E5265666C656374696F6E00417373656D626C795469746C6541747472696275746500417373656D626C794465736372697074696F6E41747472696275746500417373656D626C79436F6E66696775726174696F6E41747472696275746500417373656D626C79436F6D70616E7941747472696275746500417373656D626C7950726F6475637441747472696275746500417373656D626C79436F7079726967687441747472696275746500417373656D626C7954726164656D61726B41747472696275746500417373656D626C7943756C7475726541747472696275746500436F6D56697369626C6541747472696275746500417373656D626C7956657273696F6E4174747269627574650053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C6974794174747269627574650053706C6974537472696E674368756E6B65640053716C46756E6374696F6E417474726962757465006765745F49734E756C6C006765745F56616C75650043686172006F705F496D706C6963697400537472696E6700456D707479006765745F4C656E67746800436F6E636174005472696D00526561640000000320000000000046F581EED4391E48810AFC83870C8AA30008B77A5C561934E089020608044C1D00000800021209120D120D0A0003011C1011111011150320000107200201120D1D030320001C0320000202060E0306120D02060303061D03030612100328001C05200101120C0306120C032000080320000E032800080328000E12010001005408074D617853697A65FFFFFFFF12010001005408074D617853697A6501000000042001010E04200101020420010108808001000300540E1146696C6C526F774D6574686F644E616D651753706C6974537472696E675F53696E676C655F46696C6C540E0F5461626C65446566696E6974696F6E204F7574506172616D206E766172636861722834303030292C20526F7720494E54540E044E616D651573706C69745F737472696E675F776974685F726F770420001D03052001011D0305000111110E0500011115080407011210072003011D0308080500020E0E0E0820040A0A1D03080804070202081701001253706C6974537472696E674368756E6B656400000501000000000E0100094D6963726F736F667400002001001B436F7079726967687420C2A9204D6963726F736F6674203230313200000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F77730100A02D00000000000000000000BE2D0000002000000000000000000000000000000000000000000000B02D000000000000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF2500204000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000100100000001800008000000000000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058400000780300000000000000000000780334000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE0000010000000100E55B171200000100E55B17123F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B004D8020000010053007400720069006E006700460069006C00650049006E0066006F000000B4020000010030003000300030003000340062003000000034000A00010043006F006D00700061006E0079004E0061006D006500000000004D006900630072006F0073006F00660074000000500013000100460069006C0065004400650073006300720069007000740069006F006E0000000000530070006C006900740053007400720069006E0067004300680075006E006B00650064000000000040000F000100460069006C006500560065007200730069006F006E000000000031002E0030002E0034003600330031002E00320033003500320035000000000050001700010049006E007400650072006E0061006C004E0061006D0065000000530070006C006900740053007400720069006E0067004300680075006E006B00650064002E0064006C006C00000000005C001B0001004C006500670061006C0043006F007000790072006900670068007400000043006F0070007900720069006700680074002000A90020004D006900630072006F0073006F006600740020003200300031003200000000005800170001004F0072006900670069006E0061006C00460069006C0065006E0061006D0065000000530070006C006900740053007400720069006E0067004300680075006E006B00650064002E0064006C006C0000000000480013000100500072006F0064007500630074004E0061006D00650000000000530070006C006900740053007400720069006E0067004300680075006E006B00650064000000000044000F000100500072006F006400750063007400560065007200730069006F006E00000031002E0030002E0034003600330031002E00320033003500320035000000000048000F00010041007300730065006D0062006C0079002000560065007200730069006F006E00000031002E0030002E0034003600330031002E003200330035003200350000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000C000000D03D00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
WITH PERMISSION_SET = SAFE;
CREATE FUNCTION dbo.SplitStringWithRow
(
@Input nvarchar(max),
@Delimiter nvarchar(1)
)
RETURNS TABLE
(
Item nvarchar(4000) NULL,
RowNo integer NULL
)
AS
EXTERNAL NAME SplitStringWithRow.UserDefinedFunctions.SplitString_Single;
September 5, 2012 at 2:30 am
Adam Machanic (9/4/2012)
The code that follows is my crack at a more memory efficient solution. It works only for a single delimiter (I never did see the point of multi-character delimiters, to be honest)
What about a DOS-formatted text file where the delimiter at the end of lines is a carriage return/line feed pair? 🙂
September 5, 2012 at 3:47 am
paul.knibbs (9/5/2012)
Adam Machanic (9/4/2012)
The code that follows is my crack at a more memory efficient solution. It works only for a single delimiter (I never did see the point of multi-character delimiters, to be honest)
The point of multi-character delimiters is to delimit strings which may contain delimiter as legitimate characters in itself. So, it's quite often that combination of non-alpha-numeric characters is used to delimit text data. And of course an example of mixed multi-line delimiters: CRLF, CR or LF or even LFCR.
Also, I would like to add my two pence into discussion of CLR performance:
1. You may declare the same CLR function (operating with strings) multiple times in SQL. What will it give you? You will find that performance of VARCHAR(100) and VARCHAR(MAX) is quite different... So, the same function can be "optimised" for use with smaller strings.
2. If you use RegEx in CLR, you will find that declaring it as static with compiled option boost its performance. Yes, it is less flexible as you can not pass regular expression as parameter, but it's significantly faster as RegEx object needs to initialise once.
September 5, 2012 at 6:56 am
paul.knibbs (9/5/2012)
Adam Machanic (9/4/2012)
The code that follows is my crack at a more memory efficient solution. It works only for a single delimiter (I never did see the point of multi-character delimiters, to be honest)
What about a DOS-formatted text file where the delimiter at the end of lines is a carriage return/line feed pair? 🙂
Those are handled separately and don't require the use of multi-character delimiters unless you've made the mistake of trying to handle the whole file as a single blob. If you are trying to handle the file as a single blob, then, yes, a multi-character delimiter splitter would be in order. However, understand that splitting on a single delimiter is a performance optimization and that you should consider having two splitters. One to handle single character delimiters in a very high speed fashion and one to handle multi-character delimiters at slightly lower speeds.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 481 through 495 (of 522 total)
You must be logged in to reply to this topic. Login to reply