Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Sacramento, California, USA
    Posts
    491
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query running too slow (WIN2000 5.0SP3 ACCESS'97 SR2)

    I have a query (actually a few) that used to take 20 seconds to run, and now take about 2 minutes.
    Database has 35,000 records.
    I have created a new db and imported everything in. Compacted, etc.
    Front End/Back End. Query runs the same on either.
    All fields in query are indexed. ( I played around with different combinations but no dice)
    I have run the query on different machines - no difference (I was hoping to blame the network guys <img src=/S/sneaky.gif border=0 alt=sneaky width=15 height=15> )

    I have attached the query in design & SQL view.

    Any advice is welcome !!

    Thank you,
    Michael
    Attached Files Attached Files

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

    Re: Query running too slow (WIN2000 5.0SP3 ACCESS'97 SR2)

    One suggestion to try is to split the query into a Select and a GroupBy query and see if it still takes 2 minutes as a Select. You could also try doing a subquery on the Select portion. In addition, you should have indexes on all of the fields with WHERE criteria.
    Wendell

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

    Re: Query running too slow (WIN2000 5.0SP3 ACCESS'97 SR2)

    >>I have a query (actually a few) that used to take 20 seconds to run, and now take about 2 minutes.<<
    When it ran for 20 secs how many records were in the database, has this been a degradation over a fair period of time, or has it been over a short period of time?

    As Wendell suggests, split the query into a Select and a Group By, don't forget to put the WHERE clause in the Select query to filter out as many records as you can so the group by query has less work to do.

    Post back on your progress.
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  4. #4
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Sacramento, California, USA
    Posts
    491
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query running too slow (WIN2000 5.0SP3 ACCESS'97 SR2)

    Thanks Wendell & Pat - I will split the query when I get back to work Monday.

    Ya know, I tried to think of all details to include in the initial post, but I was on my
    way out and did forget to mention:
    The query, when it eventually runs, gives the correct results. So I'm not worried about the logic.
    The degradation happened when 'they' (IT), switched computers on me. I did check that they supplied me
    with the same SR & SPs. This machine actually has more memory (256) compared to 128 on my
    previous machine.
    It's still WIN2000 and still on the same network. This occurs on any machine, so I don't think IT did
    anything different to this machine.

    I will post back on Monday - Have a good weekend and Thanks again!

    Michael

  5. #5
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Sacramento, California, USA
    Posts
    491
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query running too slow (WIN2000 5.0SP3 ACCESS'97 SR2)

    OK - I split it into 2 queries and now it works beautifully. I appreciate your help fellas.

    Now I would like to understand why.
    Why would it work fine as 1 query until a while ago (maybe 500 record difference between it working
    in 20 seconds to working in 2 minutes) and now it takes 2 queries?

    Is this a better way, in general, to do these types of queries regardless? (I do have many queries
    similar to this one)

    Thanks again for your help - it makes a world of difference now running these ! <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>

    Michael

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

    Re: Query running too slow (WIN2000 5.0SP3 ACCESS'97 SR2)

    My hunch - no guess - is that the joins are causing the slow-down. Are any of these tables in SQL Server? In any event, the way Access works, it has to bring the entire 35000 records into the workstation in order to determine what it wants and what it doesn't want - if there are indexes on the fields in the WHERE clause, this makes it faster as it only has to find those records that the index indicates are appropriate. So I would guess that something changed to cause the sudden decrease in speed. As to whether this is the best way, it often boils down to trying it to see if it makes a difference - there really aren't any hard and fast rules. The same thing is true of subqueries versus nested queries - sometimes one is faster, sometimes the other is. Bottom line - database design still has elements of art (as opposed to science) associated with it.
    Wendell

  7. #7
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Sacramento, California, USA
    Posts
    491
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query running too slow (WIN2000 5.0SP3 ACCESS'97 SR2)

    Thanks Wendell for taking the time to explain.

    Are any of these tables in SQL Server?
    No.

    ...there are indexes on the fields in the WHERE
    There are indexes on all Where fields.

    I would guess that something changed to cause the sudden decrease in speed
    That's the $64,000 question !! I would love to have a clue - but can't seem to figure it out.
    1-I have created a new db and imported everything in. Compacted, etc.
    2-Front End/Back End. Query runs the same on either.
    3-I have run the query on different machines - no difference

    Well, they are running fine now - I should just be happy !! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    Thanks again !

    Michael

Posting Permissions

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