Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2014
    Posts
    4

    Unanswered: ACCESS queries going turtle mode

    UtterAccess Addict
    Posts: 104



    I have been running a split database of ~50mb for the back end and 14mb for the front. This has been working fine with back end on server on front ends on a few user desktops. Maybe coincidentally - maybe not when our I.T. people changed the server my queries are acting up! Mostly they are taking 20x longer than they did and that is unworkable (trust me). What could I have done or could have happened to me to cause this horribly stressful mess???
    The crazy thing is the queries return the correct information but it takes so so much longer that they did. I tried remaking the database, repairing, checking linking table manager, and more. I'm bad stuck!!
    Thanks for any assistance. Please.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    I'd first perform a network audit (or have one performed). In Access, with the FE/BE schema, all computations occur in the FE, the BE acting as a data store. Either there is a problem with the disks on the server (have seen that) or the bottleneck occurs at the network level.
    Have a nice day!

  3. #3
    Join Date
    Feb 2014
    Posts
    4

    Turtlemode

    Do I understand that the query portion is somehow processed in the front end and that it should not take any time at all to retrieve from the datastore backend. When I execute the query which searches the main table by date range... at the bottom right status area it says "Query running" for over a minute and then it appears to actually load the data and showing a green status bar. When the DB is not split it runs like it should. I was blaming the location of the backend but it does not seem to be the network. *I have remade the query a number of times but I am puzzled why the front end could have the "query running" message for such a long time.

    Thank you for any response.

  4. #4
    Join Date
    Feb 2014
    Posts
    4

    Best Explanation of query craziness

    Through much grief I have narrowed down the query turtle-mode problem. It is at the point to where I use a date range for my results. I can return the entire table of 90,000 records quickly - however when I use a WHERE statement.
    "WHERE (((tbl_Sample_Login_table.LoginDate)=Date()-7))" <<- that is where I want to get the subset of records that are from the past 7 days only. The field LoginDate is a short date format. I feel like I'm getting close to being able to at least explain what's happening -- and then have some kind soul straighten it out.

  5. #5
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Quote Originally Posted by ARWhiston View Post

    ...Do I understand that the query portion is somehow processed in the front end and that it should not take any time at all to retrieve from the datastore backend...
    That's correct...the Query is processed in the Front End, and if the network is performing correctly, there really should be little difference in processing times when the app is split.

    Quote Originally Posted by ARWhiston View Post

    ...I was blaming the location of the backend but it does not seem to be the network...
    And what are you basing this on? The Query has to retrieve the data from the Back End. i.e. the server, by way of the network, and this is most likely where the problem lies.

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  6. #6
    Join Date
    Feb 2014
    Posts
    4

    Based on this

    I rebuilt the query one field parameter at a time and ran it against the back end. All results returned quickly UNTIL I added the most important parameter and that was the date range to get the last 7 days of the rest of the stuff.
    However when I use a WHERE statement.
    "WHERE (((tbl_Sample_Login_table.LoginDate)=Date()-7))"

    (where I want to get the subset of records that are from the past 7 days only). BTW, the field LoginDate is a short date format.

    It seems to me as though the query itself in Access front end is put together, converted to sql, or something at the front end Access program. Is there some setting perhaps that I could have jacked up with Access?

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1. Is there an index on LoginDate?
    2. I suspect multiple implicit conversions in an expression such as:
    Code:
    tbl_Sample_Login_table.LoginDate)=Date()-7
    However, all this does not explain why it ran faster before the server change.
    Have a nice day!

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    check your network

    check the latency.. how long it takes for a request to get to the file server
    bear in mind Access when using JET (the default db engine) does all its processing on the local machine so all the indexes have to be sent to the local client, and if a tablescan is involved all the (relevant) data has to be sent. potentailly thats a lot of traffic. so check what columns are indexed, make certain (using EXPLAIN) that the queries are running effectivley

    Check with your network trolls what else they have running on the server. ie is the server getting choked with other work, does it need more memory

    has anyone else got performance issues using that server or that network segment

    make certain that there are no netwrok issues (say loop backs, of significant netwokr discovery going on (pings, IP Broadcasts, SNMP admin and so on).

    its most likely to be the network, and if 'all' they have done is swap a server, rather than connect you to a different server in a different physical area then either they have changed they network topography (put in a few more hops to between the server) or they have got a rogue switch
    I'd rather be riding on the Tiger 800 or the Norton

Tags for this Thread

Posting Permissions

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