Is SQL going the way of Disco?

15 commentsWritten on July 22nd, 2008 by
Categories: Software Development

How many developers do you know who can write solid SQL queries? Practically every developer knows SQL, but there aren't too many who know how to use it well. Now don't get me wrong, i am by no means a SQL guru... but i like to think i can hold my own when it comes to writing 'good' queries. With the advent of querying techniques that shield you from directly using SQL (like the LINQ syntax, or NHibernate's Criteria) the number of developers with solid SQL knowledge will probably only get smaller and smaller in the coming years. I wonder if in 5 years time, developers with good SQL skills will be as rare as politicians that don't take money, athletes that don't use performance enhancing drugs, doctors who care about your health instead of their wallet, gangsta rappers who actually used to be 'gangsta', single women that don't... well, i'm sure you catch my drift.

Pretty soon, most .NET developers will be using nothing but LINQ statements to query their databases. Obviously, a part of the problem will shift from lousy SQL statements to lousy LINQ statements. That's pretty much unavoidable, given the ratio of 'bad' developers out there. But the productivity gains from using LINQ (or Criteria) over directly using SQL will probably become more and more important as time goes on. Not to mention the fact that LINQ is 'sexy' and will be pushed heavily by Microsoft.

Would that actually be a bad thing though? I kinda think the situation is similar to the shift we've seen over the years in programming languages. We used to write code in assembly language. Well, 'we' didn't, but the poor souls who were in this industry before us did. Then people moved to higher level languages (like C) for the increased productivity. Fast-forward a couple of years and people moved to development platforms where the actual code would be compiled to machine code 'just-in-time', at runtime even! Oh, the horrors!

In each and every one of those shifts, there were developers who said "i don't care, my hand-written code is definitely gonna outperform that". And at the beginning of those shifts, this was probably true for a lot of developers. But as time went on, the newer technologies pretty much always 'won' because of the productivity gains. Even if they were slower in direct comparisons. Then again, those technologies evolve as well and they usually get faster along the way. The runtime performance of current Java byte code is a lot better than it was in the first 3 years that Java was around. I'm not sure if the .NET runtime has actually gotten faster in the past couple of years, but then again, it probably picked up on a lot of lessons learned in the Java world before .NET was released.

One thing that has happened consistently is that compilers have always gotten better. Well, they've always gotten better at optimizing the code they generate. This leads to situations where the same code actually runs better when it's compiled with newer versions of a compiler (well, if it still compiles...). Obviously, this isn't true for all code, but it usually is true for certain statements, or certain frequently-used bad-performing techniques (string concatenation for instance).

I wouldn't be surprised to see the same thing happening with LINQ providers (or other SQL querying abstractions for that matter). The SQL that is generated by them is pretty good already. And it's never gonna get worse... they will always become 'smarter' and they will generate better SQL statements in future versions. When that happens, we all benefit from that.

Sure, there will always be die hard SQL fans who keep writing their own SQL queries and there will always be edge-cases where hand-written SQL queries will be better than those generated by higher level libraries. Especially if those SQL queries were written by Disco Stu. I mean SQL Ben (laugh Ben, it's just a joke ;) ). But i do believe that will only be true in edge-cases.

« Mocking Dilemma, Solved!I can’t help it… »

This entry was posted on Tuesday, July 22nd, 2008 at 21:35 and is filed under Software Development. You can subscribe to any comments to this entry via RSS 2.0. You can leave a response, or trackback from your own site.

  • Rob

    Even if you write plain old sql you have no idea how the database is going to execute it. Query “optimizers” and stuff already analyze your sql and create executionpaths you can’t easily foresee.

  • kve

    Sorry, stopped reading somewhere at “guru”…

  • http://davybrion.com Davy Brion

    @Kve:

    you do realize i said i wasn’t a guru, right? :p

  • kve

    Yes, my world fell apart…

  • http://davybrion.com Davy Brion

    @Kve:

    i always suspected it to be pretty sad ;)

  • http://yoot.be Stiiifff

    Aahahaha, excellent :) … I made myself the same comment recently about how bad junior .Net developers generally are at SQL statements ! Hopefully they are still some (old) guys who come from the ‘Client/Server days’ and had to learn stuff like Oracle’s “select connect by prior” and other niceties ;) lol

    It also made me think about my first reaction when I saw a presentation by Eric Meijer about Volta … “This is insane, the compiler breaking up the application for you ! Developers are going to write Linq queries on every tiers and they won’t even notice that it will generate zillions of network / db requests”. But as you said, this is part of evolution. The systems are getting smarter & more complex every day passing … it’s getting close to impossible to understand how everything is working ‘inside the box’, although it will probably save you some time when things start to fall apart ;)

  • http://davybrion.com Davy Brion

    wait, just to avoid any confusion… i really don’t think generating a bunch of network and/or db requests should be a part of evolution :) . No matter how fast our systems and networks are getting, it is still wrong ;)

  • http://yoot.be Stiiifff

    Yeah, of course ;) I mean that, eventually, solutions to those problems are being integrated into the platform (Linq queries batching ?) and optimizations are done for you, transparently, so that you can focus on what’s most important : solving business problems.

  • den Ben (aka SQL~)

    I think that what Stiiifff is saying is that in about 10 years you shouldn’t worry about db- and/or network requests anymore. The ‘framework’ will do that for you. If you have a hard time believing that, then consider that thought next time you are writing a linq query.

    However… i also believe edge cases will still remain to exist ;-)

    And I am _NOT_ old :p
    dammit ;-)

  • http://davybrion.com Davy Brion

    well since that’s what i’m already doing with all my service request batching and query batching i don’t think we need to wait another 10 years ;)

    and i didn’t say you were old :p

  • Rob

    I do think Ben might be considered pretty old though.

  • http://davybrion.com Davy Brion

    Rob, aren’t you only about a year younger than he is? :)

  • J

    Well I realize this is an old topic, but I am of the old school that would prefer to do everything in SQL. As the original commenter stated…SQL is a high level language already that abstracts away what happens when you join multiple tables etc. That being said, I was (am still?) skeptical on how LinQ to SQL does under such circumstances as data manipulation. So, I took 2 hours today, and converted a relatively simple transformation based off of the AdventureWorks DB to see what the equivalent code is like.

    Original SQL:

    Declare @StartDate datetime, @EndDate datetime

    Select @StartDate = '1/1/2003', @EndDate = '12/31/2003';

    Select *,
    Commission = TotalSales *
    case
    When PctAttained < 25 Then 0
    When PctAttained < 50 Then CommissionPct * 0.50
    When PctAttained = 50
    order by SalesPersonID, OrderYear, OrderMonth;

    My equivalent LINQ (mind you, this is my VERY FIRST Linq to SQL attempt):

    AWDataContext context = new AWDataContext();
    DateTime startdate, enddate;
    startdate = DateTime.Parse("1/1/2003");
    enddate = DateTime.Parse("1/1/2004");

    var query = from soh in context.SalesOrderHeaders
    join sp in context.SalesPersons on soh.SalesPersonID equals sp.SalesPersonID
    join c in context.Contacts on soh.SalesPersonID equals c.ContactID
    where soh.OrderDate >= startdate && soh.OrderDate c.SubTotal),
    PctAttained = soh_group.Sum(c => c.SubTotal) / soh_group.Key.SalesQuota//,
    //Commission = soh_group.Sum(c => c.SubTotal) / soh_group.Key.SalesQuota .5
    select new{q.SalesPersonID,
    q.FirstName,
    q.LastName,
    q.Year,
    q.Month,
    q.SalesQuota,
    q.CommissionPct,
    q.TotalSales,
    q.PctAttained,
    Commission = ( Convert.ToDouble(q.PctAttained) > .75 ? Convert.ToDouble(q.TotalSales) * Convert.ToDouble(q.CommissionPct):
    Convert.ToDouble(q.PctAttained) > .50 ? Convert.ToDouble(q.TotalSales) * Convert.ToDouble(q.CommissionPct) * .75 :
    Convert.ToDouble(q.PctAttained) > .25 ? Convert.ToDouble(q.TotalSales) * Convert.ToDouble(q.CommissionPct) * .50 :
    0)
    };

    dataGridView1.DataSource = commission;

    I’m sure I could have done this in one step, but I couldn’t quite put the pieces together.

    After examining the generated SQL code, I’m pleased that it looks semi-decent.
    Also, that being said, LINQ to SQL doesn’t fit into many scenarios that I deal with on a regular basis, so I will probably continue down the plain old SQL route for the time being.

  • J

    Hmmm…looks like it truncated my code even thoug I used code blocks…oh well.

  • http://zoldello.wordpress.com Phil

    SQL is not going the way of the Disco. Consider, LINQ is not always good for complex SQL queries and queries that need a lot of fine-tuning. Plus, there is legacy SQL that need to be maintained. So, while it may not be as prevalent as it was in the early 2000′s; it will still be around in the early 2010′s.

Meta

Recent Posts

Categories

Archives