Thread: Which database software?
11-06-06, 18:00 #1Registered User
- Join Date
- Nov 2006
Unanswered: Which database software?
Our desktop software is written in VB6/ADO and uses Foxpro tables. We have converted these to Access, but I've got a problem.
One project has 100,000 records in a table 50 fields wide.
Now, since our Foxpro data was in flat-file, non-relational format and our budget for software development is basically non-existant, we pretty much just imported our Fox files into an MDB and changed our connection strings. This isn't pretty but it works.
The problem is this 100K monster. What am I going to do about it? Our software is engineering/analytical and requires searches and updates on this data.
Do I have any options for remaining in Access with this many records? If I turn our flat-file table format into a true relational database, I can relieve some of the pressure on ADO having to update 100K records with 50 mixed fields in each record row, but 100K is kind of getting into the area where the big boys play.
11-06-06, 18:18 #2Registered User
Provided Answers: 6
- Join Date
- May 2005
- Nevada, USA
Access should be able to handle 100k records with no problems. I would certainly look at correcting the design.Paul
11-06-06, 18:55 #3Jaded Developer
Provided Answers: 59
- Join Date
- Nov 2004
- out on a limb
agree 100k records shouldnt be a problem, Access MDB's have a theoretical limit of around 2GB
However thats when using Access's inbuilt data storage engine (Jet/MDB).
if you are migrating from xBase then Id suggest considering moving straight to MSDE / or what ever the cut down version of SQL server is called these days
The problem with Jet/MDB is concurrancy, ie how many users you can support concurrently... its a bit of black art where Jet/MDB's have issues (its can be as low as 1..10, it can be 50+, it depends on how ell the application is written and what it predominantly does (it can support more users who are reading, and feewer wha re writing), presuming its developed in VB6/ADO then the worst of the Jet problems should be minimised, as you are not using the (true) Acccess product (the RAD to generate forms & reports etc...)
However there is another issue that you may care to consider, and that is security, Jet security is fine if you want to stop or deter noobies, its not clever if you have a determined attempt at a breach, and or if the data inside is sensitive or requires an audit trail. if thats the case then Id defiantely suggest binning Jet in favour of a server db.that may be appropriate to do as a two stage step.... first one get the data into Jet, then at a later date move to a server backend, under a well planned & budgeted process in hte forthcoming months/years etc....I'd rather be riding on the Tiger 800 or the Norton
11-07-06, 15:45 #4Registered User
- Join Date
- Feb 2004
- Chicago, IL
Are you updating all 100K records at once? I have a similar table (~50 fields) and it routinely holds 1.5 to 2 Million records. However, I am not updating them very often and I archive them frequently.
So questions you need to answer:
1. How quickly do you need the updates to happen? Within a minute? Within seconds?
2. How many people will access (Healdem has a good suggestion with the MSDE, but if I remember correctly there is a limit to the number of concurrent users for that. 2?)
Some other ideas. When working with large tables there is no reason why you need to have them in the same database. I have a collection of databases that store my tables. I have found that if a database grows beyond 700Mb that you will see some serious performance issues. That may just be my experience. I have also been able to 'cheat'. If I have to do a task that could take 20 minutes or more, I have found that copying the database locally and doing the update on the local system is much faster thatn over a network. You can copy, do the update, and copy back the database faster than doing it over the network. Again this might be my network. Also this bit of advice probably won't be feasible in most environments.
The other thing is choosing the right number of indexes for your tables.