Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2004
    Posts
    145

    Unanswered: Query performance anomally

    Earlier today I combined some purchase tables into one. It came to about 1.7million rows in an Access 2003 mdb. It is a local table. I was running some test queries on the data and my results were coming back in a flash. I was pretty suprised. The basics of the query were I specified a store number and purchase year and the totals for each product purchased by that store were displayed. Later on I decided to create some relationships for the tables used. I did so along with some data integrity constraints. I went back to my query and it jumped to about 15-20 seconds to run. So I deleted all the relationships and it still takes 15-20 seconds. All I remember changing since the great performance was adding relationships. Can this slow my query down that much? If so why didn't the performance improve once I deleted the relationship?

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Have you run a compact/repair since removing your relationships?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Jan 2004
    Posts
    145
    Teddy - I just tried and it didn't make a difference. Another thing I noticed which I thought was odd is when I opened the combined table I was able to goto the last record almost instantly which was really suprising for a 1.7million record table. Afterwards it took much longer which is what I'm used to.

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I'm surprised also that you're getting that fast of a return for so many records but I've also had the same situation you mentioned where I added some relationships and things got slower (which is expected) but even after deleting the relationships the returns were still slow (and I compacted/repaired the mdb). I wonder what would happen if you imported just the table into another mdb. I also wonder if the indexes get affected in some way or another with creating relationships and they need to be rebuilt (like you can do in SQL Server). I'm curious what would happen if you removed the indexes, imported, and put the indexes back on the table or even made a new table with the indexes and appended the data sorted to that table (I think that's what I did once but it was a long time ago). Just a thought.
    Last edited by pkstormy; 10-04-06 at 22:59.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    Jan 2004
    Posts
    145
    pk I imported the huge purchase table into a new mdb and it was lightning fast again both on the query and jumping to the last record of the table. The indexes persisted through the importing. I then imported a few more tables and ran the query with some inner joins with no relationships and it slowed to about 5 seconds. I then added the relationships and still about 5 seconds.

    I just opened the original huge purchase table and it only took about a second to scroll to the last record. However the query took forever.

    I guess it really doesn't matter. I'm not going to use Access anyway. I just thought this was interesting.

  6. #6
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Out of curiosity, what program did you decide to use?
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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