Results 1 to 10 of 10
  1. #1
    Join Date
    Oct 2007
    Posts
    11

    Unanswered: ODBC very slow when doing MoveNext + read

    I have an application that's been developed in MS Access, that's being accessed over the network. I'm considering to migrate the database to pgSQL and use it as back-end, and MSAccess as front-end, connecting them over ODBC.

    When timing different operations, I found out that using the moveNext together with reads seriously drops performance, making the whole setup totally useless. Here's an output from the timings:

    Code:
    Access        MySQL         pgSQL         Function      Operation
     1.311875      1.484375      1.546        testDLookup   opens/reads
     3.858875      3.2185        3.062125     testDAvg      reads
     3.655625      2.81175       1.64025      testDMin      reads
     0.343         8.42125      17.07738      testCustMin   moveNexts/reads
     0.155625      0.280375      0.296125     testCustTrav  moveNexts
    the testCustMin function contains the following:
    Code:
    While Not d.EOF And Not d.BOF
    e = d(tc)
    d.MoveNext
    Wend
    where d is a Recordset.

    the testCustTrav function contains the following instead:
    Code:
    While Not d.EOF And Not d.BOF
    d.MoveNext
    Wend
    I've been searching forums etc. for a cpl days straight without finding anything helpful. I think the problem lies somewhere between my vba code and the odbc driver. It shouldn't be anything wrong with pgSQL itself since the same problem shows for MySQL, and I don't think the problem should be in the ODBC driver since other operations (like testDMin with a lot of reads) works even better than native Access.

    Does anyone have a suggestion of how to fix this problem?
    I would really appriciate if someone could help out!

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    And this is an Access problem because......

    I think it's a pgSQL / ODBC problem.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Is there any indexing on any of the fields done for the tables? Is there an autonumber type field created on the tables?
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  4. #4
    Join Date
    Oct 2007
    Posts
    11

    Arrow

    StarTrekker: I think it's an Access problem because other test operations (like testDMin with a lot of reads) works even faster than native Access, and they also connect to pgSQL over ODBC. The performance drop only occurs, as far as I can understand, when I use vba code to traverse a recordset combined with reads. But again, I'm not sure; if I knew what the problem was, I would've fixed it.

    TestDMin and testCustMin are doing the same type of calculations, so it kind of points in the direction that the method should be the difference, am I right?

    pkstormy: The table queried in the three different databases has the same structure (and data): an autonumber field and ten integer fields. the integer fields are filled with random integers, and the table has 10000 rows. the autonumber field is primary key and the only field used in conditions. For every operation listed above, the exact same operation is performed on the three databases.

  5. #5
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Ok....

    Do you get the same results with

    e = d!tc

    as you do with

    e = d(tc)

    ?

    Also, what is e?

    How are you opening the recordset d?
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  6. #6
    Join Date
    Oct 2007
    Posts
    11
    e = d!tc

    doesn't work since "tc" is not the name of the field, but tc is a variable holding the field name. I tried changing the field name to "tc" and using d!tc instead, resulting in the same type of performance drop.

    e is just a variable to store values as they are read, but it isn't used for anything at all afterwards.

    I open the recordset by

    Set d = CurrentDb.OpenRecordset("select " & tc & " as tc from " & table & " where ID<=" & b)

    when using d!tc and by

    Set d = CurrentDb.OpenRecordset("select " & tc & " from " & table & " where ID<=" & b)

    when using d(tc). ID is the auto-increment primary key of the table.

  7. #7
    Join Date
    Jul 2004
    Location
    South Dakota
    Posts
    267
    Would it help to specify the type of cursor to open the recordset with? ForwardOnly, etc. Just a thought.

    C

  8. #8
    Join Date
    Oct 2007
    Posts
    11

    Thumbs up

    Indeed, Canupus. Changing the opening of recordset from

    Set d = CurrentDb.OpenRecordset("select " & tc & " from " & table & " where ID<=" & b)

    to

    Set d = CurrentDb.OpenRecordset("select " & tc & " from " & table & " where ID<=" & b, 4)

    resulted in these much happier timings:

    Code:
    Access        MySQL         pgSQL         Function
     1.296         1.437125      1.593        testDLookup   opens/reads
     4.093         3.140625      3.14         testDAvg      reads
     3.921125      2.765625      1.780375     testDMin      reads
     1.07725       1.1085        1.234125     testCustMin   moveNexts/reads
     0.343125      0.562375      0.671875     testCustTrav  moveNexts
    I'd say you just solved my problem. Thank you!

  9. #9
    Join Date
    Jul 2004
    Location
    South Dakota
    Posts
    267
    Even a blind squirrel finds a nut every once in a while. Glad I could help.

    C

  10. #10
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    That was more than a nut
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •