Results 1 to 10 of 10
  1. #1
    New Lounger
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    response on computers (Access 2002)

    Will Jet 4.0 take advantage of multiple processors on a single query. I am looking at upgrading my computer due to response time concerns. Am I better off with two medium speed processors or one fast processor? I am running on Windows 2000 server as an operating system. I run jet 4.0 and have plenty of memory. I am compute bound on these queries. Any feedback will be appreciated.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: response on computers (Access 2002)

    I am not sure, but I doubt very much that the Jet engine will take advantage of multiple processors for a single query. If performance is that important for you, consider migrating to SQL server. That definitely can use multiple processors.

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: response on computers (Access 2002)

    I also doubt that Jet would utilize 2 processors. But what is the situation you have? Is it a single, stand-alone computer? A network situation? How big is the database? Depending on the kind of "response time concerns", there may be other things you could do, such as re-evaluate your indexes, etc. Or, as Hans suggested, move to an backend server (like SQL).
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    New Lounger
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: response on computers (Access 2002)

    Thank you for your responses Hans and Mark. The situation is a batch process that runs on the server. It does a number of queries against multiple data sets with 350K records in each. It is not an option to go to SQL or MDSE. I have eliminated unused or seldom used indexes. The two linked databases go 265 MB and 465 MB. Network is not involved for this process. Current processor is 1.2 GHz running at 100% during this process. Running jet 4.0 on W2k with lots of memory and have just increased MaxLocksPerFile to 400K to prevent out of memory or disk space warnings. Development is going slow and need faster response. That is the reason for the upgrade.

  5. #5
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: response on computers (Access 2002)

    In addition to eliminating indexes, you might also check to make sure that you have indexes on all fields that might be involved in selection criteria or sorting. Another possibility is to eliminate all indexes (except the absolute necessary ones, that link tables), then recreate them after processing. This is often useful when doing append queries (but I don't know what kind of processing you are doing). Other than that, I doubt there is much more you can do with Jet and Access.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  6. #6
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: response on computers (Access 2002)

    I'm afraid you are pretty much stuck if you can't even consider SQL Server 2000 Desktop version that comes with Office 2003. The Jet 4.0 engine is a single thread engine, and won't take advantage of the additional processor. In any event, processor usage is always at 100% when Access is doing anything. You would find that SQL Server does this much more efficiently, and does use multiple processors to great advantage. One thing you might find useful is to add more memory to your system. Jet does everything in memory - so a GB of RAM would let you hold both databases in RAM, assuming that other people aren't pounding on the server as well. Just out of curiosity, why is the Desktop version out of the question?
    Wendell

  7. #7
    New Lounger
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: response on computers (Access 2002)

    Thanks for your replies. Although my choice ( I am a MCDBA) the desktop engine is out because of the amount of code (vba) written using these databases. As a secondary item, the director is comfortable with JET.

  8. #8
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: response on computers (Access 2002)

    My guess is that buying the fastest P4 out there might get you a 25% speed boost - and you might get another 25% out of fast SCSII drives, but that's about the best you are going to get with Jet. Do you expect the record counts to grow much in the foreseeable future? If you do you will bump up against the max size of Jet. And if this is still in the development stage and is slow, then I'd be really concerned. An alternative with your extensive VBA would be to link to SQL Server tables using ODBC. That would get you some of the SQL efficiency while still using VBA. We use that approach much of the time.
    Wendell

  9. #9
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: response on computers (Access 2002)

    Have you isolated the slow queries in question? Can you break these queries up into multiple queries that could be run a lot quicker. This is where indexes really come to the fore.
    I used to have a humungous query upon query upon query that was executed against a datawharehouse that used to take about an hour. By breaking this job into smaller queries I was able to get the time taken down to just a few minutes.

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

    Re: response on computers (Access 2002)

    I just wanted to jump in on this because I do have experience of running large queries on a dual-processor machine using Access 97 and NT Workstation. In that case, the record count was in the range you are describing and I found that queries that would not ever complete on a single-processor box would run on my dual-processor workstation. It appeared that the situation wasn't that Jet could use the second processor, but that Windows used it for other processes and let Access take control of a single processor without having to give way to other processes. I don't know whether the same would hold true in your circumstances, but I thought I would point it out for what it's worth.
    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
  •