InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 06:55:46 GMT20http://www.sqlservercentral.com/Forums/Topic794383-1604-1.aspxselect First.* from (Select H1.* ,(select order_amt from #DailySalesTotals where H1.Odate = order_date and customer_id = H1.customer_id) order_amtfrom (select customer_id ,max(order_date)as Odatefrom #DailySalesTotals group by customer_id having count(order_date)> 1) H1)Firstinner join(Select H1.* ,(select order_amt from #DailySalesTotals where H1.Odate = order_date and customer_id = H1.customer_id) order_amtfrom (select customer_id ,max(order_date)as Odatefrom (select t1.* from #DailySalesTotals t1 ,(select customer_id,max(order_date) as order_date from #DailySalesTotals group by customer_id )t2 where t1.customer_id = t2.customer_id and t1.order_date <> t2. order_date)#DailySalesTotals group by customer_id ) H1)Secondon First.customer_id = Second.customer_id and First.order_amt < Second.order_amtabove query run fine in sql server 2000Fri, 11 Jun 2010 03:01:54 GMTRipal Patelhttp://www.sqlservercentral.com/Forums/Topic794383-1604-1.aspxWell, I see that the basic tenets of my solution have all been covered by previous posters, with one exception. nevertheless, here's the code I came up with:[code="sql"];WITH ordered AS( SELECT customer_ID, order_Date, order_amt, ROW_NUMBER() OVER (ORDER BY customer_id DESC, order_Date DESC)-rank() OVER(ORDER BY customer_id DESC) AS logicalOrder FROM dailySalesTotals),filtered AS(SELECT customer_ID, order_Date, order_amt, logicalOrderFROM ordered where logicalOrder < 2)SELECT l.customer_ID, ' dropped purchase amount on ', l.order_dateFROM filtered l INNER JOIN filtered r ON l.customer_id = r.customer_id AND l.logicalOrder = 0 AND r.logicalOrder = 1WHERE l.order_Amt < r.order_Amt[/code]The one thing that I'd like to point out about my code is that using PARTITION BY customer_id forced the inclusion of an extra sort in the query plan, since partition by does not allow the ASC/DESC specification. Using RANK avoids that sort, thus saving me between 12 and 40 % of the overall query cost, depending on the particular data in the table.The problem with this approach is in the duplicate index scans. It appears that some posters have been able to create an optimal plan by the use of the pivot operator. Clearly I need ot investigate...Thu, 12 Nov 2009 19:54:57 GMTweitzerahttp://www.sqlservercentral.com/Forums/Topic794383-1604-1.aspxHaving taken the 'Phil Factor SQL Speed Phreak' award in this competition, Peso won the next competition, ... [b][url=http://ask.sqlservercentral.com/questions/92/the-subscription-list-sql-problem]Phil Factor SQL Speed Phreak Competition: No 1: the The ‘Subscription List’ SQL Problem. [/url][/b]... It was quite a struggle with some close competition from many of those who participated in this competition. However, Peso came up with a blindingly fast winner that produced an aggregation from a million rows in a third of a second.We decided that the winner of the competition should in turn present the next problem. We felt it would be a good way of keeping the ideas fresh, and preventing one person from the whole time. Peso has come up with a very intriguing problem[b][url=http://ask.sqlservercentral.com/questions/826/the-fifo-stock-inventory-sql-problem]Phil Factor SQL Speed Phreak Competition: No 2: The ‘FIFO Stock Inventory’ SQL Problem [/url] [/b]... This looks as if it will be an even more interesting challenge and so we're hoping for even more entrants to join together to see if there is a lightening-fast solution. Peso has submitted a cursor-based solution already for you to compare your routine to. See you there!!Fri, 23 Oct 2009 14:30:47 GMTPhil Factorhttp://www.sqlservercentral.com/Forums/Topic794383-1604-1.aspxOK, I know the competition is over, but I could not resist!I combined the last query I posted with a technique I borrowed from Barry Young. I don't know if he already has given a name to it, but I think "Quirky XML Select" could fit:[code]DECLARE @customer_id char(10)DECLARE @alreadyMatched int = 0DECLARE @order_date date = GETDATE()DECLARE @order_amt decimal(8,2) = 0DECLARE @x nvarchar(max)SET @x = ''DECLARE @xml AS XMLSELECT @alreadyMatched = CASE @customer_id WHEN customer_id THEN @alreadyMatched + 1 ELSE 0 END, @x = CASE @alreadyMatched WHEN 1 THEN CASE WHEN @order_amt < order_amt THEN @x + '<C ID="' + RTRIM(customer_id) + '" DT="'+ CONVERT(char(8), @order_date ,112) +'"/>' ELSE @x END ELSE @x END, @customer_id = customer_id, @order_date = order_date, @order_amt = order_amtFROM DailySalesTotalsORDER BY customer_id ASC, order_date DESCSET @xml = CAST('<R>' + @x + '</R>' AS XML)SELECT T.results.value('@ID[1]','char(10)') as [id], T.results.value('@DT[1]','char(8)') as [dt]FROM @xml.nodes('/R/C') AS T(results)[/code]Although Joe isn't going to like it, it still works blindigly fast.Mon, 12 Oct 2009 03:04:37 GMTspaghettidbahttp://www.sqlservercentral.com/Forums/Topic794383-1604-1.aspxThank you all, guys!It really was a great experience and great fun!Peso, I came across your blog on sqlteam last friday and I saw your new post: congratulations to you and your wife!That is the best prize one can win!Mon, 12 Oct 2009 02:32:36 GMTspaghettidbahttp://www.sqlservercentral.com/Forums/Topic794383-1604-1.aspxI have started a new[b] Phil Factor SQL Speed Phreak Competition[/b] on the new beta site ask.sqlservercentral.com http://ask.sqlservercentral.com/questions/92/the-subscription-list-sql-problem The ‘Subscription List’ SQL Problem It would be great if you would join in. I'm keen on seeing if the 'stackOverflow' model is a good way of doing SQL code competitions, and if we can use voting to determine winners if there is a tie-break. The site will ask you to register, as we haven't got the links working yet to carry your login details.Mon, 12 Oct 2009 02:31:23 GMTPhil Factorhttp://www.sqlservercentral.com/Forums/Topic794383-1604-1.aspxThank you both!Congratulations Gianluca, well done!I guess we all see eachother for next stumper?Mon, 12 Oct 2009 02:13:17 GMTSwePesohttp://www.sqlservercentral.com/Forums/Topic794383-1604-1.aspxThank you all, guys for this challenge and discussion thread. It was very good learning experience. Congratulations to Gianluca and Peso!AndriyMon, 12 Oct 2009 02:10:41 GMTandriy.zabavskyyhttp://www.sqlservercentral.com/Forums/Topic794383-1604-1.aspxCongratulations Gianluca! Well deserved.Well done also to Peso - I was merely in both your shadows.....This was great fun! I went off the boil towards the end, as I couldn't keep up with all the entries.Can't wait for the next one!KevSat, 10 Oct 2009 04:00:28 GMTkevrileyhttp://www.sqlservercentral.com/Forums/Topic794383-1604-1.aspxApologies for taking so long to announce the winners but we wanted to go through all the entries once again to be certain there was not a winner in there that we'd overlooked. The quality of entries has been remarkable.We have decided that the quality of the entrants was so high that we had to award two winners. The overall winner, with code that met, most closely, all Joe’s rules, and which showed a great deal of analysis and fine-tuning was, we both felt, Gianluca Sartori. It was a great contribution and he receives a $100 Amazon voucher and a round of applause.However, Peso, the great Peter Larsson, made a great contribution to the competition and provided what seems about the fastest-performing solution. He came up with at least two very original ideas and so is the first recipient of…The [b]Phil Factor Speed Phreak [/b]award, a $60 amazon voucher and the right to display the coveted Phil Factor SQL Speed Phreak badge on his site.(OK, OK, it is still being drawn as we speak).And now for the detailsThere were ten entrants who we’d cheerfully have given the award to. They provided good, very fast, solutions that conformed to the spirit of Joe’s rules. The rest were of a very high standard. I’m in awe of themFirst off the blocks was Chris Howarth with A CTE solution that allowed him to join the latest order of every customer with the second. It was a very good solution that performed pretty well, but we were surprised by the overhead. His second solution was the first that introduced the idea of the pivot, and was strikingly ingenious. His was the first of many entries to use the ‘ROW_NUMBER() OVER(PARTITION BY customer_id ORDER BY order_date DESC’ technique, which was subsequently chosen with many ingenious variations by JeriHatTrick, andriy.zabavskyy, Plamen, Ivan Yong, Herman van Midden, Eric Pratley and Alex Kuznetsov. Andriy’s version was lightning fast. The PIVOT solution had probably its finest hour in a nice simple example by Gustavo that deserved to run faster than it did.William Brewer was the first to wonder if there was a way to get a faster result than Joe’s by avoiding the correlated subquery. There wasn’t. Paul Ireland later proved that Celko could be beaten with a ‘traditional’ approach, and there were some very fine examples such as RobertFolkerts. Well done, Paul. Mark Marinovic had a solution that performed in the same region as Chris Howarth’s but was probably too complex. GSquared came up with an idea of creating a temporary table with each customer’s totals ordered, so he could then put a primary key on both customer_id and order_date. The subsequent SQL statement was simple but one can’t help thinking that a non-clustered covering index might have performed better. GSquared’s entry was important as he was the first with ‘ROW_NUMBER() OVER (PARTITION BY Customer_ID ORDER BY Order_Date)’ that fuelled several top entries. It might have done better but the time taken to create the temporary table was greater than the fastest entries total duration. Peter Brinkhaus’s entry used two CTEs and produced a solution that had a respectable speed but was never going to please Joe with its’ '1753/01/01' date trick. back_of_the_fleet tried a solution somewhat similar to Chris Howarths but decided to try to avoid the CTE. It didn’t perform any better, though I can’t help thinking that it could be improved. Steve Rowland’s routine was snappy and beautifully simple. I felt sure that it was going to win. Suddenly, from nowhere, Peso entered the ring, and a new level of performance took over. Peso had done some careful work and his routine was very clever. He had avoided a second ROW_NUMBER by a very deft GROUP BY, but the HAVING clause was dead clever. He then went on to experiment with various other versions but I think he got it right first time. Peso is a man who lives and breathes SQL Performance. Sometimes his routines looked odd but they always worked well. A string of variations followed, such as Ivan Yongs, but they failed to improve much on the original. v_paronov, by contrast tried a ‘traditional’ approach that I really enjoyed, but which couldn’t compete, performance-wise with what came to be a favourite, the ‘ROW_NUMBER() OVER (PARTITION BY Customer_ID ORDER BY Order_Date)’ solution by GSquared, Peter Brinkhaus, Steve Rowland and PesoAndyM came up with a fast and ingenious solution that had us wondering if anyone could improve on it. Like Peso, he had gone for a single ROW_NUMBER followed by a GROUP BY and it is fascinating to compare the two.I was amazed by maxdemarzi’s solution. It was so compact. It was magnificent, and it made many of the previous entries seem verbose and awkward. Even now I wonder if it is possible to tweak it into contention as far as its performance goes. He followed up with a second solution that was faster, but not as fast as Peso and the other front-runners. Quan_L_Nguyen later produced a variant, likewise based on ‘RANK() OVER (PARTITION BY’.John McVay came in with a variation of the two-barrelled CTE solution which performed well but his entry was followed by Gianluca Sartori’s first SQL that took so long that it had to be removed from the test harness. This was swiftly followed by a version that performed better than any other. What made it surprising was that it didn’t use any new features from sQL Server 2005/8 It was jaw-droppingly ingenious. Gianluca had thought a lot about the problem. As Joe put it, the analysis was impressive. Things were definitely hotting up. KevRiley then came in with the first CROSS APPLY entry, and later produced some interesting variations. Just when it seemed we’d thought of all ways around the problem, Kev had come up with a great and original solution. The CROSS APPLY technique was used to excellent effects by mByther. It was neat and fast.I liked the solutions that used a view. Joe glowed with pleasure. girish Bhat did a great job with the first view-based solution, though it made the test harness lightly more complicated.For the other entrants that I have not the space to mention, many thanks for entering and there were a lot of excellent ideas there. Reading through the two forums was an education. Many thanks once again for everyone who contributed and stand by for a new competition! Next time ...who knows the winner may be you.Fri, 09 Oct 2009 10:46:12 GMTPhil Factorhttp://www.sqlservercentral.com/Forums/Topic794383-1604-1.aspx[quote][b]Gianluca Sartori (10/7/2009)[/b][hr]Okay, since I already lost my beer, let's be bad until the end :-P [/quote]Gianluca,You've inspired me to lost another beer and rewrite your beautiful query not to use sorting, so that in this way the performance had been improved :-PSo it would be following:[code="sql"]DECLARE @customer_id char(10) DECLARE @prev_customer_id char(10)DECLARE @prev_prev_customer_id char(10)DECLARE @customers nvarchar(max) = ''DECLARE @order_date date = GETDATE()DECLARE @order_amt decimal(8,2) = 0DECLARE @prev_order_amt decimal(8,2) = 0DECLARE @prev_prev_order_amt decimal(8,2) = 0SELECT @prev_prev_customer_id = @prev_customer_id, @prev_customer_id = @customer_id, @customer_id = customer_id, @prev_prev_order_amt = @prev_order_amt, @prev_order_amt = @order_amt, @order_amt = order_amt, @customers = case when @customer_id <> @prev_customer_id and @prev_customer_id = @prev_prev_customer_id and @prev_prev_order_amt > @prev_order_amt then case @customers when '' then @customers else @customers + ' UNION ALL ' end + ' SELECT ''' + RTRIM(@prev_customer_id) + ''' as customer_id, '''+ CONVERT(char(8), @order_date ,112) +''' as order_date ' else @customers end, @order_date = order_date FROM DailySalesTotals ORDER BY customer_id ASC, order_date ascSET @customers = ' SELECT customer_id, CONVERT(date, order_date, 112) AS order_date FROM (' + @customers + ') AS data' exec sp_executesql @customers[/code]Best regards,AndriyFri, 09 Oct 2009 10:11:07 GMTandriy.zabavskyyhttp://www.sqlservercentral.com/Forums/Topic794383-1604-1.aspxOkay, since I already lost my beer, let's be bad until the end :-P[code]DECLARE @customer_id char(10)DECLARE @alreadyMatched int = 0DECLARE @customers nvarchar(max) = ''DECLARE @order_date date = GETDATE()DECLARE @order_amt decimal(8,2) = 0SELECT @alreadyMatched = CASE @customer_id WHEN customer_id THEN @alreadyMatched + 1 ELSE 0 END, @customers = CASE @alreadyMatched WHEN 1 THEN CASE WHEN @order_amt < order_amt THEN CASE @customers WHEN '' THEN @customers ELSE @customers + ' UNION ALL ' END + ' SELECT ''' + RTRIM(customer_id) + ''' as customer_id, '''+ CONVERT(char(8), @order_date ,112) +''' as order_date ' ELSE @customers END ELSE @customers END, @customer_id = customer_id, @order_date = order_date, @order_amt = order_amtFROM DailySalesTotalsORDER BY customer_id ASC, order_date DESCSET @customers = ' SELECT customer_id, CONVERT(date, order_date, 112) AS order_date FROM (' + @customers + ') AS data' EXEC sp_executesql @customers[/code]Wed, 07 Oct 2009 04:34:39 GMTspaghettidbahttp://www.sqlservercentral.com/Forums/Topic794383-1604-1.aspx[quote][b]Joe Celko (10/6/2009)[/b][hr]>> I also understand that many DBAs don't want to rely on undocumented and unsopported features like this.. No problem: I will take care of the problem when it arises, meanwhile I think I will take all the advantages. <<BAD PROGRAMMER!! No beer for you on Friday after work! How do you know that your would even be around to take care of the problem when it arises? Imagine a civil engineer who built bridges on that kind of assumption. Do oun really want to never find another job? Seriously, you should always favor data integrity over speed. If it does not have to be right, then answer is 42 (Douglas Addams). Favor portable over dialect; 80% or more of the lifetime cost is in maintaining code. This is all basic Software Engineering.[/quote]Joe, I'm 100% with you. In all the three cases where I applied the quirky update to a production routine I put a long comment in the procedure to help identify the issue, just in case a tile falls from a roof into my head... :Whistling:Imagine you have a 10 minutes window to run a job over a huge amount of data and a running total is involved. Any other approach takes at least 40 minutes and the quirky update takes 4 minutes: which one would you choose? In this case I decided to keep my current job rather than worrying about never finding a new one. :-DIt's also true that documented and supported features change and stop working without notice applying patches and service packs (I could cite the behaviour of SQL Server 2008 parameter sniffing, just to make an example), so I think I'll take the risk in this case, counting the advantage that I expect the quirky update to stop working at some point, so I always test it deeply when I apply patches.As a side note, I always try to stick to portable code, even if I know that complete portability nowadays is nearer to possibile than it was 10 years ago, but still very far. I'm sure nobody on Earth knows it better than you do. I worked for many years on a multi-platform ERP software and the biggest issue was writing portable code: lots of branches there to make it work on different RDBMS.Anyway, I didn't want to turn this thread into a discussion on the pros and cons of the quirky update: SSC is full of threads on this topic and they all are hot discussions. At some points it tends to turn to some kind of religion war, and I don't want to be one of the soldiers...My apologies to everybody on the thread for annoying them with this.Wed, 07 Oct 2009 01:42:35 GMTspaghettidbahttp://www.sqlservercentral.com/Forums/Topic794383-1604-1.aspx>> I also understand that many DBAs don't want to rely on undocumented and unsopported features like this.. No problem: I will take care of the problem when it arises, meanwhile I think I will take all the advantages. <<BAD PROGRAMMER!! No beer for you on Friday after work! How do you know that your would even be around to take care of the problem when it arises? Imagine a civil engineer who built bridges on that kind of assumption. Do oun really want to never find another job? Seriously, you should always favor data integrity over speed. If it does not have to be right, then answer is 42 (Douglas Addams). Favor portable over dialect; 80% or more of the lifetime cost is in maintaining code. This is all basic Software Engineering.Tue, 06 Oct 2009 20:52:48 GMTCELKOhttp://www.sqlservercentral.com/Forums/Topic794383-1604-1.aspxI, for one, like the new results. I think it makes sense to use for the judging. :-)I've been trying to improve performance by getting rid of the sort step but continuing to use the row_number function. I tried putting the sort in a CTE (below) but it doesn't seem to help. I was able to do one thing, further below.------------------------------------WITH dailysalestotals1 AS (SELECT customer_id,order_date,order_amt, row_number() over(partition by customer_id order by order_date desc) as row_number FROM dailysalestotals) select customer_id, 'dropped purchase amount on',last_order_date = max(order_date)from (select row_number, customer_id, order_date, order_amt from dailysalestotals1) dtwhere row_number in (1,2)group by customer_idhaving sum(case when row_number = 1 then order_amt else -order_amt end) < 0-------------------------------------------------------This is all I could come up with. It's pretty fast but requires two scans. I used with rollup in the CTE to pull out a single record with a count of orders by customer, then compared the count to the row number of the indivdual order dates to pull the last two.WITH dailysalestotals1 AS (SELECT top 100000 d1.customer_id,d1.order_date,d1.order_amt, row_number() over(partition by d1.customer_id order by (select 1)) as row_number, COUNT(1) count FROM dailysalestotals d1 GROUP BY d1.customer_id,d1.order_date,d1.order_amt with rollup HAVING order_amt is not null or (order_date is null and order_amt is null))select dt.customer_id, 'dropped purchase amount on',last_order_date = max(dt.order_date)from (select customer_id,order_date,order_amt, row_number from dailysalestotals1 where order_amt is not null ) dtcross apply (select d2.customer_id, d2.count from dailysalestotals1 d2 where order_date is null and order_amt is null and dt.customer_id = d2.customer_id) dt1where dt.row_number + 1 >= dt1.countgroup by dt.customer_idhaving sum(case when dt.row_number = dt1.count then order_amt when dt.row_number = dt1.count-1 then -order_amt end) < 0Tue, 06 Oct 2009 15:42:40 GMTivan.yonghttp://www.sqlservercentral.com/Forums/Topic794383-1604-1.aspxWhere do you guys get the idea that 'Quirky update' isn't documented? It's in all my old Sybase documentation. It is even documented in SQL Server 2008 http://msdn.microsoft.com/en-us/library/ms177523.aspx. They've actually added some new features! Sure it does not always clearly explain the order of update. - but it explains some of the quirks such as....[quote] SET @variable = column = expression sets the variable to the same value as the column. This differs from SET @variable = column, column = expression, which sets the variable to the pre-update value of the column.[/quote]And the things you can do with the WRITE clause! Ah yes.Tue, 06 Oct 2009 14:20:58 GMTPhil Factorhttp://www.sqlservercentral.com/Forums/Topic794383-1604-1.aspx[quote][b]Phil Factor (10/6/2009)[/b][hr]Sure, but as all the entries had been submitted by this Monday, I felt we were safe to do the judging! Also, we didn't want to have to check out any more submissions by Peso! :-)[/quote]Oh come on.. Unconventional algorithms are always interesting :-D Tue, 06 Oct 2009 13:15:30 GMTSwePesohttp://www.sqlservercentral.com/Forums/Topic794383-1604-1.aspx[quote][b]Peso-284236 (10/6/2009)[/b][hr]http://weblogs.sqlteam.com/mladenp/archive/2009/07/28/SQL-Server-2005-Fast-Running-Totals.aspx[/quote]Yes, I already knew this technique. In this case the order is ensured by the ORDER BY clause in the CTE (the top maxint is there for that purpose), but in the temp table there's no index defined, so I suppose the order is enforced by the table storage order, something I've always been taught not to trust.I was introduced to the quirky update by Jeff Moden and his great article here at SSC. Jeff clearly stated that the conditions to ensure the correct order were:* a clustered index* no parallelism ( = MAXDOP 1)* exclusive table lockJust a curiosity on my side.I also understand that many DBAs don't want to rely on undocumented and unsopported features like this, but I must say that I used it in some cases to calculate running totals, even in production environment, and it performs incredibly faster than any other technique I tried. Will it stop working without notice with some future service pack? No problem: I will take care of the problem when it arises, meanwhile I think I will take all the advantages.Tue, 06 Oct 2009 13:11:22 GMTspaghettidbahttp://www.sqlservercentral.com/Forums/Topic794383-1604-1.aspx[quote]http://weblogs.sqlteam.com/mladenp/archive/2009/07/28/SQL-Server-2005-Fast-Running-Totals.aspx[/quote]The CTE with Update trick is new to me. Along with a lot of other people, I've used the old 'quirky' Update technique since the old Sybase days. I've never had it go wrong on me once it has been built and tested on the production data. Mind you, practically every release of SQL Server has had the occasional oddity with syntax happen (Robyn named it 'quirky' for that reason and the name has stuck). The most difficulty comes with the order in which the SET list is done. Of course, the SELECT INTO trick was verboten until recently.I've been using it for parsing complex JSON and XHTML strings. Blindingly fast: great fun.[quote]So, have you collated the results?Please, make a post when you will have finished.Thanks,Andriy[/quote]Yes, but there have been so many good entries, far more than we expected, that we decided to do a second prize, and I needed to get Red-Gate's permission to do this. I have modestly named it the [b]Phil Factor Speed Phreak [/b] award and the winner will receive a $60 amazon voucher, along with the right to exhibit the coveted and collectible [b]Phil Factor Speed Phreak [/b] .JPG badge image on their blog or website. Matt at Red-Gate is currently doing the art work for this. I asked him for something that looked as if it had been levered off a fifties motor-bike.Joe and I are doing the write-up. hopefully we'll post it tomorrow.[quote]the deadline for presentation of results is on October 9th.[/quote]Sure, but as all the entries had been submitted by this Monday, I felt we were safe to do the judging! Also, we didn't want to have to check out any more submissions by Peso! :-)Tue, 06 Oct 2009 12:27:05 GMTPhil Factorhttp://www.sqlservercentral.com/Forums/Topic794383-1604-1.aspxAccording to the original article[quote]We will take entries for a week after the first Monday of publication, posted as comments to the articles. Two weeks after the challenge is sent out, the judge's decision and comments will be sent out in the newsletter, and published on the site. Joe Celko and Phil Factor will judge the answers to this puzzle. Your answer should : [/quote]the deadline for presentation of results is on October 9th.Tue, 06 Oct 2009 11:57:55 GMTSwePesohttp://www.sqlservercentral.com/Forums/Topic794383-1604-1.aspxhttp://weblogs.sqlteam.com/mladenp/archive/2009/07/28/SQL-Server-2005-Fast-Running-Totals.aspxTue, 06 Oct 2009 09:40:15 GMTSwePesohttp://www.sqlservercentral.com/Forums/Topic794383-1604-1.aspxPhil, your "quirky update" doesn't comply to Jeff Moden's best practices: is the order guranteed even without a clustered index on customer_id + order_date?Tue, 06 Oct 2009 03:16:50 GMTspaghettidbahttp://www.sqlservercentral.com/Forums/Topic794383-1604-1.aspxHi Andriy.Still eager to know? :-)http://www.sqlservercentral.com/Forums/Topic759697-1604-23.aspxTue, 06 Oct 2009 02:45:06 GMTSwePesohttp://www.sqlservercentral.com/Forums/Topic794383-1604-1.aspx[quote][b]Phil Factor (10/5/2009)[/b][hr]Sure.Joe and I have already done the judging, (we did it over the weekend) and we're just collating the results[/quote]So, have you collated the results?Please, make a post when you will have finished. Thanks,AndriyTue, 06 Oct 2009 02:07:28 GMTandriy.zabavskyyhttp://www.sqlservercentral.com/Forums/Topic794383-1604-1.aspxWell, it's [b][i]semi[/i][/b]-pseudocode for Microsoft SQL Server, that's why I wrote "semi".And as for unittesting, my approach with CROSS JOIN will do exactly the same thing as your suggestion, Mr Celko. It will reveal and report every drop in order history.Give it a try, if you already haven't.Mon, 05 Oct 2009 10:23:47 GMTSwePesohttp://www.sqlservercentral.com/Forums/Topic794383-1604-1.aspxAaaahh! The "quirky update"! I knew somebody at some point would have posted it!I would have expected this from Jeff Moden, BTW!:-)Phil, I can't figure out why my laptop seems to be much faster than your test env, but I actually got my results in 2.3 seconds with the cursor solution. If you go back to my times comparison post (the one with excel file attached) you will notice that my laptop performs better than my 16-core test box, but I thought it was due to the old crappy SAN it is attached to...Mon, 05 Oct 2009 07:46:00 GMTspaghettidbahttp://www.sqlservercentral.com/Forums/Topic794383-1604-1.aspxGianluca's cursor routine took 4mins 48secs here! Here is a sort of cursor solution. It comes in at 466 ms in our league table, despite having to copy the entire data into a temporary table! If you cheat and use an existing table, it is down at 190 ms. I hope Joe doesn't spot this as it he's going to hate it! (uses proprietary extensions from the old Sybase days)[font="Courier New"][size="2"][color="black"][/color][color="blue"]IF [/color][color="gray"]EXISTS ( [/color][color="blue"]SELECT  [/color][color="gray"]*            [/color][color="blue"]FROM    [/color][color="black"]tempDB.information_schema.tables            [/color][color="blue"]WHERE   [/color][color="black"]table_name [/color][color="gray"]LIKE [/color][color="red"]'#temp%' [/color][color="gray"])     [/color][color="blue"]DROP TABLE [/color][color="#434343"]#temp[/color][color="blue"]SELECT  [/color][color="gray"]*,        [/color][color="magenta"]CAST[/color][color="gray"](NULL [/color][color="blue"]AS DATETIME[/color][color="gray"]) [/color][color="blue"]AS [/color][color="black"]dropped[/color][color="gray"],        [/color][color="magenta"]CAST[/color][color="gray"](NULL [/color][color="blue"]AS DATETIME[/color][color="gray"]) [/color][color="blue"]AS [/color][color="black"]latest[/color][color="blue"]INTO    [/color][color="#434343"]#temp[/color][color="blue"]FROM    [/color][color="black"]DailySalesTotals[/color][color="blue"]DECLARE [/color][color="#434343"]@firstOrder [/color][color="blue"]DATETIMEDECLARE [/color][color="#434343"]@Customer_ID [/color][color="blue"]CHAR[/color][color="gray"]([/color][color="black"]10[/color][color="gray"])[/color][color="blue"]DECLARE [/color][color="#434343"]@FirstOrderamount [/color][color="blue"]DECIMAL[/color][color="gray"]([/color][color="black"]8[/color][color="gray"], [/color][color="black"]2[/color][color="gray"])[/color][color="blue"]DECLARE [/color][color="#434343"]@dropped [/color][color="blue"]DATETIMESELECT  [/color][color="#434343"]@Customer_ID[/color][color="blue"]=[/color][color="red"]''[/color][color="blue"]UPDATE  [/color][color="#434343"]#temp[/color][color="blue"]SET     [/color][color="#434343"]@Dropped[/color][color="blue"]=[/color][color="black"]dropped[/color][color="blue"]=[/color][color="magenta"]CASE [/color][color="blue"]WHEN [/color][color="#434343"]@Firstorder [/color][color="blue"]IS [/color][color="gray"]NOT NULL                              [/color][color="blue"]THEN [/color][color="magenta"]CASE [/color][color="blue"]WHEN [/color][color="#434343"]@FirstOrderamount[/color][color="gray"]<[/color][color="black"]order_amt                                        [/color][color="blue"]THEN [/color][color="#434343"]@FirstOrder                                        [/color][color="blue"]ELSE [/color][color="gray"]NULL                                   [/color][color="blue"]END                              ELSE [/color][color="gray"]NULL                         [/color][color="blue"]END[/color][color="gray"],        [/color][color="#434343"]@FirstOrderamount[/color][color="blue"]=[/color][color="black"]order_amt[/color][color="gray"],        [/color][color="#434343"]@FirstOrder[/color][color="blue"]=[/color][color="black"]latest[/color][color="blue"]=[/color][color="magenta"]CASE [/color][color="blue"]WHEN [/color][color="black"]customer_ID[/color][color="gray"]<>[/color][color="#434343"]@customer_ID [/color][color="blue"]THEN [/color][color="black"]order_date                                [/color][color="blue"]ELSE [/color][color="gray"]NULL                           [/color][color="blue"]END[/color][color="gray"],               [/color][color="green"]--oldcustomerID = @Customer_ID,         [/color][color="#434343"]@customer_ID[/color][color="blue"]=[/color][color="black"]customer_ID[/color][color="green"]--,            [/color][color="blue"]SELECT  [/color][color="black"]customer_ID[/color][color="gray"],        [/color][color="red"]'dropped purchase amount on '[/color][color="gray"],        [/color][color="black"]dropped [/color][color="blue"]AS [/color][color="black"]Last_purchase_date[/color][color="blue"]FROM    [/color][color="#434343"]#temp[/color][color="blue"]WHERE   [/color][color="black"]dropped [/color][color="blue"]IS [/color][color="gray"]NOT NULL[/color][/size][/font]Mon, 05 Oct 2009 07:33:03 GMTPhil Factorhttp://www.sqlservercentral.com/Forums/Topic794383-1604-1.aspxThe above cursor based code runs in around 67.20 ms on the 28K row table and in around 2309 ms on the 102K row table (average times over 100 executions on my laptop).Compared to the best set based solutions, it's 4 times slower over 28K rows and 11 times slower over 102K rows.I'm sure something can be improved in the cursor code, but I'm not expecting big changes in the comparison.Mon, 05 Oct 2009 07:08:32 GMTspaghettidbahttp://www.sqlservercentral.com/Forums/Topic794383-1604-1.aspxThis is the cursor based solution.I haven't tested the performance yet, but I'll be back with some news.[code]DECLARE @Results TABLE ( customer_id char(10), order_date date)DECLARE @customer_id char(10)DECLARE @order_date dateDECLARE @order_amt decimal(8,2)DECLARE @prev_customer_id char(10)DECLARE @prev_order_date dateDECLARE @prev_order_amt decimal(8,2)DECLARE @row intSET @row = 0DECLARE cur CURSOR STATIC LOCAL FORWARD_ONLYFORSELECT customer_id, order_date, order_amtFROM DailySalesTotalsORDER BY customer_id, order_date DESCOPEN curFETCH NEXT FROM cur INTO @customer_id, @order_date, @order_amtWHILE @@FETCH_STATUS = 0BEGIN IF @customer_id = @prev_customer_id BEGIN SET @row = @row + 1 IF @row = 1 BEGIN IF @order_amt > @prev_order_amt BEGIN INSERT INTO @Results SELECT @customer_id, @prev_order_date END END END ELSE BEGIN SET @row = 0 END SELECT @prev_customer_id = @customer_id, @prev_order_date = @order_date, @prev_order_amt = @order_amt FETCH NEXT FROM cur INTO @customer_id, @order_date, @order_amtENDSELECT *FROM @Results[/code]Mon, 05 Oct 2009 06:45:14 GMTspaghettidbahttp://www.sqlservercentral.com/Forums/Topic794383-1604-1.aspxPeso, the ROW/RANGE clause is not pseudo-code. It is core SQL-2003 Standard syntax, available in DB2, Oracle and other products for years. SQL Server is just behind the rest of the pack. That little query I posted will actually return EVERY drop in purchase level, not just the most recent one.Mon, 05 Oct 2009 06:37:36 GMTCELKOhttp://www.sqlservercentral.com/Forums/Topic794383-1604-1.aspxTo give another approach for some time comparisons, here's a long hand route to the same result using code I normally use when attempting to avoid using a cursor. It's still set based but only just. It also betrays my COBOL origins :blush:.[code="plain"]declare @t table -- work table( rowno int primary key, customer_id char(10), order_date smalldatetime, order_amt decimal(8,2) )declare @t2 table -- for recordset output( customer_id char(10), order_date smalldatetime )declare-- data buffer @buff_customer_id char(10), @buff_order_date smalldatetime, @buff_order_amt decimal(8,2),-- saved data for comparison to data buffer @sv_customer_id char(10), @sv_order_date smalldatetime, @sv_order_amt decimal(8,2),-- order amount is less that prior amount flag @flgLT_YN char(1),-- subscripts @ix int, @imax int, @order_count int-- Load data applying a row numberinsert into @t select row_no = row_number() over ( partition by 'x' order by customer_id, order_date ), customer_id, order_date, order_amt from DailySalesTotals-- Save first row valuesselect @sv_customer_id = customer_id, @sv_order_date = order_date, @sv_order_amt = order_amt, @order_count = 1from @twhere rowno = 1-- Initialize subscriptsselect @ix = 2, @imax = count(*)from @twhile @ix <= @imax begin--Get next row select @buff_customer_id = customer_id, @buff_order_date = order_date, @buff_order_amt = order_amt from @t where rowno = @ix-- Level Break? if @buff_customer_id != @sv_customer_id and @order_count > 1 begin select @order_count = 1 if @flgLT_YN = 'Y' begin insert into @t2 select @sv_customer_id, @sv_order_date end end--Set level checking fields if @buff_customer_id = @sv_customer_id begin select @order_count = @order_count + 1 if @buff_order_amt < @sv_order_amt begin select @flgLT_YN = 'Y' end else begin select @flgLT_YN = 'N' end end--Save buffer values select @sv_customer_id = @buff_customer_id, @sv_order_date = @buff_order_date, @sv_order_amt = @buff_order_amt--Increment subscript select @ix=@ix+1 end-- Process last rowif @order_count > 1 begin if @flgLT_YN = 'Y' begin insert into @t2 select @sv_customer_id, @sv_order_date end end -- Display final recordsetselect * from @t2[/code]Mon, 05 Oct 2009 06:31:58 GMTback_of_the_fleethttp://www.sqlservercentral.com/Forums/Topic794383-1604-1.aspxYes. For Joe and I, this has been a fascinating learning exercise. We both very much hope it has been as useful and entertaining for the folks who have supplied entries. It would be nice to get a really good cursor/While-loop-based solution to compare timings with, if anyone has a moment. (we had a cursor-based entry, but I couldn't get it to work).Mon, 05 Oct 2009 04:48:07 GMTPhil Factorhttp://www.sqlservercentral.com/Forums/Topic794383-1604-1.aspxI was only joking........Mon, 05 Oct 2009 04:30:49 GMTkevrileyhttp://www.sqlservercentral.com/Forums/Topic794383-1604-1.aspxSure.Joe and I have already done the judging, (we did it over the weekend) and we're just collating the results. As Peso and some others had had the idea of the change in the clustered index, I thought I'd give it a try just to show them what it did with the results. It is not going to effect the competition itself, and I'd hate to think that anyone feels obliged to alter their entry.Joe and I have awarded points as much for analysis/cleverness as for performance, and even in the performance stakes, we are not operating a 'first-past-the-post' judgement. If your routine is within the first cluster of results, that's a tick in the box!Mon, 05 Oct 2009 04:24:01 GMTPhil Factorhttp://www.sqlservercentral.com/Forums/Topic794383-1604-1.aspxPhil, looks like this new classification raised some panic...Maybe you'll want to clarify that the desc index is an option, not a must.Mon, 05 Oct 2009 03:57:33 GMTspaghettidbahttp://www.sqlservercentral.com/Forums/Topic794383-1604-1.aspxHey Joe & Phil,Could you please post the latest table definition (with the redefined index). I am not really tracking each reply in these 12 pages (so far) of the post but suddenly happened to see that my query has gone from 7th position to the last in the pack:w00t: Maybe I can have another shot at it if the new table definition is posted.GirishMon, 05 Oct 2009 03:45:52 GMTGirish Bhathttp://www.sqlservercentral.com/Forums/Topic794383-1604-1.aspxIsn't changing the index a bit like changing the question......:hehe:I'm not surprised that the 'best' solutions now don't fit, as we are now answering a different question. If you imagine the question as 'given a table of X rows, with Y structure and Z indexes, whats the best way of obtaining a result R', then changing any part of that is really a completely different question.Plus in the original article states "What is your answer in one SQL Statement?" I don't consider amending/creating indexes and then querying as one statement!I'm just sour as one of my 'good' solutions has now plummetted out of contention! :sick:Although saying that, they were never in contention as they don't port!KevMon, 05 Oct 2009 03:24:46 GMTkevrileyhttp://www.sqlservercentral.com/Forums/Topic794383-1604-1.aspxOh no they don't! I find my solution has gone from the middle of the pack to second from last. I suspect that you guys also mark 'A' levels in your spare time.Mon, 05 Oct 2009 03:20:07 GMTback_of_the_fleethttp://www.sqlservercentral.com/Forums/Topic794383-1604-1.aspxWow!It seems almost all solutions performed better using the revised index definition.Mon, 05 Oct 2009 02:46:21 GMTSwePesohttp://www.sqlservercentral.com/Forums/Topic794383-1604-1.aspxUsing the revised clustered index, the timings came out as...[code="sql"]/*63ms Peso63ms AndyM63ms Peso 363ms andriy.zabavskyy63ms ivan.yong63ms Herman 2106ms mByther 2106ms KevRiley106ms Peso's GianLuca-copy110ms KevRiley 5110ms Gianluca Again110ms Eric Pratley110ms giancula 3110ms Paul Ireland110ms Gianluca Sartori110ms Steve Rowland110ms Gianluca no top or cross apply123ms Chris Howarth123ms Mark Marinovic123ms eemore-571761126ms Kevriley 4126ms Peter Brinkhaus126ms Celko140ms plamen140ms maxdemarzi143ms mByther 1153ms Alex Kuznetsov170ms Gustavo 2173ms Quan 2236ms jfortuny250ms Quan_L_Nguyen250ms maxdemarzi313ms RobertFolkerts343ms Peso 4343ms Wm Brewer420ms John McVay466ms Peso 4 Revised500ms Chris Howarth 2500ms Gustavo576ms v_paronov626ms Peso 2686ms Herman van Midden923ms GSquared950ms back_of_the_fleet970ms girish Bhat */[/code]I couldn't include Ruzanna's result as it had some extra rows, but it was returning timings in the mid-field.Mon, 05 Oct 2009 02:42:58 GMTPhil Factor