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
MUST Do's
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.
Once in design mode, select Properties from the View menu and select an ODBC
DSN or enter a connection string.
Stored Procs
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.
Conclusion
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.
»
See All Articles by Columnist Danny J. Lesandrini
» 
Comment and Contribute