What affects Access database performance more: thousands of tables or millions of records?

Question

We use the Access database as the source code for our software product. The program passed alpha / beta testing at the company for about 2 years, and we noted that at that time one of our tables was filled with more than one hundred thousand records. This is probably not an example of the hardest use that our product will endure, and we are concerned about the performance of 5-10 years in the future.

There is a logical way to split this huge table into several thousand tables containing several hundred records, but I don’t think that this solution will probably help with any possible slowdown, since the database will inflate with tables instead of data (although I do not have formal training in the databases, so I know).

I was hoping that someone would be more informed than I could give some idea of ​​whether we would see a significant slowdown, and if so, which solution is likely to improve productivity in the long run?

Answer 1

The program has passed alpha / beta testing in the company for about 2 years

10 Microsoft Access , SQL Server .

5-10

- - 10 , . , Access 10 , " SQL-" .

, , , ,

5 . SQL Server . , Access, , - , - - , - - MS 10 .

, , ; SQL . SQL Server Enterprise ( ) .

- . .

, 18 , FoxPro, ( , ), ( db, .. - .

SQL Server, otoh, 650 , 10 20 6 , , .

Answer 2

, , ; , ? ( , )

, . , , db ( , MS Access, ) .

, , ( ?) , : table table_archived ( / ). .

Answer 3

overkill, , - , .

MS Access , . , , , . , , / .

rub: , , UNION, . , , , . , , .

. 100 , , , -.

Answer 4

- , , , , , . 2 , , - ( 50% , ), upsizing path in mind.

Jet/ACE, , , 100 , , . /, , , - .

- - Jet/ACE, , . Mini Cooper, , , , , - , - Mini Cooper, , , .

Answer 5

-v-SQL OP.

If the data can be shared and people won’t ask for these separators, then this might be an option worth checking out, but there is a limit to 2048 open tables in access, so you might want to keep track of this.

It was said earlier that if you should ask what is the maximum amount of something, then most likely you are doing it wrong, I think this is an example of this. If he broke it into 10 tables, maybe thousands? I pass this