Fast and very robust database server

[Marc van Breemen]

Hi,

I'm planning to create a client/server application (financial/logistic) for 5-6 concurrent users at most (small businesses). It will generate about 30K-150K records per year spread over about 50 tables. The program must be lightning fast and very low on system resources.

The program language shall be (of course) PowerBasic. I still haven't decided on which database I should use. One thing is to be sure: 1) it has to be very reliable, 2) it has to be very fast. In that order.

If possible it has to be cheap if not free. I have three choices left: MSSQL Express (disadv: memory usage, installation), MySQL (a bit too bloated) or SQLite (looks very good).

No. 1 on my list is SQLite, but I have doubts. Is it really that good for my needs? Will development be likely continued on the long run?

I'll appreciate your suggestions/opinions!

Addition:
I don't need features like stored procedures, triggers and database functions.

[Michael Mattias]

Take a look at Oracle/XE.

Not only is it the fastest, most reliable database I've ever worked with, but if you can go with the XE version, it's free.

There are some limits, like total data can't exceed 4 gb or something like that. Just go to Oracle.com and start hitting links and you'll find all the info you need.

[Edwin Knoppert]

I have 0.01% experiance with it, try PostGreSQL
Maybe we learn from your experiances how this fella performs.
However, like MS SQL Server a real installation is required.

SQLite is still typeless and i find that a major lack.
Restrictions are required to find programming errors.
Though it is good and simple in use.

[Michael Mattias]

BTW, if "reliable" is really important to you, you should make sure your DBMS supports "transactions." (Oracle does and SQL/Server does, but I am not sure about SQLServer/Express)

Transaction support may actually be provided by the OLE provider (if using), so if you select a DBMS without instrinsic support, make sure it offers an OLE provider. (and double-check my memory!)

I know when using the ODBC interface, transaction support is drawn from the underlying DBMS.

MCM

[Marc van Breemen]

BTW, if "reliable" is really important to you, you should make sure your DBMS supports "transactions." (Oracle does and SQL/Server does, but I am not sure about SQLServer/Express)

Transaction support may actually be provided by the OLE provider (if using), so if you select a DBMS without instrinsic support, make sure it offers an OLE provider. (and double-check my memory!)

I know when using the ODBC interface, transaction support is drawn from the underlying DBMS.

MCM

I'm familiar with the advantages of transactions (worked with that with f.e. MSSQL), but right now I don't think I'll need that. For me 'reliable' means that the data will not be corrupted and if that should happen, the dbms should be able to handle/correct that. So in other words: no data may be lost whatever happens.

[Paul Squires]

If you decide to use SQLite then check out our (well, Fred Meier mainly) client/server implementation. It is pretty robust. It uses SQLite for the database and has a PB source code server built on top of it.

Web Page:

http://planetsquires.com/sqlite_client_server.htm

Support forum:

http://planetsquires.com/support/index.php?board=23.0

... oh, and it's FREE !!!!!!!!!!!!

[Edwin Knoppert]

Was SQLite not poor on multiuser?

[Marc van Breemen]

SQLite is still typeless and i find that a major lack.

For me that's not a problem as I will control the contents and will not allow direct external access (if possible to prevent that).

[Marc van Breemen]

If you decide to use SQLite then check out our (well, Fred Meier mainly) client/server implementation. <snip>

Yes, via your site I got to know SQLite . With 'client/server' I guess it's suitable for multi-user access?

<snip>It is pretty robust.<snip>

Pretty or 'very' .... ?

[Marc van Breemen]

Take a look at Oracle/XE.

I will, thanks for the tip. Although, without looking at the product, the name Oracle doesn't give me a good feeling regarding easy installation / low memory profile .....

[Edwin Knoppert]

For me that's not a problem as I will control the contents and will not allow direct external access (if possible to prevent that).

I think i get fired if i told them to abandon all kinds of 'rules' a good DBMS provides.
Btw, you where talking about 60 tables and over 100k of records.
That doesn't sound like job for a single and silly dll.
There will be a time you have to give up and convert your DBMS for something serious.
Installation is not really an issue in practise.

I think you'll have to think about your requirements about memory and 'very low on system resources'.. what does that mean?
We have some issues with our webserver running MS Sql Server but that is a server side solution, it currently supports between 6 and 10 websites/webservices.
Will your 'client/server' DBMS still run locally or remote?

Sqlite is at the opposite side of being a serious DBMS, it may fulfill the need of thousands of peoples but i am sure not for bigger tasks.
Your task is somewhat over-intermediate (due the amount of tables)
The number of records is somewhat of a limit for MS Access databases.
(The MDB type i mean)

"back to the old drawing board"..

[Michael Mattias]

will, thanks for the tip. Although, without looking at the product, the name Oracle doesn't give me a good feeling regarding easy installation / low memory profile .....

Installation? Download the file and click on it.

I have no problems with memory here... let me start the database (I have the server installed) and see what I am using... hmmm, all of 8476K (the service).

I never got around to installing the client (on my laptop) , but surely that cannot take more than the server, can it?


MCM

[Edwin Knoppert]

We have customers using a foxpro driver.
Maybe this is a solution as well.
Each folder represents a database and each dbf a table.

Maybe?

http://msdn.microsoft.com/en-us/vfoxpro/bb190233.aspx

[Stuart McLachlan]

The number of records is somewhat of a limit for MS Access databases.
(The MDB type i mean)

Not so.

150K records per year spread over 60 tables is trivial for an Access database. I regularly deal with several hundred thousand records per table in multi-user Access databases with no problems.

I have one with 4 million records in the primary table and as long as I only do indexed seeks, it is still very fast.

[Edwin Knoppert]

I had different experiances, i think you are lucky and simply not have triggered some nasty feature.
We where lucky we had a backup, we lost a great deal of our records at some point.
This MDB was used for a (ASP.NET) website, we moved to SQL Server.

[Stuart McLachlan]

I had different experiances, i think you are lucky and simply not have triggered some nasty feature.
We where lucky we had a backup, we lost a great deal of our records at some point.
This MDB was used for a (ASP.NET) website, we moved to SQL Server.

I've been developing Access applications professional for the last 16 years (ever since Ver 1.0). I've got literally hundreds of them deployed in in a wide range of organisations. If they are designed and used properly, there aren't any "nasty features".

However, Access is a very bad choice for website backends. Anyone who uses them for that deserves everything they get. Even Microsoft themselves warn against it for anything which is likely to experience anything more that light traffic.

[Robert Wallace]

Stuart:

Totally off topic - Watching, every week, a BBC program "Lost Land of the Volcano" - you live in a fantastic country

[Michael Mattias]

>150K records per year spread over 60 tables is trivial for an Access database

Jet ("Access") databases can get funny when the physical size of the *.mdb file approaches 1 Gb.

However, "Compacting the Database" is a nearly-universal fix when this happens.

If you go to http://www.providerpaymentpartner.com/, then hit the "User Support" tab near the top and get to the support page, you will find a link to a "white paper" I created for users of the Provider Payment Partner(tm) system on this subject.

That paper includes a link to the relevant KB article, as well as step-by-step instructions to compact the database.

And unless I am mistaken, I posted a 'compact Jet Database' routine here (somewhere, not in source code forum, that much I recall) just last week.

With this you can do the compacting from your application, which should eliminate the Susie User Effect which occurs when you ask a user to do anything with more than two steps.

[ADDED]
Link to code to compact Jet Database from your program:

http://www.powerbasic.com/support/pbforums/showpost.php?p=322633&postcount=9

MCM

[Stanley Durham]

No. 1 on my list is SQLite, but I have doubts. Is it really that good for my needs? Will development be likely continued on the long run?

I don't know if SQLite is the best choice, but you don't have to worry about speed, reliability or continued development.

It's estimated to be the most deployed SQL database in the world.

Used by Google, Apple, McAfee, Skype, Bloomberg and Adobe.
Adobe supports it. (along with Bloomberg and Symbian)
You probably use it every day on your cellphone.
If you have anything, Apple, you're using it.

You may have to do more roll-your-own to make it fit.
A complete package might be a better choice.

[Michael Mattias]

It's good to invest a lot of time to make 'The Right Choice' of DBMS for this application.

You need to appreciate this is a luxury.


Most often your clients (who could be your employer!) will have "official" applications to be used for e-mail, database management, spreadsheets and a host of other applications and support services. Many, too, will have "blacklists" - applications or support software/utilities which may not be used... usually because of some Bad Experience in the past.


Just don't forget to include in your criteria for selection:

1. Performance and reliability are pretty much a given; but don't forget to think about what it might take to "restore from catastrophe." This application is described as being required to store data for at least a year; while today's hardware systems are so much more reliable than they used to be, <stuff> happens. If being without the application for the three days it will take to get it up and running again following a 'crash' would cripple the user's business, perhaps something a bit easier to restore is in order.

1A. Related would be, "How easily can we move the application to a new computer?"

2. Cost of the chosen DBMS: initial licence fee, annual maintenance, and - often overlooked - the cost of adding additional users ("seats") to the application.

3. Maintainability. While from the description provided whomever must maintain the application will have to be at least familiar with the PowerBASIC compiler family, it will also be important they have the skill set to use the ODBC or OLE/ADO interface to the database, or the proprietary client interface (eg SQLLite) of the DBMS.. whatever you choose.

Yes, being allowed to pick like this is a luxury... but it's also a responsibility. You will be judged as much on your ability to 'choose wisely' as on your programming prowess.