InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 03:08:42 GMT20http://www.sqlservercentral.com/Forums/Topic966348-2761-1.aspxNice article!Tue, 25 Dec 2012 08:12:03 GMTNeha05http://www.sqlservercentral.com/Forums/Topic966348-2761-1.aspxNice article.Mon, 24 Dec 2012 12:20:09 GMTNeha05http://www.sqlservercentral.com/Forums/Topic966348-2761-1.aspx[quote][b]N.Johnson (11/5/2012)[/b][hr]Just at the time when we're trying to find a way to improve our data import this article comes along. Perhaps it is a God-send.While your article does not answer all of the questions it may provide a jumping-off point to leap to a solution -- after we put our heads to our challenge. Our current method ties up the computer for an hour and a half bringing in the data and we'd like something faster.Here is our challenge: We receive multiple tables of data in a pipe-delimited text file.Each table begins with 2 rows that identify the columns (one row with a column "name", the second row with a "field-code" for the column).Thereafter come the data rows until we encounter the next TABLE headers.The method in this article may pave the way to (1) parse the table headers to build temporary tables, and then (2) insert the table data into the temporary table. The method may also lead us to a way to extract selected columns for insertion into the final data tables.Thanks, again, for this timely article (at least for us it's timely).[/quote]Hi Norm,I recommend that you actually write a separate post about this subject. Parsing input files usually is quite different than shredding a line or parameters. For example, the use of BULK INSERT would probably do much better for you. Once you start the separate thread, please feel free to PM me with the URL for the thread and I'll try to take a look at it. For sure, there will be many others to respond with some great ideas, as well.Mon, 05 Nov 2012 10:36:31 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic966348-2761-1.aspx[quote][b]Peter Di (11/5/2012)[/b][hr]Jeff, I agree with you. Having thousands or millions of calls to a procedure like this will be a problem. However stored procedures with multiple parameters are not used widely and usually have much slower part after the parsing. Also, as you can see in the article, there are other ways to send values instead of using comma separated strings ... so optimizing the parsing is not the thing I will start with.Anyway , you are right that there is a faster way to parse the values and it will be stupid to ignore it ( especially if you already tested and prepared the functions for an easy implementation) . If you don't mind I will update my article to include one of the methods in your test and to include a link to the full article.[/quote]Thanks for the feedback, Peter.We must work in different worlds, though. Most of our stored procedures do contain multiple parameters and round trip time for the GUI has been greatly improved by redacting hfow they're currently handled both in their parsing and post-parse processing. In fact, such has been the case for the last 4 companies I've worked at.The other thing about parsing is that if someone finds a parsing "routine" that works well for one row, they might try to use it on thousands of rows without knowing the performance ramifications. So I bring that fact up even when something is "guaranteed" to only be used for one row.It's all positive proof that "It Depends". :-)Mon, 05 Nov 2012 10:32:39 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic966348-2761-1.aspxJust at the time when we're trying to find a way to improve our data import this article comes along. Perhaps it is a God-send.While your article does not answer all of the questions it may provide a jumping-off point to leap to a solution -- after we put our heads to our challenge. Our current method ties up the computer for an hour and a half bringing in the data and we'd like something faster.Here is our challenge: We receive multiple tables of data in a pipe-delimited text file.Each table begins with 2 rows that identify the columns (one row with a column "name", the second row with a "field-code" for the column).Thereafter come the data rows until we encounter the next TABLE headers.The method in this article may pave the way to (1) parse the table headers to build temporary tables, and then (2) insert the table data into the temporary table. The method may also lead us to a way to extract selected columns for insertion into the final data tables.Thanks, again, for this timely article (at least for us it's timely).Mon, 05 Nov 2012 08:16:18 GMTN.Johnsonhttp://www.sqlservercentral.com/Forums/Topic966348-2761-1.aspxJeff, I agree with you. Having thousands or millions of calls to a procedure like this will be a problem. However stored procedures with multiple parameters are not used widely and usually have much slower part after the parsing. Also, as you can see in the article, there are other ways to send values instead of using comma separated strings ... so optimizing the parsing is not the thing I will start with.Anyway , you are right that there is a faster way to parse the values and it will be stupid to ignore it ( especially if you already tested and prepared the functions for an easy implementation) . If you don't mind I will update my article to include one of the methods in your test and to include a link to the full article.Mon, 05 Nov 2012 08:15:41 GMTPeter Dihttp://www.sqlservercentral.com/Forums/Topic966348-2761-1.aspx[quote][b]peter.row (11/5/2012)[/b][hr][quote][b]Jeff Moden (11/2/2012)[/b][hr][quote][b]peter.row (11/2/2012)[/b][hr]Hey Diego nice article. I understand what you meant about the implied performance concerns and I found Jeff to be very abrasive virtually to the point of being offensive.[/quote]It would be interesting to know why you actually thought that. Care to explain further? Seriously... I don't like to offend folks but I do also like to get points across about not using RBAR to do such things.[/quote]I think I was having a pre-tea early morning moment and apologise (ironically) for being overly harsh.I guess it was mainly that people were being chastised for not doing stuff based on assumptions on what their use case might be.[/quote]Thanks for the feedback, Peter. I sometimes have the same problem with coffee. :-)Just to clear the air a bit, it's not the immediate use case that I always worry about. It's how other people might use it for something else. It's not always clear to others how badly something can turn because they've just not seen such a thing happen, yet, and, as you saw in my recent previous post, it can take a whole lot of typing trying to avoid sounding harsh.Mon, 05 Nov 2012 05:23:21 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic966348-2761-1.aspx[quote][b]Jeff Moden (11/2/2012)[/b][hr][quote][b]peter.row (11/2/2012)[/b][hr]Hey Diego nice article. I understand what you meant about the implied performance concerns and I found Jeff to be very abrasive virtually to the point of being offensive.[/quote]It would be interesting to know why you actually thought that. Care to explain further? Seriously... I don't like to offend folks but I do also like to get points across about not using RBAR to do such things.[/quote]I think I was having a pre-tea early morning moment and apologise (ironically) for being overly harsh.I guess it was mainly that people were being chastised for not doing stuff based on assumptions on what their use case might be.Mon, 05 Nov 2012 01:21:26 GMTpeter.rowhttp://www.sqlservercentral.com/Forums/Topic966348-2761-1.aspxSpeaking of garbage in - garbage out. In case when I have to split a CSV into a list of items I spend at least one hour trying to understand why some substrings still not spltted until I realised that end-users were able to choose ANY character as a separator. So, here we go again - no proper DESIGN on any layer, starting from UI. And by the way source data store was not a LEGACY system - it was One of the brand new vendor's packaged solutions.Sun, 04 Nov 2012 14:53:32 GMTIrozenberg 1347http://www.sqlservercentral.com/Forums/Topic966348-2761-1.aspx[quote][b]GPO (11/3/2012)[/b][hr][quote][b]Jeff Moden (8/11/2010)[/b][hr]If the data is worth having, it's worth validating.[/quote]I'm gonna print that out and stick it on the wall of my office and make people read it to me every time they ask me to write a report that will magically filter out or "correct" all the junk data entered into [insert shoddy application here].[/quote]:-DSun, 04 Nov 2012 11:36:35 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic966348-2761-1.aspxDamn! I want [b]that [/b]on a sign above my desk. :-D Guess I'll have to settle for a bookmark.Sun, 04 Nov 2012 01:01:24 GMTSDMhttp://www.sqlservercentral.com/Forums/Topic966348-2761-1.aspx[quote][b]Peter Di (11/3/2012)[/b][hr]Jeff, This article is about calling a stored procedure with multiple parameters not for retrieving data from a column with values separated by comma. Do you really believe that it matters if one call with few comma separated values will take 30 milliseconds or 100% more (60 milliseconds)? I agree that there are ways to optimize the parsing methods, but be real, in a year of use you will save 5 seconds processor time and you will spend 1 hour to develop it.[/quote]Very good questions, Peter. Seriously. And, apologies for the long winded answer but good questions deserve thoughtful answers.First, my objections weren't just about time/duration. There was nothing in the code to identify when one or more elements of the passed parameter weren't in the comparison table. The design was missing the necessary validation and feedback. That, of course, could be easily fixed but that would also eat into the hour you're talking about. If it wasn't found, it could cost much more than time as a missed error depending on what it was actually doing.Shifting gears back to the focus of the time related questions. I agree. One call with a few comma separated values against a relatively tiny table that takes 60 or even 600 millseconds isn't going to matter in the grand scheme of an application... until it's no longer one call. If we take the lower number of just 30 milliseconds and put it up against a higher usage application where it might be used a thousand times per second, we're suddenly talking about 30 seconds of CPU time per second. In broader terms, that will take 30 minutes of CPU time for every hour for this simple task when it should take nearly zero minutes per hour. If the method is used in many places in the app for each call, you're suddenly talking about a whole lot of CPU time being used for something so simple. Yeah, I know. "Hardware is cheap". We'll talk about that at the end of this.Ok... agreed. Not everyone is going to have that frequency (1.000 times per second) of usage for an app and it might, in fact, only be used once in the app. As you correctly point out (I'm right there with you on this), is it really worth spending an hour on to make it take only a couple of milliseconds (hopefully, less) instead of 30? The answer is patently "No" but not quite the way you might be thinking. Developers shouldn't have to spend but a minute or two on something like this because they should already know that nearly every app is going to need to handle such things and, unless they're a rank newbie, they should have already studied what the best methods are (combination of 100% accuracy and excellent performance) and have it ready to copy and paste from their library. I guess that's a personal gripe of mine. A lot of people don't study for the job they're supposed to be doing.Now, let's get to the more insidious side of this. Low usage, low row counts, and supposedly low frequency should never be used as an excuse for "good enough" code for several reasons. First and like I pointed out, a Developer should already know what the fast stuff really is and have it at the ready for CPR (Copy, Paste, Replace). Second, there are those developers who haven't taken quite that interest in their job so when they're hit with a tight schedule, they'll use anything they can find so long as it works on one to ten rows (or calls) never giving consideration to the fact that their work will have to scale.I just went through this at work, again, with code very similar to that in the article but backwards in flow of data. Someone wrote some code against a table that everyone "knew" wasn't going to grow by much. The table was supposed to have 28 rows in it and it wasn't likely that it would ever even double. They were right. After 2 years, it should have only 51 rows in it. The trouble is that they didn't want to maintain the table manually so as the number of possible selections started to grow, they used the very same method they previously used (to save development time)but against a data table. When I discovered the problem, the data table had grown to 4.7 million rows and they had "thoughtfully" added a DISTINCT to create the lookup list with a UNION against the original table. The code was only used 1,000 times per hour (low frequency, right?) but it had grown (to >2 seconds per call) to using a total of 40 CPU minutes and more than an eighth of a Terabyte in reads per hour... to return just 51 rows for a drill down menu that was only used 1,000 times per hour.Like I said, it had grown to taking slightly more than 2 seconds to return. Let's see… it gets used by our internal people 1,000 times per hour times 12 hours per day (extended coverage phone center), every 21.7 working days of the month. That's over 144 FTE hours per month that we're paying people to wait for a pulldown menu to load. Considering nearly linear growth of the data table over the two years the code had existed, that's 1,800 hours of wait time we paid people for. Even if a Developer wasn't prepared to handle this eventuality, that's a hell of a trade off compared to the 1 hour of development time you were talking about.It only took me a half hour to discover this problem. It took me about an hour to write the code to fix the problem. The reason why it took so long is because I didn't want someone to have to fix any front-end code to support the fix so it had to be 100% transparent to the GUI and it had to continue to be self maintaining. Just adding the rows to the original table wasn't going to hack that little requirement. Then, it had to go through QA testing where they had to not only verify that the screen was still working correctly but that the underlying data to build the menu was being interpreted correctly. That took one person an hour because they're not database people. In order for them to test what I had done, I had to spend another half hour writing up what I had done and how it worked. That's another 3 hours spent on a problem that could have been avoided by spending an hour to do it right the first time.For the record, I got each call down to where it belongs at about 200 microseconds and the total number of reads down to just 16 megabytes per hour. There was no rocket science involved in the fix, either. Any developer with just a couple of years of experience could have pulled the same thing off in about an hour of original development time.So, with apologies to my good friend, Kevin Boles, and to answer your question as to whether or not I believe people should worry about a small comma delimited list taking "just" 30 milliseconds to be used on a relatively infrequent basis against a relatively small number of rows, my answer is a resounding "YES THEY SHOULD!" even if takes them an hour because you don't know how the method will eventually be used by someone. A whole lot of managers really need to learn this particular lesson when they write a development schedule. Compare the one hour invested to the 1,800 hours of wasted employee time and the several hours coming up with and testing a fix.Why did I apologize to Kevin for this? Because he's an expert performance tuning consultant that gets paid big bucks to fix things like this. There goes more time and money towards something that could have only taken an hour to do right the first time or even the second time they did it… or even just a couple of minutes by someone who knows to expect and is prepared to handle these types of development requirements as a part of their everyday job.Will the particular method in the article ever be exposed to so much growth? Maybe not but you just don't know for sure because requirements change. Plan for the worst so that if it does happen, you don't have to make any repairs just because scale increased. Bullet proof code just doesn't take that much longer to write and it's worth every penny down the road.Sat, 03 Nov 2012 20:31:25 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic966348-2761-1.aspx[quote][b]Peter Di (11/3/2012)[/b][hr]Jeff, This article is about calling a stored procedure with multiple parameters not for retrieving data from a column with values separated by comma. Do you really believe that it matters if one call with few comma separated values will take 30 milliseconds or 100% more (60 milliseconds)? I agree that there are ways to optimize the parsing methods, but be real, in a year of use you will save 5 seconds processor time and you will spend 1 hour to develop it.[/quote]I had precisely that line from a colleague of mine. About a procedure doing just such a simple parameter strip.Sounds like a compelling argument? Not when the procedure was being run [b]a couple million times per day[/b].And yes, the greater performance issue in this case was poor application design, that it was being run more frequently than necessary. But the point is that even micro-performance counts, depending on the context.Sat, 03 Nov 2012 17:35:27 GMTSDMhttp://www.sqlservercentral.com/Forums/Topic966348-2761-1.aspx[quote][b]Jeff Moden (8/11/2010)[/b][hr]If the data is worth having, it's worth validating.[/quote]I'm gonna print that out and stick it on the wall of my office and make people read it to me every time they ask me to write a report that will magically filter out or "correct" all the junk data entered into [insert shoddy application here].Sat, 03 Nov 2012 13:08:50 GMTGPOhttp://www.sqlservercentral.com/Forums/Topic966348-2761-1.aspxJeff, This article is about calling a stored procedure with multiple parameters not for retrieving data from a column with values separated by comma. Do you really believe that it matters if one call with few comma separated values will take 30 milliseconds or 100% more (60 milliseconds)? I agree that there are ways to optimize the parsing methods, but be real, in a year of use you will save 5 seconds processor time and you will spend 1 hour to develop it.Sat, 03 Nov 2012 10:57:54 GMTPeter Dihttp://www.sqlservercentral.com/Forums/Topic966348-2761-1.aspx[quote][b]peter.row (11/2/2012)[/b][hr]Hey Diego nice article. I understand what you meant about the implied performance concerns and I found Jeff to be very abrasive virtually to the point of being offensive.[/quote]It would be interesting to know why you actually thought that. Care to explain further? Seriously... I don't like to offend folks but I do also like to get points across about not using RBAR to do such things.Fri, 02 Nov 2012 15:22:55 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic966348-2761-1.aspx[quote][b]HextallFanForLife (11/2/2012)[/b][hr]Here is a function to do the same thing:SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCreate FUNCTION [dbo].[fnParseStringTSQL](@string NVARCHAR(MAX),@separator NCHAR(1))RETURNS @parsedString TABLE (string NVARCHAR(MAX))AS BEGIN DECLARE @position int SET @position = 1 SET @string = @string + @separator WHILE charindex(@separator,@string,@position) <> 0 BEGIN INSERT into @parsedString SELECT substring(@string, @position, charindex(@separator,@string,@position) - @position) SET @position = charindex(@separator,@string,@position) + 1 END RETURNEND[/quote]If it has a WHILE loop in it, it's going to be slow.Fri, 02 Nov 2012 15:18:48 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic966348-2761-1.aspxIf anyone still cares...I used Jeff's test data (#PostalArea and #CsvTest) and did the split (output = 1,000,000 rows) using the DelimitedSplit8K function and an XML splitter function. The XML method took 2 mins 23 secs whereas the DelimitedSplit8K method took just 19 secs. This was on my local machine which is not a speed demon.Total Execution Time stats in ms (avg of 5 runs)Rows XML Splitter1,000 218.4 58.810,000 1299.6 131.0100,000 9140.3 1116.8Of course, these times will vary from machine to machine.[code="sql"]/* this query took 2 mins 23 secs to run for 1,000,000 rows */IF OBJECT_ID('tempdb..#CsvSplit2') IS NOT NULLDROP TABLE #CsvSplit2 SELECT csv.RowNum, split.[ID] AS ItemNumber, split.[Value] AS Abbreviation INTO #CsvSplit2 FROM #CsvTest csv CROSS APPLY ( SELECT [ID],[Value] FROM dbo.tvfParseDelimitedString(csv.CsvParameter,',') WHERE [ID] > 0 ) split WHERE csv.RowNum > 0 SELECT * FROM #CsvSplit2[/code][code="sql"]/* this query took 19 secs to run for 1,000,000 rows */IF OBJECT_ID('tempdb..#CsvSplit3') IS NOT NULLDROP TABLE #CsvSplit3 SELECT csv.RowNum, split.ItemNumber, split.Item AS Abbreviation INTO #CsvSplit3 FROM #CsvTest csv CROSS APPLY ( SELECT ItemNumber, Item FROM dbo.tvfDelimitedSplit(csv.CsvParameter,',') ) splitSELECT * FROM #CsvSplit3[/code]Fri, 02 Nov 2012 14:16:27 GMTSteven Willishttp://www.sqlservercentral.com/Forums/Topic966348-2761-1.aspxHere is a function to do the same thing:SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCreate FUNCTION [dbo].[fnParseStringTSQL](@string NVARCHAR(MAX),@separator NCHAR(1))RETURNS @parsedString TABLE (string NVARCHAR(MAX))AS BEGIN DECLARE @position int SET @position = 1 SET @string = @string + @separator WHILE charindex(@separator,@string,@position) <> 0 BEGIN INSERT into @parsedString SELECT substring(@string, @position, charindex(@separator,@string,@position) - @position) SET @position = charindex(@separator,@string,@position) + 1 END RETURNENDFri, 02 Nov 2012 13:25:45 GMTHextallFanForLifehttp://www.sqlservercentral.com/Forums/Topic966348-2761-1.aspx[quote][b]Peter-359208 (11/2/2012)[/b][hr]There is another article from 2008, which covers the same scenario : [b][url=http://www.r-tag.com/Pages/MultivalueParametersWithSQLSP.aspx]Multivalue Parameters With SQL SP[/url][/b]It addition of the 2 methods posted here [b]Comma separated text[/b] and [b]SQL Server table[/b] the other article explains also [b]Bitwise numbers[/b] and [b]XML[/b][/quote]I have to warn you that the fnTextToTable is a multi-line table valued function with a WHILE loop (it's a "nibbler" splitter). That's going to be relatively slow (3 times as slow, in fact). Please see the following article for a much faster 8k splitter.[url]http://www.sqlservercentral.com/articles/Tally+Table/72993/[/url]Fri, 02 Nov 2012 09:35:30 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic966348-2761-1.aspx...Fri, 02 Nov 2012 09:03:17 GMTstolbovoyhttp://www.sqlservercentral.com/Forums/Topic966348-2761-1.aspxThere is another article from 2008, which covers the same scenario : [b][url=http://www.r-tag.com/Pages/MultivalueParametersWithSQLSP.aspx]Multivalue Parameters With SQL SP[/url][/b]It addition of the 2 methods posted here [b]Comma separated text[/b] and [b]SQL Server table[/b] the other article explains also [b]Bitwise numbers[/b] and [b]XML[/b]Fri, 02 Nov 2012 08:55:21 GMTPeter Dihttp://www.sqlservercentral.com/Forums/Topic966348-2761-1.aspxHere is what I've done in the past for supplying complex multi-values parameters to a reporting stored procedure. The user specifies various options for ad-hoc reporting into a form. The application inserts a REPORTRUN table (one row for each report run) and other related tables like REPORTRUN_RULE, which contains one row for each filtering option chosen by the user from a dropdown listbox. What gets passed to the stored procedure that queries a resultset for the report is a single @reportrun_id parameter. I'll also contain things like the start time, end time, and even the actual SQL (which is generated dynamically because it's an ad-hoc report) in REPORTRUN columns. From an operational perspective, I have a historical record of useful stuff like how a report arrived at it's result, utilization, and runtime duration.Fri, 02 Nov 2012 08:34:03 GMTEric M Russellhttp://www.sqlservercentral.com/Forums/Topic966348-2761-1.aspxThis seems fraught with error, as well as inefficient.What if you're doing high volume transaction processing? How will you keep one set of parameters separate from others? Another column with a GUID? Ask the calling code to pass in a unique identifier of its own (user ID or something)?Is each instance going to create a new temporary table and do its own inserts, read them right back, and drop the table?How can this possibly be more efficient than simply processing the values in a loop and using no table (temporary or otherwise) at all?Also, you're going to need validation somewhere, like stripping leading and/or trailing spaces, making sure there are no missing parameters (AZ,,ME), etc., which would be easier to catch in "for each parameter" a loop rather than a ."for each row" loop.Do you recommend making a solution "pure SQL" simply because procedural code "has coodies" or something?Fri, 02 Nov 2012 07:56:30 GMTextremehttp://www.sqlservercentral.com/Forums/Topic966348-2761-1.aspx[quote][b]CELKO (8/12/2010)[/b][hr][quote][b]jhood (8/10/2010)[/b][hr]I see what you are saying Jeff, but would you want to do validation if the application did not allow the end user to send invalid data? perhaps using a drop down list..[/quote]LOL! Why of course nobody would ever send garbage to a database! All users are perfect and the DB programmer should never have to worry about data integrity in his schema. ;-) Want to get rid of all constraints:-P while we are at it?:-PI did an article on using a long parameter list instead of trying to write a home-made parser in T-SQL or contaminating the schema with XML and CLR code. I am still working on Part II with some examples of how the home-made parsers produce different results than the T-SQL compiler parameter parser.[/quote]So true. The user (client, customer, or whatever) may always be right when one is in the business of marketing, website design or bussing tables at Waffle House. However, from a database engineer's perspective, what gets stored in a database on the back end should be objective, not subjective. Joe, it's been a couple of years since your comment above about XML contaminating the schema or transaction SQL processing of a database, has this stance softened any at all, at least in terms of using XML for passing input parameters?Fri, 02 Nov 2012 07:56:07 GMTEric M Russellhttp://www.sqlservercentral.com/Forums/Topic966348-2761-1.aspxGood post on the XML. This is how I prefer to do it since you can just select from the nodes. I find this extremely useful when writing SSRS reports that contain a mutli-value parameter.Fri, 02 Nov 2012 07:52:14 GMTaparker 50461http://www.sqlservercentral.com/Forums/Topic966348-2761-1.aspxI've seen this on SQL Server Central before, so I squirreled away the code where I could always find it when needed. I typically use it in a stored procedure with the parameter as a csv list. It works well when I have a multiple-select dropdown list where, as the developer, I'm building the csv parameter string coming back, so I know that it's well formed, and I don't need any validation. The nice thing about it is that I don't even have to worry about removing the trailing comma. There are three variations that work and if you use a convert(varchar,FieldName) then you can even check integers instead of just strings:declare @statelist varchar(50)select @statelist = '1,2,3,4,10,'select stateid, stateabbrev, statenamefrom states1. where charindex(',' + convert(varchar, stateid) + ',', ',' + @statelist + ',') > 02. where patindex('%,' + convert(varchar, stateid) + ',%', ',' + @statelist + ',') > 03. where ',' + @statelist + ',' like '%,' + convert(varchar, stateid) + ',%' ;I've never had an issue with performance, but I really don't think I've used it on large tables.Fri, 02 Nov 2012 05:46:58 GMTDennis Wagner-347763http://www.sqlservercentral.com/Forums/Topic966348-2761-1.aspxPost deletedFri, 02 Nov 2012 05:10:53 GMTd.burtonhttp://www.sqlservercentral.com/Forums/Topic966348-2761-1.aspxHey Diego nice article. I understand what you meant about the implied performance concerns and I found Jeff to be very abrasive virtually to the point of being offensive.Although Jeff I feel fell foul of making his own assumptions too.The assumption was that you have to validate data because users might be calling this and what-if-this-what-if-that.Missing the obvious case where the DB is only access via an app/product (in our case written in .NET) that accesses the SQL Server DB, the user will never call the SP directly only via the app. In the case of the example given of US states if we were implementing such a thing the user would pick from a list and therefore the SP would not need to validate the input because the app would do that and not allow for free-form user entry. Developer/unit testing and QA testing would in this simple case pick up mistakes.Fri, 02 Nov 2012 02:46:53 GMTpeter.rowhttp://www.sqlservercentral.com/Forums/Topic966348-2761-1.aspxGuys, I must be missing a WOW factor here, but why do we need to re-invent the wheel? The most ROBUST solution wiould be a SINGLE strongly typed XML parameter with bound XSD schema. This would provide a strong typing for any data passed to stored procedure.:cool:Thu, 01 Nov 2012 22:58:22 GMTfregatepalladahttp://www.sqlservercentral.com/Forums/Topic966348-2761-1.aspxThis solution can be useful when we already know the input parameter values. when we do not have fix information for parameter then this may not be applied.Thu, 01 Nov 2012 22:49:28 GMTAmit Gajjarhttp://www.sqlservercentral.com/Forums/Topic966348-2761-1.aspx[quote][b]dbuendiab (8/15/2010)[/b][hr][quote][b]SQLZ (8/13/2010)[/b][hr]This was an interesting article with an even more interesting discussion.The thing I'd like to pick up on, that drew my attention, was your reasons for moving away from the loop method. You commented that you found there was too much coding for so little functionality.The interesting thing though, is that the loop method performs as much as 5 times faster than the alternative method you provided. This becomes apparent when you consider 4000 different states.Now I come across this a lot. I'll look at a piece of code that a developer has produced and I'll wonder why they've adopted a certain approach - very often the answer is "it's a neater solution". My approach is, "sure, but is it a fast solution"?Not withstanding the already mentioned solutions that others have commented on that improve on the loop method, and not withstanding that your intention was probably never to deal with 100's or thousands of states, in my book, if a procedure has 5 lines more code but gives me performance gain by a factor of 5 then it's worth keeping those extra lines. Heck, for 5-times the performance, give me 100 extra lines of code ;).[/quote]I was about to answer Jeff about performance question and your post lights a little the arguments a wanted to expose.One thing I've learned with this my first writting experience is the range of levels your readers have, and the different approach everyone has when reading some other point of view.Having said this, let me tell you why I did not insist on performance issue. As someone stated, Erland's work on arrays is the ultimate reference on methods to split a string of parameters. No substantial things can be added on this question, I think. Moreover, the method I expose is really one of the worst, if you read his work.As I teach SQL from the last year on, the big trouble I've seen in my students is to adopt the set based thinking. They all come from procedural languages, they all are clever and should find how to split a string by the loop method I discard. Why I do it - to show them how to think in SQL. It took me several months to get it, and I think that's the key point to leverage their SQL skills.That is, performance is a concept every programmer, procedural or not, can adquire naturally. SQL thinking is not so straight forward, though. Sadly, in my writting all this considerations where implicit, as I had only my point of view in mind. That's why I thank from heart received criticism, particularly those of Jeff, a little radical in style but right in the bottom.OFF TOPIC> I'm on holidays, I've survived an interoceanic/intercontinental flight between Madrid and San Francisco, via New York - only 18 hours, worth the pay for living two weeks away from SQL. I'll try to check email but it will not be easy - mainly / it's so cool to be on vacation, you know :-D ... See you all, and thank for your inputs.\\Diego BuendiaBarcelona Spain[/quote]Very cool. Thanks Diego.Sun, 15 Aug 2010 11:55:22 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic966348-2761-1.aspx[quote][b]SQLZ (8/13/2010)[/b][hr]This was an interesting article with an even more interesting discussion.The thing I'd like to pick up on, that drew my attention, was your reasons for moving away from the loop method. You commented that you found there was too much coding for so little functionality.The interesting thing though, is that the loop method performs as much as 5 times faster than the alternative method you provided. This becomes apparent when you consider 4000 different states.Now I come across this a lot. I'll look at a piece of code that a developer has produced and I'll wonder why they've adopted a certain approach - very often the answer is "it's a neater solution". My approach is, "sure, but is it a fast solution"?Not withstanding the already mentioned solutions that others have commented on that improve on the loop method, and not withstanding that your intention was probably never to deal with 100's or thousands of states, in my book, if a procedure has 5 lines more code but gives me performance gain by a factor of 5 then it's worth keeping those extra lines. Heck, for 5-times the performance, give me 100 extra lines of code ;).[/quote]I was about to answer Jeff about performance question and your post lights a little the arguments a wanted to expose.One thing I've learned with this my first writting experience is the range of levels your readers have, and the different approach everyone has when reading some other point of view.Having said this, let me tell you why I did not insist on performance issue. As someone stated, Erland's work on arrays is the ultimate reference on methods to split a string of parameters. No substantial things can be added on this question, I think. Moreover, the method I expose is really one of the worst, if you read his work.As I teach SQL from the last year on, the big trouble I've seen in my students is to adopt the set based thinking. They all come from procedural languages, they all are clever and should find how to split a string by the loop method I discard. Why I do it - to show them how to think in SQL. It took me several months to get it, and I think that's the key point to leverage their SQL skills.That is, performance is a concept every programmer, procedural or not, can adquire naturally. SQL thinking is not so straight forward, though. Sadly, in my writting all this considerations where implicit, as I had only my point of view in mind. That's why I thank from heart received criticism, particularly those of Jeff, a little radical in style but right in the bottom.OFF TOPIC> I'm on holidays, I've survived an interoceanic/intercontinental flight between Madrid and San Francisco, via New York - only 18 hours, worth the pay for living two weeks away from SQL. I'll try to check email but it will not be easy - mainly / it's so cool to be on vacation, you know :-D ... See you all, and thank for your inputs.\\Diego BuendiaBarcelona SpainSun, 15 Aug 2010 07:29:38 GMTdbuendiabhttp://www.sqlservercentral.com/Forums/Topic966348-2761-1.aspx[quote][b]Adam Gojdas (8/13/2010)[/b][hr][quote][b]Jeff Moden (8/12/2010)[/b]Someone already said it... I'd love to read that article. Would you post the URL on this thread so we can take a peek? Thanks, Joe.[/quote]Maybe this is the article he was referring to:[url]http://www.simple-talk.com/sql/learn-sql-server/values%28%29-and-long-parameter-lists/[/url][/quote]Thanks, Adam.Fri, 13 Aug 2010 23:47:21 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic966348-2761-1.aspx[quote][b]Jeff Moden (8/12/2010)[/b]Someone already said it... I'd love to read that article. Would you post the URL on this thread so we can take a peek? Thanks, Joe.[/quote]Maybe this is the article he was referring to:[url]http://www.simple-talk.com/sql/learn-sql-server/values%28%29-and-long-parameter-lists/[/url]Fri, 13 Aug 2010 12:22:11 GMTAdam Gojdashttp://www.sqlservercentral.com/Forums/Topic966348-2761-1.aspxThis was an interesting article with an even more interesting discussion.The thing I'd like to pick up on, that drew my attention, was your reasons for moving away from the loop method. You commented that you found there was too much coding for so little functionality.The interesting thing though, is that the loop method performs as much as 5 times faster than the alternative method you provided. This becomes apparent when you consider 4000 different states.Now I come across this a lot. I'll look at a piece of code that a developer has produced and I'll wonder why they've adopted a certain approach - very often the answer is "it's a neater solution". My approach is, "sure, but is it a fast solution"?Not withstanding the already mentioned solutions that others have commented on that improve on the loop method, and not withstanding that your intention was probably never to deal with 100's or thousands of states, in my book, if a procedure has 5 lines more code but gives me performance gain by a factor of 5 then it's worth keeping those extra lines. Heck, for 5-times the performance, give me 100 extra lines of code ;).Fri, 13 Aug 2010 05:43:15 GMTSQLZhttp://www.sqlservercentral.com/Forums/Topic966348-2761-1.aspxThe limitation of the process discussed here is that we need to know the exact list of valid parameters and need them to have it in a table. Though this might be a fullproof way but it is quite limited.In actual scenarios we get "," separated values which are generally userinputs and that too from free flow text boxes. Ifthe user control is a dropdown/listbox the explained way can be implemented effectively.Fri, 13 Aug 2010 01:25:24 GMTkoustav_1982http://www.sqlservercentral.com/Forums/Topic966348-2761-1.aspxPlease use this way to aviod looping.--Please pass through Query stringDeclare @sParameter varchar(max)Begin Set @sParameter = 'select ''WA'' union all select ''AZ'' union all select ''CA'' union all select ''TS''' Declare @tStates table ( state char(2) ) Insert @tStates exec(@sParameter) Select * From @tStatesEndFri, 13 Aug 2010 00:06:01 GMTskumar.klmhttp://www.sqlservercentral.com/Forums/Topic966348-2761-1.aspxAh... I almost forgot... if you don't really want to do any validation against the code... in other words, you just want to do the split, check out the following code...[code="sql"] SELECT csv.RowNum, split.ItemNumber, split.Item AS Abbreviation INTO #CsvSplit3 FROM #CsvTest csv CROSS APPLY ( SELECT ItemNumber, Item FROM dbo.DelimitedSplit8k(csv.CsvParameter,',') ) split[/code]That only takes 9 seconds or so to split out the million elements. When working with splits of 8K or less, the splitter method rivals CLRs. A properly written CLR splitter will still be the Tally Splitter, but not by enough to make it worthwhile for me to have separately compiled code on my box.I'll go back through the thread and find one of those XML splitter solutions to test next.Thu, 12 Aug 2010 21:17:08 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic966348-2761-1.aspxOk... here we go with the first phase of testing. Sorry it takes so long. It takes a bit of time to put such code together and it has to be done absolutely correctly.This test compares the method in the article with a "Tally CTE Splitter". I include the code to build the splitter, the code to build a 100K row 10 element CSV table, the code to build the "State" table, and the test code. The bottom line for all that testing is that when validation AND numbering of elements are added to the splitter method, it still runs about as fast as the method from the article with no such enhancements. Also, check out the profiler runs at the end. If you're one of those that wonders about such things, the number of reads on the method from the article is comparatively brutal.If you don't know me well enough yet, let me tell you that the details of everything that has been done is in the comments in the code. And, YES, by all means... feel free to run the code.Here's the code for the "Tally Splitter". This is what I use in production when the local DBA won't allow me to build a Tally Table. The comments are much longer than the actual code which is quite simple....[code="sql"] CREATE FUNCTION dbo.DelimitedSplit8K/*************************************************************************************************** Purpose: Split a given string at a given delimiter and return a list of the split elements (items). Returns: iTVF containing the following: ItemNumber = Element position of Item as a BIGINT (not converted to INT to eliminate a CAST) Item = Element value as a VARCHAR(8000) CROSS APPLY Usage Example:-----------------------------------------------------------------------------------------------------===== Conditionally drop the test tables to make reruns easier for testing. -- (this is NOT a part of the solution) IF OBJECT_ID('tempdb..#JBMTest') IS NOT NULL DROP TABLE #JBMTest;--===== Create and populate a test table on the fly (this is NOT a part of the solution). SELECT * INTO #JBMTest FROM ( SELECT 1,'1,10,100,1000,10000,100000,1000000' UNION ALL SELECT 2,'2000000,200000,20000,2000,200,20,2' UNION ALL SELECT 3, 'This,is,a,test' UNION ALL SELECT 4, 'and so is this' UNION ALL SELECT 5, 'This, too (no pun intended)' ) d (SomeID,SomeValue);GO--===== Split the CSV column for the whole table using CROSS APPLY (this is the solution) SELECT test.SomeID, split.ItemNumber, split.Item FROM #JBMTest test CROSS APPLY ( SELECT ItemNumber, Item FROM dbo.DelimitedSplit8k(test.SomeValue,',') ) split;--------------------------------------------------------------------------------------------------- Notes: 1. Optimized for VARCHAR(7999) or less. No testing or error reporting for truncation at 7999 characters is done. 2. Optimized for single character delimiter. Multi-character delimiters should be resolved externally from this function. 3. Optimized for use with CROSS APPLY. 4. Does not "trim" elements just in case leading or trailing blanks are intended. 5. If you don't know how a Tally table can be used to replace loops, please see the following... http://www.sqlservercentral.com/articles/T-SQL/62867/ 6. Changing this function to use VARCHAR(MAX) will cause it to run twice as slow. It's just the nature of VARCHAR(MAX) whether it fits in-row or not. 7. Multi-machine testing for the method of using UNPIVOT instead of 10 SELECT/UNION ALLs shows that the UNPIVOT method is quite machine dependent and can slow things down quite a bit. 8. Performance testing shows using "TOP" for the limiting criteria of "N" is actually slower and slightly more CPU intensive than the traditional WHERE N < LEN(@pString) + 2. 9. Performance testing shows using ORDER BY (SELECT x) where "x" is anything is actually slower and slightly more CPU intensive than the traditional ORDER BY (SELECT N). Credits: This code is the product of many people's efforts including but not limited to the following: cteTally concept originally by Iztek Ben Gan and "decimalized" by Lynn Pettis (and others) for a bit of extra speed and finally redacted by Jeff Moden for a different slant on readability and compactness. Hat's off to Paul White for his simple explanations of CROSS APPLY. Finally, special thanks to Erland Sommarskog for his tireless efforts to help people understand what you can actually do with T-SQL. I also thank whoever wrote the first article I ever saw on "numbers tables" which is located at the following URL ... http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-numbers-table.html Revision History: Rev 00 - 20 Jan 2010 - Concept: Lynn Pettis and others. Redaction/Implementation: Jeff Moden - Base 10 redaction and reduction for CTE. (Total rewrite) Rev 01 - 13 Mar 2010 - Jeff Moden - Removed one additional concatenation and one subtraction from the SUBSTRING in the SELECT List for that tiny bit of extra speed. Rev 02 - 14 Apr 2010 - Jeff Moden - No code changes. Added CROSS APPLY usage example to the header, some additional credits, and extra documentation. Rev 03 - 18 Apr 2010 - Jeff Moden - No code changes. Added notes 7, 8, and 9 about certain "optimizations" that dont' actually work for this type of function. Rev 04 - 29 Jun 2010 - Jeff Moden - Added WITH SCHEMABINDING thanks to a note by Paul White. This prevents an unnecessary "Table Spool" when the function is used in an UPDATE statement even though the function makes no external references.***************************************************************************************************/--===== Define I/O parameters ( @pString VARCHAR(7999), @pDelimiter CHAR(1) )RETURNS TABLE WITH SCHEMABINDINGASRETURN--===== "Inline" CTE Driven "Tally Table” produces values up to -- 10,000... enough to cover VARCHAR(8000)WITH E1(N) AS ( --=== Create Ten 1's SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 --10 ), E2(N) AS (SELECT 1 FROM E1 a, E1 b), --100 E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10,000cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT N)) FROM E4) --===== Do the split SELECT ROW_NUMBER() OVER (ORDER BY N) AS ItemNumber, SUBSTRING(@pString, N, CHARINDEX(@pDelimiter, @pString + @pDelimiter, N) - N) AS Item FROM cteTally WHERE N <= LEN(@pString) AND SUBSTRING(@pDelimiter + @pString, N, 1) = @pDelimiter;GO[/code]Here's the code to build the "State" table and the CSV test table. Don't let the number of rows scare you... it only takes 26 seconds to fun on my 8 year old single 1.8GHz CPU desktop box.[code="sql"]--=====================================================================================================================-- Setup the require State Lookup table (#PostalArea) and a test table that contains CSV values.-- As a side bar, don't ever store CSV's in a real table because it violates normalization rules. --=====================================================================================================================--===== Do this testing in a nice safe place that everyone has access to USE TempDB;--===== Conditionally drop temp tables to make it easier to do reruns IF OBJECT_ID('TempDB..#PostalArea','U') IS NOT NULL DROP TABLE #PostalArea --Lookup table contains States and Territories ; IF OBJECT_ID('TempDB..#CsvTest','U') IS NOT NULL DROP TABLE #CsvTest --Contains the CSV's to be split;--===== Create the Postal Area table and populate it. CREATE TABLE #PostalArea ( Abbreviation CHAR(2) PRIMARY KEY CLUSTERED, LongName VARCHAR(30) ) INSERT INTO #PostalArea (LongName, Abbreviation) SELECT 'ALABAMA','AL' UNION ALL SELECT 'ALASKA','AK' UNION ALL SELECT 'AMERICAN SAMOA','AS' UNION ALL SELECT 'ARIZONA ','AZ' UNION ALL SELECT 'ARKANSAS','AR' UNION ALL SELECT 'CALIFORNIA ','CA' UNION ALL SELECT 'COLORADO ','CO' UNION ALL SELECT 'CONNECTICUT','CT' UNION ALL SELECT 'DELAWARE','DE' UNION ALL SELECT 'DISTRICT OF COLUMBIA','DC' UNION ALL SELECT 'FEDERATED STATES OF MICRONESIA','FM' UNION ALL SELECT 'FLORIDA','FL' UNION ALL SELECT 'GEORGIA','GA' UNION ALL SELECT 'GUAM ','GU' UNION ALL SELECT 'HAWAII','HI' UNION ALL SELECT 'IDAHO','ID' UNION ALL SELECT 'ILLINOIS','IL' UNION ALL SELECT 'INDIANA','IN' UNION ALL SELECT 'IOWA','IA' UNION ALL SELECT 'KANSAS','KS' UNION ALL SELECT 'KENTUCKY','KY' UNION ALL SELECT 'LOUISIANA','LA' UNION ALL SELECT 'MAINE','ME' UNION ALL SELECT 'MARSHALL ISLANDS','MH' UNION ALL SELECT 'MARYLAND','MD' UNION ALL SELECT 'MASSACHUSETTS','MA' UNION ALL SELECT 'MICHIGAN','MI' UNION ALL SELECT 'MINNESOTA','MN' UNION ALL SELECT 'MISSISSIPPI','MS' UNION ALL SELECT 'MISSOURI','MO' UNION ALL SELECT 'MONTANA','MT' UNION ALL SELECT 'NEBRASKA','NE' UNION ALL SELECT 'NEVADA','NV' UNION ALL SELECT 'NEW HAMPSHIRE','NH' UNION ALL SELECT 'NEW JERSEY','NJ' UNION ALL SELECT 'NEW MEXICO','NM' UNION ALL SELECT 'NEW YORK','NY' UNION ALL SELECT 'NORTH CAROLINA','NC' UNION ALL SELECT 'NORTH DAKOTA','ND' UNION ALL SELECT 'NORTHERN MARIANA ISLANDS','MP' UNION ALL SELECT 'OHIO','OH' UNION ALL SELECT 'OKLAHOMA','OK' UNION ALL SELECT 'OREGON','OR' UNION ALL SELECT 'PALAU','PW' UNION ALL SELECT 'PENNSYLVANIA','PA' UNION ALL SELECT 'PUERTO RICO','PR' UNION ALL SELECT 'RHODE ISLAND','RI' UNION ALL SELECT 'SOUTH CAROLINA','SC' UNION ALL SELECT 'SOUTH DAKOTA','SD' UNION ALL SELECT 'TENNESSEE','TN' UNION ALL SELECT 'TEXAS','TX' UNION ALL SELECT 'UTAH','UT' UNION ALL SELECT 'VERMONT','VT' UNION ALL SELECT 'VIRGIN ISLANDS','VI' UNION ALL SELECT 'VIRGINIA ','VA' UNION ALL SELECT 'WASHINGTON','WA' UNION ALL SELECT 'WEST VIRGINIA','WV' UNION ALL SELECT 'WISCONSIN','WI' UNION ALL SELECT 'WYOMING','WY';--===== This creates and populates a test table on the fly containing a -- sequential column and a randomly generated CSV Parameter column. SELECT TOP (100000) --Controls the number of rows in the test table ISNULL(ROW_NUMBER() OVER (ORDER BY(SELECT NULL)),0) AS RowNum, ( SELECT CAST(STUFF( --=== STUFF get's rid of the leading comma ( --=== This builds CSV row with a leading comma SELECT TOP (10) --Controls the number of CSV elements in each row ','+Abbreviation FROM #PostalArea pa WHERE ac1.Object_ID <= 2147483647 --Without this, all rows would be the same ORDER BY NEWID() FOR XML PATH('') ) ,1,1,'') AS VARCHAR(8000)) ) AS CsvParameter INTO #CsvTest FROM Master.sys.All_Columns ac1 --Classic cross join pseudo-cursor CROSS JOIN Master.sys.All_Columns ac2 --can produce row sets up 16 million;--===== Wound a couple of rows with some bad data UPDATE #CsvTest SET CsvParameter = REPLACE(CsvParameter,'MH','XX') WHERE CHARINDEX('MH',CsvParameter) > 0;--===== Show the first five rows from each table as a sanity check SELECT TOP (5) * FROM #PostalArea; SELECT TOP (5) * FROM #CsvTest;[/code]Here's the code being tested. Again, read the comments.[code="sql"]--=====================================================================================================================-- And now, with profiler turned on, let's do some testing!--=====================================================================================================================GO--===== Conditionally drop temp tables to make it easier to do reruns IF OBJECT_ID('TempDB..#CsvSplit1','U') IS NOT NULL DROP TABLE #CsvSplit1 --Contains final split data from article method; IF OBJECT_ID('TempDB..#CsvSplit2','U') IS NOT NULL DROP TABLE #CsvSplit2 --Contains final split data from CROSS APPLY splitter;GO--===== Method from the article modified to do a whole table as if there were many hits on a stored procedure. SELECT csv.RowNum, area.Abbreviation INTO #CsvSplit1 FROM #PostalArea area JOIN #CsvTest csv ON CHARINDEX(area.Abbreviation, csv.CsvParameter) > 0;GO--===== The "split" method modified to do a whole table as if there were many hits on a stored procedure. -- Notice that this method not only produces the correct answer, but also numbers the elements for -- each "input row" AND validates ALL the inputs IN THE SAME TIME AS THE METHOD FROM THE ARTICLE.WITHcteValidate AS( SELECT csv.RowNum, split.ItemNumber, split.Item AS Abbreviation FROM #CsvTest csv CROSS APPLY ( SELECT ItemNumber, Item FROM dbo.DelimitedSplit8k(csv.CsvParameter,',') ) split) SELECT checked.RowNum, checked.ItemNumber, checked.Abbreviation, 3-ISNULL(LEN(area.Abbreviation),3) AS IsValid INTO #CsvSplit2 FROM cteValidate checked LEFT OUTER JOIN #PostalArea area ON checked.Abbreviation = area.Abbreviation ;GO[/code]And here's the profiler trace for a couple of runs. Keep in mind that the method from the article isn't doing any validation or enumeration whereas the splitter method is doing both of those.[img]http://www.sqlservercentral.com/Forums/Attachment6802.aspx[/img]Thu, 12 Aug 2010 20:58:50 GMTJeff Moden