Access database performance

Question

Hi,

For a few different reasons one of my projects is hosted on a shared hosting server and developed in asp.Net/C# with access databases (Not a choice so don't laugh at this limitation, it's not from me).

Most of my queries are on the last few records of the databases they are querying.

My question is in 2 parts:

1- Is the order of the records in the database only visual or is there an actual difference internally. More specifically, the reason I ask is that the way it is currently designed all records (for all databases in this project) are ordered by a row identifying key (which is an auto number field) ascending but since over 80% of my queries will be querying fields that should be towards the end of the table would it increase the query performance if I set the table to showing the most recent record at the top instead of at the end?

2- Are there any other performance tuning that can be done to help with access tables?

"Access" and "performance" is an euphemism but the database type wasn't a choice and so far it hasn't proven to be a big problem but if I can help the performance I would sure like to do whatever I can.

Thanks.

Edit:

• No, I'm not currently experiencing issues with my current setup, just trying to look forward and optimize everything.

• Yes, I do have indexes and have a primary key (automatically indexes) on the unique record identifier for each of my tables. I definitely should have mentioned that.

• You're all saying the same thing, I'm already doing all that can be done for access performance. I'll give the question "accepted answer" to the one that was the fastest to answer. Thanks everyone.

Answer 1

As far as I know...

1 - That change would just be visual. There'd be no impact.

2 - Make sure your fields are indexed. If the fields you are querying on are unique, then make sure you make the fields a unique key.

Answer 2

You can use indexes on the fields you search for (aren't you already?). http://www.google.com.br/search?q=microsoft+access+indexes

Answer 3

The order is most likely not the problem. Besides, I don't think you can really change it in Access anyway.

What is important is how you are accessing those records. Are you accessing them directly by the record ID? Whatever criteria you use to find the data you need, you should have an appropriate index defined.

By default, there will only be an index on the primary key column, so if you're using any other column (or combination of columns), you should create one or more indexes.

Don't just create an index on every column though. More indexes means Access will need to maintain them all when a new record is inserted or updated, which makes it slower.

Here's one article about indexes in Access.

Answer 4

Have a look at the field or fields you're using to query your data and make sure you have an index on those fields. If it's the same as SQL server you won't need to include the primary key in the index (assuming it's clustering on this) as it's included by default.

If you're running queries on a small sub-set of fields you could get your index to be a 'covering' index by including all the fields required, there's a space trade-off here, so I really only recommend it for 5 fields or less, depending on your requirements.

Answer 5

Yes there is an actual order to the records in the database. Setting the defaults on the table preference isn't going to change that.

I would ensure there are indexes on all your where clause columns. This is a rule of thumb. It would rarely be optimal, but you would have to do workload testing against different database setups to prove the most optimal solution.

I work daily with legacy access system that can be reasonably fast with concurrent users, but only for smallish number of users.

Answer 6

Are you actually experiencing a performance problem now or is this just a general optimization question? Also from your post it sounds like you are talking about a db with 1 table, is that accurate? If you are already experiencing a problem and you are dealing with concurrent access, some answers might be:

1) indexing fields used in where clauses (mentioned already)

2) Splitting tables. For example, if only 80% of your table rows are not accessed (as implied in your question), create an archive table for older records. Or, if the bulk of your performance hits are from reads (complicated reports) and you don't want to impinge on performance for people adding records, create a separate reporting table structure and query off of that.

3) If this is a reporting scenario, all queries are similar or the same, concurrency is somewhat high (very relative number given Access) and the data is not extremely volatile, consider persisting the data to a file that can be periodically updated, thus offloading the querying workload from the Access engine.

Answer 7

In regard to table order, Jet/ACE writes the actual table date in PK order. If you want a different order, change the PK.

But this oughtn't be a significant issue.

Indexes on the fields other than the PK that you sort on should make sorting pretty fast. I have apps with 100s of thousands of records that return subsets of data in non-PK sorted order more-or-less instantaneously.

I think you're engaging in "premature optimization," worrying about something before you actually have an issue.

The only circumstances in which I think you'd have a performance problem is if you had a table of 100s of thousands of records and you were trying to present the whole thing to the end user. That would be a phenomenally user-hostile thing to do, so I don't think it's something you should be worrying about.

If it really is a concern, then you should consider changing your PK from the Autonumber to a natural key (though that can be problematic, given real-world data and the prohibition on non-Null fields in compound unique indexes).

Answer 8

Hi tbate,

I've got a couple of things to add that I didn't notice being mentioned here, at least not explicitly:

• Field Length, create your fields as large as you'll need them but don't go over - for instance, if you have a number field and the value will never be over 1000 (for the sake of argument) then don't type it as a Long Integer, something smaller like Integer would be more appropriate, or use a single instead of a double for decimal numbers, etc. By the same token, if you have a text field that won't have more than 50 chars, don't set it up for 255, etc, etc. Sounds obvious, but it's done, often times with the idea that "I might need that space in the future" and your app suffers in the mean time.

• Not to beat the indexing thing to death...but, tables that you're joining together in your queries should have relationships established, this will create indexes on the foreign keys which greatly increases the performance of table joins (NOTE: Double check any foreign keys to make sure they did indeed get indexed, I've seen cases where they haven't been - so apparently a relationship doesn't explicitly mean that the proper indexes have been created)

• Apparently compacting your DB regularly can help performance as well, this reduces internal fragmentation of the file and can speed things up that way.

• Access actually has a Performance Analyzer, under tools Analyze > Performance, it might be worth running it on your tables & queries at least to see what it comes up with. The table analyzer (available from the same menu) can help you split out tables with alot of redundant data, obviously, use with caution - but it's could be helpful.

This link has a bunch of stuff on access performance optimization on pretty much all aspects of the database, tables, queries, forms, etc - it'd be worth checking out for sure.

http://office.microsoft.com/en-us/access/hp051874531033.aspx