لطفا منتظر باشید ...
Recipe 8.8 Accelerate Client/Server Applications
8.8.1 Problem
You are using Access as a front end
to linked tables stored in a client/server database.
You're not satisfied with the response time of your
client/server application. What can you do to make it run faster?
8.8.2 Solution
You can apply a variety of optimization
techniques when developing client/server applications. If you are
attaching remote tables in databases such as SQL Server or Oracle,
you are accessing data through open database connectivity (ODBC)
drivers. Typically, client/server applications using ODBC require
more horsepower on the part of workstations and the network. By
knowing how data is retrieved from the server, you can make your
application run faster.Another
option is to create an Access Data Project (ADP). This is possible
only if your data is stored in SQL Server. Instead of using ODBC,
ADPs use a newer technology, OLE DB, to connect to the data. However,
although OLE DB is newer, it isn't necessarily
faster than linking to tables using ODBC. Chapter 14 includes several solutions related to the
use of Access project applications.There is no sample database for this solution. Here are some
suggestions to consider when optimizing your linked-table
client/server application:
- Your forms should retrieve as few records
as possible when loading (fetching data is a significant bottleneck
in client/server applications). Design your form to retrieve few or
no records by using the technique demonstrated in the Solution in
Recipe 8.7. - Optimize the way your application connects
to the server. When the user starts your application, log the user
into the server using the OpenDatabase method. This establishes a
connection and caches it in memory. Subsequent data access is faster
because the connection has already been established. Use code similar
to the following:Sub PreConnectUser (strUser As String, strPass As String)
Dim wrk As DAO.Workspace
Dim db As DAO.Database
Dim strConnect As Database
strConnect = "ODBC;DSN=MyServer;DATABASE=dbCustomers;" _
& "UID=" & strUser & ";" _
"PWD=" & strPass & ";"
Set wrk = DBEngine.Workspaces(0)
Set db = wrk.OpenDatabase(", False, False, strConnect)
End Sub - Reduce
connections by limiting recordsets to 100 records or fewer. Most
servers (such as SQL Server) require two connections for recordsets
of more than 100 records. By limiting the size of the recordset, you
reduce the number of connections that need to be made, speeding up
your application. - Offload as much query processing as
possible to the server. Generally, your server will search and
process data faster than the local Jet engine, especially if there
are many concurrent users (this is probably the reason you moved to
client/server in the first place). Design your queries to eliminate
expressions or functionality not supported by the server. If the
server does not support an expression or function used in your query,
Access will process the query locally and performance will suffer.
Read the documentation that comes with your database server to
determine which functionality is supported, and use profiling tools
on the server (like the SQL Server Profiler) to see what is actually
being processed on the server. - Add a timestamp field to a table to
improve update and deletion performance. The server automatically
updates Timestamp fields, also called Rowversion fields, when any
data in a row is modified. If a table has a Timestamp field, Access
can use it to determine quickly whether a record has changed. If the
table doesn't have this field, Access needs to
compare the contents of every field to see if the record has changed.
Obviously, checking a single field is a lot faster. To add a
Timestamp field to a table on the server, you can create and execute
a SQL-specific query in Access using the ALTER
TABLE statement with syntax similar to the
following:ALTER TABLE Customers ADD MyTimeStampCol TIMESTAMP
- Avoid using server data to fill list box
and combo box controls. The performance of these controls is
generally poor when accessing server data. Instead, consider storing
the data for the list box or combo box in a local database. This
approach works if the data does not change frequently and can be
easily copied from the server. See the Solution in Recipe 8.2 for more on list box and combo box performance
issues and alternatives to their use. - For working with server data
in code, ADO is more efficient than DAO. We can't
discuss ADO coding techniques fully here, but take the time to learn
ADO if you want to fill recordsets with server data or to execute
server commands. (On the other hand, DAO recordsets tend to be more
efficient, and simpler to use, when working with Jet-based
datadata retrieved from MDB or
MDE files.) Pay special attention to the
CursorLocation property, which allows you to close a connection and
still be able to work with the data in a client-side ADO recordset.
Here is an example of opening a client-side recordset, disconnecting
from the database, and then working with the data in the cached
recordset:Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim strEmployees As String
Set cnn = New ADODB.Connection
cnn.Open "Provider=SQLOLEDB.1;" _
& "Data Source=(local);Initial Catalog=Northwind;" _
& "User ID=username;Password=secretpwd"
Set rst = New ADODB.Recordset
rst.CursorLocation = adUseClient
rst.Open _
Source:="SELECT EmployeeID," _
& " LastName, FirstName" _
& " FROM Employees" _
& " WHERE EmployeeID = 5", _
ActiveConnection:=cnn, _
CursorType:=adOpenStatic, _
Options:=adCmdText
Set rst.ActiveConnection = Nothing
cnn.Close
Set cnn = Nothing
Debug.Print rst("FirstName")
rst.Close
Set rst = Nothing
8.8.3 Discussion
Understanding how client/server
applications differ from single-user and file-server applications is
crucial to optimizing their performance. The key is in deciding when
to let Access do the work and when to let the server do the work.
With a few exceptions, you want the server to perform queries and
Access to perform user-interface operations. Concentrate on
minimizing the traffic across the network by reducing the data
retrieved from and written to the server. To work with server data
programmatically, use ADO rather than DAO.
|
