InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 18 Jun 2013 00:15:13 GMT20http://www.sqlservercentral.com/Forums/Topic437688-203-1.aspx[quote][b]Tom Brown (8/21/2008)[/b][hr]JeffIts brilliant. But how on earth can anyone be expected to figure out the STUFF(... WHERE ... FOR XML PATH('') Syntax - especially when there is no XML involved!I'll be using this, but I'm a bit wary as I don't know exactly how it works[/quote]Thanks... I wish I could take credit for it, but I can't.I haven't done an indepth analysis of the method, but I've read that it tries to make an XML path out of the individual items and, because the "root" is supressed, it generates not tags. The "STUFF" is just to kill the leading comma.Sat, 23 Aug 2008 10:51:38 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic437688-203-1.aspxJeffIts brilliant. But how on earth can anyone be expected to figure out the STUFF(... WHERE ... FOR XML PATH('') Syntax - especially when there is no XML involved!I'll be using this, but I'm a bit wary as I don't know exactly how it worksThu, 21 Aug 2008 09:29:23 GMTTom Brownhttp://www.sqlservercentral.com/Forums/Topic437688-203-1.aspxThanks for the additional reply. I wish there was an easy escape character or set of characters to say "hey, please print this text as-shown". Sort of like doubling a single quote in a string to let SQL Server know that it really is just a single quote.Thu, 20 Mar 2008 10:50:03 GMTJJ Bhttp://www.sqlservercentral.com/Forums/Topic437688-203-1.aspxThat's what I basically did (after about 10 edit attempts). I italicized everything after the ampersand. It appears that when the parser for this editor sees an ampersand it wipes out all alphabetic characters up to the semicolon. So the trick of using &[i]amp;gt;[/i] for instance, wipes out the [i]gt;[/i] but converts the &[i]amp;[/i] to an ampersand. Seems like it might be a bug in the editor control. Apparently when it encounters a nonalphabetic character like [ it acts as a break between the ampersand and the alphabetic characters.Thu, 20 Mar 2008 10:44:42 GMTMike Chttp://www.sqlservercentral.com/Forums/Topic437688-203-1.aspxMatt: Great tip! I used it on my earlier post and it worked. Thanks.Thu, 20 Mar 2008 09:42:17 GMTJJ Bhttp://www.sqlservercentral.com/Forums/Topic437688-203-1.aspx[quote][b]JJ B (3/20/2008)[/b][hr]Mike: Thanks for looking into it. Glad to know I didn't miss something terribly obvious.re: "replace required on certain characters. "I would add: & # x 0 D ; as characters that may need replacing. (without the spaces. I can't figure out how to get this to display as-is. What did you do to get the above encodings to appear as-is?)[/quote]I usually "con" it by "bolding" one of the characters in the string. by having the tag in the middle of the string - HTML doesn't "kick in " and encode it.As in - (x is bolded)&#[b]x[/b]0D;Thu, 20 Mar 2008 09:34:45 GMTMatt Miller (#4)http://www.sqlservercentral.com/Forums/Topic437688-203-1.aspxMike: Thanks for looking into it. Glad to know I didn't miss something terribly obvious.re: "replace required on certain characters. "I would add: & # x 0 D ; as characters that may need replacing. (without the spaces. I can't figure out how to get this to display as-is. What did you do to get the above encodings to appear as-is?)Thu, 20 Mar 2008 09:30:23 GMTJJ Bhttp://www.sqlservercentral.com/Forums/Topic437688-203-1.aspx[quote][b]JJ B (3/19/2008)[/b][hr]Mike: I'd be interested to know if you ever get the TYPE option to work. I had read about it and tried it, but it never worked for me. Then again, I may have been doing something wrong.[/quote]I just tried it and it didn't change the outcome :( I'll play around with it later and see if I can get it working. It may end up being a case where REPLACE is required on the 5 entities:[code]&[i]lt[/i]; &[i]gt[/i]; &[i]quot[/i]; &[i]apos[/i]; &[i]amp[/i];[/code]Wed, 19 Mar 2008 14:46:21 GMTMike Chttp://www.sqlservercentral.com/Forums/Topic437688-203-1.aspxMike: I'd be interested to know if you ever get the TYPE option to work. I had read about it and tried it, but it never worked for me. Then again, I may have been doing something wrong.Wed, 19 Mar 2008 14:27:48 GMTJJ Bhttp://www.sqlservercentral.com/Forums/Topic437688-203-1.aspxMatt: I've run into that very problem on more than one occasion. Sometimes I do a replace() to fix the problem. Sometimes I can change the text to avoid the encoding (?not sure if that's the right term) text. Here's another trick. What if you want a line return between each row instead of a comma? You might be tempted to use the following to create a new row:[quote] SELECT Char(13) + Char(10) + MyColumn ... [/quote]But if you do that, you get this garbeldyguk at the end of each line: "&#[b]x[/b]0D[b];[/b]" Ugh. The trick is to do the Char(10) by itself without the Char(13). Then you get line returns without the added text. This may be obvious to people, but it wasn't to me. It took some playing to figure it out. So, I thought I would share.Wed, 19 Mar 2008 14:24:17 GMTJJ Bhttp://www.sqlservercentral.com/Forums/Topic437688-203-1.aspxThat shouldn't be a problem with comma-separated lists of numbers, but for character data it could present a problem. You might be able to use the TYPE directive to avoid this, but I don't have a chance to test it fully right now.Wed, 19 Mar 2008 14:18:10 GMTMike Chttp://www.sqlservercentral.com/Forums/Topic437688-203-1.aspx[quote][b]Jeff Moden (2/22/2008)[/b][hr]Thanks for the feedback guys... Ryan and Mike C... thanks for the performance comparisons. That's good stuff. ;)[/quote]Sorry to resurrect this - but I came across a caveat the other day with the FOR XML method listed above...It tends to "HTMLize" certain characters.For example, if your character contains "&" as a character... it gets turned into &[b]amp;[/b]. Makes a little sense, since regular XML would do that, but can make for some nasty surprises if you don't expect it. I didn't get a change to try, but I'd imagine it may have trouble with < and > as well.Now - you CAN run replace on the result - but that's going to slow stuff down.....Caveat Emptor.....Wed, 19 Mar 2008 13:53:01 GMTMatt Miller (#4)http://www.sqlservercentral.com/Forums/Topic437688-203-1.aspxThanks for the feedback guys... Ryan and Mike C... thanks for the performance comparisons. That's good stuff. ;)Fri, 22 Feb 2008 20:19:06 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic437688-203-1.aspxI just knew this article would come in handy.And you see, it only took 52 days before I got to it. :w00t:I used the stuff example, because there was a requirement of a devteam to generate dynamic routing-numbers based on the contatenated MachineIds of ceveral rows for a certain orderid,ordered by a sequence number.Thanks again for sharing the knowledge :smooooth:Fri, 22 Feb 2008 08:36:45 GMTALZDBAhttp://www.sqlservercentral.com/Forums/Topic437688-203-1.aspxOn the 1,000,000 row example with clustered and nonclustered index I get 16.7 seconds with this one:[code]DBCC FREEPROCCACHEDBCC DROPCLEANBUFFERSGOSET STATISTICS TIME ONGOSELECT d.SomeID, dbo.fnConcatTest(d.SomeID) AS CSVString FROM ( --==== Derived table "d" finds the unique SomeID's first SELECT DISTINCT SomeID FROM dbo.TestData) d[/code]This drops to 15.8 seconds if I replace the DISTINCT with GROUP BY SomeID in the subquery. I get 10.6 seconds with this one:[code]DBCC FREEPROCCACHEDBCC DROPCLEANBUFFERSGOSET STATISTICS TIME ONGOSELECT t1.SomeID,REPLACE (( SELECT t2.SomeCode AS 'data()' FROM dbo.TestData t2 WHERE t1.SomeID = t2.SomeID FOR XML PATH('')), ' ', ',') AS CSVStringFROM dbo.TestData t1GROUP BY t1.SomeID[/code]And 9.3 seconds with this one:[code]DBCC FREEPROCCACHEDBCC DROPCLEANBUFFERSGOSET STATISTICS TIME ONGOSELECT t1.SomeID, STUFF((SELECT ',' + t2.SomeCode FROM dbo.TestData t2 WHERE t1.SomeID = t2.SomeID FOR XML PATH('')),1,1,'') AS CSVStringFROM dbo.TestData t1GROUP BY t1.SomeID[/code]Of course YMMV.Mon, 18 Feb 2008 10:50:30 GMTMike Chttp://www.sqlservercentral.com/Forums/Topic437688-203-1.aspxWhat are you using to measure the times with? Typically, I'll use SET STATISTICS TIME ON... the CPU time is the actual time it took sans any display. The Duration includes the time to display.Mon, 18 Feb 2008 10:11:27 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic437688-203-1.aspxHello Everyone,I'm getting results back in my examples that the function query is actually faster than the XML query.The following function query returns in a round trip of 9 seconds.[code]SELECT SomeID, dbo.fnConcatTest(SomeID) AS CSVStringFROM dbo.TestDataGROUP BY SomeID[/code]The following XML and STUFF() query returns in a round trip of 10 seconds.[code]SELECT t1.SomeID, STUFF(( SELECT ',' + t2.SomeCode FROM dbo.TestData t2 WHERE t1.SomeID = t2.SomeID FOR XML PATH('')),1,1,'') AS CSVStringFROM dbo.TestData t1GROUP BY t1.SomeID[/code]The following XML and data() query returns in a round trip of 12 seconds.[code]SELECT t1.SomeID, REPLACE (( SELECT t2.SomeCode AS 'data()' FROM dbo.TestData t2 WHERE t1.SomeID = t2.SomeID FOR XML PATH('')), ' ', ',') AS CSVStringFROM dbo.TestData t1GROUP BY t1.SomeID[/code]Are these queries' times in the same order with your results?Regards,RyanMon, 18 Feb 2008 09:28:26 GMTRyan-487455http://www.sqlservercentral.com/Forums/Topic437688-203-1.aspxCool... thanks for the feedback, Ian. The way the standards were created is kind of interesting... after a year at my current company, I saw enough crap and ran into enough problems to literally write a book about it. Every single item in the standards is from an actual problem in someone's code at work. For example, you'll run into a standard about how updates with a join should be written (object of the update [i]must [/i]be included in the FROM clause)... that standard came about when an update that should have taken 20 seconds, slammed 4 CPU's into the wall for 2 hours.Some standards like the one about not updating an "alias" is meant to do two things... 1 is to force the developers to write code that will never force self recompilation for every row updated and to make it easier to search for tables in SysComments that are being updated because of all the cruddy RBAR everyone had in their code. That's one of the changes we were going to make... we were going to start allowing the object of an Update to be an alias just to simplify some code.Wed, 13 Feb 2008 10:54:01 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic437688-203-1.aspx[quote][b]Jeff Moden (2/12/2008)[/b][hr]Ian, my apologies for the delay in this matter... please see the attached. Also, keep in mind that "XXX" throughout the document is where the company name went.We were also going to release another minor update, but other priorities got in the way.[/quote]What, you mean you aren't just sitting around with nothing to do like I am... (sorry, I just can't say that with a straight face:hehe: ).Seriously though, thanks for the document (besides a 1 month turn around for something in IT is really good ;)). I will have to see how much of this I can get implemented at my office. If anything, I will keep it in my toolbox. I see some things that are going to force me to do some learning, which is cool.Thanks,Wed, 13 Feb 2008 07:47:32 GMTIan Crandellhttp://www.sqlservercentral.com/Forums/Topic437688-203-1.aspx[quote][b]Ian Crandell (1/9/2008)[/b][hr][quote][b]Jeff Moden (1/8/2008)[/b][hr]We've actually put some "documentation standards" into play at work. They're not so limiting as to suppress innovation, but they do state what must be documented within the code. Basically, it's the way I've always written code because I forget stuff (done... next problem please!). ;)[/quote]I know I have seen some of your commenting in your posts, but could you provide some of the "documentation standards" you use. After working with some of our legacy stored procedures, I would like to try to implement something like that at my office.Also, in the article there was a code block that didn't show up properly. It was after[quote]The next thing folks might try, is a full "covering" index...[/quote]Could you provide what was put there?Thanks,[/quote]Ian, my apologies for the delay in this matter... please see the attached. Also, keep in mind that "XXX" throughout the document is where the company name went.We were also going to release another minor update, but other priorities got in the way.Tue, 12 Feb 2008 22:27:03 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic437688-203-1.aspxJust an update from my earlier email and our friends with the count(*)'s :DThe developers `moved some tables` and `made some tweaks`, gave us an update :)15 minute process now takes 1 :PI wonder if their other customers noticed!Martin :)Tue, 12 Feb 2008 06:33:11 GMTMartin Bastablehttp://www.sqlservercentral.com/Forums/Topic437688-203-1.aspxMore interesting thing on how to format the concatenated string[url=http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254#344547]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254#344547[/url]Tue, 12 Feb 2008 01:28:15 GMTSwePesohttp://www.sqlservercentral.com/Forums/Topic437688-203-1.aspx[quote][b]timothyawiseman (1/9/2008)[/b][hr]Awesome article. There are not nearly enough resources focusing on code efficiency and even fewer on making code legible in my opinion, and this one was well written and articulate.[/quote]Thanks, Timothy :blush:On the same note of "making code legible", it's just amazing to me how many people don't take pride in their code. I believe I've said on these forums before... my four major rules of writing code are "Make it work, make it fast, make it pretty, and it ain't done 'till it's pretty". :PHeh... Lot's of folks think I do the documentation and readability thing for them... it's not that at all... I'm old and can't remember too much more without forgetting something else so I [i]have [/i]to document the code so I remember what [i]I [/i]did. :hehe:Again, thanks for the great compliment.Wed, 09 Jan 2008 16:56:17 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic437688-203-1.aspx[quote]Also, in the article there was a code block that didn't show up properly. It was afterThe next thing folks might try, is a full "covering" index...Could you provide what was put there?[/quote]Funny how these things work out... I can see it just fine... but here's what it says...[quote]CREATE INDEX IXC_TestData_Cover1 ON dbo.TestData(SomeID,SomeCode,RowNum)[/quote][quote]I know I have seen some of your commenting in your posts, but could you provide some of the "documentation standards" you use. After working with some of our legacy stored procedures, I would like to try to implement something like that at my office.[/quote]Sure... I gotta get it from work...Wed, 09 Jan 2008 16:49:13 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic437688-203-1.aspxAwesome article. There are not nearly enough resources focusing on code efficiency and even fewer on making code legible in my opinion, and this one was well written and articulate.Wed, 09 Jan 2008 15:56:12 GMTtimothyawisemanhttp://www.sqlservercentral.com/Forums/Topic437688-203-1.aspx[quote][b]Jeff Moden (1/8/2008)[/b][hr]We've actually put sime "documentation standards" into play at work. They're not so limiting as to suppress innovation, but they do state what must be documented within the code. Basically, it's the way I've always written code because I forget stuff (done... next problem please!). ;)[/quote]I know I have seen some of your commenting in your posts, but could you provide some of the "documentation standards" you use. After working with some of our legacy stored procedures, I would like to try to implement something like that at my office.Also, in the article there was a code block that didn't show up properly. It was after[quote]The next thing folks might try, is a full "covering" index...[/quote]Could you provide what was put there?Thanks,Wed, 09 Jan 2008 07:14:15 GMTIan Crandellhttp://www.sqlservercentral.com/Forums/Topic437688-203-1.aspxThanks for the great feedback, Ian... especially about keeping it casual (ie. fun) and the remarks about documentation... We've actually put sime "documentation standards" into play at work. They're not so limiting as to suppress innovation, but they do state what must be documented within the code. Basically, it's the way I've always written code because I forget stuff (done... next problem please!). ;)Tue, 08 Jan 2008 17:03:03 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic437688-203-1.aspx[quote][b]Jeff Moden (1/3/2008)[/b][hr][quote][b]Martin Bastable (1/3/2008)[/b][hr]Jeff:I loved your comments on embedded documentation. I do that all the time, I find it helps me loads! (And other developers here when needs be). Even though I get some ear ache from developer friends who strongly belive in not commenting, for various reasons :)Martin[/quote]Thanks for the feedback, Martin... yeah... I just wish more folks felt like we do... life would be a lot easier in the code world. And, yeah, I've got "friends" like that, too. Someday they'll learn... :D[/quote]Jeff,Another great article. I like how you made it more personable and not quite so textbook. I enjoy reading your articles and your posts.I completely agree that a lot of performance issues can be traced back to the code. I worked on some code last week that would run 2 select statements (by the way it was SELECT *) for every employee to get 3 values! In one case it was running against 140 employees. I was able to get all of it down to one SELECT statement.Fortunately, there are some programmers (like myself) that try to comment as much as possible and hate lack of documentation. Reading code (often poorly written) with no comments or proper formatting can be frustrating (I have to deal with that now). I have seen too many SPs that would benefit from embedded documentation.Tue, 08 Jan 2008 13:08:01 GMTIan Crandellhttp://www.sqlservercentral.com/Forums/Topic437688-203-1.aspxYes, thank you for the feedback and I agree that a clustered index will, in fact, make the concatenation run [i]much [/i]faster... except that most folks aren't going to dedicate the power and functionality of the clustered index to a concatenation function.Fri, 04 Jan 2008 07:55:37 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic437688-203-1.aspxHi Jeff,I've seen a lot of discussions here but no one mentioned indexes and choice of clustered one. What you can find in practice is that db designers usually create primary key and put it as clustered without thinking on usage of the table.OK, if you change indexes in your example and set IX as clustered one instead of PK you will see the difference (even if estimation plan says it is more expensive). On my 2005 box (like yours but with 2GB RAM) it runs 2 sec comparing to 10 sec with original one.Fri, 04 Jan 2008 07:42:54 GMTNebojsa Ilichttp://www.sqlservercentral.com/Forums/Topic437688-203-1.aspxAwesome... thanks for the feedback, Derek!Fri, 04 Jan 2008 06:57:53 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic437688-203-1.aspxHi Jeff,Just wanted to say that I too think this is a great article - well written with plenty of food for thought.I've got several complex queries I need to look into improving and will be thinking now of the difference between DISTINCT and GROUP BY and the possible need to use subqueries or DTEs as well as considering the UDF call overhead (which I was already very aware of).The final query in the article has also prompted me to mentally raise the priority on my task to look into what can be done with XML. It looks like there could be some useful tricks there.Keep up the *very* good work.ThanksDerek.Fri, 04 Jan 2008 06:20:59 GMTDerek Dongrayhttp://www.sqlservercentral.com/Forums/Topic437688-203-1.aspxJust trying to clarify some details of why the exec plan doesn't show the 4 million rows. If you'd prefer, I'll remove the postsI agree that the exec plan doesn't show everything. The root problem is that no details of what compute scalar did ever shown, even if that compute had an execution plan of its own. I don't believe the cost % of the comute scalar can ever be trusted either. The row counts as they are shown are correctThu, 03 Jan 2008 23:52:03 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic437688-203-1.aspxNot sure why you're whackin' at me on this one, Gail... real fact of the matter is that 4 million internal rows were generated and they don't show on the execution plan... that's what I meant by the execution plan lied... that's all I was trying to make people aware of... ;)Thu, 03 Jan 2008 23:40:03 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic437688-203-1.aspx[quote][b]Jeff Moden (1/3/2008)[/b][hr]No... my turn to disagree... execution plan showed 10,000 rows... there were only 25 in the result set...[/quote]There are only 25 in the final result set, as shown by the left-most arrow on the exec plan (the one that runs to the SELECT operator). I'm talking about the result sets passed from one operator in the query to another10 000 rows were retrieved from the index scan. That internal resultset was then passed to the compute scalar that operated on each of those 10 000 rows (reading another 400 or so for each of those 10 000, but not showing that info anywhere) and output 10 000 rows. The result set from the compute scalar (still 10 000 rows) then went to the distinct operator that operated on those 10 000 rows and outputted a result set of 25. That result set was then passed to the SELECt operator and hence returned to the client.Thu, 03 Jan 2008 23:29:57 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic437688-203-1.aspx[quote]No, they won't. At best an exec plan shows the number of rows that each operator accepted, processed and returned, but that's the number of rows in the result set, not the number touched to get that result set.[/quote]No... my turn to disagree... execution plan showed 10,000 rows... there were only 25 in the result set...Thu, 03 Jan 2008 23:24:19 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic437688-203-1.aspx[quote][b]Jeff Moden (1/3/2008)[/b][hr]Hi Gail,I guess my point was that the execution plans aren't always going to show the number of rows touched [/quote]No, they won't. At best an exec plan shows the number of rows that each operator accepted, processed and returned, but that's the number of rows in the result set at that point in the query processing, not the number touched by the operator to get that result set.[quote]and that you cannot always determine which of two or more code snippets will prove to be the most effective just by comparing execution plans.[/quote]That's a fact. Only way to determine which piece of code is more efficient is to run it. Exec plan, and stats IO help, nothing more.Edit: Clarifying a bit.Thu, 03 Jan 2008 23:01:08 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic437688-203-1.aspxHi Gail,I guess my point was that the execution plans aren't always going to show the number of rows touched and that you cannot always determine which of two or more code snippets will prove to be the most effective just by comparing execution plans.Thu, 03 Jan 2008 17:09:27 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic437688-203-1.aspxNice article, and a nice informal tone.I am going to argue one point though. You say[quote]Even the estimated and actual execution plans lie! Both say that we're only processing (touching) 10,000 rows.[/quote]Actually, the exec plan isn't lying. The 10 000 rows count is for the initial index scan on the TestData table to get the 10 000 SomeIDs from TestData. Not for the total number of rows touched in total for the query.There are only 10 000 rows flowing from one operator to another in the plan. The problem with the exec plan is that the compute scalar operator doesn't mention that it is also doing reads, and is reading around 400 rows for each row of the 10 000 that it processes.I'm curious to know if there's some mention of that in the xml plan on 2005. I've noticed before that there's lots of info in the xml plan that isn't displayed in the graphical rendition. Will test tomorrow cause management studio is foobar on my home machine.What does SET Statistics IO on show? My guess is somewhere around 10 001 scans of the table. That should be a red flag for anyone trying to performance tune a bad concatenation query. As should the indicator showing that 10 000 rows are going into the compute scalar operator.Thu, 03 Jan 2008 13:00:43 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic437688-203-1.aspx[quote][b]Martin Bastable (1/3/2008)[/b][hr]Jeff:I loved your comments on embedded documentation. I do that all the time, I find it helps me loads! (And other developers here when needs be). Even though I get some ear ache from developer friends who strongly belive in not commenting, for various reasons :)Martin[/quote]Thanks for the feedback, Martin... yeah... I just wish more folks felt like we do... life would be a lot easier in the code world. And, yeah, I've got "friends" like that, too. Someday they'll learn... :DThu, 03 Jan 2008 10:42:14 GMTJeff Moden