Derby/MySQL comparison request

tkilshaw

How usable is Cloudscape/Derby in deployed applications? On reasonable small office hardware with a dedicated box for the servers, is Derby OK?

Do you have any metrics on number of simulatneous users, size of data sets and so on?

When would you opt to go with Derby or to move to MySQL?

Any end-users out there with real world experiences they could share.

I know that my questions are quite vague. I'm just looking for some basis to evaluate these options.

thanks,

Terry

aware_support2

Derby is a very capable database and should be sufficient for most small business applications.

There may be a number of considerations that can affect the decision on which database to choose. Organizations with existing IT infrastructure that already have MySQL or MS SQL Server running may prefer Aware IM to use their existing database. On the other hand, Derby may be a more convenient choice for applications designed for small businesses since Derby is included in the Aware IM installation package and the installation process takes care of all the database configuration/connection issues. Also, Aware IM runs Derby as an embedded process, which makes it easier from the ongoing maintenance point of view.

tkilshaw

Thanks for your response. Do you have any numbers though? Your response was almost as vague as my original questions, which serves me right I guess.

For example, do you know of any users using Derby? If so how many concurrent users are they supporting and how big are their record sets?

So are they supporting 3 to 5 concurrent user or 30 to 50? Is the largest number of records in one of their tables 500, 5,000, 50,000 or 500,000?

I need to get some kind of handle on its performance

Here are two links that may be of interest:

IBM's Cloudscape Versus MySQL
http://www.devx.com/IBMDB2/Article/28526

DeveloperPipelines Cloudscape & the Derby Project
http://devnet.developerpipeline.com/documents/s=9857/q=1/ddj0412d/0412d.html

thanks,

Terry

aware_support2

All our users I know of to use Derby are small companies. My rough estimate is that they may have several tens of thousands of records in a single table and a few concurrent users (probably less than 5). We did not get reports of any database or multi-user related issues.

In our own tests we successfully batch-created several hundreds of thousands of instances (under a million, I think) using different databases, and measured their performance. MS SQL Server was the quickest, then MySQL (about twice as fast as Derby). However, this difference in performance may not necessarily be as obvious, or even noticeable, to the end user performing normal data search/create/edit operations on a client machine.

In our latest test of Aware IM with Derby we used a specially configured Aware IM application with a process that batch-creates 10,000 instances to gradually increase the number of records in a table from 0 to 100,000. After each process run we used the browser to manually create a new instance and perform a text search using specific criteria. The results were as follows. The creation of a single new instance was as quick (almost instantaneous) on a 100,000 records table as on an empty table. There was no noticeable slowing down in search up to about 50,000 records, which took under 1 second. With 100,000 instances the search took about a second longer with the overall time still under 2 seconds. Note that these results include the browser page refresh, browser-server communication and Aware IM Server processing time, all of which take place outside of the database. The computer used in this test had AMD Athlon CPU at 1.6 GHz and 512MB of RAM. Both the Aware IM Server and the browser (the operation client) were running on the same machine.

Let me know if you would like to reproduce this test on your own hardware to get a feeling of what the performance is really like.

Also, please keep in mind that if for whatever reasons an Aware IM application needs to be moved to a different Aware IM-supported database (more powerful, different location, etc.) it will be possible to do it at a later date.

Let me know if this information helps you in your evaluation exercise.

greg

I used Derby during testing of my database and I switched to MySQL for a production database. Both systems were running on remote servers. I did have an impression that MySQL was returning queries faster but its server was more powerful as well.

The main disadvantage of Derby from a practical point of view was that text fields in the database are case sensitive. For example, if a name is entered as SMITH a query for 'Smith' will not return it.

tkilshaw

Greg,

thanks for your post. Can you say a bit more about what you mean when you say that MySQL was more powerful? Your post seems to be saying that Derby perfomed as well as MySQL.

Also the case sensitive issue is an important one. Mostly we don't want case sensitive searches. I don't recall the Aware documents saying anything about this.

It would be useful for Aware to clarify case sensitve search issues with respect to the various databases that they support.

Terry

greg

Terry,

I meant the remote server hardware, not the database server, sorry for the confusion. We installed MySQL and Aware on a newer and beefier machine than the one that run Aware and Derby.

As I said, the main disadvantage of Derby from a practical point of view was this case sensitivity. I am not a hard-core database developer and it was easier for me to find MySQL than Derby tools and to access the database on a table level. Though it may be unnecessary but I found it useful on the development stage when I was fiddling with batch importation of user data and assigning passwords.

greg