By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,966 Members | 2,054 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,966 IT Pros & Developers. It's quick & easy.

performance with 2 mln record

P: 1
we are looking to create a db with about 2 mln records.
as of now we have 180000 and the size of the file is abot 140Mb.
primarily the database will be queried for reports and every month get imported next portion of 180000 records...

is there any performance pitfalls?

TIA
Sep 12 '08 #1
Share this Question
Share on Google+
2 Replies


Expert Mod 2.5K+
P: 2,545
Hi. I'd advise that you consider a SQL Server back-end database or (or some other server-based database) to handle the projected 2m records. Access has an overall size limit of 2GB for any one table (and indeed for the whole database), and although you are well short of that total at present you are increasing the number of records by 180k a month.

Using facilities already available in Access you can develop and test your project and then change your tables onto a SQL Server back-end when you feel it is right to do so.

In terms of overall performance there is no simple answer. If tables are set up with correctly-defined primary and foreign key relationships performance on large datasets in Access should not be a particular problem, but really there are too many variables that can affect performance to be able to advise you at this stage without knowing more about the application.

-Stewart
Sep 12 '08 #2

P: 69
I endorse Stewart's comments very strongly. If you are using a front-end back-end setup, you are likely to need large amounts of data transfer when querying the back end. A huge benefit of SQL server solutions is that you can design for the data selection to be done on the server with transfer of needed data only. That way, you should reduce network traffic significantly. Of course traffic volumes may not ne an issue in your particular situation, but with millions of records to interrogate, I'd expect such problems.
Sep 13 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.