Subscribe

Harness the power of SQL to become a top DBA


Johannesburg, 24 Oct 2002

The quality of the SQL code written by a database professional will determine, to a large extent, the success of their craft. Alan Raubenheimer, CEO of Embarcadero Africa, analyses the issue of SQL quality, its impact on companies, and suggests ways of improving it.

When companies report that their data warehouse is under-performing, they tend to point at the technology platform: the database, the server, the storage subsystem, all of these could come in for tight scrutiny.

However, it might not be the technology that`s at fault: instead, it could be the SQL (structured query language) code that is causing the problem. Poorly written, inefficient code will cause a query to run far slower than it should.

SQL has been around an awfully long time and on the surface of it is not the most complex language in the world. You would expect quality SQL code to be the norm, but it isn`t, especially in today`s world where PC and Web mentalities rule.

IT departments today are often characterised by SQL inexperience. Indeed, at a time when business intelligence and data warehousing have entered the mainstream; when databases are becoming increasingly distributed and the requirement for discipline grows apace, the number of SQL-qualified and battle-hardened DBAs and developers is on the decline.

Clearly, such a situation is not conducive to good database performance, and will lead to the business being compromised. Herewith, then, some guidelines on writing quality SQL code so as to improve your ability as a database professional:

* Parse your SQL code before submitting it. Validating your code will ensure that the query you run is syntactically correct. If you don`t do this, you will continually experience syntax problems which erode your productivity.

* Ensure you are familiar with all the intimate details of your database. One database might automatically update all objects in the data dictionary; another might need you to refresh the catalogue manually - and often! It isn`t hard - but it does have to be done.

* Get to grips with the intricacies of the EXPLAIN analysis (Oracle) or SHOWPLAN (Sybase and SQL Server). Your coding flaws and shortcomings are certain to be more easily identifiable if you know where to look!

* Mix and match. If you have executed the first three steps correctly, you can begin experimenting with combinations to enhance query performance. The use of hints is indicated here, but because hints do not yield syntax errors, I would recommend the deployment of a good SQL tuning tool or a debugger to highlight possible problems.

* Measure your incremental improvements. If you have passed the previous steps, you are well on your way to being an efficient database professional. But how do you measure yourself? By running live tests and benchmarking each result against further tests to see what improvements are possible. Measure elapsed time; physical and logical I/O; memory usage; sort operations; CPU usage; and parse time.

* Take nothing for granted, and keep on retracing the same ground to look for performance improvements or degradation. What today is a hare of a database can rapidly become a tortoise, often due to SQL code that is creating a system bottleneck. Rapidly growing data volumes and missing indices are two typical technical issues that can bring a database to its knees: both need to be identified early and corrective actions instituted.

Generating quality SQL code is a challenge for beginners and veterans alike. For both, I would hazard to say, the commonality is that you need to work in a standards-based code framework. That`s likely to give you the response times your systems, database and business need and demand.

Share

Editorial contacts

Augusta Liebenberg
FHC
(011) 608 1228
augusta@fhc.co.za
Alan Raubenheimer
Embarcadero Africa
(012) 346 3155
alanr@embarcadero.co.za