Hands on: Understanding SQL queries

All relational databases use SQL (Structured Query Language) to query the data. But when you think about it (and even when you don’t), SQL belongs to an odd class of computer language because it is used to tell the database what to do, but not how to do it.

So, at some point the engine itself has to decide how it should carry out our wishes. Understanding how it does this is vital to be able to tune databases for speed.
So, stepping back a fraction, there are essentially two classes of programming language: procedural and declarative. SQL is declarative and is also an excellent illustration of the difference between the two classes.

In a procedural language (such as C++ or Java) we specify the steps the computer must follow in order to solve the problem ­ take variable A, add it to variable B2, divide by variable J and place the result in variable Z. In declarative languages such as SQL, we simply describe what we want to see as the result, without spelling out the necessary steps. For example, if you write an SQL query like this:
SELECT Employee.FirstName, Car.CarName
FROM Car INNER JOIN
Employee ON Car.CarID = Employee.CarID
WHERE (Car.CarName = ‘GT6’)
you are telling the database engine to find out who was allocated a GT6 ­ pulling the CarName from the Car table and the FirstName from the Employee table.

But you haven’t told it, for example, which table to query first. (Even though the tables are named in the order Car then Employee, this doesn’t carry any information about which is to be queried first). But the order in which the tables are queried can make a huge difference to the performance. Suppose Car has one row and Employee has 20 million. It will be much more efficient to query the car table first. So how is that decision made?

When the SQL string arrives at the database engine it is passed to the ‘query optimiser’ which works out a procedural plan for executing the SQL. The optimiser will look at many pieces of information, including the sizes of the tables, which columns are indexed and so on.

Based on this information the optimiser builds an ‘execution plan’, a procedural description of how it will deliver the requested data. An execution plan is also created in Access but, sadly, it cannot be viewed in this way. As the query becomes more complex so, of course, does the plan.