Splitting table that has potential to be very large

[gavin31]

I have designed a db to record employees clocking in/out of various jobs during their working day.

One table (tblHours) holds the job number, work type, employee ID (all of Number type), clock-in and clock-out times (all of General Data type).

This database has just been put into use in the last 3 weeks.

However having thought a bit more about it, this table has the potential to grow very large. Although this is may only have about 20-30 new entries a day, it could obviously grow very large over time.

I have a number of questions....

1. How large can a table get before SELECT and UPDATE queries (especially with a JOIN in them) start to become slow when querying this table?

2. I was thinking about splitting the table at the end of every year, e.g. tblHours2006, tblHours2007, etc. What is the best way to go about doing this? How will this affect queries that need to look at all records spanning numerous tblHours<year> tables?

3. Does Access have a wizard/function/automatic procedure/ etc to accomplish splitting a table?

Any info/advice much appreciated.

Gavin

 

[dhookom]

I wouldn't do anything to divide the records into multiple tables or even archive off records. In 20 years you will only have a little over 200,000 records which isn't much.

You should make sure you have indexes on any fields that might be used for joining or filtering.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[gavin31]

Duane

Thanks for the advice, at least I have a rationale when I tell my boss the tables don't need to be split.

I will check my indexes aswell as I don't think I've set them up on all the fields used for joining.

Just out of curiosity what size (i.e. number of records multplied by number of fields) of table would be considered a very large table? Assuming fields are a basic type, say, Number.

Gavin

 

[dhookom]

I am aware of other MVPs who have tables of more than a million records. These would be considered very large tables.

I would just keep adding records until performance became an issue.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Golom]

Performance issues have much to do with the types of queries you are running on the table. If they are (for example) INNER JOINs on indexed fields then you can probably get up into the hundreds of thousands of records without much noticible performance impact. If you are doing theta joins (i.e. joins where the join condition involves inequalities) or complex WHERE clauses on non-indexed fields then Access does a full cross-join and filters the result. That is very sensitive to file size because full table scans rather than index scans come into play.

 

[Ed2020]

Another aspect to performance in MS Access is PC and network performance. MS Access runs queries locally, rather than at the server, so potentially large amounts of data are pulled over the network. A fast network (or a database running on a local drive) is important.

200000 records is nothing to worry about providing the database is correctly designed. I run a system with well in excess of 2 million records in a table with no performance problems at all.

Ed Metcalfe.

Please do not feed the trolls.....