Sign in / up

back to article Tune your service oriented applications with SQL

Performance is one of the more insidious problems that a developer can face. We don't seem to have time for it with all work just getting an application's functionally correct. The problem is that it's a non-functional requirement and as with all non-functional requirements the specification is often either vague or non- …

COMMENTS

House rules Send corrections
This topic is closed for new posts.
  1. RichardB
    Alert

    Hardly news, perhaps a lesson that must be relearned

    As a DBA I would say that the majority of developers I see working on databases have no concept of performance considerations.

    There are often tuning opportunities well beyond the order of magnitude - you can often find things that take 10 - 30s and pare them down to a few milliseconds. Daily loading tasks that ease over the 'day in a day' threshold can be brought down to a few minutes.

    This is all usually before you even get as far as remodelling the database design, just making sure the queries aren't doing frankly stupid things.

    By investing in serious DBAs and serious dedicated SQL Developers to work alongside the client side developers you can make quite astonishing improvements to concurrency and throughput.

    As I said - not a new concept at all, but then lessons do seem to have to be learned over, and over and over again in the IT industry.

    0 0
  2. Bruno Girin
    Thumb Up

    Spot on

    The last J2EE project I worked on, we did some serious performance tuning: more than 90% of the tuning was optimizing SQL queries. So when on Oracle, learn how to use and understand stats packs and execution plans.

    As usual, it's a case of using the KISS principle. If your database schema looks too complicated, it probably is and you will end up doing a stupid amount of joins that will make your app grind to a halt. This tends to happen when you try to map a complex object model to a relational database.

    One thing on Oracle that should make alarm bells ring is if someone tells you they used materialised views to increase performance. This should only be used with caution and you should understand the trade-off: if a normal view performs badly when you read from it, a materialised view will perform badly when you write to the tables the view is built upon. You can't have it both ways.

    Happy tuning!

    0 0
  3. Anonymous Coward
    Thumb Up

    Good. Someone's noticed.

    SQL Server optimisation is my hobby.

    On a recent job, I turned a system from one DWH query every 45 seconds, to 280 in 45 seconds. I wrote a TOPANDBOTTOM(1000) clause ordered by multiple columns, in six lines of transact SQL. Didn't even need the CLR, though you can do some outrageous things with custom aggregates.

    The thing is most people don't believe it's possible until they see it.

    I regularly achieve greater than 50 times performance improvement. Once I reached a factor 400, but this was on an nhibernate system, and all that object relational technology was designed to be slow as pigs just to sell more computers.

    I've failed interviews because there are guys out there who genuinely believe it's more sensible to scale out middle tier servers and cache everything, than to scale out an array of read only replicated SQL servers, and load balance them.

    Noone believes it when they see a stored procedure go down from 10 seconds, to 0.2 mS.

    I find the people who can do this kind of thing have a set based mindset, often at the expense of something else.

    I'm so glad Linq has arrived, because it means we can do the same thing in the business logic too.

    0 0
  4. Martin Gregorie

    What's surprising about this?

    The problem is usually not the data model. If the high level design is a reasonable match to the requirements and the data model was derived from that and then reduced to Third Normal Form its usually OK.

    The problem is lack of communication. If there's no feedback to the database designers from the technical designers and module developers then the database indexes and storage schema are unlikely to match the access paths required for SQL to execute efficiently. Its as simple as that.

    Typically, the system will pass acceptance tests and then show poor performance as the data volume builds up toward the designed levels. The DBAs can often sort out the problem by analysing SQL in the source repository to discover the access patterns and then adjusting the storage schema and indexing to suit: I've been there, and done exactly that. I've never needed to redesign the data model, but on a couple of occasions I have needed to rewrite SQL.

    In the first case the SQL had been automatically generated by PowerBiulder and was obvious garbage. A simple manual rewrite reduced the response time from around 45 seconds to under a second. That was about a two hour fix from a cold start on the problem.

    The second occasion was the result of a particularly lame-brained technique used by the MFC Foundation Classes when they had to expand the column list in a "SELECT * FROM..." statement. In this case all we had to do was to manually specify the column list (and kick some programmer arse for using the "*" shorthand in the first place) and again we got better than an order of magnitude speed up. The SELECT statements were only retrieving a row or two, but the MFC glob expansion technique forced an additional full table scan: not clever when the table contains tens of thousands of rows. This nonsense was due to some M$ coder not having read the ODBC manual (there was an almost zero cost ODBC function he should have used) and to his supervisors for not having done a code review.

    0 0
  5. Tuomo Stauffer
    Mars

    I feel so old

    SOA or not ! Good comments except this is an age old problem, how to access information. I agree with RichardB except my experience of DBAs ( unfortunately ) is not very good, sorry. SQL ( structured query language NOT necessary using a relational database ) mostly refers to relational access ( it is a little more ), now you define your database relations instead programmers in programs and if you do it right it works, in theory. But right means that you really know how your 1000+ tables and 10000+ relations are used AND on which channel, disk array, cluster node, RAID array, read or write or both they they are used and what kind of crazy queries the end users create. One query wrong way and the whole system stalls, maybe sorting something over and over again or maybe accessing disk in random order instead of sequentially for a billion line report and seeks killing the system or maybe the account numbers (keys) were in city order (%90 access going to one poor disk in 100+ array ) or whatever.. All old problems but now the responsibility of DBA who should (IMHO) actually be designing the business logical access, do it right and let the infrastructure people take care of physical layout. As RichardB said, an old problem and DBA should be a (good) mediator between application/end user groups and infrastructure. And please, don't let end users create their own reports, the idea was a disaster already in 70's and it is not any better today, you never know what they do but what is sure, they will stop any online system, beware especially corporate statisticians running wild!

    0 0
  6. David Keeley

    Refreshing article

    How refreshing to see an an account of the often taken for granted importance of old fashioned SQL and the RDMS. Some of the romantic things written about Javas JPS and .NETs LINQ etc, indicating that developers 'wont have to worrry about SQL' anymore defies the experience of most who have worked with relational data on a large scale enterprise systems. Certainly it is my belief that expressive set based queries with appropriate execution plans are still the key to a well performing database application and any effort to have that right early in the manufacturing process pays off.

    0 0
This topic is closed for new posts.