The Grouped String Concatenation Challenge is Closed

re: The Grouped String Concatenation Challenge is Closed

Dean — Mon, 16 Mar 2009 01:14:25 GMT

I thought the deadline was midnight, Mar 16? My entry is almost ready to be emailed.

re: The Grouped String Concatenation Challenge is Closed

Adam Machanic — Mon, 16 Mar 2009 01:36:01 GMT

Midnight, GMT. It's now 1:35 a.m.

re: The Grouped String Concatenation Challenge is Closed

Peso — Mon, 16 Mar 2009 07:23:35 GMT

Maybe a culture issue?

For example, when we in Sweden say midnight the 16th of March, we mean 24:00 (23:59:59 + 1 second).

I believe Adam meant 00:00:00 the 16th of March (12:00 AM).

re: The Grouped String Concatenation Challenge is Closed

MontyMole — Mon, 16 Mar 2009 12:19:23 GMT

Are they going to be tested to check that they work correctly with data with an & in them (the sample data doesn't have any) or do they just have to work with the sample data?

re: The Grouped String Concatenation Challenge is Closed

Virgil Rucsandescu — Mon, 16 Mar 2009 13:39:36 GMT

Probably Adam wrote a script to compare each result set with the required result, so probably each solution sent by us will be run against the data in AdventureWorks and if the result doesn't fit exactly with the required data set, then it's out of competition ...

re: The Grouped String Concatenation Challenge is Closed

Adam Machanic — Mon, 16 Mar 2009 13:39:40 GMT

No, I won't be checking for entitization problems, but perhaps I'll give a bonus to those that did include workarounds in their solutions (very few).

re: The Grouped String Concatenation Challenge is Closed

Adam Machanic — Mon, 16 Mar 2009 16:48:24 GMT

Alright, here's the deal: Three people have now e-mailed me saying they misunderstood the meaning of "midnight", and even though I think it's pretty clear I'm going to be nice about it. I'll allow entries through 11:59 p.m. GMT today. But here's the catch: I am going to judge these late entries a bit harder than the rest, because you've had more time and, perhaps, the chance to read about how I'll judge the competition. So anyone submitting late had better send me something really, really good if you want to be a competitor!

re: The Grouped String Concatenation Challenge is Closed

virgilrucsandescu — Mon, 16 Mar 2009 22:33:22 GMT

I made my query with ~ 27% faster, but I suppose it is not honest to re-send it now ... Hopefully the fastest query in this challenge will be even faster than what I have now ;-)

re: The Grouped String Concatenation Challenge is Closed

RBArryYoung — Mon, 16 Mar 2009 23:14:19 GMT

"Next I will run each query twice, and will eliminate any query that doesn't run in less than four seconds on my notebook"

Umm, is it OK to ask about your Notebook's configuration? That'll tell me real fast if I'm in or out... :)

re: The Grouped String Concatenation Challenge is Closed

Peso — Wed, 18 Mar 2009 10:39:48 GMT

It's a good thing I didn't send my suggestion that only required 60k reads for the entire solution!

It runs for 30 seconds... :-)

re: The Grouped String Concatenation Challenge is Closed

Virgil Rucsandescu — Wed, 18 Mar 2009 15:32:49 GMT

Strange... I think I have more reads even in "SET STATISTICS IO" info (my profiler is crazy - it shows ~ 100 times more reads than the stats info) and my query runs in ~ 3 sec (elapsed time). But my desktop is a quad core, I suppose it will need more on a notebook - so I am probably out of the competition. I am really curious if there is a non - FOR XML PATH('') solution that is very fast ...

re: The Grouped String Concatenation Challenge is Closed

RBArryYoung — Wed, 18 Mar 2009 16:31:51 GMT

I fell compelled to point out that comparing entitized XML solutions to corrected (non-entitized) solutions is not very fair since fixning the entitization problems adds almost 30% CPU overhead.

re: The Grouped String Concatenation Challenge is Closed

Adam Machanic — Wed, 18 Mar 2009 17:03:03 GMT

Well it's not like I asked anyone to fix that problem in the code, and I pointed out in the original post that performance should be the main concern. A lot of people, though, insist upon putting their own spin on things, for better or for worse. For example, one person sent me a solution where he used ROUND to take all of the numbers to two digit scale. I don't recall asking for that, and his solution was immediately kicked out for not matching the posted sample data. I just don't get peoples' thought processes sometimes...

Your submission, which did deal with entitization (as I'm sure you're well aware <g>), made it through the first round. We'll see whether it scales under load. It will be an interesting test...

re: The Grouped String Concatenation Challenge is Closed

Peso — Wed, 18 Mar 2009 19:23:43 GMT

To keep the suspense, will you post the number of queries passing each test?

re: The Grouped String Concatenation Challenge is Closed

Peso — Wed, 18 Mar 2009 19:27:30 GMT

SET STATISTICS IO has a bug when MERGE JOIN eller HASH JOIN is used.

See this connect issue

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=387326&wa=wsignin1.0

Using SQL Profiler however displays the total number of reads.

re: The Grouped String Concatenation Challenge is Closed

George Palacean — Thu, 19 Mar 2009 12:37:45 GMT

Adam, the original post states that "Entries will be judged first and foremost on correctness, then on a combination of performance, readability, and ability to apply your technique as a general pattern."

So it's not performance the main concern, but correctness (here I would include de-entitizing XML special characters, otherwise the output is simply incorrect)

re: The Grouped String Concatenation Challenge is Closed

Adam Machanic — Thu, 19 Mar 2009 17:43:01 GMT

George: The only thing I'm going to look at with regard to correctness is whether the output meets the output that my own query produces. I will not eliminate any queries that fail to deal with entitization, because the data is one of the givens of the contest and the data has no such issues.

That said, I don't think it will matter anyway from a performance point of view. I just ran some tests on this end and despite the fact that STATISTICS TIME is reporting slightly higher CPU utilization for FOR XML PATH queries that properly deal with the entitization, I'm seeing no difference in terms of performance (testing using 100-thread, 100-iteration SQLQueryStress runs).

re: The Grouped String Concatenation Challenge is Closed

Roman — Thu, 19 Mar 2009 23:41:59 GMT

Well, it seems strange - even with [comment()] trick (which is fast but hardly general solution) I find not so small overhead, forget about ".value".

I'm realy looking forward to see the best solution. I'v already made 10% faster query myself :( - pity that I spotted your challange so late and had only 2 hours.

re: The Grouped String Concatenation Challenge is Closed

Linchi Shea — Fri, 20 Mar 2009 02:49:05 GMT

Adam;

I'd suggest that while you continue your evaluation and your process of picking a best solution, simply post all the sulotions and let the folks see what solutions have been produced and learn from others. Performance is but one of many dimensions, and people can learn a heck a lot just by looking at what angels the problem is being appraoched.

I don't believe you said anything about the folks not posting their solutions after the deadline. So the other alternative is for people to post their own solutions here, if they so choose to (and in case they don't want to post their solutions for whatever reason).

I for one am interested in seeing all the other solutions.

re: The Grouped String Concatenation Challenge is Closed

Adam Machanic — Fri, 20 Mar 2009 14:15:26 GMT

Linchi: Fair enough; I will package up all of the solutions I received (unless someone e-mails me and asks me not to), and post it along with the results.

Roman: You just taught me a new one. I had no clue about that "comment" trick. That is fantastic, and I'm going to go modify my Who is Active? script to use it instead of FOR XML PATH for the query output. Thanks for the tip!

re: The Grouped String Concatenation Challenge is Closed

Adam Machanic — Fri, 20 Mar 2009 14:52:33 GMT

... or not -- it seems that the comment() trick has a major limitation:

select ' --- ' as [comment()]

for xml path('')

Result:

Msg 9322, Level 16, State 2, Line 3

Two consecutive '-' can only appear in a comment constructor if they are used to close the comment ('-->').

re: The Grouped String Concatenation Challenge is Closed

Roman — Fri, 20 Mar 2009 16:04:27 GMT

as I said it's hardly general - there are some limitations, but:

Replace('','-',Char(150))

Replace('','--',Char(150)+Char(150))

(reverse back if this change is a problem)

probably this leads to a little ugly looking code, but still could be faster then .value

re: The Grouped String Concatenation Challenge is Closed

Adam Machanic — Fri, 20 Mar 2009 17:32:30 GMT

Might be faster than .value, but REPLACE can be very slow for big strings so that's very much an open question. Alas, not at all useful for my needs with my Who is Active? script, which is takes advantage of the fact that SSMS lets you click on XML values to pop them up nicely formatted in a new tab. Entitization definitely bites me there and I was hoping that this comment() thing will work, but obviously most SQL has some form of comments so that is not going to be usable...

re: The Grouped String Concatenation Challenge is Closed

Roman — Fri, 20 Mar 2009 20:19:30 GMT

stupid me: [processing-instruction(a)] is much better then [comment()]; of course 2xreplace have still to be made

in this particular case for sample data .value is 50% slower

re: The Grouped String Concatenation Challenge is Closed

Adam Machanic — Fri, 20 Mar 2009 20:36:45 GMT

Wow, that looks VERY promising! But how did you find that? Is there a list somewhere of different commands that can be used in square brackets with FOR XML?

re: The Grouped String Concatenation Challenge is Closed

Roman — Fri, 20 Mar 2009 21:35:32 GMT

BOL :); although at first I tried [cdata()] by intuition - it didn't work - so I've checked the documentation.

Anyway - I have quite diffrent result using your test script on my dev maching (i7)

.value - 375ms; [processing-instruction(a)] 1179 - so on my i7 it is 3x slower, not faster (from SET

Previous result was with my challage script (a little modfifed) with your SQLQueryStress (this tool looks promising :) btw; found 1 problem: allowed query length seems to be too low - couldn't accomodate query with use plan option) 10 iterations, 100 threads

re: The Grouped String Concatenation Challenge is Closed

Adam Machanic — Fri, 20 Mar 2009 21:51:36 GMT

Ah, notice that I took down the script to play with it some more :-)

I found that converting to VARCHAR(MAX), rather than relying on the implicit conversion to NVARCHAR(MAX), makes processing-instruction() much faster (which makes perfect sense -- less data to do the REPLACEs on).

Regarding SQLQueryStress, I highly recommend a stored procedure for that case. You could, if you don't want to create a permanent stored procedure, create a temporary global stored procedure first and test against that. Sending all of that data to SQL Server on every iteration is going to stress more than just the query itself -- it's going to stress the network, the query parser, etc.

re: The Grouped String Concatenation Challenge is Closed

Roman — Fri, 20 Mar 2009 22:31:51 GMT

Thanks for the tip - you are of course right - it wouldn't make sense to test that way - I should have realized that earlier.

One observation about your taken down script: datalength takes much longer with /for xml path('')/ then with /for xml path(''),type/. 882ms - 369 ms;

besides for me execution plan shows costs wich are in contradiction to execution times

re: The Grouped String Concatenation Challenge is Closed

Roman Nowak — Tue, 24 Mar 2009 09:59:51 GMT

50% faster was not enough for me ;)

SQLQueryStress tool (10 iteration, 100 threads) (min:sec)

v1 (ent off) 4:41

v1 (ent on) 9:50* (.value method)

v1a (ent off) 4:11

v1a (ent on) 4:32* (pi method)

from the very begining I was ure I can do better - had to prove it to myself.

besides it was fun and I've learned sth.

(after some limited test it seems pi is faster than .value - depending on the string size from >3x to 5% - there are some limitation but not that big)

Adam Machanic : Who is Active? v8.40 - Now With Delta Power!

Adam Machanic : Who is Active? v8.40 - Now With Delta Power! — Mon, 30 Mar 2009 16:53:41 GMT

PingBack from http://sqlblog.com/blogs/adam_machanic/archive/2009/03/30/who-is-active-v8-40-now-with-delta-power.aspx

re: The Grouped String Concatenation Challenge is Closed

Peso — Wed, 01 Apr 2009 08:16:04 GMT

Deadline has occured, and while Adam finishes his tests I decided to publish my suggestion here

http://weblogs.sqlteam.com/peterl/archive/2009/02/28/Competition.aspx

re: The Grouped String Concatenation Challenge is Closed

mjswart — Wed, 22 Apr 2009 15:30:34 GMT

(april 22) Any word on the results? I'm very curious :-)

re: The Grouped String Concatenation Challenge is Closed

mjswart — Thu, 21 May 2009 17:19:12 GMT

(may 21) still curious :-)

re: The Grouped String Concatenation Challenge is Closed

Henrik Sjang Davidsen — Thu, 28 May 2009 18:53:51 GMT

I'm quite curious as well

Grouped String Concatenation: ... The Winner Is ...

Adam Machanic — Sun, 31 May 2009 20:58:02 GMT

After weeks of putting it off, I finally found the time and spent the last day and a half judging the