Performance problems moving to MDAC2.5
We have had a puzzling performance inconsistency using the MDAC2.5 and 2.6
components. We noticed this when we moved our application from MDAC2.1 to
We noticed this when upgrading the program at some sites and running into
unacceptable performance problems which we did not have with MDAC 2.1.
The tests were done using WindowsNT ( computer A), Windows 2000(computer
B) and Windows98 (computer C).
We are running a Access 2 database with 50,000 records. We have timed 2 lookups.
The code is as follows:
Lname,Fname, `SearchNum` are indexed and are all text fields SearchNum values
are always 10 digits
Dim Rs2 As New ADODB.Recordset
Set Conn2 = New ADODB.Connection
Conn2.CursorLocation = adUseServer
Conn2.ConnectionString = "DSN=test32"
Rs2.Open strsql, Conn2, varcursor, adLockOptimistic
varcursor can be set to any cursor for testing purposes.
Locally the lookups are less than 1 second. Across a LAN, the following problem
If code below is run, it takes about 2 seconds to find the right record.
strsql="SELECT Lname,Fname,`Chart no`From Profile WHERE LName like 'SMITH%'
and FName like 'BRIAN%' order by lname,fname"
If the next code is run, it takes from 12 to 17 seconds to find the right
strsql="SELECT Lname,Fname,`counter` FROM Profile WHERE `SearchNum` like
'9634645346' order by lname,fname"
Returning the MDAC components to 2.1using Comcheck does not solve the problem.
Upgrading the tables to Access 2000 tables does not solve the problem.
Upgrading MDAC to 2.6 solves the problem on computer A( IBM thinkpad - pentium
II) and C(Pentium MMX) but not on computer B (Pentium MMX). We have not
upgraded computer C yet as MDAC requires IE 5 which is not yet installed.
Overall, the type of computer on the network does not seem to make much difference
in speed of the lookups.
When the second query is run on the computer A and C, the first time the
second query is run the query takes 12 secs to run and there is about 30
meg of network traffic. When the next query is run there is about 2 meg traffic
and the query takes 2 secs on A and 7 secs on C.
Question - is the server making a temp table that it returns to look at for
subsequent queries? I note that during the install of MDAC 2.6 String Sort
libraries are loaded.
When the second query is run on the computer B, the first time the second
query is run the query takes 12 secs to run and there is about 30 meg of
network traffic. When the next query is run there is still about 30 meg traffic
and the query still takes 12 secs.
If we assume the answer to the question above is yes, why is computer B not
Before we distribute the application to users we need to solve this problem.
If it happens at our local sites, it is going to happen with our installed
base. Any suggestions.
Top DevX Stories
Easy Web Services with SQL Server 2005 HTTP Endpoints
JavaOne 2005: Java Platform Roadmap Focuses on Ease of Development, Sun Focuses on the "Free" in F.O.S.S.
Wed Yourself to UML with the Power of Associations
Microsoft to Add AJAX Capabilities to ASP.NET
IBM's Cloudscape Versus MySQL