Inefficient query or am I reaching the limits of Access?

[hurst2008]

I have been taking an online class on relational databases and created an Access database (for the first time) to practice my SQL queries and solve a couple of work-related problems along the way. The database consists of three tables, with the primary table being used to record company wide sales summary information at the branch/store/menu item level (e.g. lowest level of detail) and with three periods of data the database is presently 1.3GB with that one table containing 4,262,421 records.

Everything has gone well until I attempted to run the following query:

SELECT P1.*, P13.[Price?] AS P13Price

FROM (SELECT * FROM PBASE WHERE Period = 13) AS P13, (SELECT * FROM PBASE WHERE Period = 1) AS P1

WHERE P1.Key = P13.Key and P1.[Price?]<>P13.[Price?];

To explain, the big table is PriceAccData and so I first ran a query (PBASE) that added a field to the PriceAccData that I can use as a key to compare price changes from one period to the next (combination of branch, store, menu item). Then I used subqueries to create a data set from the last period of 2013 (Period 13) and the first period of 2014 (Period 1)....from there I attempted to identify items that had changed in price from one period to the next in the Where clause.

I am sure there is a better way to do it, but when I ran the query for only one of the 100 branches it took a while but finished...when I run it for the whole company it locks up Access. My question is two fold 1) is there a more efficient way to run this query which will continue to work quickly as the database grows and 2) the database only contains 3 of 13 periods of data, so I am questioning what should be my long term plan (e.g. what is the most efficient solution to simply store and query data using a desktop).

I'm not a computer science major (I am a CPA with better than average computer skills), so the simpler/clearer the solution and advice the better for me...and thank you in advance for your help.

[ItsMe]

1.3 GB is large for an Access DB. The limit is 2GB, I believe. However, the issue you are having is most likely associated with RAM. Virtual memory has its limits and considering you only have three tables, it would not be hard to create a query that would try to cram the whole DB into virtual memory.

[hurst2008]

Good point.....it worked when I ran the query for only one of the 100 branches.....it would be a pain to run it one branch at a time, but each branch is assigned one of 7 reviewers, so I'll try joining to the reviewer assignment table and limiting it to run for one reviewer at a time. I'd still appreciate any advice for making the query more efficient (if that is part of the problem) and what is the best plan for a database that will quickly outgrow the 2GB Access limit keeping in mind the one primary table will be what is responsible for exceeding the limit.

[ItsMe]

A proper RDBMS design, Normalization, sound Primary Key relationships with Foreign Keys, Indexing the appropriate fields, using the best Data Type and properties for your fields.....

These are some points to consider just for the design of the tables. How you create a query would be another topic.

[hurst2008]

Yep...the query is what I need help with today...if it is is poorly designed then maybe that is the source of my immediate problem.

What would be helpful for the future is knowing whether a table that will easily exceed 5M+ records consisting of about 8 fields the combination of which result in unique values (representing the lowest level of sales by period summary information that I don't see how can be divided into other tables) can be stored in Access even under the most efficient of circumstances. If so, then I need to improve and can invest the time to learn how to improve all that you mentioned, but if not then I may as well start addressing the primary problem of finding an appropriate back end for my situation, right?

[ItsMe]

The limit is 2GB for a single accdb or mdb file. If you can not make your tables smaller then you will need to move the tables to something else. Maybe SQL Express 2008.

[hurst2008]

Agreed...thanks for your help.

As for the query, when I limited it to only return the results for branches assigned to a specific reviewer (taking it from what would have been all 100 branches to about 18) then it appeared to complete within 2 hours, but afterward it immediately gave me the spinning circle and appears to have locked up Access....if the query can't be improved, then in the short term I suppose I'll just need to limit it to smaller batches of branches.

[ItsMe]

You can concentrate on the query, if you wish. My opinion is that would be putting the cart before the horse. Consider post #4. If the size of the tables will surpass the ability of Access you will need to use an alternative. This type of planning is part of what post #4 talks about. I would not be spending my time worrying about a query at this stage.

[hurst2008]

That's fair, I was just hoping to get through the next week (first round of reporting) without having to make the change assuming there is a learning curve, etc.