From derby-user-return-10666-apmail-db-derby-user-archive=db.apache.org@db.apache.org Fri Mar 06 17:39:24 2009 Return-Path: Delivered-To: apmail-db-derby-user-archive@www.apache.org Received: (qmail 64502 invoked from network); 6 Mar 2009 17:39:24 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.2) by minotaur.apache.org with SMTP; 6 Mar 2009 17:39:24 -0000 Received: (qmail 93483 invoked by uid 500); 6 Mar 2009 17:39:22 -0000 Delivered-To: apmail-db-derby-user-archive@db.apache.org Received: (qmail 93468 invoked by uid 500); 6 Mar 2009 17:39:21 -0000 Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm Precedence: bulk list-help: list-unsubscribe: List-Post: List-Id: Reply-To: "Derby Discussion" Delivered-To: mailing list derby-user@db.apache.org Received: (qmail 93459 invoked by uid 99); 6 Mar 2009 17:39:21 -0000 Received: from nike.apache.org (HELO nike.apache.org) (192.87.106.230) by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 06 Mar 2009 09:39:21 -0800 X-ASF-Spam-Status: No, hits=2.6 required=10.0 tests=DNS_FROM_OPENWHOIS,SPF_HELO_PASS,SPF_PASS,WHOIS_MYPRIVREG X-Spam-Check-By: apache.org Received-SPF: pass (nike.apache.org: domain of lists@nabble.com designates 216.139.236.158 as permitted sender) Received: from [216.139.236.158] (HELO kuber.nabble.com) (216.139.236.158) by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 06 Mar 2009 17:39:13 +0000 Received: from isper.nabble.com ([192.168.236.156]) by kuber.nabble.com with esmtp (Exim 4.63) (envelope-from ) id 1Lfe0Z-0007Ye-Tq for derby-user@db.apache.org; Fri, 06 Mar 2009 09:38:51 -0800 Message-ID: <22377140.post@talk.nabble.com> Date: Fri, 6 Mar 2009 09:38:51 -0800 (PST) From: DerbyNovice To: derby-user@db.apache.org Subject: Derby/HSQLDB major performance difference MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Nabble-From: clarsson@ureason.com X-Virus-Checked: Checked by ClamAV on apache.org I am using Derby as an embedded db in my swing application. Recently I decided to have a go at HSQLDB (cached tables, embedded) to see how it coped. I have written a test program which * inserts a number of records in my db with random keys * makes an index on the keys. * runs a number of select statements * updates a number of records with new random values. At the same time I measure lapse time and memory in a separate thread. I made the same run with Derby and with HSQLDB, see the two uploaded charts, with -Xmx1024m . Initially the idea was to see which db was faster, but as soon as I saw the results I realised there are other differences. The scale on the x-axis is half seconds, i 1000 is 500 seconds. The scale on the y-axis is bytes as reported by gc. Notice the difference in scale between HSQLDB and derby. I have tried to optimise the memory with HSQLDB options but it has only marginal difference and it does not change the behaviour. Observations: * HSQLDB uses a magnitude more memory than Derby. * HSQLDB does not seem to benefit from the indices. * HSQLDB is faster in total, but not to the extent the memory usage suggests. * Derby uses a very long insertion period but the select statements are very fast and memory lean * Derby manages the memory during the run, the total memory goes up AND DOWN. * Derby seems to struggle (timewise) with the inserts (the long slope initially) but breeze through the select statements which all take less than a second. The run shown uses 700000 records, but smaller runs show the same behaviour. For me this makes HSQLDB useless as it would gradually eat my applications memory. Anyone trying to weigh performance benefits between db's should be aware of these very different characteristics. I'd be pleased if anyone would care to comment on the test run and maybe shed some light on the totally different characteristics seen here. I'd be happy to upload the timing tests and my program too if there is an interest. Regards, DERBY RUN http://www.nabble.com/file/p22377140/mem.gif HSQL RUN http://www.nabble.com/file/p22377140/mem.gif -- View this message in context: http://www.nabble.com/Derby-HSQLDB-major-performance-difference-tp22377140p22377140.html Sent from the Apache Derby Users mailing list archive at Nabble.com.