Time taken to run queries

[Alice]

Hi,
I've just begiun using access, and am frankly shocked by the length of time a query is taking to run.

It's from three tables, and is selecting for one criteria, and then sorting for two. There are 3.4 million records.

I'm running a P3 machine, with 1.28 Mb RAM.

Sometimes it crashes, saying not enough temporary memory, and sometimes, I run out of patience.

Any ideas if this is normal?

cheers

Alice

 

[dk]

Hi,

Well, depending on how large each of your records is this could be normal. 3.4 million records is a LOT! Can you post exactly the definitions of each table you're using i.e. the field names, types and lengths e.g.

ID, Long Integer
LastName, Text, 50 characters
......

Then, can you also paste the SQL of your query (choose View, SQL View when in the query design screen). There may be some obvious improvements to be made to your query. As well as that you can often improve query execution time by properly indexing your tables and ensuring that you're using appropriate data types of the minimum size possible.

Once you've posted this information then someone should be able to give you some useful help (maybe me )

 

[Alice]

My SQL:

SELECT Transactions.PLU, Transactions.[Quantity Shipped], Transactions.[Date Shipped], Products.MOQ, Products.[Case Quantity], Branches.[Br No]
FROM (Transactions INNER JOIN Products ON Transactions.PLU = Products.PLU) INNER JOIN Branches ON Transactions.IUK = Branches.IUK;

Tables:
Transactions:
ID (4 digits)
Quantity Shipped (2 digits)
Date Shipped (10 digits)
Warehouse (2 digits)
IUK No (10 digits)

Products:
PLU (4 digits)
Description (25?)
MOQ (2)
Case quantity (2)

Branches:
Branch Number (4)
Address
Postcode
Retail brand
IUK no (10)
Delivery depot (7)

Thanks guys, I appreciate your help.

Alice

 

[dk]

OK, what are the data TYPES as well? I'm assuming that ID is a long integer, quantity shipped is an integer but what about the others? Most importantly, what are the data types of PLU in the Transactions and Products table and IUK in Transactions and Branches?

As an experiment you can try indexing PLU and IUK in each of the tables where these appear. If you don't know how to add an index, open the table in design view and choose View, Indexes. You can enter names into this dialog and choose which fields you wish to index. Make sure that you do this in each of the tables that you're joining.

 

[Alice]

All fields are simply 'text', or 'number', and all my tables have a primary key, if that's what you meant by indexes - I looked where you said and I think that's what you mean! Thank you for taking the time to explain it to me. Hopefully, the thread above about deleting records (once I understand it! ) will reduce my records, and make it run more quickly.

I left it running over night, and came back to a not enough temporary memory eror message - as it's a work computer, there's nothing I can do about this, right?

Many thanks guys,
Alice xxx

 

[Alice]

Have run the analyser function, which is recommending that I change one of my data types to long integer. this doesn't appear in the list in the drop down box in table design view. What am I missing?

alice

 

[dk]

All fields are simply 'text', or 'number', and all my tables have a primary key, if that's what you meant by indexes - I looked where you said and I think that's what you mean! Thank you for taking the time to explain it to me. Hopefully, the thread above about deleting records (once I understand it! ) will reduce my records, and make it run more quickly.

I left it running over night, and came back to a not enough temporary memory eror message - as it's a work computer, there's nothing I can do about this, right?

Many thanks guys,
Alice xxx

Hello again,

First of all, a primary key will automatically be indexed by Access. However, any non-primary key fields will not be indexed automatically. You must do this yourself by clicking View, Indexes when in table design view.

If you have any fields that could be number but are currently text then you should change them to number. A number can be sorted much more quickly than a text field can (as the analyser suggested).

I'm not sure what you're talking about with the deleting records part of your post. If you can get rid of some of your data then you're more likely to be able to run the query e.g. if your data is for several years and you only need the current year then archive all the old data into another table/database file.