SQL Coding and Tuning for Efficiency

Coding and tuning SQL is one of the most time consuming tasks for those involved in coding, managing and administering relational databases and applications. There can be literally thousands of individual SQL statements across hundreds of applications that access your many production databases. Although the DBA is ultimately responsible for ensuring performance of the database environment, there is quite a lot that application developers can do to help out. Frequently, developers are only concerned with getting the right answer (which is, of course, required) but not with getting it in the most efficient way.

When coding SQL statements, the following steps need to occur for each and SQL statement that you write:

  1. Identify the business data requirements
  2. Ensure that the required data is available within existing databases
  3. Translate the business requirements into SQL
  4. Test the SQL for accuracy and results
  5. Review the access paths for performance
  6. Tweak or re-write the SQL for better access paths
  7. Possibly code optimization hints
  8. Repeat steps 4 through 7 until performance is within the required range.
  9. Repeat step 8 whenever performance problems arise or a new DBMS version is installed
  10. Repeat entire process whenever business needs change

SQL tuning is a complex, time consuming, and sometimes error-prone process. Furthermore, it requires cooperation and communication between the business users and application programmers for the first three steps, and between the application programmers and the DBA for the remaining steps.

It is imperative that developers learn more about SQL performance and take steps to be proactive about coding their programs with performance in mind. This is especially the case in the modern DevOps, continuous delivery, agile world where code is moved to production rapidly and numerous times a week.

If developers are not concerned about performance – or only marginally so – then it is a certainty that your organization will experience performance problems in production. There are simply not enough DBAs and performance analysts available to examine every program before it is moved to production these days.

How can you become a performance-focused developer? Here are a few suggestions:

  • Read the manuals for your DBMS of choice (Oracle, Db2, etc.), especially the one that focus on performance. Find the SQL-related items and concentrate there, but the more you understand about all elements of database performance the better coder you will be.
  • Purchase books on SQL performance. There are several good ones that talk about performance in a heterogeneous manner and there are also many books that focus on SQL for each DBMS.
  • Talk to your DBAs about SQL techniques and methods that they have found to be good for performance.
  • Learn how to explain your SQL statements and interpret the access path information either in the plan tables or in a visual explain tool.
  • Use all of the performance tools at your disposal. Again, talk to the DBAs to learn what tools are available at your site.

And always be tuning!

Advertisements

About craig@craigsmullins.com

I'm a strategist, researcher, and consultant with nearly three decades of experience in all facets of database systems development.
This entry was posted in books, performance, SQL. Bookmark the permalink.

One Response to SQL Coding and Tuning for Efficiency

  1. Pingback: SQL Coding and Tuning for Efficiency - SSWUG.ORG

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s