Hands on: Understanding SQL queries

This illustrates the beauty of declarative languages such as SQL, because most writers of queries will not know such details as table sizes and how they’re indexed, or indeed how to make best use of this knowledge. Happily all that can be ignored as the query optimiser works out the plan for you.

The bad news is that all this takes time. For a query built and used within Access, the optimiser will work out an execution plan every time the query is run.
However, suppose your data is held in SQL Server and you’re querying from an Access client application.

If you store your query locally in Access then, when you run it, Access doesn’t do the optimisation. It sends the query to SQL Server. When it arrives, the optimiser in SQL Server will work out an execution plan and, again, it has to do this every time the query is run.

However, you can also choose to store the query as a ‘View’ on the server. In this case, the execution plan is built the first time the view is run and thereafter is stored in the database along with the SQL string.

When the view is run again, it uses the saved execution plan, pulls together the most up-to-date set of data and presents the answer. The bottom line is that in a client-server environment, views will often run faster than queries from the client application. The improvement in performance can be significant, especially when the SQL becomes more complex.

Upsizing your engine
In response to the recent reader poll, I started discussions about moving upwards from Access to SQL Server. I’ll continue with a little scene setting and then descend into more detail about some of the differences you’ll encounter should you decide to play the SQL Server game.

As I said last time, the best place to start is SQL Server Express, mainly because it’s free. As with most Express/Lite/Junior editions, there are limitations to what you can build, and Express also imposes hardware restrictions. Express will only address one physical CPU and a maximum of 1GB of Ram; it will run on machines with higher specifications, but ignores any additional CPUs or memory.

The maximum size a database can reach is 4GB. However, anything you build can be upgraded to the Enterprise edition if it becomes necessary. It is also OK to build stuff commercially: “there are no licensing restrictions for applications built using the Express Editions”, according to the Microsoft FAQ.