Facebook Sign in | Join
Pushing database changes needn't be hard work with SQL Compare
Getting Started with Adobe After Effects - Part 6: Motion Blur
First Time? You can support us by signing up. It takes only 5 seconds. Click here to sign up. If you already have an account, click here to login.

TSQL Challenge 9 Winners

Thanks to all

That's a repetitive point, but I would like to thank all challengers for their participation. Every solution has its points of interest, and we always spend a lot of time analyzing all the solutions to find the more accurate, different views and so on to find the more valuable content to present to you for a type of problem.

We plan also in a near future to  provide to the community the full set of solutions for every challenge a separate downloadable project (every solution will be anonymized obviously and with the acceptance of every challenger). If you have any idea about the best way to keep and present these solutions, don’t hesitate to send us a mail!

The Challenge

This challenge is about searching for ordered data islands in a flat table. The difficulty solving this is that it is that it is really different than usual aggregation grouping. When you aggregate, you put in the same box all values for a same field whatever their position on the table.

Here is the source data for the challenge:

ID          CreationDate            Content    SendState AckState
----------- ----------------------- ---------- --------- --------
1           2009-05-27 22:15:43.647 Msg #1     0         0
2           2009-05-28 00:39:43.647 Msg #2     0         0
3           2009-05-28 03:03:43.647 Msg #3     1         1
4           2009-05-28 05:27:43.647 Msg #4     1         1
5           2009-05-28 07:51:43.647 Msg #5     1         1
6           2009-05-28 10:15:43.647 Msg #6     1         0
7           2009-05-28 12:39:43.647 Msg #7     1         0
8           2009-05-28 15:03:43.647 Msg #8     1         0
9           2009-05-28 17:27:43.647 Msg #9     1         0
10          2009-05-28 19:51:43.647 Msg #10    1         1

Using usual aggregation, if we want to get first and last ID for each blocks based on SendState and AckState, we can only obtain something like this:

FirstIdInclusive LastIdInclusive SendState AcknoledgeState
---------------- --------------- --------- ---------------
1                2               0         0
6                9               1         0
3                10              1         1

But the challenge here is to obtain sequential series for each different couple of (SendState,AckState), like this:

FirstIdInclusive LastIdInclusive SendState AcknoledgeState
---------------- --------------- --------- ---------------
1                2               0         0
3                5               1         1
6                9               1         0
10               10              1         1

Key points

Solving this challenge involves solving two hurdles, one about finding the solution and the other about getting the best performance.

Obviously the first key point is to produce a query that works, but it has to works also in larger tables than the one provided in the example.

But, as explained in the original challenge post, one major target of this challenge is to make the best solution in terms of performance. Solving this challenge with a lot of complex sub-queries and self-joins, decoupling all, can be a solution but will it work with a table of one million rows?

By the nature of SQL it-self this kind of queries give very disparate results (from 1 to 1000 times difference) depending on how you manage it. The solutions we selected perform perfectly this performance point, and, as you will see they are also very simple and short. As usual with TSQL queries, writing the most short and accurate query is often the best way to get performance.

The Winners

First of all, I want to give great congratulations to the winners!

This challenge was one that receives the most different kind of solutions since the beginning and it was very interesting to analyze the differences between all of them. The winners show very smart queries and I want to congratulate them again for that. I want also thanks all other challengers for their participation because they show us also different point of views and sometimes they are not really far in terms of performance from the winners set.

We will see in the next days how Rob, Syed and David solve this challenge:

  • Rob Farley
    rob_sideOwner/Principal of LobsterPot Solutions, is a SQL MVP and MCT based in Adelaide, Australia where he runs the local SQL Server User Group. He consults and trains across Australia, helping people improve the way they use their databases. He has also helped create exams for Microsoft Learning and is a regular presenter at conferences around Australia. His company can be found at http://www.lobsterpot.com.au and his blog at http://msmvps.com/blogs/robfarley

  • Syed Mehroz Alam
    Syed Mehroz Alam, living in Karachi, Pakistan, is primarily a developer focusing Microsoft technologies. He has completed his bachelors as a Computer Systems Engineer in 2006 and is currently pursuing a Masters degree in Computer Science. Despite developing rich internet applications, he loves to work with SQL Business Intelligence platform that enhances his TSQL expertise. He is fond of logical challenges and has won several speed programming competitions which are listed here.
    Syed writes articles at CodeProject, and expresses his experiences with .NET and SQL server at his blog. When he has time, he contributes to MSDN and Silverlight forums. He loves to play football (Soccer) and computer games, especially first person shooters and RPGs.

  • David Barbarin
    david David, living in Lyon, France, is SQL Server DBA working as expert to improve existing systems performance. He participates actively to french Sql Server dedicated sites and forums and has a lot of certification regarding Microsoft technologies (Microsoft MCDBA, MCTS SQL Server 2005 , MCITP SQL Server 2005).
    A special note to David, as he is now part of our volunteers team 

If you have any generic questions about this challenge, the discussion is always opened on the dedicated thread of the forum: http://beyondrelational.com/groups/tsqlchallenge/forum/t/136.aspx.


Copyright © Rivera Informatic Private Ltd.