Tune your service oriented applications with SQL
Bottleneck, meet database
Customer Success Testimonial: Recovery is Everything
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-existent. It can be difficult to test performance until late in the development cycle and - trust me when I say this - late in the end of the development cycle is not a good place to discover performance problems.
With Oracle fresh in our minds thanks to OpenWorld, I want to look at the performance problem with regard to the database. Profiling and algorithmic complexity are - of course - important, but as the world becomes more distributed and service oriented we should consider if our performance dollars can be better spent improving our SQL queries. Does this sound crazy?
According to author and industry expert Gavin Powell, an order of magnitude application performance increases can be obtained simply by tuning the SQL and correctly optimizing the data access path.
This is an incredible figure and it begs the question: can developers afford to continue leaving SQL expertise to the DBA? After all, if such massive gains are regularly possible then doesn't this mean that a lot of developers are producing pretty questionable SQL? We can't argue that it's not important because it's definitely not cheap to scale the database that's for sure.
What's making it worse is that in our connected world performance bottlenecks are more likely to be discovered. In the past, barriers to integration such as obscure APIs and thick clients kept information within the application and only the most stubborn external aggregators could access it.
These days, web services and service oriented architectures (SOAs) mean we are no longer protected from scalability and performance issues. Increasing aggregation of information is pushing demands on software service infrastructure and any bottlenecks in the system will be exposed sooner or later. If the system grinds to a halt then the chances are it could spell project failure - and that does not look good on the CV!
We need to accept that application performance needs to be explicitly managed. It's good that we implement use cases and write tests that demonstrate functional correctness. Unfortunately, functional tests that use minimally populated databases do not tell us anything about the performance characteristics of a system under real world data volumes with parallel processes making simultaneous demands on the database.
Regcast training : Hyper-V 3.0, VM high availability and disaster recovery
COMMENTS
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.
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!
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.
IT infrastructure monitoring strategies
Agentless Backup is Not a Myth
Top 10 SIEM implementer’s checklist
Steps to Take Before Choosing a Business Continuity Partner
Enabling efficient data center monitoring
