Results 1 to 13 of 13
  1. #1
    Join Date
    Dec 2005
    Location
    Somewhere on the 3rd Rock
    Posts
    93

    Question Unanswered: Database size and speed

    I have created this database, and it uses very few queries, and calculations until you ask it for a report. However it is very slow during opening and closing, and when using the database.

    Some info on the Db:
    1. used on remotely operated laptops (4 to 8) that then access a secure network to sync with the master database.
    2. used to enter about 600 entries a night. Most data is date, time and notes. otherwise it just pulls data from a table in the database.

    I have tired to use an archive feature, but when I sync the db it corrupts all the databases that are synced with it. Index keys are random numbers.

    I could really use some help with this, as I am completely self taught on Access and I am still learning new things.

    I can step up a MySql server on the Master Computer, so that will not be a problem, however the guys in the field need to be able to access at least 30 days worth of records on each terminal (remote laptop) which does not always have network or internet access.

    Thanks for any assistance.
    Will Dove
    working hard is better then hardly working ...

  2. #2
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    hmmm, a LOT of factors here.

    I can only offer pointers with the information so far.

    Make sure the Front End is run locally, running the FE remotely is begging for a slow system.

    Minimize the network traffic. This can mean a lot of things, depending on how you are setup. For example, you generally want to have the data processed on the server side and only the result sent across the network when possible. In your case, since you want your users to be able to run without the server, do the reverse: move the data locally as straight data and then process. Heck, it's probably faster to move a ZIPPED copy of the BE, unzip and overwrite.

    How are you handling the "offline" anyway? How do you manage multiple users adding records independently of each other and then updating to a single back end? Are you "re-keying" the data during the update?

    tc

  3. #3
    Join Date
    Dec 2005
    Location
    Somewhere on the 3rd Rock
    Posts
    93

    Replica

    We are using a replica system right now, and the database is generating random Primary keys. The Remote terminals all have a replica of the master database, and are capable of running without server connection. Once the remote terminal has recieved all the data for the evening and the operator is ending his shift, he returns to the office and connects to a LAN to sync his terminal. The network traffic is not a problem, this network is devoted to only this traffic, and only one terminal at a time is transmitting over the network. Syncing the terminals only takes about 30 seconds, and they are done. it is the database itself that seems to be getting slower and slower.

    When told to open a form, it takes longer to accomplish this then it does to sync. I currently have the DB set to compact upon closing. The master computer is a Desktop computer with a 2.5ghz P4 chip, and the the Terminals are running 1ghz p4 chips. The db even runs slower on my laptop, and I am running a CM chip with over a gig of ram.
    Will Dove
    working hard is better then hardly working ...

  4. #4
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    ok, so we can rule out the network. You mentioned a particular form takes forever to load.

    Are there images on the form? I once had a form in a component database with the company logo on iut among other things. It was a pretty complicated form - it was the search form for the database with a bunch of bells and whistles. In any case, it literally took 45 seconds to open the form. I originally though it was the network (we have a slow network), but it still took over 35 seconds when the BE was local (P4, 2.26 GHz, 1 GB RAM).

    I ultimately created a new form, copied the controls over a few at a time, all but the image. It opened really fast ..... the image was a mere 2.5 KB JPG embedded! Anyway, I inserted a different image and the form still opened fast.

    I've had similar speed isses where the form speed was solved by recreating it, as though something got it's wires crossed while developing.

    Also, how about the queries the form relies on. Open the query and see if that takes a long time. If so, it's the query, not the form.

    There are a lot of ways to make a few queries run a long time.

    tc

  5. #5
    Join Date
    Dec 2005
    Location
    Somewhere on the 3rd Rock
    Posts
    93

    Other options

    I am looking at changing the Access database to a MSSql with Access frontend. But the question I have is how will this work with remote terminals, and a central database that will not be accessable to the remotes at all times, or will I need to be running MSSql and the database on each terminal. Haven't really used MSSql before, but I have migrated most of the db to this setting and it seems to be running much faster.

    Please give me some pointers on this.

    Thanks
    Will Dove
    working hard is better then hardly working ...

  6. #6
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    I suggest reposting specifically your last question as a new post to draw fresh attention to it.

    I don't have personal experience with MSSQL (yet) but:
    1) Running MSSQL as the BE (properly setup of course) should be faster
    2) Using Access as the FE is a common approach

    The catch here is your setup of having the Front End DB's go "solo" from the BE. When the BE is no longer available, neither is the data, unless you move the data into the FE (or a BE on the same machine).

    That's why I was suggesting making your "update process" copy the BE to the local machine and relink the FE to it (all of which can be done with some efficient code). The simple file copy will likely be faster than a batch data sort and transfer process. When a user checks back in, copy their BE to the server as a temp file and run the synchronize there.

    I just poking in the dark here, I've never really thought about running a multi user DB "breifcase style".

    Finally, did you check your queries for speed? A few easy to make slips can make a query turn into a pile of sludge, and it's a domino effect on everything else.

    hope my rambling helps,
    tc

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    Just to clarify here -

    Whenever a user uses this database all the querying is on a local database? Although the data is housed centrally on a server this is never directly accessed - the data is copied over to each user and the copies are queried? Also - (as a consequence of this) each user is a read only user? There are no transactions at all?

    If so - I wouldn't even consider SQL Server. The nature of the BE is not your problem (or at least it seems unlikely). Also - the benefit of SQL Server is having a big box do the data processing. If you are replicating to clients running a local copy of SQL Server then the principle advantage of SQL Server is negated.

    Is it a local BE and FE set up? If so - do you compact and repair the BE? (this causes the indexes to tidied up and PKs the be arranged sequentially which is particularly important if your keys are not monotonically increasing which is the case with random numbers). Also - is the FE an mde or mdb?

    What does the opening form do? Does it have a record source? Does it display any data?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Dec 2005
    Location
    Somewhere on the 3rd Rock
    Posts
    93

    I'm lost on that one

    Quote Originally Posted by pootle flump
    Hi

    Just to clarify here -

    Whenever a user uses this database all the querying is on a local database? Although the data is housed centrally on a server this is never directly accessed - the data is copied over to each user and the copies are queried? Also - (as a consequence of this) each user is a read only user? There are no transactions at all?

    If so - I wouldn't even consider SQL Server. The nature of the BE is not your problem (or at least it seems unlikely). Also - the benefit of SQL Server is having a big box do the data processing. If you are replicating to clients running a local copy of SQL Server then the principle advantage of SQL Server is negated.

    Is it a local BE and FE set up? If so - do you compact and repair the BE? (this causes the indexes to tidied up and PKs the be arranged sequentially which is particularly important if your keys are not monotonically increasing which is the case with random numbers). Also - is the FE an mde or mdb?

    What does the opening form do? Does it have a record source? Does it display any data?
    The forms do not dispaly data, or have a record source.

    As for the rest of the questions, you lost me.
    Will Dove
    working hard is better then hardly working ...

  9. #9
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    So your form(s?) is an unbound data-entry form? Do you have code that runs
    when the form opens and/or loads? If so, what does it do?

    PF was asking if you are using a back-end(tables)-Front End(forms, reports) structure for your app, or is everything in one .mdb?
    Inspiration Through Fermentation

  10. #10
    Join Date
    Dec 2005
    Location
    Somewhere on the 3rd Rock
    Posts
    93

    one DB

    everything is currently contained in one database.
    Will Dove
    working hard is better then hardly working ...

  11. #11
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    When you open the DB, is there a form that open automatically, or does it open to the DB window? I'm still trying to figure out if it's Access opening too slow, or some form after Access is running.
    Inspiration Through Fermentation

  12. #12
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    Quote Originally Posted by saileast
    everything is currently contained in one database
    That being the case, unless the DB itself is HUGE (> 100's of MB) then the problem lies in the data objects (queries, forms, etc) and moving the data to MSSQL (or any other BE) will not help.

    Have you "walked the queries" to see if there is a particular query that is buggering up the system? Do this by opening individual queries and noting the number of records returned and how long it takes to process. There's a multitude of things in query design that can blow up the system.

    Open the forms one at a time and try the different operations to see which ones are giving you the slow down. Once you know that, step through your code to see which specific operations are dragging it down. In a module window, place a red dot next to the first line of code in each critical operation by clicking to the far left margin of the line. This sets a "breakpoint", and when you run that code, it will stop and open the module window. You step one line at a time by pressing the F8 key. You can take note of how long each lines takes to execute and possibly find the culprit there.

    Other possible culprits: poor table design, desperate need for compact, requires decompile, corruption, redundant and circular operations, utilizing too many functions in your queries (especially the party favorite IIF), and the list goes on. If I think of more, I'll edit and add them.

    tc

  13. #13
    Join Date
    Dec 2005
    Location
    Somewhere on the 3rd Rock
    Posts
    93

    Walking queries

    I have walked the queries, and the database right now is 200mb, and will grow about 25mb every month.
    Will Dove
    working hard is better then hardly working ...

Posting Permissions

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