Query Discussion

Introduction

When an SQL query is turned into a result set I imagine the process is something like this:

• Break the query into other result sets or sets of result sets to break some more
• Generate the result sets and merge them together

Simple query

More complex query

This query is more complex but still quite a simple one. You can see how using indexes would speed this up, an index on the customer ID in the customers take you straight to the row data you need to construct the customer projection, similarly an index on customer id on sales targets would give you the rows you need to create the targets projection.

If you think about this some more you can also see how the order the intermediate projections are created matters. In the complex example above we could place the filter on sales targets instead of customers. Then we would be retrieving the single customer row for every sales target row, which is a lot more processing. Driving the query from the smaller table into the larger one is always a good idea. When you start to get large amounts of data you can see how putting the right indexes in the right place starts to become important too. One of the banes of well performing SQL is the full table scan on large tables, or a partial index match that actually makes more work than using the index would save.