How does Access handle large tables in a network 1

[Beren1h]

If I have a linked table in a backend (say about 30,000 records with at least 50 fields). Queries run against this table are pretty slow. I believe (and corrrect me if I am wrong) that Access has to pull the entire table from the network to the local machine before any queries can be run (hence the lousy performance).

Would anyone have any suggestions on speeding this process up? Should I pull all the data to the front end once and then query off that? Would querying from code work better?

???

 

[RickSpr]

Access doesn't always have to pull the entire table over the network. If the join fields and the criteria in your WHERE clause can be evaluated using only the fields in one index per table, the initial operations will only pull in the indexes. Once the indexes have been processed, Jet will have the "pointers" to the table records that pass the criteria, and it will then only read the table pages which contain rows for the result table. So you should see some improvement if you can create an index on each table which contains all the fields of that table needed by a given query for joins and WHERE criteria.

It's also possible that Jet can use several indexes for each table to get all these fields, but I don't know that for a fact. But if a single index containing the join and criteria fields would be very large, it would be worth experimenting. Just make sure that each field is in at least one index. It will probably work better, too, if the index contains no unneeded fields, or if any unneeded fields follow the needed ones in the index. Rick Sprague

 

[Rhonin]

Beren1h,
Do you need to pull the entire record or are you pulling a list and the user selects a record?
I am running a db with one of the tables has 100+ fields and over 50,000 record. Would be nice to migrate to SQL or Oracle but our IS group says nay.
Anyway...
You create a second table with the minimum display info (namely the fields making up the Pri key and any discriptives)and let the user see this (smaller # of fields = faster load time). Then select the record from the main table using the PriKey of the second table.
Unfortunately you have to resync the tables occasionally but the increase in speed makes it worth it.

Ex:
Table A = 100+ fields PriKey = SKU|Lot|Date}Batch
Table B = 4 fields PriKey = SKU|Lot|Date}Batch

The tables match. The user sees Table B list and the query selects the record from Table A based on the selected PriKey from Table B.

Works like a charm.

Hope this helps.

If not....

Rhonin
"seppuku means more work for somebody..."

 

[Beren1h]

Didn't know that...

Don't indexes cause the database to become bigger? This table will cause an Access database to hit 22MB all by itself. Would adding more indexes be a good approach in that situation?

 

[Rhonin]

Properly built, don't sweat it!
In my example, BEdb + Archivedb + FEdb + Compdb = 1.6gig.
all running under Win98/Acc97 on PIII's

The biggest speed problen is attempting to pull too much with too little indices.

Rhonin

 

[RickSpr]

Yes, the extra indexes will make the database larger. I don't have much experience with Jet databases this large, so I can't advise you about possibly hitting diminishing returns. However, I would think it's likely that, of the 50+ fields you have, you would only need to index a few to meet your query's needs. That probably wouldn't add a lot to the database size.

Let me add some more information: I was thinking in terms of selecting simple columns or scalar expressions on columns, with equality operators in the WHERE clause. If you're using the Like operator, you might wind up doing a table scan despite the indexes. Also, if you're using any subqueries, it gets a lot harder to figure out what you should include in your indexes.

On the other hand, if there is some particular restriction in your WHERE clause that you can predict will eliminate a large percentage of the rows you want, an effective speedup technique is to create a separate query that performs just that restriction, and then use that query in the FROM clause of your main query. For example, say your table has 30,000 products at 50 locations, and your query looks like this:
SELECT ProductName, CustID, OrderID, QtyShipped
FROM Products INNER JOIN Orders ON
Products.ProductID = Orders.ProductID
WHERE Location = 'Dallas' And ShipDate >= #05/31/02#
ORDER BY ProductName, CustID, OrderID
On average, selecting by location will eliminate 98% (49/50) of the rows in the Products table, which would be a big help. So in this case, you might create a query called DallasProducts:
SELECT * FROM Products WHERE Location = 'Dallas'
(Of course, you should have an index on Location for this.)
Then your main query would become:
SELECT ProductName, CustID, OrderID, QtyShipped
FROM DallasProducts INNER JOIN Orders ON
DallasProducts.ProductID = Orders.ProductID
WHERE ShipDate >= #05/31/02#
ORDER BY ProductName, CustID, OrderID
I think that, by doing this, you force Jet to perform the DallasProducts query first, which eliminates most of the data you would have to read from the Products table. What's left should be pretty quick to join with Orders.

Another idea: Compact your database, then for each query you need to speed up, open it, save it (without making any changes), and then switch it to Datasheet View. The compact operation updates the internal statistics that Jet uses to decide how to compile the query, and the save followed by execution of the query (to show the datasheet) recompiles the query to reflect the updated statistics. This is especially effective on a large table when the queries haven't been recompiled since the table was much smaller.

For some more ideas, check the Microsoft Knowledge Base for white papers on Jet query optimization. They can be very helpful. Rick Sprague