This article is more than 1 year old

Tune your service oriented applications with SQL

Bottleneck, meet database

If the requirements process does not provide performance information then an alternative is to perform tests with different loads and different size datasets. The goal is to identify areas where performance should be investigated and to make statements like “response in 500ms when there are 10,000 providers and 1,000 simultaneous users”.

Once the performance characteristics of the system have been established we can set to addressing the problems. Traditionally, profiling is used to identify where the CPU cycles are spent, here I suggest that the low-hanging fruit may be the SQL.

If most of the response time is being spent in the database then start by considering the following.

Is the data model appropriate for the applications needs? If the model is over normalized or inappropriate, then more costly relational joins must be used rendering the SQL more complicated and difficult to process. It's not a hard and fast rule, but chances are that if you have to do complex joins to recover basic information then you should look at your data model.

How much of the information brought back in a select is actually used? A full table scan of a large data set will hamstring the database, a real waste of both network traffic and database performance if the functional code uses only a small part of the data brought back. Where clauses should be on indexes as much as possible, this may even allow the database to calculate a result set using only information it holds in memory.

If a query can be identified as problematic then the explain function present in Oracle and many other databases allows its performance to be evaluated. The output of explain shows where the database spends its time while processing the query and gives the developer feedback so he can restructure and perhaps achieve more efficient query execution.

If the functionality is badly specified then this sort of engineering can be a losing battle. I always think of this when search engines return thousands of results: if I don't find what I want in the first few pages I try another search. For example, a search for hotels in London on Expedia takes about 10 seconds and returns 27 pages. An alternative interface and use case could have resulted in less work for their servers and better information for me.

So do you know the difference between an inner and outer join? Does your stomach turn when you see regular expressions where there should be clauses? Can you look at two queries returning the same information and know which is executed more efficiently by the database? If not, then maybe brushing up your SQL skills could save a fortune on the deployment cost of your next application.®

More about

TIP US OFF

Send us news


Other stories you might like