Performance problems moving to MDAC2.5

[Peter Richards]

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

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"
Conn2.Open

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
was noted.

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

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
doing this?


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.

[Chris Hylton]

Have you tried NOT using ODBC to get to the database...i.e. switch to the
OLEDB data provider for Access and upgrade the db to Access 2000, you may
see some performance improvements there. ODBC is a needless layer when hitting
a database that has an OLEDB data provider for it.

Check you indexes and keys...you can likely significantly improve performance
by making sure these are setup right based on the type queries you are running.

Third, not sure that it will have any effect, but adUseServer doesn't work
with Access, it's a file server database and doesn't work in 'client/server'
mode. Unless you use MSDE as the data engine to hit the Access database,
but that would be a whole different design.

As to your 'temp table' questions...NO...the server isn't doing anything
w/ your database...it can't due to the above reasoning...Access isn't client/server.
Basically, everything necessary to produce your resultset is sent down to
the client machine which accounts for your 30meg of network traffic...basically
the entire MDB file is pulled down in memory. You are in theory pulling
the entire database down to the client w/ every query in order to process
the results.

For example...answer the following question...How big is your database (MDB
file) compared to your network traffic on a single query ? ? ?

Chris

[peter richards]

On a 10 digit text field in an access database, how might one tune it for
best results.
peter richards

>Check you indexes and keys...you can likely significantly improve performance
>by making sure these are setup right based on the type queries you are running.
>
>

As to the other comments thanks for your help but in the MDAC2.1 version
the same code same lookup took 1-2 secs so something happened in the MDAC2.5
drivers.

[andy oneill]

>As to the other comments thanks for your help but in the MDAC2.1 version
>the same code same lookup took 1-2 secs so something happened in the MDAC2.5
>drivers.

Maybe it did...

I reckon upgrading from access2 to 97 or 2000 would be money well spent.
50,000 records just ain't that much.
Is the main database even 30meg?

MSDE is free...
If all your access is via ADO and your company won't pay for or won't install
access 97 or above then I'd go for MSDE.
A stored procedure on the server would just whiz that row back in a second.

I think if a key is a numeric field, you probably ought to have it numeric
rather than text.
Like is slower than =
There are no wildcards in your criteria, so you may as well use =

Did you try using the query with all default parameters?

[peter richards]

We did upgrade to Access2000 tables - no better.

>I reckon upgrading from access2 to 97 or 2000 would be money well spent.
>50,000 records just ain't that much.
>Is the main database even 30meg?
Agreed. It shouldn't be a performance problem and wasn't under MDAC 2.1 The
puzzle is if like 'arlington' finds arlington in 1-2 secs in a text field
why does '936547435' not find the same number in a text field in the same
time. We have racked our brains on that one.

>MSDE is free...
Dont want to goto MSDE as it would require new tools, code and expertise
at the sites. Besides this was not a problem 2 months ago.

will use "=" and report back

Access does allow stored proceedures so I may try that.
Thanks for your thoughts. In the meantime, replies suggets that is not an
isolated problem. There are users out there with very similar speed problems.

[Chris Hylton]

You likely won't see much on the performance improvement side when upgrading
versions of Access...you you have installed MDAC 2.1 or greater, you've already
updated to the latest version of the Jet/DAO dll files and that's where all
the work/performance will be impacted...regardless of which file version
you are using.

If you are using ADO, which I think you said you were...check and see which
OLEDB provider you are using to connect...if you are using OLEDB and subsequently
the 4.0 Jet OLEDB data provider...you are going to take a performance hit...it's
much slower against Access than using the 3.51 driver.

But, your situation seems strange b/c you seem to have not changed anything
other than an upgrade to MDAC...which shouldn't impact performance if you
don't change drivers.

Are you using OLEDB or ODBC to get to the MDB ?

If OLEDB, which Jet provider are you using 3.51 or 4.0 ?

On your 'index' question, all you can really do in Access is create an index
on that 10 character text field...you don't have the option of clustered
indexes and so forth that other databases like MSDE/SQLServer would provide.
Again, I think you problem lies elsewhere...it's not a record volume or
processing issue...since you get different results that you got originally...w/
the same code...something has changed somewhere.

I'll read back through your original post and see if I can see any other
explanation...but I don't claim to be an expert here...just trying to point
out some options or things to look at.

The MSDE suggestion is a good one, and it would be a fairly simple conversion...then
you'd have a true client/setup.

Chris

[Chris Hylton]

I read back through the thread. The other suggestion about 'like' vs. '='
is true as well, big performance hit w/ 'like', so if you don't need it,
get rid of it and use "=".

There could be some other things going on w/ the indexes, but w/o seeing
all the data in lastname and searchnum, it's hard to say...may have something
to do w/ how the indexes are created on each field and the number of distinct
values of each field there are...

Do a select distinct on SearchNum and one on LastName and see which one comes
out bigger...then you might try creating a table (just like the first one)
where you replace each SearchNum w/ that record's LastName and then rerun
the query...see if it processes as fast as before...

If SearchNum is always a numeric value and you don't need leading zeros,
I'd convert it to a numeric instead of text too...I'm not sure if indexes
run faster on numeric data, but the storage capacity might be less if you
can go to an numeric of smaller size than the text in that field...you may
not be able to though, depending on your requirements for that field.

Also, have you 'compacted' the database recently...if it's 30 megs and you
don't have anywhere near that much data, you could be taking a perf hit there
as well...w/ tons of garbage in the MDB file. I'd try compacting and then
maybe even rebuilding the indexes on that table...then rerun everything.

Chris

[Peter Richards]

Good suggetsions all. I am accessing the data through an odbc connection and
am not sure of the data providee. In the odbc driver table the msaccess driver
is version 4 therefore I assume that I am running the oledb provider 4.0

I have made the number an "=" and would like to change to a number field
but I would really like to find out why this happened in the first place.
I have also made the number lookup a stored access proceedure and will test.
It is the differences in the string performance that puzzle me.

If it was just a few offices that we could convert to MSDE then no problem.
However, rolling that out over a lot would be a major headache since only
a few are networked. ( but it's the networks I am worried about) thanks for
all the suggetstions - will report back!

[andy oneill]

I suppose DAO is out the question?
Think I mentioned this, but the general opinion seems to be that DAO is faster
and can be a lot faster.

Can't really be a subtle corruption or compaction problem if you tried copying
the database local and it speeded things up....

Another alternative would be a VB database access object running on the server...
but probably a lot of programming unless it's just a couple of queries you
got a problem with.

Copying the whole **** database local sounds like it would be faster.
FWIW
Often users work on completely separate data, SOMETIMES you can get away
with copying all the guff they work on to a local database and having some
separate process copies back to the main one.

>If it was just a few offices that we could convert to MSDE then no problem.
>However, rolling that out over a lot would be a major headache since only
>a few are networked. ( but it's the networks I am worried about) thanks
for
>all the suggetstions - will report back!

[peter richards]

Problem solved
I set the find to "= SEARCHNUM rather than "like" and made the whole thing
a stored proceedure - no small task. There is now no network traffic and
the find takes a sec. took a bit of doing.
Thanks for all the suggestions. They helped a lot - but why did it break
in the first place? It has taken oodles of work to get back to where I was
2 months ago and some very ticked off users.

[Chris Hylton]

NNTP-Posting-Host: 209.1.14.192
Message-ID: <3b39f9a0$1@news.devx.com>
Date: 27 Jun 2001 08:20:00 -0700
X-Trace: 27 Jun 2001 08:20:00 -0700, 209.1.14.192
Lines: 32
Path: news.devx.com
Xref: news.devx.com vb.database.ado:10306


The culprit was probably (as mentioned) using the like statement w/ no wildcard...not
sure how JET processes that w/ regard to indexes, but it's surely ineffiecient.

On the ODBC thing...if you are connecting via ODBC and using ADO, the you
have to be using the ODBC OLEDB provider, not the JET OLEDB provider...or
you wouldn't even be getting data back at all.

I wouldn't think that the ODBC driver for Access is the issue...it should
be fine regardless.

The issue I mentioned is when you use the two different OLEDB providers for
Access (i.e. 3.51 vs. 4.0), you will see a significant slowdown...but that
completely bypasses ODBC and you wouldn't even be using a DSN or DSN-less
ODBC connection in ADO in this case.

Glad you got it worked out...good luck w/ the rest of your project, hope
all goes well...

Chris

"peter richards" <nrichards@telus.net> wrote:
>
>Problem solved
>I set the find to "= SEARCHNUM rather than "like" and made the whole thing
>a stored proceedure - no small task. There is now no network traffic and
>the find takes a sec. took a bit of doing.
>Thanks for all the suggestions. They helped a lot - but why did it break
>in the first place? It has taken oodles of work to get back to where I was
>2 months ago and some very ticked off users.
>