Optimizing Client Server Queries
This installment concludes the series started by Marc Israel, Migrating Access Applications to SQL Server by Marc Israel. In previous articles, we discussed some of the migration issues, from upsizing tables to conversion of Access queries to SQL Server stored procs. I've procrastinated on this last topic, Query Optimization, because it's the most difficult to describe.
Optimization is a difficult subject because it comes in several forms. One form of optimization has to do with how you write your SQL queries, which may be beyond the scope of this article. However, there are some generic suggestions that might improve your application performance. They include:
- Bind to SQL Server Views instead of directly to Tables
- Use SQL Pass-Through queries for read-only access
- Implement SQL Server Stored Procs for complex data
- Consider an Access Data Project (ADP) with ADO data access.
- Leverage SQL Server Management Studio analysis tools
We Access developers are accustomed to the Link n' Go method. Point to an MDB with data tables, link to it, and you're done. When working with SQL Server, you have another option: Views. SQL Server Views are linked in the same way tables are and in face, look much like tables in the Access user interface.
SQL Server Views allow you to partition a table, limiting the number of columns exposed and/or the number of rows returned. This has obvious performance benefits. There may be forms that simply don't require tens of thousands of old records returned. Maybe it deals with the current month or year, and all other records may be filtered out. This can be a great boon to performance. The same goes for excluding columns, where possible.
Pass-Through queries are only available in Access MDB files and they are not updateable. While this makes them useless as the record source for edit forms, they are perfect for ListBox and ComboBox record sources. They work well for reports too, with one exception: Access protests if you try to use a Pass-Through Query as the record source of a sub-report. I haven't found any way around this myself, and I haven't checked the status of this 'bug' in years, so if anyone has found a solution to this, please let me know.
Create the Pass-Through query by selecting SQL Specific | Pass Through from the Query menu.
Once in design mode, select Properties from the View menu and select an ODBC DSN or enter a connection string.
SQL Server Stored Procs are great. There are times that Access Developers need to do some very creative things with local temp tables, aggregating calculations, summarizing data and returning the results. This can often be accomplished at the server by means of Stored Procs and SQL Functions. Within a Stored Proc, you can write T-SQL code, declare variables, use branching logic & encapsulate and call frequently used calculations in functions. A Stored Proc does all this work on the server, accessing any and all data necessary for your complex results set without having to pull it all down, through the network, to the Access client. All that passes across the wire is the end result.
Should Do's ... ADPs
Access Data Projects (ADP) are a topic of no little emotion and debate, but if you're starting a new Access-to-SQL Server application, it's worth consideration.
ADPs have no local Access tables, but are directly connected to one SQL Server database. Therefore, all tables are automatically connected-- there is never any linking of objects. As new tables, views, stored procs and functions are added, they appear in the Access UI.
It's also important to understand that ADPs use ADO instead of ODBC to connect to the data. In theory, this provides a performance gain, but as I said above, this assertion is disputed by some. However, I'd like to throw in my 2 cents in favor of using ADO. Back in April of 2007, I submitted an article, Manage Recordsets in ADPs, explaining how to manage data access using ADO from an ADP. In my experience, this is very fast, and extremely convenient!
One last benefit of ADPs that's worth mention is that in some cases, Stored Procs may be used as the bound recordset of edit forms. The Access UI needs to be able to identify the table involved in the results set, needs to include all required fields, and may need a hint about the "Unique Table" involved. This last criterion is assigned in the form's Unique Table property, which is only exposed on forms in ADPs.
One caveat: Converting your MDB to an ADP is not trivial, unless it is very, very simple. This suggestion is given as an option for those projects that are still in the design phase.
Access Developer Wish To Do's
The last performance consideration is, at best, an introduction to some of the tools available. If you have SQL Server tools installed, can open SQL Server Management Studio (SSMS) for SQL Server 2005, or Query Analyzer for previous versions. In either case, there are performance analysis tools available to you. The screen shot below gives you and idea of some of what's available to you.
Notice that upon running a query from the SQL window, the status bar is updated with the number of rows and the time (in seconds) required to return the results. If you turn on the option to display execution plan, even more information is returned about what SQL Server did to return the dataset. I've never been good reading these plans, so I'm not the person to write the article on how to use it, but suffice it to say that a whole new world of performance tools are opened up to the Access developer who wants to dig into it.
Query optimization is not simple, but there are things you can do. This article has outlined some development concepts that will, without question, improve performance. Additional improvement will be realized when SQL Queries are tuned to use SQL Server more efficiently. There is no magic bullet but with some concerted effort, these suggestions will have your application humming in no time.