The Artificial Intelligence In Relational Databases

Tell the Database What to Do, Not How to Do It

Every day many millions of people use artificial intelligence to access their databases. As with much good artificial intelligence, these many millions are not even aware that they are using it. We are talking about Query Optimisation.

When I started working on databases, they had inflexible storage structures and to access them we had to write programs (I mean procedural, algorithmic programs) to navigate through the structures and find the data we wanted, or add to it, or change it. One of the innovations of relational databases was that they separated the logical structures that we deal with from the many physical ways that the data could be stored. This increased productivity massively, by saving us writing so many programs and by allowing us to tune our databases without changing the programs that accessed them.

The intial query optimizers worked in many cases, but sometimes they would go off to the database and use an inefficient way to get the answer we wanted. That was long ago. Very smart people in universities and in companies that have commercialised database software, have developed query optimizers for over 50 years and they are now very robust. It is now almost impossible to beat the query optimiser using your own code, to the point where trying to do so is simply a waste of your time.

Now when we tune a database, we are aiming to give the optimizer good information to work with and then get out of its way. Relational databases all give us the ability to index particular columns and combinations of columns. Often there are choices of index methods. Relational databases all keep metadata about the tables we have, the columns in them, the data-type used for each column and the indexes. Relational databases also allow us to analyze tables to generate statistics that describe various characteristics of our data and to store these statistics with the other metadata. The optimiser uses all this metadata to find the best way to run any query and provide the results as fast as possible.

Our input, as database developers, is to:

• Design our database properly.
• Add indexes only where they can be shown to improve performance.
• Run the analyze command frequently to keep the optimizer statistics fresh.
• Write queries that honour the relational database patterns, and don't try to drive the optimiser down inefficient paths.

All these are described with extensive examples in Relational Databases For Agile Developers. I wrote this to give a readable, practical guide to using relational databases effectively. It is 350 pages (not the 1,200 pages of many database text books), reasonably priced (not the $50 - $100 of many database text books), and written for real-world smart developers (not wannabees and not boffins). I want to share what I have learned in the last 45 years and what I have been sharing with other developers (and constantly updating) over the last 20 years.

What I mean by "proper" database design is making the design a faithful representation of the data we are modelling, and using the small number of simple patterns that enable us to model all but the most esoteric of systems. Really! If you are working in any kind of record-keeping or accounting system, anything that deals with numbers, dates and times, and (mostly short) text strings then your systems are almost certainly best-served by the relational database model. If you are working with massive text indexing (like Google), or processing of images, sound or video, then a relational database is not ideal. Most of us are working in the kind of systems that are most suitable for relational databases, because these are the systems that are used most by business, government and the non-profit sector. By the way, if you are not working for Google, Facebook or possibly Twitter then you don't work for anyone like Google. The typical high-volume companies (international banks, telephone companies, etc.) have less than 1,000th of the data volume of Google and Google rules do not apply if you have less than 1,000th the data volume.

• Putting sub-selects in the list of result columns (the select list). Don't do this! Sub-selects (nested queries) are OK in the from clause, and often the best solution. (But if you are getting more that three levels deep, you should think carefully about what you are doing.)
• Randomly adding outer joins until you get a plausible result. You need to understand what you are doing. Don't "program by coincidence".
• Converting strong data-types to strings to manipulate them. The strong data-types (numbers, dates and times) have highly optimised functions to manipulate them, so don't convert them to strings and try to do it yourself.
• Thinking about your SQL query in terms of looping or if ... then ... else blocks. Think about what you want to do, not about how to do it.
• Writing the database navigation in the client application. Don't do this. Push the database access down to the database server where the query optimiser will make a better job of it than you or I can ever do.

When artificial intelligence works well and is being used for something positive (like freeing us to work on our real business problems) then it is genuinely cool. Use your query optimiser - it is one of the best examples of artificial intelligence that you have at your disposal.