You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by David Van Couvering <Da...@Sun.COM> on 2005/08/09 01:27:31 UTC
Re: Thread on Derby on The Server Side
I did hear the same thing at OSCON -- that the startup of Derby,
especially when it has to create the schema and load initial data -- is
significantly slower than HSQL -- 20 seconds startup time instead of 1-2
for HSQL. It would be very good if we could reproduce and track down
the source of this.
David
Daniel Noll wrote:
> David Van Couvering wrote:
>
>> Note there are suggestions Derby is slow. If anyone wants to chime
>> in on some of this, that would be great. We don't want the "word" to
>> get out that Derby is slow if it's based on poorly designed
>> benchmarks or misconceptions...
>>
>> http://www.theserverside.com/news/thread.tss?thread_id=35729
>
>
> Derby does have an inexplicable 8-12 second wait when creating or
> opening a database where HSQLDB has no noticeable wait, which doesn't
> hurt on a server but on a desktop client it's a bit of a problem. It
> really makes our unit tests grind too, since we use a fresh database
> for each test case.
>
> But that being said, at runtime (which is normally what matters) we
> haven't encountered any noticeable slowdown using Derby over HSQLDB.
>
> Maybe someone has some quantitative figures though, instead of these
> qualitative experiences.
>
> Daniel
>
Re: Thread on Derby on The Server Side
Posted by Sunitha Kambhampati <ks...@gmail.com>.
David Van Couvering wrote:
> Thanks, Sunitha, for looking into this! It would be *great* if you
> posted this to the server side in response to the "Derby is slow"
> judgement out there. If for whatever reason you are uncomfortable
> doing this, let me know, and I'll be happy to do it with your OK.
>
Hi David,
I just posted a comment on the serverside thread.
http://www.theserverside.com/tss?service=direct/0/PostNewsReply/postReply&sp=l35729&sp=F&sp=l180586.
Please feel free to add to it, if needed. Thanks.
> These seem to be the two most common mistakes: assuming a database is
> transactional when it's not really (doesn't sync to disk) and not
> preparing statements. We ran into this at Sun as well with a group
> doing performance tests of Derby.
> We should have a "cheat sheet" for things people should look at when
> doing internal benchmarking of Derby and make sure it's front and
> center on our web site...
>
I agree, its a good idea. I will try to get out a list and we could
discuss this on the derby-dev list. .
Sunitha.
[ snip]
Re: remote connections to embedded derby
Posted by James Cowan <co...@freenet.co.uk>.
Hi
thanks. I will try this.
James
----- Original Message -----
From: "Matt Sanford" <mz...@aol.com>
To: "Derby Discussion" <de...@db.apache.org>
Sent: Thursday, August 11, 2005 2:54 PM
Subject: Re: remote connections to embedded derby
> Hello,
>
> I have some code currently using the embedded driver with optional
> networking. I put both jars (derby and derbynet) in my classpath and
> then have the following code :
>
> if (allowNetworkConnections == true) {
> log.info("Starting network server in addition to
> embedded.");
>
System.setProperty("derby.drda.startNetworkServer","true");
> System.setProperty("derby.drda.host","0.0.0.0");
> }
>
> When this is enabled I can use ij on the same host to query the
> in-use database, when it is disabled I cannot. I have not tried it with
> truly remote clients nor tested it very thoroughly, but it worked for my
> purposes.
>
> -- Matt
>
> Oyvind.Bakksjo@Sun.COM wrote:
>
> > Knut Anders Hatlen wrote:
> >
> >> James Cowan <co...@freenet.co.uk> writes:
> >>
> >>
> >>> Hi
> >>>
> >>> is it possible to make remote connections to an embedded derby
> >>> database?
> >>
> >>
> >>
> >> No, you'll have to run the the network server. However, the network
> >> server is just a layer of networking code on top of an embedded derby
> >> database, so in some sense you can say it makes it possible to make
> >> remote connections to an embedded database. But that wasn't what you
> >> had in mind, I guess...
> >
> >
> > Isn't it?
> >
> > http://db.apache.org/derby/docs/10.1/adminguide/cadminovntsrvsample.html
> >
> > Quote:
> >
> > "The embedded driver is loaded when the Network Server is started. The
> > JVM that starts the Network Server can obtain an embedded connection
> > to the same database that the Network Server is accessing to serve
> > clients from other JVMs. This solution provides the performance
> > benefits of the embedded driver and also allows client connections
> > from other JVMs to connect to the same database."
> >
> > To illustrate this in beautiful ascii-art (best viewed with fixed font
> > width):
> >
> > +--------------------------------------+
> > | Client JVM |
> > | +------------------------+ |
> > | | Your application logic | |
> > | +------------+-----------+ |
> > | | |
> > | V |
> > | +----------------------------------+ |
> > | | Network Client JDBC driver | |
> > | +-----------------------------+----+ |
> > | | |
> > +-------------------------------|------+
> > |
> > +-------------------------------|------+
> > | Server JVM | |
> > | +------------------------+ | |
> > | | Your application logic | | |
> > | +------------+-----------+ | |
> > | | V |
> > | | +----------------+ |
> > | V | Network Server | |
> > | +-----------------+----------+-----+ |
> > | | Embedded JDBC driver | | |
> > | +----------------------------+ | |
> > | | Derby engine | |
> > | +----------------------------------+ |
> > +--------------------------------------+
> >
>
> --
> When the going gets tough, the weird turn pro.
> -- Hunter S. Thompson
>
Re: remote connections to embedded derby
Posted by Matt Sanford <mz...@aol.com>.
Hello,
I have some code currently using the embedded driver with optional
networking. I put both jars (derby and derbynet) in my classpath and
then have the following code :
if (allowNetworkConnections == true) {
log.info("Starting network server in addition to
embedded.");
System.setProperty("derby.drda.startNetworkServer","true");
System.setProperty("derby.drda.host","0.0.0.0");
}
When this is enabled I can use ij on the same host to query the
in-use database, when it is disabled I cannot. I have not tried it with
truly remote clients nor tested it very thoroughly, but it worked for my
purposes.
-- Matt
Oyvind.Bakksjo@Sun.COM wrote:
> Knut Anders Hatlen wrote:
>
>> James Cowan <co...@freenet.co.uk> writes:
>>
>>
>>> Hi
>>>
>>> is it possible to make remote connections to an embedded derby
>>> database?
>>
>>
>>
>> No, you'll have to run the the network server. However, the network
>> server is just a layer of networking code on top of an embedded derby
>> database, so in some sense you can say it makes it possible to make
>> remote connections to an embedded database. But that wasn't what you
>> had in mind, I guess...
>
>
> Isn't it?
>
> http://db.apache.org/derby/docs/10.1/adminguide/cadminovntsrvsample.html
>
> Quote:
>
> "The embedded driver is loaded when the Network Server is started. The
> JVM that starts the Network Server can obtain an embedded connection
> to the same database that the Network Server is accessing to serve
> clients from other JVMs. This solution provides the performance
> benefits of the embedded driver and also allows client connections
> from other JVMs to connect to the same database."
>
> To illustrate this in beautiful ascii-art (best viewed with fixed font
> width):
>
> +--------------------------------------+
> | Client JVM |
> | +------------------------+ |
> | | Your application logic | |
> | +------------+-----------+ |
> | | |
> | V |
> | +----------------------------------+ |
> | | Network Client JDBC driver | |
> | +-----------------------------+----+ |
> | | |
> +-------------------------------|------+
> |
> +-------------------------------|------+
> | Server JVM | |
> | +------------------------+ | |
> | | Your application logic | | |
> | +------------+-----------+ | |
> | | V |
> | | +----------------+ |
> | V | Network Server | |
> | +-----------------+----------+-----+ |
> | | Embedded JDBC driver | | |
> | +----------------------------+ | |
> | | Derby engine | |
> | +----------------------------------+ |
> +--------------------------------------+
>
--
When the going gets tough, the weird turn pro.
-- Hunter S. Thompson
Re: Distinct subselect bug?
Posted by Knut Anders Hatlen <Kn...@Sun.COM>.
Hi,
I browsed the derby user archives and didn't see any responses to your
(very old) article. This is definitely a bug and should be filed in
Jira. I wasn't able to find a Jira issue for this particular bug, so
I'll look into it tomorrow and file an issue if no one points out
something obvious that I have missed.
For the record: I can verify that the reported behaviour also is
present in the latest development version of Derby.
--
Knut Anders
Andrew Shuttlewood <an...@futureroute.co.uk> writes:
> While trying out some queries recently, we came across some weird
> issues.
>
> We sometimes work off of queries that are provided by the user, so we
> heavily use subselects. This is a recent one
>
> select distinct(col1) from (select col1,col2,col3,col4 from table) t
>
> Interestingly enough, this doesn't actually work.
>
> I've attached a sample CSV file to demonstrate some sample data.
>
> With this query
>
> select distinct name from (select name,ID from names) n
>
> And a schema of ID:string, Name: String
>
> I get
>
> "Carl"
> "Anna"
> "Julian"
> "Julian"
> "Julian"
> "Andrew"
> "Andrew"
> "Andrew"
> "Ben"
> "George"
> "George"
> "George"
> etc.
>
> if I omit the other column (ie, 'select distinct name from (select name
> from names) n' then it works fine, and it also works okay if I flatten
> the query.
>
> It ALSO works okay if I do an ORDER BY on the top entry. ie
>
> select distinct name from (select name,ID from names) n ORDER BY name
>
> I assume this is a bug?
> names.csv: attached
Re: remote connections to embedded derby
Posted by Oy...@Sun.COM.
Knut Anders Hatlen wrote:
> James Cowan <co...@freenet.co.uk> writes:
>
>
>>Hi
>>
>>is it possible to make remote connections to an embedded derby database?
>
>
> No, you'll have to run the the network server. However, the network
> server is just a layer of networking code on top of an embedded derby
> database, so in some sense you can say it makes it possible to make
> remote connections to an embedded database. But that wasn't what you
> had in mind, I guess...
Isn't it?
http://db.apache.org/derby/docs/10.1/adminguide/cadminovntsrvsample.html
Quote:
"The embedded driver is loaded when the Network Server is started. The
JVM that starts the Network Server can obtain an embedded connection to
the same database that the Network Server is accessing to serve clients
from other JVMs. This solution provides the performance benefits of the
embedded driver and also allows client connections from other JVMs to
connect to the same database."
To illustrate this in beautiful ascii-art (best viewed with fixed font
width):
+--------------------------------------+
| Client JVM |
| +------------------------+ |
| | Your application logic | |
| +------------+-----------+ |
| | |
| V |
| +----------------------------------+ |
| | Network Client JDBC driver | |
| +-----------------------------+----+ |
| | |
+-------------------------------|------+
|
+-------------------------------|------+
| Server JVM | |
| +------------------------+ | |
| | Your application logic | | |
| +------------+-----------+ | |
| | V |
| | +----------------+ |
| V | Network Server | |
| +-----------------+----------+-----+ |
| | Embedded JDBC driver | | |
| +----------------------------+ | |
| | Derby engine | |
| +----------------------------------+ |
+--------------------------------------+
--
Øyvind Bakksjø
Sun Microsystems, Web Services, Database Technology Group
Haakon VII gt. 7b, N-7485 Trondheim, Norway
Tel: x43419 / +47 73842119, Fax: +47 73842101
Re: remote connections to embedded derby
Posted by James Cowan <co...@freenet.co.uk>.
can the network server be embedded?
>From reading http://db.apache.org/derby/docs/adminguide/cadminov825266.html
it does not look that it can be.
James
----- Original Message -----
From: "Knut Anders Hatlen" <Kn...@Sun.COM>
To: "Derby Discussion" <de...@db.apache.org>
Cc: "James Cowan" <co...@freenet.co.uk>
Sent: Thursday, August 11, 2005 10:31 AM
Subject: Re: remote connections to embedded derby
> James Cowan <co...@freenet.co.uk> writes:
>
> > Hi
> >
> > is it possible to make remote connections to an embedded derby database?
>
> No, you'll have to run the the network server. However, the network
> server is just a layer of networking code on top of an embedded derby
> database, so in some sense you can say it makes it possible to make
> remote connections to an embedded database. But that wasn't what you
> had in mind, I guess...
>
> --
> Knut Anders
>
Re: remote connections to embedded derby
Posted by Knut Anders Hatlen <Kn...@Sun.COM>.
James Cowan <co...@freenet.co.uk> writes:
> Hi
>
> is it possible to make remote connections to an embedded derby database?
No, you'll have to run the the network server. However, the network
server is just a layer of networking code on top of an embedded derby
database, so in some sense you can say it makes it possible to make
remote connections to an embedded database. But that wasn't what you
had in mind, I guess...
--
Knut Anders
Re: remote connections to embedded derby
Posted by James Cowan <co...@freenet.co.uk>.
I want to embed a database in an application and I want other applications
to be able to talk to the database.
For example, openoffice now runs an embedded version of hpersonic/hsqldb but
remote applications can talk to the database. Basically the hypersonic jdbc
driver allows connections from both clients in the same jvm and from remote
clients. Hypersonic has some disadvantages which is why I am looking at
derby instead.
James
----- Original Message -----
From: "Shreyas Kaushik" <Sh...@Sun.COM>
To: "Derby Discussion" <de...@db.apache.org>
Sent: Thursday, August 11, 2005 10:37 AM
Subject: Re: remote connections to embedded derby
> Can you eloborate your question a bit more ?
>
> thanks
> Shreyas
>
> James Cowan wrote:
>
> > Hi
> >
> > is it possible to make remote connections to an embedded derby database?
> >
> > James
>
Re: remote connections to embedded derby
Posted by Shreyas Kaushik <Sh...@Sun.COM>.
Can you eloborate your question a bit more ?
thanks
Shreyas
James Cowan wrote:
> Hi
>
> is it possible to make remote connections to an embedded derby database?
>
> James
remote connections to embedded derby
Posted by James Cowan <co...@freenet.co.uk>.
Hi
is it possible to make remote connections to an embedded derby database?
James
Re: Thread on Derby on The Server Side
Posted by Mamta Satoor <ms...@gmail.com>.
Yes, I was referring to Embedded driver.
Mamta
On 8/10/05, Jonathan Eric Miller <je...@uchicago.edu> wrote:
>
> I could be wrong too, but, from what I remember (I did this testing
> several
> months ago), it was a pretty huge difference. If I remember correctly, I
> found that Derby was noticeable slower than other native DBMSs like MySQL,
> but, if I used batch updates, it had a substantial speed increase and was
> then comparable in speed to the other DBMSs. From what I remember the IBM
> JDBC driver chewed up way more memory than the MySQL and other drivers.
> e.g.
> if I ran the app with the IBM driver, it would consume say 150 MB RAM,
> whereas it would only use say 35 MB RAM when using MySQL (processing the
> same data and starting with a fresh database).
>
> I was using the IBM JDBC driver and not the embedded driver. I don't know
> if
> maybe you were referring to the embedded driver or not...
>
> Jon
>
> ----- Original Message -----
> From: "Mamta Satoor" <ms...@gmail.com>
> To: "Derby Discussion" <de...@db.apache.org>
> Sent: Wednesday, August 10, 2005 12:06 AM
> Subject: Re: Thread on Derby on The Server Side
>
>
> Hi Jon,
> Looking at the code, I don't think Derby does anything special for batch
> updates. org.apache.derby.impl.jdbc.EmbedStatement.executeBatch() method
> simply executes one statement in the batch at a time. This is from a quick
> look at the code and hence I could be mistaken.
> Mamta
>
> On 8/9/05, Jonathan Eric Miller <je...@uchicago.edu> wrote:
> >
> > From what I remember, I found that if you're doing batch updates, using
> > JDBC
> > batch updates improves performance greatly with Derby. Using
> > PreparedStatements alone was much slower than other DBMSs such as MySQL
> or
> > MSSQL.
> >
> > Jon
> >
> > ----- Original Message -----
> > From: "David Van Couvering" <Da...@Sun.COM>
> > To: "Derby Discussion" <de...@db.apache.org>
> > Sent: Tuesday, August 09, 2005 11:24 AM
> > Subject: Re: Thread on Derby on The Server Side
> >
> >
> > > Thanks, Sunitha, for looking into this! It would be *great* if you
> > > posted this to the server side in response to the "Derby is slow"
> > > judgement out there. If for whatever reason you are uncomfortable
> > > doing this, let me know, and I'll be happy to do it with your OK.
> > >
> > > These seem to be the two most common mistakes: assuming a database is
> > > transactional when it's not really (doesn't sync to disk) and not
> > > preparing statements. We ran into this at Sun as well with a group
> doing
> > > performance tests of Derby.
> > >
> > > We should have a "cheat sheet" for things people should look at when
> > > doing internal benchmarking of Derby and make sure it's front and
> center
> > > on our web site...
> > >
> > > David
> > >
> > > Sunitha Kambhampati wrote:
> > >
> > >> I took a quick look at the benchmark at polepos that is mentioned in
> > >> the thread on theserverside , specially the bahrain test. This test
> > >> uses Statements instead of PreparedStatement for the select queries.
> > >> ( see BahrainJdbc.java where select queries are like "select * from
> > >> bahrain where LicenseID=" + i
> > >> One important performance tip is to use PreparedStatement with
> > >> dynamic markers instead of Statements. This can show considerable
> > >> improvements.
> > >> If you use Statement with literal values as in the case above, that
> > >> will involve derby having to compile a query plan for each of the
> > >> statements which affects performance. Using PreparedStatement with
> > >> dynamic markers ('?') avoids unnecessary compilation cost , and thus
> > >> faster.
> > >>
> > >> So for example - if you are executing the query above(in 1st para)
> > >> with different LicenseID values, using a Statement will involve
> > >> compilation cost for each statement executed. But in case of
> > >> PreparedStatement, the query would be ' select * from bahrain where
> > >> LicenseID=?" and the statement is compiled only the first time or
> when
> > >> the optimizer thinks the plan is stale.
> > >>
> > >> Check out the tuning manual for more details :-
> > >> http://db.apache.org/derby/docs/dev/tuning/ctunperf18705.html
> > >>
> >
> http://db.apache.org/derby/docs/dev/tuning/ctundepth29804.html#ctundepth29804
> > >>
> > >>
> > >> ---------
> > >> Also I think, sometimes default behaviors of databases are different
> > >> and this can lead to different performance out of the box - some key
> > >> factors such as the default isolation level, I/O syncs on a commit or
> > >> not. When I looked at the docs for HSQLDB and Pointbase last, I saw
> > >> that by default they dont force a sync call to the disk on a commit.
> > >> Sunitha.
> > >>
> > >> ps: There are some more differences documented in Derby-110 comments,
> > >> and also in Dan's blog entry.
> > >>
> >
> http://www-128.ibm.com/developerworks/blogs/dw_blog.jspa?roll=-6&blog=397
> > >>
> > >>>
> > >>> Daniel Noll wrote:
> > >>>
> > >>>> David Van Couvering wrote:
> > >>>>
> > >>>>> Note there are suggestions Derby is slow. If anyone wants to chime
> > >>>>> in on some of this, that would be great. We don't want the "word"
> > >>>>> to get out that Derby is slow if it's based on poorly designed
> > >>>>> benchmarks or misconceptions...
> > >>>>>
> > >>>>> http://www.theserverside.com/news/thread.tss?thread_id=35729
> > >>>>
> > >>>>
> > >>>>
> > >>>>
> > >>>> Derby does have an inexplicable 8-12 second wait when creating or
> > >>>> opening a database where HSQLDB has no noticeable wait, which
> > >>>> doesn't hurt on a server but on a desktop client it's a bit of a
> > >>>> problem. It really makes our unit tests grind too, since we use a
> > >>>> fresh database for each test case.
> > >>>>
> > >>>> But that being said, at runtime (which is normally what matters) we
> > >>>> haven't encountered any noticeable slowdown using Derby over
> HSQLDB.
> > >>>>
> > >>>> Maybe someone has some quantitative figures though, instead of
> these
> > >>>> qualitative experiences.
> > >>>>
> > >>>> Daniel
> > >>>>
> > >>
> > >
> >
> >
>
>
Re: Thread on Derby on The Server Side
Posted by Jonathan Eric Miller <je...@uchicago.edu>.
I could be wrong too, but, from what I remember (I did this testing several
months ago), it was a pretty huge difference. If I remember correctly, I
found that Derby was noticeable slower than other native DBMSs like MySQL,
but, if I used batch updates, it had a substantial speed increase and was
then comparable in speed to the other DBMSs. From what I remember the IBM
JDBC driver chewed up way more memory than the MySQL and other drivers. e.g.
if I ran the app with the IBM driver, it would consume say 150 MB RAM,
whereas it would only use say 35 MB RAM when using MySQL (processing the
same data and starting with a fresh database).
I was using the IBM JDBC driver and not the embedded driver. I don't know if
maybe you were referring to the embedded driver or not...
Jon
----- Original Message -----
From: "Mamta Satoor" <ms...@gmail.com>
To: "Derby Discussion" <de...@db.apache.org>
Sent: Wednesday, August 10, 2005 12:06 AM
Subject: Re: Thread on Derby on The Server Side
Hi Jon,
Looking at the code, I don't think Derby does anything special for batch
updates. org.apache.derby.impl.jdbc.EmbedStatement.executeBatch() method
simply executes one statement in the batch at a time. This is from a quick
look at the code and hence I could be mistaken.
Mamta
On 8/9/05, Jonathan Eric Miller <je...@uchicago.edu> wrote:
>
> From what I remember, I found that if you're doing batch updates, using
> JDBC
> batch updates improves performance greatly with Derby. Using
> PreparedStatements alone was much slower than other DBMSs such as MySQL or
> MSSQL.
>
> Jon
>
> ----- Original Message -----
> From: "David Van Couvering" <Da...@Sun.COM>
> To: "Derby Discussion" <de...@db.apache.org>
> Sent: Tuesday, August 09, 2005 11:24 AM
> Subject: Re: Thread on Derby on The Server Side
>
>
> > Thanks, Sunitha, for looking into this! It would be *great* if you
> > posted this to the server side in response to the "Derby is slow"
> > judgement out there. If for whatever reason you are uncomfortable
> > doing this, let me know, and I'll be happy to do it with your OK.
> >
> > These seem to be the two most common mistakes: assuming a database is
> > transactional when it's not really (doesn't sync to disk) and not
> > preparing statements. We ran into this at Sun as well with a group doing
> > performance tests of Derby.
> >
> > We should have a "cheat sheet" for things people should look at when
> > doing internal benchmarking of Derby and make sure it's front and center
> > on our web site...
> >
> > David
> >
> > Sunitha Kambhampati wrote:
> >
> >> I took a quick look at the benchmark at polepos that is mentioned in
> >> the thread on theserverside , specially the bahrain test. This test
> >> uses Statements instead of PreparedStatement for the select queries.
> >> ( see BahrainJdbc.java where select queries are like "select * from
> >> bahrain where LicenseID=" + i
> >> One important performance tip is to use PreparedStatement with
> >> dynamic markers instead of Statements. This can show considerable
> >> improvements.
> >> If you use Statement with literal values as in the case above, that
> >> will involve derby having to compile a query plan for each of the
> >> statements which affects performance. Using PreparedStatement with
> >> dynamic markers ('?') avoids unnecessary compilation cost , and thus
> >> faster.
> >>
> >> So for example - if you are executing the query above(in 1st para)
> >> with different LicenseID values, using a Statement will involve
> >> compilation cost for each statement executed. But in case of
> >> PreparedStatement, the query would be ' select * from bahrain where
> >> LicenseID=?" and the statement is compiled only the first time or when
> >> the optimizer thinks the plan is stale.
> >>
> >> Check out the tuning manual for more details :-
> >> http://db.apache.org/derby/docs/dev/tuning/ctunperf18705.html
> >>
> http://db.apache.org/derby/docs/dev/tuning/ctundepth29804.html#ctundepth29804
> >>
> >>
> >> ---------
> >> Also I think, sometimes default behaviors of databases are different
> >> and this can lead to different performance out of the box - some key
> >> factors such as the default isolation level, I/O syncs on a commit or
> >> not. When I looked at the docs for HSQLDB and Pointbase last, I saw
> >> that by default they dont force a sync call to the disk on a commit.
> >> Sunitha.
> >>
> >> ps: There are some more differences documented in Derby-110 comments,
> >> and also in Dan's blog entry.
> >>
> http://www-128.ibm.com/developerworks/blogs/dw_blog.jspa?roll=-6&blog=397
> >>
> >>>
> >>> Daniel Noll wrote:
> >>>
> >>>> David Van Couvering wrote:
> >>>>
> >>>>> Note there are suggestions Derby is slow. If anyone wants to chime
> >>>>> in on some of this, that would be great. We don't want the "word"
> >>>>> to get out that Derby is slow if it's based on poorly designed
> >>>>> benchmarks or misconceptions...
> >>>>>
> >>>>> http://www.theserverside.com/news/thread.tss?thread_id=35729
> >>>>
> >>>>
> >>>>
> >>>>
> >>>> Derby does have an inexplicable 8-12 second wait when creating or
> >>>> opening a database where HSQLDB has no noticeable wait, which
> >>>> doesn't hurt on a server but on a desktop client it's a bit of a
> >>>> problem. It really makes our unit tests grind too, since we use a
> >>>> fresh database for each test case.
> >>>>
> >>>> But that being said, at runtime (which is normally what matters) we
> >>>> haven't encountered any noticeable slowdown using Derby over HSQLDB.
> >>>>
> >>>> Maybe someone has some quantitative figures though, instead of these
> >>>> qualitative experiences.
> >>>>
> >>>> Daniel
> >>>>
> >>
> >
>
>
Re: Thread on Derby on The Server Side
Posted by Mamta Satoor <ms...@gmail.com>.
Hi Jon,
Looking at the code, I don't think Derby does anything special for batch
updates. org.apache.derby.impl.jdbc.EmbedStatement.executeBatch() method
simply executes one statement in the batch at a time. This is from a quick
look at the code and hence I could be mistaken.
Mamta
On 8/9/05, Jonathan Eric Miller <je...@uchicago.edu> wrote:
>
> From what I remember, I found that if you're doing batch updates, using
> JDBC
> batch updates improves performance greatly with Derby. Using
> PreparedStatements alone was much slower than other DBMSs such as MySQL or
> MSSQL.
>
> Jon
>
> ----- Original Message -----
> From: "David Van Couvering" <Da...@Sun.COM>
> To: "Derby Discussion" <de...@db.apache.org>
> Sent: Tuesday, August 09, 2005 11:24 AM
> Subject: Re: Thread on Derby on The Server Side
>
>
> > Thanks, Sunitha, for looking into this! It would be *great* if you
> > posted this to the server side in response to the "Derby is slow"
> > judgement out there. If for whatever reason you are uncomfortable
> > doing this, let me know, and I'll be happy to do it with your OK.
> >
> > These seem to be the two most common mistakes: assuming a database is
> > transactional when it's not really (doesn't sync to disk) and not
> > preparing statements. We ran into this at Sun as well with a group doing
> > performance tests of Derby.
> >
> > We should have a "cheat sheet" for things people should look at when
> > doing internal benchmarking of Derby and make sure it's front and center
> > on our web site...
> >
> > David
> >
> > Sunitha Kambhampati wrote:
> >
> >> I took a quick look at the benchmark at polepos that is mentioned in
> >> the thread on theserverside , specially the bahrain test. This test
> >> uses Statements instead of PreparedStatement for the select queries.
> >> ( see BahrainJdbc.java where select queries are like "select * from
> >> bahrain where LicenseID=" + i
> >> One important performance tip is to use PreparedStatement with
> >> dynamic markers instead of Statements. This can show considerable
> >> improvements.
> >> If you use Statement with literal values as in the case above, that
> >> will involve derby having to compile a query plan for each of the
> >> statements which affects performance. Using PreparedStatement with
> >> dynamic markers ('?') avoids unnecessary compilation cost , and thus
> >> faster.
> >>
> >> So for example - if you are executing the query above(in 1st para)
> >> with different LicenseID values, using a Statement will involve
> >> compilation cost for each statement executed. But in case of
> >> PreparedStatement, the query would be ' select * from bahrain where
> >> LicenseID=?" and the statement is compiled only the first time or when
> >> the optimizer thinks the plan is stale.
> >>
> >> Check out the tuning manual for more details :-
> >> http://db.apache.org/derby/docs/dev/tuning/ctunperf18705.html
> >>
> http://db.apache.org/derby/docs/dev/tuning/ctundepth29804.html#ctundepth29804
> >>
> >>
> >> ---------
> >> Also I think, sometimes default behaviors of databases are different
> >> and this can lead to different performance out of the box - some key
> >> factors such as the default isolation level, I/O syncs on a commit or
> >> not. When I looked at the docs for HSQLDB and Pointbase last, I saw
> >> that by default they dont force a sync call to the disk on a commit.
> >> Sunitha.
> >>
> >> ps: There are some more differences documented in Derby-110 comments,
> >> and also in Dan's blog entry.
> >>
> http://www-128.ibm.com/developerworks/blogs/dw_blog.jspa?roll=-6&blog=397
> >>
> >>>
> >>> Daniel Noll wrote:
> >>>
> >>>> David Van Couvering wrote:
> >>>>
> >>>>> Note there are suggestions Derby is slow. If anyone wants to chime
> >>>>> in on some of this, that would be great. We don't want the "word"
> >>>>> to get out that Derby is slow if it's based on poorly designed
> >>>>> benchmarks or misconceptions...
> >>>>>
> >>>>> http://www.theserverside.com/news/thread.tss?thread_id=35729
> >>>>
> >>>>
> >>>>
> >>>>
> >>>> Derby does have an inexplicable 8-12 second wait when creating or
> >>>> opening a database where HSQLDB has no noticeable wait, which
> >>>> doesn't hurt on a server but on a desktop client it's a bit of a
> >>>> problem. It really makes our unit tests grind too, since we use a
> >>>> fresh database for each test case.
> >>>>
> >>>> But that being said, at runtime (which is normally what matters) we
> >>>> haven't encountered any noticeable slowdown using Derby over HSQLDB.
> >>>>
> >>>> Maybe someone has some quantitative figures though, instead of these
> >>>> qualitative experiences.
> >>>>
> >>>> Daniel
> >>>>
> >>
> >
>
>
Re: Thread on Derby on The Server Side
Posted by Jonathan Eric Miller <je...@uchicago.edu>.
>From what I remember, I found that if you're doing batch updates, using JDBC
batch updates improves performance greatly with Derby. Using
PreparedStatements alone was much slower than other DBMSs such as MySQL or
MSSQL.
Jon
----- Original Message -----
From: "David Van Couvering" <Da...@Sun.COM>
To: "Derby Discussion" <de...@db.apache.org>
Sent: Tuesday, August 09, 2005 11:24 AM
Subject: Re: Thread on Derby on The Server Side
> Thanks, Sunitha, for looking into this! It would be *great* if you
> posted this to the server side in response to the "Derby is slow"
> judgement out there. If for whatever reason you are uncomfortable
> doing this, let me know, and I'll be happy to do it with your OK.
>
> These seem to be the two most common mistakes: assuming a database is
> transactional when it's not really (doesn't sync to disk) and not
> preparing statements. We ran into this at Sun as well with a group doing
> performance tests of Derby.
>
> We should have a "cheat sheet" for things people should look at when
> doing internal benchmarking of Derby and make sure it's front and center
> on our web site...
>
> David
>
> Sunitha Kambhampati wrote:
>
>> I took a quick look at the benchmark at polepos that is mentioned in
>> the thread on theserverside , specially the bahrain test. This test
>> uses Statements instead of PreparedStatement for the select queries.
>> ( see BahrainJdbc.java where select queries are like "select * from
>> bahrain where LicenseID=" + i
>> One important performance tip is to use PreparedStatement with
>> dynamic markers instead of Statements. This can show considerable
>> improvements.
>> If you use Statement with literal values as in the case above, that
>> will involve derby having to compile a query plan for each of the
>> statements which affects performance. Using PreparedStatement with
>> dynamic markers ('?') avoids unnecessary compilation cost , and thus
>> faster.
>>
>> So for example - if you are executing the query above(in 1st para)
>> with different LicenseID values, using a Statement will involve
>> compilation cost for each statement executed. But in case of
>> PreparedStatement, the query would be ' select * from bahrain where
>> LicenseID=?" and the statement is compiled only the first time or when
>> the optimizer thinks the plan is stale.
>>
>> Check out the tuning manual for more details :-
>> http://db.apache.org/derby/docs/dev/tuning/ctunperf18705.html
>> http://db.apache.org/derby/docs/dev/tuning/ctundepth29804.html#ctundepth29804
>>
>>
>> ---------
>> Also I think, sometimes default behaviors of databases are different
>> and this can lead to different performance out of the box - some key
>> factors such as the default isolation level, I/O syncs on a commit or
>> not. When I looked at the docs for HSQLDB and Pointbase last, I saw
>> that by default they dont force a sync call to the disk on a commit.
>> Sunitha.
>>
>> ps: There are some more differences documented in Derby-110 comments,
>> and also in Dan's blog entry.
>> http://www-128.ibm.com/developerworks/blogs/dw_blog.jspa?roll=-6&blog=397
>>
>>>
>>> Daniel Noll wrote:
>>>
>>>> David Van Couvering wrote:
>>>>
>>>>> Note there are suggestions Derby is slow. If anyone wants to chime
>>>>> in on some of this, that would be great. We don't want the "word"
>>>>> to get out that Derby is slow if it's based on poorly designed
>>>>> benchmarks or misconceptions...
>>>>>
>>>>> http://www.theserverside.com/news/thread.tss?thread_id=35729
>>>>
>>>>
>>>>
>>>>
>>>> Derby does have an inexplicable 8-12 second wait when creating or
>>>> opening a database where HSQLDB has no noticeable wait, which
>>>> doesn't hurt on a server but on a desktop client it's a bit of a
>>>> problem. It really makes our unit tests grind too, since we use a
>>>> fresh database for each test case.
>>>>
>>>> But that being said, at runtime (which is normally what matters) we
>>>> haven't encountered any noticeable slowdown using Derby over HSQLDB.
>>>>
>>>> Maybe someone has some quantitative figures though, instead of these
>>>> qualitative experiences.
>>>>
>>>> Daniel
>>>>
>>
>
Re: Thread on Derby on The Server Side
Posted by David Van Couvering <Da...@Sun.COM>.
Thanks, Sunitha, for looking into this! It would be *great* if you
posted this to the server side in response to the "Derby is slow"
judgement out there. If for whatever reason you are uncomfortable
doing this, let me know, and I'll be happy to do it with your OK.
These seem to be the two most common mistakes: assuming a database is
transactional when it's not really (doesn't sync to disk) and not
preparing statements. We ran into this at Sun as well with a group doing
performance tests of Derby.
We should have a "cheat sheet" for things people should look at when
doing internal benchmarking of Derby and make sure it's front and center
on our web site...
David
Sunitha Kambhampati wrote:
> I took a quick look at the benchmark at polepos that is mentioned in
> the thread on theserverside , specially the bahrain test. This test
> uses Statements instead of PreparedStatement for the select queries.
> ( see BahrainJdbc.java where select queries are like "select * from
> bahrain where LicenseID=" + i
> One important performance tip is to use PreparedStatement with
> dynamic markers instead of Statements. This can show considerable
> improvements.
> If you use Statement with literal values as in the case above, that
> will involve derby having to compile a query plan for each of the
> statements which affects performance. Using PreparedStatement with
> dynamic markers ('?') avoids unnecessary compilation cost , and thus
> faster.
>
> So for example - if you are executing the query above(in 1st para)
> with different LicenseID values, using a Statement will involve
> compilation cost for each statement executed. But in case of
> PreparedStatement, the query would be ' select * from bahrain where
> LicenseID=?" and the statement is compiled only the first time or when
> the optimizer thinks the plan is stale.
>
> Check out the tuning manual for more details :-
> http://db.apache.org/derby/docs/dev/tuning/ctunperf18705.html
> http://db.apache.org/derby/docs/dev/tuning/ctundepth29804.html#ctundepth29804
>
>
> ---------
> Also I think, sometimes default behaviors of databases are different
> and this can lead to different performance out of the box - some key
> factors such as the default isolation level, I/O syncs on a commit or
> not. When I looked at the docs for HSQLDB and Pointbase last, I saw
> that by default they dont force a sync call to the disk on a commit.
> Sunitha.
>
> ps: There are some more differences documented in Derby-110 comments,
> and also in Dan's blog entry.
> http://www-128.ibm.com/developerworks/blogs/dw_blog.jspa?roll=-6&blog=397
>
>>
>> Daniel Noll wrote:
>>
>>> David Van Couvering wrote:
>>>
>>>> Note there are suggestions Derby is slow. If anyone wants to chime
>>>> in on some of this, that would be great. We don't want the "word"
>>>> to get out that Derby is slow if it's based on poorly designed
>>>> benchmarks or misconceptions...
>>>>
>>>> http://www.theserverside.com/news/thread.tss?thread_id=35729
>>>
>>>
>>>
>>>
>>> Derby does have an inexplicable 8-12 second wait when creating or
>>> opening a database where HSQLDB has no noticeable wait, which
>>> doesn't hurt on a server but on a desktop client it's a bit of a
>>> problem. It really makes our unit tests grind too, since we use a
>>> fresh database for each test case.
>>>
>>> But that being said, at runtime (which is normally what matters) we
>>> haven't encountered any noticeable slowdown using Derby over HSQLDB.
>>>
>>> Maybe someone has some quantitative figures though, instead of these
>>> qualitative experiences.
>>>
>>> Daniel
>>>
>
Re: Thread on Derby on The Server Side
Posted by Sunitha Kambhampati <ks...@gmail.com>.
I took a quick look at the benchmark at polepos that is mentioned in the
thread on theserverside , specially the bahrain test. This test uses
Statements instead of PreparedStatement for the select queries. ( see
BahrainJdbc.java where select queries are like "select * from bahrain
where LicenseID=" + i
One important performance tip is to use PreparedStatement with dynamic
markers instead of Statements. This can show considerable improvements.
If you use Statement with literal values as in the case above, that will
involve derby having to compile a query plan for each of the statements
which affects performance. Using PreparedStatement with dynamic markers
('?') avoids unnecessary compilation cost , and thus faster.
So for example - if you are executing the query above(in 1st para) with
different LicenseID values, using a Statement will involve compilation
cost for each statement executed. But in case of PreparedStatement, the
query would be ' select * from bahrain where LicenseID=?" and the
statement is compiled only the first time or when the optimizer thinks
the plan is stale.
Check out the tuning manual for more details :-
http://db.apache.org/derby/docs/dev/tuning/ctunperf18705.html
http://db.apache.org/derby/docs/dev/tuning/ctundepth29804.html#ctundepth29804
---------
Also I think, sometimes default behaviors of databases are different and
this can lead to different performance out of the box - some key factors
such as the default isolation level, I/O syncs on a commit or not. When
I looked at the docs for HSQLDB and Pointbase last, I saw that by
default they dont force a sync call to the disk on a commit.
Sunitha.
ps: There are some more differences documented in Derby-110 comments,
and also in Dan's blog entry.
http://www-128.ibm.com/developerworks/blogs/dw_blog.jspa?roll=-6&blog=397
>
> Daniel Noll wrote:
>
>> David Van Couvering wrote:
>>
>>> Note there are suggestions Derby is slow. If anyone wants to chime
>>> in on some of this, that would be great. We don't want the "word"
>>> to get out that Derby is slow if it's based on poorly designed
>>> benchmarks or misconceptions...
>>>
>>> http://www.theserverside.com/news/thread.tss?thread_id=35729
>>
>>
>>
>> Derby does have an inexplicable 8-12 second wait when creating or
>> opening a database where HSQLDB has no noticeable wait, which doesn't
>> hurt on a server but on a desktop client it's a bit of a problem. It
>> really makes our unit tests grind too, since we use a fresh database
>> for each test case.
>>
>> But that being said, at runtime (which is normally what matters) we
>> haven't encountered any noticeable slowdown using Derby over HSQLDB.
>>
>> Maybe someone has some quantitative figures though, instead of these
>> qualitative experiences.
>>
>> Daniel
>>