Results 1 to 7 of 7
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Youngstown, Ohio, USA
    Posts
    705
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Running Time Quandry

    We have been testing the database we are developing, to get an idea on how fast the eventual users will be able to pull up their reports. To do this, we have simulated approximately a year's worth of data. My test database (running Access 2000 on a Celeron 400, 64MB RAM, Win98a) had around 515,000 records in it, and could pull up the reports in 8 to 10 seconds on average. My co-worker's test database had around 605,000 records, and to pull the report took 12 to 15 seconds -- on a Celeron 700, 128MB RAM, Win98B.

    We figured that his machine should be geting better results than mine, but we felt that since his test database was about 20% larger than mine, comparing these test results was like comparing apples to oranges. We copied his test data to a machine comparable to mine, and the run speeds rose to 20-24 seconds to pull the report. This was a significant variance from the run times that I was getting, but we still had the 20% size difference to even out. I imported his test data to my database, so that we would be able to get a true comparison: same data, nearly identical machines. The first time I pulled the report on mine, it took over 90 seconds!

    I re-indexed and compacted the databases to be sure I was working with the system as optimized as I had been working with, but the 90 second run times continued like clockwork.

    Mystified, I created a new set of sample data, around 550,000 records, and the first report I pulled took just over 8 seconds. Using a wider range of criteria, pulling different and larger sections of data, and all the results were in the same 8 to 10 second ballpark.

    All of the sample databases were created using the same method - using append queries to replicate a small set of data, but changing key portions of each record to make each new, "duplicated", record unique when compared to the original.

    Here is my burning question: <font color=red>What caused the sudden poor performance when the data was copied onto my PC?</font color=red> Same data, same queries & reports, same selections to pull the reports on, and virtually identical platforms running Access. Does importing data tables from one database to another somehow have an impact on performance? We're completely baffled by this.

  2. #2
    Lounger
    Join Date
    Feb 2001
    Location
    TX, USA
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Running Time Quandry

    without answering you questions, i have some of my own:

    You are using A2K, but are you utilizing the MSDE, or JET?
    Is the db split?
    When you imported the data, did you compact before running?

  3. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Youngstown, Ohio, USA
    Posts
    705
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Running Time Quandry

    To answer your questions, and perhaps add some additional clarification:

    We are using Jet (4.0 I believe).

    The database is split into a back-end (containing the main data table and a couple of supporting tables, and the macros necessary to load and update the data), and a front-end, which contains all the report-generating forms, queries, and reports. Front-to-back access is via linked tables, and both of the databases are located in the same directory.

    To get the data into my file, I created a temp file on the originating machine, imported the data table into this temp file, and then sent the file to my PC across the office intranet. Once the file was on my drive, I imported the data table into my back-end file, replacing my test data.

    I ran my first test at this point and got my first 90 second timing. The database was then compacted and retested, yielding another 90 second timing. To help my computer along, I rebooted it while preventing the network load up. Running only Explorer, Systray, and Access, I continued to get the poor timings.

    Today we tried the same thing, but in reverse. We took my newer test data and sent it to my co-worker's PC... and the timings were consistently in the 8 to 10 second range, just as they had been on my PC. <img src=/S/joy.gif border=0 alt=joy width=23 height=23> Because of this, my first instinct is to say was that something became corrupted in the data table when it was transferred to my PC. <img src=/S/bummer.gif border=0 alt=bummer width=15 height=15>

  4. #4
    Lounger
    Join Date
    Feb 2001
    Location
    TX, USA
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Running Time Quandry

    my recommendation would be to move your tables into MSDE and transform your queries into stored procs and views. The benefits of these are well known and the MSDE functions the same as an SQL server. MSDE comes on the office cd.

  5. #5
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Running Time Quandry

    Just for kicks, you *might* try putting it all in one file.. Definitley unorthodox Access programming... however it's been my experience that it sometimes decreases the load-time.

    just my <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15>...
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  6. #6
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Youngstown, Ohio, USA
    Posts
    705
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Running Time Quandry

    I would be tempted to have it "all in one", except that compacting the database once it gets up to its full operating size becomes a real time-consuming chore. Either I do not use "compact on close", or I would have to expect the database to never be compacted. <img src=/S/ouch.gif border=0 alt=ouch width=15 height=15>

    I have set up some databases that were "all in one", since they were so small (and were not ever expected to get to an unmanageable size) that splitting was actually a less efficient way of working.

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Running Time Quandry

    Try this: decompile the misbehaving database front-end using the "undocumented" decompile switch (which you can find in numerous knowledgebase articles and by searching the Access forum), then switch to the VBE and compile and save the project. Switch back to the UI and compact and repair. Close and reopen the database holding down the shift key to bypass any startup code, then compact again. Then try your test. Does that make any difference?
    Charlotte

Posting Permissions

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