Performance problems moving to MDAC2.5


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 11 of 11

Thread: Performance problems moving to MDAC2.5

  1. #1
    Peter Richards Guest

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






  2. #2
    Chris Hylton Guest

    Re: Performance problems moving to MDAC2.5


    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


  3. #3
    peter richards Guest

    Re: Performance problems moving to MDAC2.5



    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.

  4. #4
    andy oneill Guest

    Re: Performance problems moving to MDAC2.5



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




  5. #5
    peter richards Guest

    Re: Performance problems moving to MDAC2.5


    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.

  6. #6
    Chris Hylton Guest

    Re: Performance problems moving to MDAC2.5


    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

  7. #7
    Chris Hylton Guest

    Re: Performance problems moving to MDAC2.5


    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

  8. #8
    Peter Richards Guest

    Re: Performance problems moving to MDAC2.5


    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!

  9. #9
    andy oneill Guest

    Re: Performance problems moving to MDAC2.5


    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!



  10. #10
    peter richards Guest

    Re: Performance problems moving to MDAC2.5


    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.


  11. #11
    Chris Hylton Guest

    Re: Performance problems moving to MDAC2.5

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



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center