The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

Scalebility

I wonder about the following: A application written in C# that uses an Acces-database. It's purpose it to store a table with records that contain about 20 strings and about 20 dates. The number of records would range up to at most 5000 records (over its lifetime). It's should also store a second table with smaller records: 2 or 3 strings, a date and a currency field. the number of these records would be larger, about 50,000. How well does this scale? Would this work if more than one instance of the application uses the database simultanious? It's quite unlikely that the same record would be modified by more than one person at the same time.
Patrik Van Ostaeyen Send private email
Monday, September 24, 2007
 
 
What do you mean by "scale"? 

As in, how many copies of the program running on the same DB would have to run before something crashes?

Remember that while something may be unlikely at a given instant (like 2 people modifying the same record at the same time), that probability adds up the longer the program is run.

Handling the records you're talking about wouldn't even hit 50 MB of data.  In terms of running well when filled up, you shouldn't have any major problems, unless you're doing something extraordinarily bad (e.g. looping through all the records many times).

If you're really worried, look into something slightly stronger, such as SQL Server.  Don't they have a 'lite' version?
Lally Singh Send private email
Monday, September 24, 2007
 
 
Using Access as a database back end for anything other than a single user application scales horribly.

If you have multiple users connecting to an Access database, expect it to become corrupted at least once a quarter. Once you pass 10 users concurrently, it becomes more of a nightmare (corruption is likely to occur weekly), and you may very well start running into other limits within the Jet engine.

In short, don't use Access for any real multi-user database application, especially if it's over a network.
Josh McFarlane Send private email
Monday, September 24, 2007
 
 
I was going to say the limit is about 5 users, and that's only if the data access code was well designed & written.

If it's like the typical Access code I've seen, I would expect to see problems starting at 2 users.
xampl Send private email
Monday, September 24, 2007
 
 
We had a single user application that used Access.
However, someone had the bright idea to access this DB from several outproc COM-servers without prior investigation of how to do something like this the right way.

About a million of lines of code and bugs later (and angry users) we finally ditched Access and went for OracleXE....
Boink Send private email
Monday, September 24, 2007
 
 
I agree with Josh.

I would recommend you to try Firebird. It's small, robust, scale well and very easy to maintain. The license is good too (MPL-compatible). I love Firebird because it saves my butt many times; It just repairs tables silently after power-outages.
SmallerBrain
Monday, September 24, 2007
 
 
Looks like for your scenarios Access can be scalable and perform as much as any other database in addition to getting some real benefits of easy to use interfaces to manage and create reports, query and data centric user inerfaces, also look at Access 2007 http://office.microsoft.com/en-us/access/FX100487571033.aspx and check if it can work out for you .
In your case since you are using C# application for user interface and if there is a possibility to have both data and users to grow exponentially then give a try to SQL 2005 express - it is free and can scale and perform to the levels that you want - http://msdn2.microsoft.com/en-us/express/bb410791.aspx
Ashish Jaiman Send private email
Tuesday, September 25, 2007
 
 
I concur with the general sentiment above.  Access is a great tool in its place, but its best place is either as a front end only or as a back end to only one user. (I love it as a front end to SQL Server for small apps because it is so quick to develop.) Sure, you CAN make it work for more (I have), but you have to be sure your code is very clean. 

I tend not to think of Access as "scaleable" because that gives the idea of unknown growth potential.  I have seen Access used successfully up to 13 in a client-server setting, but the programmer was quite good and still had problems crop up frequently.  So when you grow, you'll end up upgrading to something new.

Also, Access uses it's own version of SQL.  If you intend to use any queries for your database, the syntax would need to be adapted should you ever upgrade to something else closer to the ANSI standard.  If you use SQL in your front end at all, you'll have to rewrite it to work with a new backend.  (At least, I have had to do so in the past.)

In the end, if you expect growth, don't waste time now developing a back-end in Access.  Use one of the  recommendations above, because it's what you'll end up moving to in the long run.
C.E.
Wednesday, September 26, 2007
 
 

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
 
Powered by FogBugz