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

Views
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
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.

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.

Full article...