InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 06:37:48 GMT20http://www.sqlservercentral.com/Forums/Topic838388-403-1.aspxMay be late to the game, but MSSQL 2008 R2 SP2 fix that.the other option /works for me/:My main function indeed had some arguments as SqlBytes XX1, XX2 ....and by the end foreach( var row in rows) yield return row; I was getting error, shown in the article, while return rows was OK.\Simple move of yield statement in separate function fixed it.....return InfoOut( row); private static IEnumerable InfoOut( List<Row> rows) { foreach( var row in rows) yield return row; }Tue, 06 Nov 2012 09:23:13 GMTalgor 52977http://www.sqlservercentral.com/Forums/Topic838388-403-1.aspx[quote][b]Matjaz Justin (9/10/2010)[/b][hr]My question is: is it possible to have CLR table-valued function with variable number of return columns?[/quote]All functions (T-SQL or SQLCLR) must return a deterministic set of columns. The same requirement does not apply to stored procedures, so a SQLCLR stored procedure is a better bet if you truly need this functionality.If you would like a more detailed reply from more than one person, please repost your question in the CLR forums on this site.Fri, 10 Sep 2010 06:03:35 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic838388-403-1.aspx[quote][b]Matjaz Justin (9/10/2010)[/b][hr]I would like to take advantage of a chance to join to the discussion with a question which is not directly related to the title but I cannot miss all of eminent professionals on this thread.My question is: is it possible to have CLR table-valued function with variable number of return columns? [/quote]Probably... but that can also be done in T-SQL quite easily (although it won't be in a function). Please see the following article for how...[url]http://www.sqlservercentral.com/articles/Crosstab/65048/[/url]Fri, 10 Sep 2010 05:45:11 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic838388-403-1.aspxI would like to take advantage of a chance to join to the discussion with a question which is not directly related to the title but I cannot miss all of eminent professionals on this thread.My question is: is it possible to have CLR table-valued function with variable number of return columns? Fri, 10 Sep 2010 05:21:29 GMTMatjaz Justinhttp://www.sqlservercentral.com/Forums/Topic838388-403-1.aspx[quote][b]Solomon Rutzky (1/12/2010)[/b][hr]Paul, thanks for the great compliment as well as the code and explanation of it. I think it will be very helpful to many people.[/quote]You're most welcome - it was thoroughly deserved.@Barry: you can break the iteration by returning false at any point from the MoveNext() call, as you probably know :-)Tue, 12 Jan 2010 16:29:46 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic838388-403-1.aspxPaul, thanks for the great compliment as well as the code and explanation of it. I think it will be very helpful to many people.Barry, hopefully you can get what you were looking for from this information, including the ability to "break" the iteration.Take care,Solomon...Tue, 12 Jan 2010 16:24:33 GMTSolomon Rutzkyhttp://www.sqlservercentral.com/Forums/Topic838388-403-1.aspxOk Barry here we go. I've tried to cover everything so it's readable for the wider audience - so apologies for the amount of detail:1. SQL Server expects an object that implements IEnumerable from the initial call to the static method named in the EXTERNAL NAME part of the CREATE FUNCTION statement. In this case, that's the StreamingHandcoded method.2. The StreamingHandcoded method passes the input parameter to a new instance of the nested Helper class. The constructor for the Helper class stores the passed parameter in a read-only member variable. If we had a streaming type as an input parameter (SqlChars or SqlBytes), we would store a reference to that in the same way.3. SQL Server calls the GetEnumerator() method on the IEnumerable interface of the Helper object we pass back in order to get the object that will allow it to iterate over the results. The IEnumerable interface specifies that the object passed back will implement the IEnumerator interface. In our case, the Helper class implements both IEnumerable and IEnumerator, so IEnumerable.GetEnumerator() just returns a reference to the same class via 'this'. This avoids the need to create separate classes to handle the two calls.4. Now that SQL Server has an object implementing IEnumerator, it can use the interface's Current property and MoveNext method to fetch results (the Reset method is also required by the interface, but SQL Server doesn't call it for TVFs). Current returns the data for the current row, MoveNext moves to the next row of the result set, and returns true until there are no more rows.5. The code to do the work required appears in the helper class. This is what makes this way of doing things 'lazy' - no work on the task in hand is done in the method called in step 1. The work is often coded in the MoveNext method, with Current just returning a simple reference to some member variable in the helper class. In this very simple example, the work consists of "++index < maxValue;" - incrementing the member variable and returning a boolean.6. SQL Server calls the Current property after every call to MoveNext that returns true. This returns an object (untyped) as defined by the interface. In our case, the returned object is an int (the index variable).7. SQL Server then passes this object to the FillRowMethodName specified in the SqlFunction attribute. In this case, that is the static FillRow(object i, out SqlInt32 Value) method on the outer class. That method simply casts the object back to an int, and uses that to construct a SqlInt32 which is returned via the out parameter.PaulTue, 12 Jan 2010 16:06:13 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic838388-403-1.aspx[quote][b]RBarryYoung (1/12/2010)[/b][hr]Any chance you could explain your Helper class a little? (you know, a Helper Helper? :-D)For obviously selfish reasons I am very interested in implementing it in VB.net, and although I don't see anything non-doable in VB, I also can't figure out how it does its magic either, which makes me think that I am missing something important.[/quote]Sure thing Barry! It is, however, 6:36am from the wrong end, so I'm going to grab some sleep first. I'll post back then, assuming someone else hasn't already covered it. Oh and 'hi' by the way :-)PaulTue, 12 Jan 2010 10:37:23 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic838388-403-1.aspxPaul:Any chance you could explain your Helper class a little? (you know, a Helper Helper? :-D)For obviously selfish reasons I am very interested in implementing it in VB.net, and although I don't see anything non-doable in VB, I also can't figure out how it does its magic either, which makes me think that I am missing something important.Tue, 12 Jan 2010 10:09:46 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic838388-403-1.aspxI like this article a lot. In fact, it is one of the very best I have come across on SSC. Congratulations are definitely in order, both for style and content.It is slightly sad that only a small fraction of the potential audience will appreciate exactly what you have done here. I think that's probably a reflection of how few SQL people have a good grasp of .NET code and a willingness to extend the capabilities of T-SQL in sensible, appropriate, and efficient ways. It's so easy to misuse the CLR integration and I guess that makes it all the more refreshing to see a well thought out article on a key subject.I certainly do like the concise code made possible by the use of yield, and it certainly helps people to avoid the all-too-common mistake of generating a huge memory object in the Init method and then iterating over that in the FillRow calls.That said, I don't tend to use yield in practice, for the following reasons:1. The bug you refer to in the article with SqlChars and SqlBytes is a worry. I haven't come across that myself; however that may just be dumb luck. To be fair, I don't know whether that bug only manifests itself with yield. It is a consideration though - especially since SqlChars and SqlBytes are the only streaming input types.2. The compiler emits a fair chunk of MSIL when it encounters yield - in fact it generates an entire class. More on that in a bit. I have a natural (and, I hope, healthy) skepticism with this sort of thing: the compiler must generate code which is general in nature and provably correct in all situations. This necessarily means that yield will be somewhat slower than a hand-written equivalent.3. The extra code emitted by the compiler cannot be debugged in the usual way - you can't 'step into' it in a debugging session.4. Yield cannot be used in a try...catch block. This is an annoying restriction and complicates error handling somewhat.Of course you can't argue with the code economy - it can produce extremely neat code - and I do find that appealing.The alternative is to hand-code a helper class. This involves more typing for sure, but in the 20 million row test featured at the end of your article the hand-rolled version used 18.7 seconds of CPU on my home machine, compared to 19.8 seconds for the yield version - a 6% difference.Here's a rig based on the article's example code showing both a yield and helper-class implementation, just in case any is interested:(The helper class code is pretty close to the code emitted by the compiler for yield)[code]using System.Data.SqlTypes;using Microsoft.SqlServer.Server;using System.Collections;public partial class TestFunctions{ [Microsoft.SqlServer.Server.SqlFunction ( DataAccess = DataAccessKind.None, IsDeterministic = true, IsPrecise = true, SystemDataAccess = SystemDataAccessKind.None, FillRowMethodName = "FillRow", TableDefinition = "Value INT" ) ] public static IEnumerable StreamingHandCoded(SqlInt32 MaxValue) { return new Helper(MaxValue.Value); } [Microsoft.SqlServer.Server.SqlFunction ( DataAccess = DataAccessKind.None, IsDeterministic = true, IsPrecise = true, SystemDataAccess = SystemDataAccessKind.None, FillRowMethodName = "FillRow", TableDefinition = "Value INT" ) ] public static IEnumerable StreamingYield(SqlInt32 MaxValue) { for (int i = 0; i < MaxValue; i++) { yield return i; }; } public static void FillRow(object i, out SqlInt32 Value) { Value = new SqlInt32((int)i); } #region HelperClass public sealed class Helper : IEnumerable, IEnumerator { readonly int maxValue; int index = -1; public Helper(int maxValue) { this.maxValue = maxValue; } #region IEnumerable Members IEnumerator IEnumerable.GetEnumerator() { return this; } #endregion #region IEnumerator Members object IEnumerator.Current { get { return index; } } bool IEnumerator.MoveNext() { return ++index < maxValue; } void IEnumerator.Reset() { index = -1; } #endregion } #endregion};[/code]Tue, 12 Jan 2010 07:17:46 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic838388-403-1.aspxHello again Per. I found this, which seems related but might not be the same issue since you say that you closed the connection before returning:[url]http://social.msdn.microsoft.com/Forums/en-IE/sqlnetfx/thread/e9e7c707-87fb-49cc-bd0b-d426ed53a534[/url]For now, I would just stick with the connection string that works since it is still better than filling up memory with the entire collection before releasing it back to the calling query.Take care,Solomon...PS, if you want, you can just send me the code and I can try to look at it. Just click on my name to the left to see my details and then select "Send Email".Mon, 04 Jan 2010 06:45:41 GMTSolomon Rutzkyhttp://www.sqlservercentral.com/Forums/Topic838388-403-1.aspxI forgot to answer your other question. Yes it works if I create the whole collection and then returns it without using the yield return functionality.Bu I would like to use the yield return as the function might get memory problems if to much data is asked for./PerMon, 04 Jan 2010 05:56:49 GMTperhhttp://www.sqlservercentral.com/Forums/Topic838388-403-1.aspxHello Solomon,The table where I read data from is in a local database. I tried to add the SystemDataAccessKind.Read as well but with no difference.I found an article about this on http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=442200and tested the suggestion of using a connectionstring with "enlist=false". So I changed my SqlConnection from "Context Connection=true"to"data source=SENOVOPC005\\MSSQLSERVER2008;initial catalog=ReportTest;integrated security=SSPI;enlist=false"and then it worked. But I want to use "Context connection=true" as it is a much nicer way for the CLR to connect to the local database.RegardsPerMon, 04 Jan 2010 05:52:25 GMTperhhttp://www.sqlservercentral.com/Forums/Topic838388-403-1.aspx[quote][b]perh (1/4/2010)[/b][hr]I have tried using the yield return method in one of my test projects but I am getting the error below: "An error occurred while getting new row from user defined Table Valued Function : System.InvalidOperationException: Data access is not allowed in this context. Either the context is a function or method not marked with DataAccessKind.Read or SystemDataAccessKind.Read, is a callback to obtain data from FillRow method of a Table Valued Function, or is a UDT validation method."I have the DataAccessKind.Read property set of the UDF and the SqlConnection is closed before the yield return is called. The SqlConnection is using "Context Connection=true".[/quote]Hello Per. What table are you reading from? Is it a user table or a system table? If a system table is used then you also need to set SystemDataAccessKind. Have you tried this function with the regular, non-yield return syntax? I am not sure that this error relates specifically to yield return. If neither of these help, do you mind attaching the code?Take care,Solomon...Mon, 04 Jan 2010 05:24:59 GMTSolomon Rutzkyhttp://www.sqlservercentral.com/Forums/Topic838388-403-1.aspxI have tried using the yield return method in one of my test projects but I am getting the error below: "An error occurred while getting new row from user defined Table Valued Function : System.InvalidOperationException: Data access is not allowed in this context. Either the context is a function or method not marked with DataAccessKind.Read or SystemDataAccessKind.Read, is a callback to obtain data from FillRow method of a Table Valued Function, or is a UDT validation method."I have the DataAccessKind.Read property set of the UDF and the SqlConnection is closed before the yield return is called. The SqlConnection is using "Context Connection=true".I have read that you cannot use a sqlconnection in the fillrow function in SQL server 2008 but my sqlconnection is used in the UDF and it is closed before the calls to yield return.Any ideas?RegardsPerMon, 04 Jan 2010 02:39:44 GMTperhhttp://www.sqlservercentral.com/Forums/Topic838388-403-1.aspxSolomon: OK, that's good. Thanks for the direction on that and I'll let you know when ever I get the chance to pursue this further...-- Thanks Again, :-)Fri, 25 Dec 2009 22:07:36 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic838388-403-1.aspx[quote][b]Solomon Rutzky (12/25/2009)[/b][hr]Hey Jeff, actually, the RegEx delimiter is needed (as far as I can tell) in order to do true CSV / TSV parsing.[/quote]That's what I'm talking about... You don't need RegEx to parse True CSV although using RegEx is one of the easiest ways to pull that task off.Fri, 25 Dec 2009 21:16:56 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic838388-403-1.aspx[quote][b]Jeff Moden (12/25/2009)[/b][hr]Not sure you need the minor overhead of RegEx for such a thing either but, being pretty much a hard core data troll, I've not worked outside SQL Server very much in the last 7 years and wouldn't know for sure.[/quote]Hey Jeff, actually, the RegEx delimiter is needed (as far as I can tell) in order to do true CSV / TSV parsing. The problem with splitting on a regular comma comes in when you have commas embedded in data fields. At this point you do text-qualification but need to be able to determine if it is being used (might not be on all fields) and even if it is all fields you still need to know that a comma inside of a text-qualified region is not a field-separator. And at that point it gets a little trickier since now you need to also rule out instances of the text-qualifier character that are likewise embedded in the text-qualified data field.[quote]Anyway, thank you (and the others) very much for answering my questions and entertaining my strong bit of skepticism on the subject of CLRs. Just like the Friday poll I put out a couple of weeks ago, it seems that people (on this thread, anyway), are using CLRs in a highly appropriate fashion. Thanks to Barry for taking the time to dumb down what you're doing in the article for me, all that's left to say is "Well done, Solomon" and "It's about time folks started thinking this way". :-)[/quote]Thank you for that and you are welcome. I think this has been a good discussion on the topic of CLR. And yes, thanks also to Barry for explaining that in more technical detail than I could have :-).[quote][b]RBarryYoung (12/25/2009)[/b][hr]In other words, if "yield break" is used to replace the end-of-routine return when you don't actually want to end your routine yet, then (hopefully) you should be able to do the converse as well: use the end of your C# routine instead of "yield break". Except of course for some of those edge cases that he mentions, and the whole question of whether the code logic [i]can[/i] be gerrymandered around like that in each specific case (which is why there's no deterministic solution that works all the time, just usually).[/quote]Ok, I understand what you are asking for now but am still not sure that I can help. The only two things I can think of I am sure you have already thought about:1) Structure the code differently (I think you did mention this earlier). In my example, I could have reversed the initial IF statement to be "if parameter is not null proceed" and then left the "else" condition to simply dump out at the end of the function.2) This one is not really recommended, but technically possible: use the evil "goto" statement. If C# even allows for this I could have done that inside of that initial IF statement and gone directly to the end of the function. Sloppy but workable if the goto statement is available.3) Ok so maybe 3 things, but this one is really more tongue-in-cheeck than serious: you could always work in C# instead of VB.Net ;-). I know, I know, not very helpful.Other than that I really don't know. I don't even really know what it is that I stumbled upon other than it works great! ;-). I really wish I could be of more help.Fri, 25 Dec 2009 20:25:10 GMTSolomon Rutzkyhttp://www.sqlservercentral.com/Forums/Topic838388-403-1.aspxOK, Solomon, here's a quote from Jon Skeet at his C# site ([url]http://www.yoda.arachsys.com/csharp/csharp2/iterators.html[/url]), that I think explains what I am getting at:[quote]As mentioned before, yield break is used to stop iterating. Usually this is not needed, as you naturally reach the end of the iterator block. As well as stopping iterating, yield break can also be used to create a simple "empty" iterator which doesn't yield anything. If you had a completely empty method body, the compiler wouldn't know whether you wanted to write an iterator block or a "normal" block (with normal return statements etc). A single yield break; statement as the whole method body is enough to satisfy the compiler.yield break can be useful if you want to stop iterating due to some external signal - the user clicking on a "cancel" button for instance. Sometimes it is easier to stop the code which is providing the data than the code which is requesting that data. In simple cases, of course, you can just use while loops to only keep going while more data is really wanted. In more complicated scenarios, however, that can make the code messy - [b]yield break ends the method abruptly in the same way that a normal return statement does[/b], with no need to make sure that every level of iteration checks whether or not to continue. Here's an example:...[/quote]In other words, if "yield break" is used to replace the end-of-routine return when you don't actually want to end your routine yet, then (hopefully) you should be able to do the converse as well: use the end of your C# routine instead of "yield break". Except of course for some of those edge cases that he mentions, and the whole question of whether the code logic [i]can[/i] be gerrymandered around like that in each specific case (which is why there's no deterministic solution that works all the time, just usually).Fri, 25 Dec 2009 19:50:30 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic838388-403-1.aspx[quote][b]Solomon Rutzky (12/25/2009)[/b][hr][quote][b]RBarryYoung (12/24/2009)[/b][hr]However, "yield break" is another story altogether. I have not heard of any way by anyone of a way to emulate a "yield break" in Vb other than the obvious: execute a hard return. So if you can rewrite your CLR so that you can replace the "yield break" with your functions exit/return, then it *should* be possible for me to convert it into VB.Net.[/quote]Hey there. I tried reworking my example to use a "return" in a few different manners but always got this error:[color="#ff0000"]Cannot return a value from an iterator. Use the yield return statement to return a value, or yield break to end the iteration.[/color]Sorry I could not help more here. :([/quote]I don't think I explained myself very well (I have a hard time remember the right terminology for the C world sometimes). Anyway what I meant by a "hard return", was not an [i]explicit[/i] [font="Courier New"]return[/font] statement, but rather the [i]implicit[/i] return marked by the physical end of the method. (looking at it now, my attempted description seems awful to me). In other words, you have to arrange the logic so that the "yield break" is replaced by the physical end of the routine.(EDIT: Hmmm, or maybe that message means that you must use "return" without a value? *sigh* I'm just not good enough at C# to figure these subtle differences out by myself ... :().That's my understanding anyway, which is probably dicey at best. I'll try and dig up some reference that might make this clearer...Fri, 25 Dec 2009 19:33:55 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic838388-403-1.aspxHeh, glad I could help,Jeff. :-)And as far as things coming full circle, yeah. "The more things change the more they stay the same". Or as I like to say sometimes "those who forget the lessons of history are doomed to repeat them. But those who remember them, will eventually be able to exploit them." :-DFri, 25 Dec 2009 19:26:54 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic838388-403-1.aspx[quote][b]Solomon Rutzky (12/25/2009)[/b][hr]Hey Jeff. As I mentioned in a reply to one of Matt's posts, I basically solved this problem, in a general sense, using a CLR Stored Proc in my SQL# project called File_SpllitIntoFields which takes a Regular Expression delimiter. Using a RegEx delimiter means that you can truly parse CSV files that have text-qualification on some (not all) fields an embedded double-quotes. It also returns "empty" fields and can skip any number of "header" rows. And because it releases each row as it reads it from disk, it consumes very little memory.[/quote]Thanks, Solomon. Well done. You've absolutely nailed several of my main complaints with the likes of BCP and Bulk Insert. And Yep... I get that part and have previously said that a well written CLR is the way to go for splitters. It's easy to do well and can be done with some excellent performance especially when it comes to the splitting and text-qualifier recognition. Splitters are one of those places where a CLR will just about always beat T-SQL. Not sure you need the minor overhead of RegEx for such a thing either but, being pretty much a hard core data troll, I've not worked outside SQL Server very much in the last 7 years and wouldn't know for sure. Heh... as a side bar, I gave all that other stuff up when my manager (at the time) insisted that I make a "warning" field on the GUI with White lettering and a pale Yellow background and a "danger" field with a particular color Pink lettering on a Fusia background.Anyway, thank you (and the others) very much for answering my questions and entertaining my strong bit of skepticism on the subject of CLRs. Just like the Friday poll I put out a couple of weeks ago, it seems that people (on this thread, anyway), are using CLRs in a highly appropriate fashion. Thanks to Barry for taking the time to dumb down what you're doing in the article for me, all that's left to say is "Well done, Solomon" and "It's about time folks started thinking this way". :-)Fri, 25 Dec 2009 09:31:52 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic838388-403-1.aspx[quote][b]RBarryYoung (12/25/2009)[/b][hr][quote][b]Jeff Moden (12/24/2009)[/b][hr]...Do you think this streaming method will solve the memory issues on extremely large files? If it does, then that makes this article really worth the price of admission because, as Barry mentioned, one of the code places to use CLR's is for parsing raw data. (Actually, that question is for anyone who might like to jump in).[/quote]Oh yeah, absolutely. Streaming is all the rage in the .Net set these days (even aside from SQL, SQL CLR, etc.), it solves the memory overflow problems by yards, because its basically pipelining the recordsets internally, it never has to have more than a fraction of them allocated at a time. But more than that, its just way faster, even when you [i]don't[/i] have a max memory problem. First it saves on the overhead of memory allocation, because it can just keep reusing those buffers that it freed up. And secondly, it can heavily leverage multi-processors/cores through on-server co-processing, because the sender and receiver are both working at the same time, instead of first the sender in one long huge lump, and then the receiver, in an equally long huge lump.[/quote]Ah... now I get it. I've not worked with C and I haven't worked with VB in about 7 years. I couldn't tell you an IENumerator from a Terminator. Thanks for the simple answer.It's ironic, though... seems we've come full circle back to the "old way" of doing things. We've come back to the natural cycle of read a line, process a line, write a line, loop until done. No memory worries, no scalability problems, no speed problems. Open two "file" connections and go like hell one line or one controlable batch of lines at a time. It's about time.Thanks, Barry.Fri, 25 Dec 2009 09:06:11 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic838388-403-1.aspx[quote][b]RBarryYoung (12/25/2009)[/b][hr]And of course, Streaming is a dream for scalability. You no longer have worry about scaling up physical memory to match the size of your Import/Export datasets, if they never have to have more than a couple of hundred rows in memory at anyone time.[/quote]Agreed, and if you can get the return stream out of context while keeping the incoming stream "in context", you tend to get very good perf (although this seems to not work using a loopback OPENQUERY for that). using the context connection for both isn't bad, but it's not quite as fast as the other method.I'm just surprised they decided not to build this into functions when they made it so very easy to do in Stored procs.Fri, 25 Dec 2009 08:19:47 GMTMatt Miller (#4)http://www.sqlservercentral.com/Forums/Topic838388-403-1.aspx[quote][b]RBarryYoung (12/24/2009)[/b][hr]However, "yield break" is another story altogether. I have not heard of any way by anyone of a way to emulate a "yield break" in Vb other than the obvious: execute a hard return. So if you can rewrite your CLR so that you can replace the "yield break" with your functions exit/return, then it *should* be possible for me to convert it into VB.Net.[/quote]Hey there. I tried reworking my example to use a "return" in a few different manners but always got this error:[color="#ff0000"]Cannot return a value from an iterator. Use the yield return statement to return a value, or yield break to end the iteration.[/color]Sorry I could not help more here. :(Fri, 25 Dec 2009 06:08:40 GMTSolomon Rutzkyhttp://www.sqlservercentral.com/Forums/Topic838388-403-1.aspx[quote][b]Jeff Moden (12/24/2009)[/b][hr]The files also had "file headers" and "file footers". Heh... while everyone else was pitching a fit, I used the information in those to confirm what was in the file, made sure it matched the file name, and used the data in the footer to confirm the entire file was sent and received in good order because it also contained a row count.[/quote]Hey Jeff. As I mentioned in a reply to one of Matt's posts, I basically solved this problem, in a general sense, using a CLR Stored Proc in my SQL# project called File_SpllitIntoFields which takes a Regular Expression delimiter. Using a RegEx delimiter means that you can truly parse CSV files that have text-qualification on some (not all) fields an embedded double-quotes. It also returns "empty" fields and can skip any number of "header" rows. And because it releases each row as it reads it from disk, it consumes very little memory.[quote]It's funny that you'd mention the memory problems with CLRs... the Perl scripts had the same bloody problem. As business increased, the files got bigger and memory usage became a real issue. Do you think this streaming method will solve the memory issues on extremely large files? If it does, then that makes this article really worth the price of admission because, as Barry mentioned, one of the code places to use CLR's is for parsing raw data. (Actually, that question is for anyone who might like to jump in).[/quote]As I have been saying (and now Barry has been helpful in explaining the technical "why") there is no reason to not always do this (or even the full IEnumerator syntax as noted via links in my previous post) since keeping one row (or a small set of data) in memory is always preferable over the entire file / set. For example, I have a GZip CLR Function in my SQL# project that will compress a file on disk. I open the read and write streams at the same time and process a small amount of data per each read/write so the file size never matters. I use this function at work and I GZip 3 gig files down to 700 megs in about 5 minutes. In fact, the 3 Gig file is generated by the DB_BulkExport CLR Proc that is also in the SQL# project that exports the result set of a passed-in Query. This will also not consume much memory as it only has one row in memory at any given time since it writes each row to disk as it is read from the datareader.So file size should never really matter (unless for some reason you need to make multiple passes on the full set of data). And if someone is processing large files and does not want to use (or even enable) CLR, there is a wonderful alternative if they are using SQL Server 2008 which is to stream the data in from an application, which is the basis of my previous article (which is 100% set-based and no RBAR :-)): [url]http://www.sqlservercentral.com/articles/SQL+Server+2008/66554/[/url]Take care,Solomon...Fri, 25 Dec 2009 05:39:51 GMTSolomon Rutzkyhttp://www.sqlservercentral.com/Forums/Topic838388-403-1.aspxAnd of course, Streaming is a dream for scalability. You no longer have worry about scaling up physical memory to match the size of your Import/Export datasets, if they never have to have more than a couple of hundred rows in memory at anyone time.Fri, 25 Dec 2009 01:44:54 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic838388-403-1.aspx[quote][b]Jeff Moden (12/24/2009)[/b][hr]...Do you think this streaming method will solve the memory issues on extremely large files? If it does, then that makes this article really worth the price of admission because, as Barry mentioned, one of the code places to use CLR's is for parsing raw data. (Actually, that question is for anyone who might like to jump in).[/quote]Oh yeah, absolutely. Streaming is all the rage in the .Net set these days (even aside from SQL, SQL CLR, etc.), it solves the memory overflow problems by yards, because its basically pipelining the recordsets internally, it never has to have more than a fraction of them allocated at a time. But more than that, its just way faster, even when you [i]don't[/i] have a max memory problem. First it saves on the overhead of memory allocation, because it can just keep reusing those buffers that it freed up. And secondly, it can heavily leverage multi-processors/cores through on-server co-processing, because the sender and receiver are both working at the same time, instead of first the sender in one long huge lump, and then the receiver, in an equally long huge lump.Fri, 25 Dec 2009 01:37:15 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic838388-403-1.aspx[quote][b]Matt Miller (#4) (12/24/2009)[/b][hr]Sorry to say - those files are long gone for another company I no longer deal with. They were some delimited files, with varying numbers of inputs on each line (the generating system would "skip" the ending inputs with no values).Again nothing too horrible, and I wanted to play with SQLCLR early on with it, so I took a shot.I've been pleasantly surprised to see my 2008 doing better with resource mgmt (probably due to the 64-bit in part), so CLR does seems to be doing somewhat better from my small testing with it. Don't have much in the way of play time these days, so CLR doesn't get much air time. There is one reasonably interesting application I am attempting which might be useful (CLR call to an ERE or enterprise rules engine), but I haven't had a chance to stress-test it.[/quote]Ah... got it. I ran into a couple of similar buzz saws last year. I had delimited files like the ones you speak of where empty "fields" at the end of the row were simply dropped just as you described. I also had some where there were an unknown number of fields where there was a two row column header and if they contained certain words, either had to be handled as column pairs or column quads and THEN be routed to the correct tables. I ended up using my old friend the Tally table. The original job for the imports and splits were written in Perl and each file would take about 40 minutes to render out. Between Bulk Insert, xp_DirTree, and the Tally table, I could load, render, glean, and clean 8 files in less than 2 minutes.The files also had "file headers" and "file footers". Heh... while everyone else was pitching a fit, I used the information in those to confirm what was in the file, made sure it matched the file name, and used the data in the footer to confirm the entire file was sent and received in good order because it also contained a row count. It's funny that you'd mention the memory problems with CLRs... the Perl scripts had the same bloody problem. As business increased, the files got bigger and memory usage became a real issue. Do you think this streaming method will solve the memory issues on extremely large files? If it does, then that makes this article really worth the price of admission because, as Barry mentioned, one of the code places to use CLR's is for parsing raw data. (Actually, that question is for anyone who might like to jump in).Thu, 24 Dec 2009 23:08:20 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic838388-403-1.aspx[quote][b]Solomon Rutzky (12/24/2009)[/b][hr][quote][b]RBarryYoung (12/24/2009)[/b][hr]Actually, Jeff, the line-splitting functions and (less often)parsing functions are usually best done as TVF's, whether SQL or CLR. Unfortunately, as Matt pointed out, CLR TVF's by default do not want to do a streaming return. Solomon's "trick" here is really quite significant. Up till now, I have not even heard any of the "Big Guns" in CLR's say anything about it other than to complain about the situation. [/quote]Ok, so English lesson's aside ;-), I really cannot lay claim to this being my technique. I was hoping to indicate in the article that this is just something that I came across in my workings with SQLCLR. I think I picked it up from my last article dealing with Streaming INTO SQL Server from an application and figured I would give the "yield return" syntax a shot in a TVF and was very pleasantly surprised when it worked. However, I have since done some searches on "yield return" and have found that a few other people have been doing this so maybe it is documented somewhere. Unfortunately the examples are all in blog posts about something not working correctly and not attempting to show a good way of doing TVFs. Hence nobody looking for "how to do a streaming TVF" (or maybe, "how to do a TVF properly" since I am not sure why anyone would choose to store the entire collection in memory, unless maybe you needed to do secondary logic over the set) will ever find those posts. This is why I felt it was important to do this article: because, as you mentioned, for some odd reason nobody else has.[/quote]Fair enough. And I should mention here that my concern with conversion here is with "yield break", [i]not[/i] with "yield return". There [i]are[/i] well known (though difficult) ways to emulate "yield return" in VB.Net; the two most common are 1) Inherit someone else's IEnumerable and then force it to do your bidding (this is the canonical article on that technique: [url]http://visualstudiomagazine.com/columns/article.aspx?editorialsid=2972[/url]), and 2) use VB's "Static" keyword (not the same as C#'s "static", which is called "Shared" in VB) to write a state-machine to emulate the Co-routine context retention that "yield return" provides.However, "yield break" is another story altogether. I have not heard of any way by anyone of a way to emulate a "yield break" in Vb other than the obvious: execute a hard return. So if you can rewrite your CLR so that you can replace the "yield break" with your functions exit/return, then it *should* be possible for me to convert it into VB.Net.[quote]Now, I don't really know much about VB.Net but I did mention in my article that there is the standard implementation of IEnumerator that does the same thing. I would have to assume that while the examples are in C# that they would work the same in VB.Net since they do not rely upon the "yield" construct. Here are two:1) [url]http://www32.brinkster.com/srisamp/sqlArticles/article_46.htm[/url] (bottom half of the page)2) [url]http://sqlblog.com/blogs/adam_machanic/archive/2009/04/28/sqlclr-string-splitting-part-2-even-faster-even-more-scalable.aspx[/url][/quote]Right. I also assume that that's the same as I mentioned above. The "yield break" is the sticking point there.As for the Errors that you were getting in your routine, I assume that those are actually related to T-SQL's inability to create and maintain a class-instance (object) context in any consistent way, and conversely, CLR's great difficulty telling different calling contexts & threads apart. When I wrote my own CLR "running totals" solution, that was the biggest problem that I ran into (and [i]not[/i] the sequencing problem). I did finally come up a couple of solutions to it, but they are somewhat kludgey and not at all elegant.Thu, 24 Dec 2009 21:56:00 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic838388-403-1.aspx[quote][b]Jeff Moden (12/24/2009)[/b][hr][quote][b]Matt Miller (#4) (12/23/2009)[/b][hr]I had attempted to use something like this to bring in data from an external file. One of those "ugly delimited" files, kind of delimited and kind of not, that I needed to bring in. This would have offered a way to parse it and clean it up in one single shot, and I would simply need to insert the results from the TVF into my destination table, skipping the whole staging table, clean up the data, etc...I ended up abandoning it because it was trying to build the entire table in mem before returning it, so the CLR function would crash before getting anything out. So - I ended up going in another direction and got it done that way.I had also looked at building the old running totals this way, running into the same problem (order is in fact guaranteed this way so no controversy over documented vs not). I got around that using the CLR SP (since you can stream the results without having to build the entire return set first), but still use the "other way" until I am formally forbidden from doing so , since it is still faster than the CLR SP (although not by a whole lot).[/quote]That's one of the problems that I seem to see popping up a lot... CLR's running the system out of memory. I've not asked any DBA in particular, but I'm thinking that's one of the reasons why many DBA's simply say "No! No CLR's. Not on my box."Shifting gears, it would be fun to see the file you were trying to split if it's not proprietary and has no private info in it.Heh... it's also good to hear that the "other" method for running aggregates beats the CLR method... thanks for the feedback on that. :-)[/quote]Sorry to say - those files are long gone for another company I no longer deal with. They were some delimited files, with varying numbers of inputs on each line (the generating system would "skip" the ending inputs with no values).Again nothing too horrible, and I wanted to play with SQLCLR early on with it, so I took a shot.I've been pleasantly surprised to see my 2008 doing better with resource mgmt (probably due to the 64-bit in part), so CLR does seems to be doing somewhat better from my small testing with it. Don't have much in the way of play time these days, so CLR doesn't get much air time. There is one reasonably interesting application I am attempting which might be useful (CLR call to an ERE or enterprise rules engine), but I haven't had a chance to stress-test it.Thu, 24 Dec 2009 21:51:40 GMTMatt Miller (#4)http://www.sqlservercentral.com/Forums/Topic838388-403-1.aspx[quote][b]RBarryYoung (12/24/2009)[/b][hr][quote][b]Jeff Moden (12/24/2009)[/b][hr]...That's one of the problems that I seem to see popping up a lot... CLR's running the system out of memory. I've not asked any DBA in particular, but I'm thinking that's one of the reasons why many DBA's simply say "No! No CLR's. Not on my box."...[/quote]I think I saw Paul or Florian talking about that recently: why it happens, how to avoid it, and what version it gets fixed in. I'll see if I can find it...[/quote]Hmm, now that I think about it, I think Paul (or Flo, can't remember which, sorry...) said pretty much the same things that Matt and Solomon (and Adam) have been saying about it; "Use Streaming".Thu, 24 Dec 2009 20:36:14 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic838388-403-1.aspx[quote][b]Jeff Moden (12/24/2009)[/b][hr]...That's one of the problems that I seem to see popping up a lot... CLR's running the system out of memory. I've not asked any DBA in particular, but I'm thinking that's one of the reasons why many DBA's simply say "No! No CLR's. Not on my box."...[/quote]I think I saw Paul or Florian talking about that recently: why it happens, how to avoid it, and what version it gets fixed in. I'll see if I can find it...Thu, 24 Dec 2009 20:30:12 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic838388-403-1.aspx[quote][b]Jeff Moden (12/24/2009)[/b][hr]That's one of the problems that I seem to see popping up a lot... CLR's running the system out of memory. I've not asked any DBA in particular, but I'm thinking that's one of the reasons why many DBA's simply say "No! No CLR's. Not on my box."[/quote]Hey Jeff. I think the problem of CLR's taking up too much memory is more an issue of technique than of CLR usage in general. Hence why I wrote this article: to help people alleviate memory consumption in the majority of CLR TVFs.And I would be curious if you would ask these DBA's about why they say "No". It could just as easily be a case of misinformation and general ignorance regarding CLR usage, especially in terms of security.[quote]Heh... it's also good to hear that the "other" method for running aggregates beats the CLR method... thanks for the feedback on that. :-)[/quote]To be fair to the conversation, it is not necessarily that the "other" method was a clear winner, just that he did not try this streaming method (or even the other means of streaming it via full IEnumerator implementation) so it was building the whole collection in memory. A fair test would be for Matt to go back to his code for the CLR TVF and to make a few minor modifications for this new "yield return" syntax and THEN we could see which is better (or maybe just the same).And just to be clear since forum posts can easily be misread as overreacting due to no tone of voice (outside of emoticons): I do not think that you are picking on me and I am not being defensive. I am just trying to make sure that this side is being fairly represented.Thu, 24 Dec 2009 17:59:59 GMTSolomon Rutzkyhttp://www.sqlservercentral.com/Forums/Topic838388-403-1.aspxSorry... didn't mean to turn this into a "I hate CLR's" post... I just wanted to know what this "streaming CLR" would be used for and why folks thought it couldn't be done in T-SQL.Thu, 24 Dec 2009 17:20:49 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic838388-403-1.aspx[quote][b]RBarryYoung (12/24/2009)[/b][hr][quote][b]Jeff Moden (12/23/2009)[/b][hr]Next question, please... What application would have the need for such a thing where it could not be done using T-SQL? Not trying to be a smart guy here... I really want to know where folks have used or will use this type of thing and why they couldn't do it in T-SQL.Going back to my previous post... no, I wouldn't use a stored proc if a native TVF would do either (of course, "it depends"). I was just providing a direct method for how to use the output of an existing stored proc.[/quote]Actually, Jeff, the line-splitting functions and (less often)parsing functions are usually best done as TVF's, whether SQL or CLR.[/quote]Yep... I agree and because you were involved on the great "splitter" post, you already know I agree. ;-) In SQL Server 2000 and above, I use Inline T-SQL TVF's for the task. We also saw that a super simple CLR could beat that (although the Tally table gave it a really good race on VARCHAR(8000)... One of the few places where CLR's beat T-SQL when there's a solution available in both.I do like the idea of someone building a handy "file handler" CLR but it would have the same problems as equivalent code in T-SQL... many OPS folks and many DBA's wouldn't allow it if it could move, delete, create, or rename files unless the code was "protected" from casual use which both can be.Thu, 24 Dec 2009 17:15:13 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic838388-403-1.aspx[quote][b]Matt Miller (#4) (12/23/2009)[/b][hr]I had attempted to use something like this to bring in data from an external file. One of those "ugly delimited" files, kind of delimited and kind of not, that I needed to bring in. This would have offered a way to parse it and clean it up in one single shot, and I would simply need to insert the results from the TVF into my destination table, skipping the whole staging table, clean up the data, etc...I ended up abandoning it because it was trying to build the entire table in mem before returning it, so the CLR function would crash before getting anything out. So - I ended up going in another direction and got it done that way.I had also looked at building the old running totals this way, running into the same problem (order is in fact guaranteed this way so no controversy over documented vs not). I got around that using the CLR SP (since you can stream the results without having to build the entire return set first), but still use the "other way" until I am formally forbidden from doing so , since it is still faster than the CLR SP (although not by a whole lot).[/quote]That's one of the problems that I seem to see popping up a lot... CLR's running the system out of memory. I've not asked any DBA in particular, but I'm thinking that's one of the reasons why many DBA's simply say "No! No CLR's. Not on my box."Shifting gears, it would be fun to see the file you were trying to split if it's not proprietary and has no private info in it.Heh... it's also good to hear that the "other" method for running aggregates beats the CLR method... thanks for the feedback on that. :-)Thu, 24 Dec 2009 17:06:13 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic838388-403-1.aspx[quote][b]timothyawiseman (12/24/2009)[/b][hr][quote][b]Jeff Moden (12/23/2009)[/b][hr] I'm not picking on the author here... it's a general statement that I bring up to anyone who may be considering the use of CLRS... Don't use the excuse of a lack of knowledge of T-SQL functionality as a justification for CLR usage. You'll frequently pay (yes, there are exceptions) for such a mistake in the form of performance and resource usage. There are very few things that can't actually be done in T-SQL and there are very few things where a CLR will actually win the performance foot race if the T-SQL is properly written for performance. Even when T-SQL does lose the performance footrace, it's many times so close to the performance of CLR's that it's just not worth the time and effort to maintain a separate code base outside of T-SQL.[/quote]A good point and well taken. Still there are some times where CLR can make sense, particularly if you want to do things with Regex for instance. Also, Itzik Ben-Gan showed that at least for some types of large data sets CLR outperforms T-SQL in running aggregates. There are some details here: [url=http://www.sqlmag.com/articles/index.cfm?articleid=102336]http://www.sqlmag.com/articles/index.cfm?articleid=102336 [/url](subscription required.)[/quote]Heh... you apparently haven't read the "running total" article I wrote, have you? ;-)And Matt Miller and I did a fair amount of test several years back and the T-SQL methods and CLR Regex methods pretty much took turns winning even on some of the Regex Replace stuff. I believe those tests (posted on this forum somewhere) have been lost because both Matt and I have looked for them and can't find them.Thu, 24 Dec 2009 17:01:35 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic838388-403-1.aspx[quote][b]RBarryYoung (12/24/2009)[/b][hr]Actually, Jeff, the line-splitting functions and (less often)parsing functions are usually best done as TVF's, whether SQL or CLR. Unfortunately, as Matt pointed out, CLR TVF's by default do not want to do a streaming return. Solomon's "trick" here is really quite significant. Up till now, I have not even heard any of the "Big Guns" in CLR's say anything about it other than to complain about the situation. [/quote]Ok, so English lesson's aside ;-), I really cannot lay claim to this being my technique. I was hoping to indicate in the article that this is just something that I came across in my workings with SQLCLR. I think I picked it up from my last article dealing with Streaming INTO SQL Server from an application and figured I would give the "yield return" syntax a shot in a TVF and was very pleasantly surprised when it worked. However, I have since done some searches on "yield return" and have found that a few other people have been doing this so maybe it is documented somewhere. Unfortunately the examples are all in blog posts about something not working correctly and not attempting to show a good way of doing TVFs. Hence nobody looking for "how to do a streaming TVF" (or maybe, "how to do a TVF properly" since I am not sure why anyone would choose to store the entire collection in memory, unless maybe you needed to do secondary logic over the set) will ever find those posts. This is why I felt it was important to do this article: because, as you mentioned, for some odd reason nobody else has.Now, I don't really know much about VB.Net but I did mention in my article that there is the standard implementation of IEnumerator that does the same thing. I would have to assume that while the examples are in C# that they would work the same in VB.Net since they do not rely upon the "yield" construct. Here are two:1) [url]http://www32.brinkster.com/srisamp/sqlArticles/article_46.htm[/url] (bottom half of the page)2) [url]http://sqlblog.com/blogs/adam_machanic/archive/2009/04/28/sqlclr-string-splitting-part-2-even-faster-even-more-scalable.aspx[/url]Hopefully one or both of those helps you do the same thing in VB.Net. True, it won't be a single-line like "yield return", but I think the end-result performance gain / memory reduction will be the same.Take care,Solomon...Thu, 24 Dec 2009 15:38:04 GMTSolomon Rutzkyhttp://www.sqlservercentral.com/Forums/Topic838388-403-1.aspx[quote][b]RBarryYoung (12/24/2009)[/b][hr][quote][b]Gift Peddie (12/24/2009)[/b][hr][quote][b]RBarryYoung (12/24/2009)[/b][hr][quote][b]Gift Peddie (12/23/2009)[/b][hr][quote][b]RBarryYoung (12/23/2009)[/b][hr]C# "[font="Courier New"]yield break[/font]"? Yikes!I don't suppose there's a way to implement your example in VB.net, is there? (I don't know of any way to directly emulate [font="Courier New"]yield break[/font] in VB.)[/quote]No yield is one those C# only definition because per Microsoft there is no valid reason to define it in VB.[/quote]I already indicated that I knew that, Gift. Despite that fact that there is no deterministic general conversion for C# [font="Courier New"]yield break[/font], it is quite often possible to work out a case-specific method that can enable the same functionality in VB.net. Again, entirely dependent on the specific case, in this case, Solomon's clever method for streaming CLR-TVF data returns. It would be [i]extremely[/i] valuable to me to be able to duplicate this performance in VB.net.[/quote]I did not say you don't know that, I actually wanted to point out the yield being an iterator dependence on C# before your post.[/quote]Actually you did in fact imply exactly that, Gift:[quote][b]Gift Peddie[/b][hr][quote][b]RBarryYoung[/b][hr]C# "[font="Courier New"]yield break[/font]"? Yikes!I don't suppose there's a way to implement your example in VB.net, is there? (I don't know of any way to directly emulate [font="Courier New"]yield break[/font] in VB.)[/quote][b][i]No[/i][/b] yield is one those C# only definition because per Microsoft there is no valid reason to define it in VB.[/quote]Note your use of the word [b][i]No[/i][/b]. In English that means that you are indicating that something that I said was wrong or incorrect. I am unaware of anything that I said that was incorrect.[/quote]Thanks for the English lesson but that was not my intention and, English lesson given to a second generation English very funny.And I thought this was a SQL Server forum. Note to self proofread before post.Thu, 24 Dec 2009 13:44:22 GMTGift Peddie