Slowdown with HSQLDB

[Marc Novakouski-2]

Hi all,

I'm maintaining an eclipse RCP application and am having some issues integrating HSQLDB.  I'm only peripherally familiar with HSQLDB so I was hoping someone here could help me out.  My issue is that my application seems to slow down significantly as I add data to my HSQL DB instance.  I'm currently using:

Java 1.5 (it's a legacy app)
HSQLDB 1.8.1.1
Spring 2.0.6 + IBATIS 2.3.0.677 framework

I should also note that I'm using a standalone ("In Process") HSQL database on the local file system; the application is a stripped down training app that will only ever talk to its own local db so that seemed appropriate.  Data does need to be persistent however thus it cannot be a memory-only db.

Basically the application performs queries constantly upon the db, presents the information, and if the user indicates any change through the gui, makes those changes.  These can be updates, additions, or deletions.  

I've done a few things to try to figure out what the problem is.  The reason why I think it is HSQL that is causing the slowdown is the fact that the application can be switched between a MySQL datasource and a HSQL datasource via a config file, and when using MySQL there are no slowdown problems.  Here are the things I've tried:
1. Just querying the data over & over, not adding/modifying/deleting: doing this resulted in no slowdown issues, so it seems to be related to actually modifying the db.
2. I modified the tables, which are normally memory tables, to be cached tables (right now I use normal CREATE TABLE statements, so I changed that to CREATE CACHED TABLE).  I did observe that a .data file was created so I'm pretty sure I made the change correctly; however the slowdown issue persisted.
3. I added connection pooling to the datasource definition within the application context file; due to #1 above I didn't think that mounting unclosed connections was the issue, but I tried it anyways.  The slowdown persists.

Some things to note:
1. My .script file is ~2000 lines.  I didn't get the sense that this was a particularly large db so I didn't think I needed some large-size db customization, but if anyone can suggest something along those lines that would help, that'd be great.
2. As I make changes in the application, I can watch as my .log file slowly grows.  My understanding is that this is the normal & correct operation, but just in case it raises any red flags I'm mentioning it here.

I'm about out of ideas; the only other thing I thought to try would be to upgrade to HSQL 1.9 to see if performance improves.  I also figure that modifying parameters associated to the spring/ibatis framework interface might be helpful, but I'm not really sure where to go with that.  Any ideas or suggestions that anyone could provide would be very much appreciated.

Thanks,
Marc

------------------------------------------------------------------------------
Come build with us! The BlackBerry(R) Developer Conference in SF, CA
is the only developer event you need to attend this year. Jumpstart your
developing skills, take BlackBerry mobile applications to market and stay
ahead of the curve. Join us from November 9 - 12, 2009. Register now!
http://p.sf.net/sfu/devconference
_______________________________________________
Hsqldb-user mailing list
Hsqldb-user@...
https://lists.sourceforge.net/lists/listinfo/hsqldb-user

[Fred Toussi-2]

Hi Marc,

The script file (for a memory database) is pretty small.

Can you describe the data that was modified (amount and the type of
statements) after point 1.

Are you saying adding data is slow, or query speed after adding data is
slow?

Once possible cause that you do not have indexes on your tables. You can
execute EXPLAIN PLAIN FOR SELECT [rest of your select satement] to see if it
is reporting index use or full scan.

Another possible cause is that you are performing SELECT COUNT(*) over and
over again.

Yet another possible casue is Java heap limitations or use of virtual memory
for heap.

Fred

----- Original Message -----
From: "Marc Novakouski" <novakom@...>
To: <hsqldb-user@...>
Sent: 27 October 2009 15:35
Subject: [Hsqldb-user] Slowdown with HSQLDB


Hi all,

I'm maintaining an eclipse RCP application and am having some issues
integrating HSQLDB.  I'm only peripherally familiar with HSQLDB so I was
hoping someone here could help me out.  My issue is that my application
seems to slow down significantly as I add data to my HSQL DB instance.  I'm
currently using:

Java 1.5 (it's a legacy app)
HSQLDB 1.8.1.1
Spring 2.0.6 + IBATIS 2.3.0.677 framework

I should also note that I'm using a standalone ("In Process") HSQL database
on the local file system; the application is a stripped down training app
that will only ever talk to its own local db so that seemed appropriate.
Data does need to be persistent however thus it cannot be a memory-only db.

Basically the application performs queries constantly upon the db, presents
the information, and if the user indicates any change through the gui, makes
those changes.  These can be updates, additions, or deletions.

I've done a few things to try to figure out what the problem is.  The reason
why I think it is HSQL that is causing the slowdown is the fact that the
application can be switched between a MySQL datasource and a HSQL datasource
via a config file, and when using MySQL there are no slowdown problems.
Here are the things I've tried:
1. Just querying the data over & over, not adding/modifying/deleting: doing
this resulted in no slowdown issues, so it seems to be related to actually
modifying the db.
2. I modified the tables, which are normally memory tables, to be cached
tables (right now I use normal CREATE TABLE statements, so I changed that to
CREATE CACHED TABLE).  I did observe that a .data file was created so I'm
pretty sure I made the change correctly; however the slowdown issue
persisted.
3. I added connection pooling to the datasource definition within the
application context file; due to #1 above I didn't think that mounting
unclosed connections was the issue, but I tried it anyways.  The slowdown
persists.

Some things to note:
1. My .script file is ~2000 lines.  I didn't get the sense that this was a
particularly large db so I didn't think I needed some large-size db
customization, but if anyone can suggest something along those lines that
would help, that'd be great.
2. As I make changes in the application, I can watch as my .log file slowly
grows.  My understanding is that this is the normal & correct operation, but
just in case it raises any red flags I'm mentioning it here.

I'm about out of ideas; the only other thing I thought to try would be to
upgrade to HSQL 1.9 to see if performance improves.  I also figure that
modifying parameters associated to the spring/ibatis framework interface
might be helpful, but I'm not really sure where to go with that.  Any ideas
or suggestions that anyone could provide would be very much appreciated.

Thanks,
Marc

------------------------------------------------------------------------------
Come build with us! The BlackBerry(R) Developer Conference in SF, CA
is the only developer event you need to attend this year. Jumpstart your
developing skills, take BlackBerry mobile applications to market and stay
ahead of the curve. Join us from November 9 - 12, 2009. Register now!
http://p.sf.net/sfu/devconference
_______________________________________________
Hsqldb-user mailing list
Hsqldb-user@...
https://lists.sourceforge.net/lists/listinfo/hsqldb-user 


------------------------------------------------------------------------------
Come build with us! The BlackBerry(R) Developer Conference in SF, CA
is the only developer event you need to attend this year. Jumpstart your
developing skills, take BlackBerry mobile applications to market and stay
ahead of the curve. Join us from November 9 - 12, 2009. Register now!
http://p.sf.net/sfu/devconference
_______________________________________________
Hsqldb-user mailing list
Hsqldb-user@...
https://lists.sourceforge.net/lists/listinfo/hsqldb-user

[Marc Novakouski-2]

Hi Fred,

Thanks for getting back to me so fast.  My responses are embedded below.

Marc

-----Original Message-----
From: fredt [mailto:fredt@...]
Sent: Tuesday, October 27, 2009 1:08 PM
To: HSQLdb user discussions
Subject: Re: [Hsqldb-user] Slowdown with HSQLDB

Hi Marc,

The script file (for a memory database) is pretty small.
MARC: That's what I thought; I didn't think it would be an issue.

Can you describe the data that was modified (amount and the type of
statements) after point 1.
MARC: Very basic actually.  I'm doing a single insert into one to three tables.  I did notice, going through the log, that there's an additional 2 lines for every update where a line is removed from a key table and re-inserted; however this would seem to be an error in the code; I'm trying to track that down now.

Are you saying adding data is slow, or query speed after adding data is slow?
MARC: I can definitively say that both updating data and query speed is slow.  There are a number of different actions that can result in a data query (no update) on the screen, and each operation, when performed individually, has the same slowly increasing lag time.  Additionally, the save action which writes the data to the db has the same lag time, so I would argue that it seems to be both.

Once possible cause that you do not have indexes on your tables. You can execute EXPLAIN PLAIN FOR SELECT [rest of your select satement] to see if it is reporting index use or full scan.
MARC: This might be hard within the application itself; as I noted, I am using Spring + iBatis to access the DB, and the queries are set generally to work both with HSQL and MySQL, so I'd rather not modify them.  I tried doing this through the HSQL DB manager while accessing the db directly, but I get an error of "Unexpected token PLAIN, requires PLAN in statement ...".  Would I need to do this within the application, and if so, what is the expected result?

Another possible cause is that you are performing SELECT COUNT(*) over and over again.
MARC: There are some SELECT COUNT statements, but none should be being performed.  I could monitor the application to verify this, but I doubt that this is the problem.  Does HSQL have a way to record all queries so you can verify on that end?

Yet another possible casue is Java heap limitations or use of virtual memory for heap.
MARC: I'll take a look at this.

Fred

----- Original Message -----
From: "Marc Novakouski" <novakom@...>
To: <hsqldb-user@...>
Sent: 27 October 2009 15:35
Subject: [Hsqldb-user] Slowdown with HSQLDB


Hi all,

I'm maintaining an eclipse RCP application and am having some issues
integrating HSQLDB.  I'm only peripherally familiar with HSQLDB so I was
hoping someone here could help me out.  My issue is that my application
seems to slow down significantly as I add data to my HSQL DB instance.  I'm
currently using:

Java 1.5 (it's a legacy app)
HSQLDB 1.8.1.1
Spring 2.0.6 + IBATIS 2.3.0.677 framework

I should also note that I'm using a standalone ("In Process") HSQL database
on the local file system; the application is a stripped down training app
that will only ever talk to its own local db so that seemed appropriate.
Data does need to be persistent however thus it cannot be a memory-only db.

Basically the application performs queries constantly upon the db, presents
the information, and if the user indicates any change through the gui, makes
those changes.  These can be updates, additions, or deletions.

I've done a few things to try to figure out what the problem is.  The reason
why I think it is HSQL that is causing the slowdown is the fact that the
application can be switched between a MySQL datasource and a HSQL datasource
via a config file, and when using MySQL there are no slowdown problems.
Here are the things I've tried:
1. Just querying the data over & over, not adding/modifying/deleting: doing
this resulted in no slowdown issues, so it seems to be related to actually
modifying the db.
2. I modified the tables, which are normally memory tables, to be cached
tables (right now I use normal CREATE TABLE statements, so I changed that to
CREATE CACHED TABLE).  I did observe that a .data file was created so I'm
pretty sure I made the change correctly; however the slowdown issue
persisted.
3. I added connection pooling to the datasource definition within the
application context file; due to #1 above I didn't think that mounting
unclosed connections was the issue, but I tried it anyways.  The slowdown
persists.

Some things to note:
1. My .script file is ~2000 lines.  I didn't get the sense that this was a
particularly large db so I didn't think I needed some large-size db
customization, but if anyone can suggest something along those lines that
would help, that'd be great.
2. As I make changes in the application, I can watch as my .log file slowly
grows.  My understanding is that this is the normal & correct operation, but
just in case it raises any red flags I'm mentioning it here.

I'm about out of ideas; the only other thing I thought to try would be to
upgrade to HSQL 1.9 to see if performance improves.  I also figure that
modifying parameters associated to the spring/ibatis framework interface
might be helpful, but I'm not really sure where to go with that.  Any ideas
or suggestions that anyone could provide would be very much appreciated.

Thanks,
Marc

------------------------------------------------------------------------------
Come build with us! The BlackBerry(R) Developer Conference in SF, CA
is the only developer event you need to attend this year. Jumpstart your
developing skills, take BlackBerry mobile applications to market and stay
ahead of the curve. Join us from November 9 - 12, 2009. Register now!
http://p.sf.net/sfu/devconference
_______________________________________________
Hsqldb-user mailing list
Hsqldb-user@...
https://lists.sourceforge.net/lists/listinfo/hsqldb-user 


------------------------------------------------------------------------------
Come build with us! The BlackBerry(R) Developer Conference in SF, CA
is the only developer event you need to attend this year. Jumpstart your
developing skills, take BlackBerry mobile applications to market and stay
ahead of the curve. Join us from November 9 - 12, 2009. Register now!
http://p.sf.net/sfu/devconference
_______________________________________________
Hsqldb-user mailing list
Hsqldb-user@...
https://lists.sourceforge.net/lists/listinfo/hsqldb-user

------------------------------------------------------------------------------
Come build with us! The BlackBerry(R) Developer Conference in SF, CA
is the only developer event you need to attend this year. Jumpstart your
developing skills, take BlackBerry mobile applications to market and stay
ahead of the curve. Join us from November 9 - 12, 2009. Register now!
http://p.sf.net/sfu/devconference
_______________________________________________
Hsqldb-user mailing list
Hsqldb-user@...
https://lists.sourceforge.net/lists/listinfo/hsqldb-user

[Marc Novakouski-2]

Hi Fred,

A few updates:

1. The extra add/delete I mentioned in the 2nd point is actually by design and removing it does not improve performance, so that's not the issue.

2. I did try bumping heap size to 1 GB and that seemed to have no effect.

3. I tried to track down one of the lines in the code where the application lags.  I managed to pinpoint (at least one instance) to a single query.  It's actually a pretty complex query, and using Spring/iBatis there's actually 2 nested queries that gather additional information for each of the returned result rows.  It's also true that the query returns exactly the amount of data that has already been added via the insert statements (i.e., the inserts add rows to table x, and this complex query queries table x) so as that table grows the output of this query would increase in a possibly exponential manner. I'm currently looking at if what we're doing is actually Necessary or just bad design (I didn't write the original code) however the point still stands that HSQL chugs on this query whereas MySQL seems to handle it fine.  If you have any additional insight into why this sort of situation might be happening I'd very much appreciate it.

Thanks,
Marc

-----Original Message-----
From: Marc Novakouski [mailto:novakom@...]
Sent: Tuesday, October 27, 2009 1:40 PM
To: HSQLdb user discussions
Subject: Re: [Hsqldb-user] Slowdown with HSQLDB

Hi Fred,

Thanks for getting back to me so fast.  My responses are embedded below.

Marc

-----Original Message-----
From: fredt [mailto:fredt@...]
Sent: Tuesday, October 27, 2009 1:08 PM
To: HSQLdb user discussions
Subject: Re: [Hsqldb-user] Slowdown with HSQLDB

Hi Marc,

The script file (for a memory database) is pretty small.
MARC: That's what I thought; I didn't think it would be an issue.

Can you describe the data that was modified (amount and the type of
statements) after point 1.
MARC: Very basic actually.  I'm doing a single insert into one to three tables.  I did notice, going through the log, that there's an additional 2 lines for every update where a line is removed from a key table and re-inserted; however this would seem to be an error in the code; I'm trying to track that down now.

Are you saying adding data is slow, or query speed after adding data is slow?
MARC: I can definitively say that both updating data and query speed is slow.  There are a number of different actions that can result in a data query (no update) on the screen, and each operation, when performed individually, has the same slowly increasing lag time.  Additionally, the save action which writes the data to the db has the same lag time, so I would argue that it seems to be both.

Once possible cause that you do not have indexes on your tables. You can execute EXPLAIN PLAIN FOR SELECT [rest of your select satement] to see if it is reporting index use or full scan.
MARC: This might be hard within the application itself; as I noted, I am using Spring + iBatis to access the DB, and the queries are set generally to work both with HSQL and MySQL, so I'd rather not modify them.  I tried doing this through the HSQL DB manager while accessing the db directly, but I get an error of "Unexpected token PLAIN, requires PLAN in statement ...".  Would I need to do this within the application, and if so, what is the expected result?

Another possible cause is that you are performing SELECT COUNT(*) over and over again.
MARC: There are some SELECT COUNT statements, but none should be being performed.  I could monitor the application to verify this, but I doubt that this is the problem.  Does HSQL have a way to record all queries so you can verify on that end?

Yet another possible casue is Java heap limitations or use of virtual memory for heap.
MARC: I'll take a look at this.

Fred

----- Original Message -----
From: "Marc Novakouski" <novakom@...>
To: <hsqldb-user@...>
Sent: 27 October 2009 15:35
Subject: [Hsqldb-user] Slowdown with HSQLDB


Hi all,

I'm maintaining an eclipse RCP application and am having some issues integrating HSQLDB.  I'm only peripherally familiar with HSQLDB so I was hoping someone here could help me out.  My issue is that my application seems to slow down significantly as I add data to my HSQL DB instance.  I'm currently using:

Java 1.5 (it's a legacy app)
HSQLDB 1.8.1.1
Spring 2.0.6 + IBATIS 2.3.0.677 framework

I should also note that I'm using a standalone ("In Process") HSQL database on the local file system; the application is a stripped down training app that will only ever talk to its own local db so that seemed appropriate.
Data does need to be persistent however thus it cannot be a memory-only db.

Basically the application performs queries constantly upon the db, presents the information, and if the user indicates any change through the gui, makes those changes.  These can be updates, additions, or deletions.

I've done a few things to try to figure out what the problem is.  The reason why I think it is HSQL that is causing the slowdown is the fact that the application can be switched between a MySQL datasource and a HSQL datasource via a config file, and when using MySQL there are no slowdown problems.
Here are the things I've tried:
1. Just querying the data over & over, not adding/modifying/deleting: doing this resulted in no slowdown issues, so it seems to be related to actually modifying the db.
2. I modified the tables, which are normally memory tables, to be cached tables (right now I use normal CREATE TABLE statements, so I changed that to CREATE CACHED TABLE).  I did observe that a .data file was created so I'm pretty sure I made the change correctly; however the slowdown issue persisted.
3. I added connection pooling to the datasource definition within the application context file; due to #1 above I didn't think that mounting unclosed connections was the issue, but I tried it anyways.  The slowdown persists.

Some things to note:
1. My .script file is ~2000 lines.  I didn't get the sense that this was a particularly large db so I didn't think I needed some large-size db customization, but if anyone can suggest something along those lines that would help, that'd be great.
2. As I make changes in the application, I can watch as my .log file slowly grows.  My understanding is that this is the normal & correct operation, but just in case it raises any red flags I'm mentioning it here.

I'm about out of ideas; the only other thing I thought to try would be to upgrade to HSQL 1.9 to see if performance improves.  I also figure that modifying parameters associated to the spring/ibatis framework interface might be helpful, but I'm not really sure where to go with that.  Any ideas or suggestions that anyone could provide would be very much appreciated.

Thanks,
Marc

------------------------------------------------------------------------------
Come build with us! The BlackBerry(R) Developer Conference in SF, CA is the only developer event you need to attend this year. Jumpstart your developing skills, take BlackBerry mobile applications to market and stay ahead of the curve. Join us from November 9 - 12, 2009. Register now!
http://p.sf.net/sfu/devconference
_______________________________________________
Hsqldb-user mailing list
Hsqldb-user@...
https://lists.sourceforge.net/lists/listinfo/hsqldb-user 


------------------------------------------------------------------------------
Come build with us! The BlackBerry(R) Developer Conference in SF, CA is the only developer event you need to attend this year. Jumpstart your developing skills, take BlackBerry mobile applications to market and stay ahead of the curve. Join us from November 9 - 12, 2009. Register now!
http://p.sf.net/sfu/devconference
_______________________________________________
Hsqldb-user mailing list
Hsqldb-user@...
https://lists.sourceforge.net/lists/listinfo/hsqldb-user

------------------------------------------------------------------------------
Come build with us! The BlackBerry(R) Developer Conference in SF, CA is the only developer event you need to attend this year. Jumpstart your developing skills, take BlackBerry mobile applications to market and stay ahead of the curve. Join us from November 9 - 12, 2009. Register now!
http://p.sf.net/sfu/devconference
_______________________________________________
Hsqldb-user mailing list
Hsqldb-user@...
https://lists.sourceforge.net/lists/listinfo/hsqldb-user

------------------------------------------------------------------------------
Come build with us! The BlackBerry(R) Developer Conference in SF, CA
is the only developer event you need to attend this year. Jumpstart your
developing skills, take BlackBerry mobile applications to market and stay
ahead of the curve. Join us from November 9 - 12, 2009. Register now!
http://p.sf.net/sfu/devconference
_______________________________________________
Hsqldb-user mailing list
Hsqldb-user@...
https://lists.sourceforge.net/lists/listinfo/hsqldb-user

[Fred Toussi-2]

You can try with version 1.9 which has better query optimisation.

We are constantly improving in this area. It is therefore worth sending
me a sample database together with the slow SELECT and we will speed it
up.

It seem this is the issue, and the rest of the possible causes do not
apply to this case.

Fred

On Tue, 27 Oct 2009 15:15 -0400, "Marc Novakouski" <novakom@...>
wrote:

> Hi Fred,
>
> A few updates:
>
> 1. The extra add/delete I mentioned in the 2nd point is actually by
> design and removing it does not improve performance, so that's not the
> issue.
>
> 2. I did try bumping heap size to 1 GB and that seemed to have no effect.
>
> 3. I tried to track down one of the lines in the code where the
> application lags.  I managed to pinpoint (at least one instance) to a
> single query.  It's actually a pretty complex query, and using
> Spring/iBatis there's actually 2 nested queries that gather additional
> information for each of the returned result rows.  It's also true that
> the query returns exactly the amount of data that has already been added
> via the insert statements (i.e., the inserts add rows to table x, and
> this complex query queries table x) so as that table grows the output of
> this query would increase in a possibly exponential manner. I'm currently
> looking at if what we're doing is actually Necessary or just bad design
> (I didn't write the original code) however the point still stands that
> HSQL chugs on this query whereas MySQL seems to handle it fine.  If you
> have any additional insight into why this sort of situation might be
> happening I'd very much appreciate it.
>
> Thanks,
> Marc
>
> -----Original Message-----
> From: Marc Novakouski [mailto:novakom@...]
> Sent: Tuesday, October 27, 2009 1:40 PM
> To: HSQLdb user discussions
> Subject: Re: [Hsqldb-user] Slowdown with HSQLDB
>
> Hi Fred,
>
> Thanks for getting back to me so fast.  My responses are embedded below.
>
> Marc
>
> -----Original Message-----
> From: fredt [mailto:fredt@...]
> Sent: Tuesday, October 27, 2009 1:08 PM
> To: HSQLdb user discussions
> Subject: Re: [Hsqldb-user] Slowdown with HSQLDB
>
> Hi Marc,
>
> The script file (for a memory database) is pretty small.
> MARC: That's what I thought; I didn't think it would be an issue.
>
> Can you describe the data that was modified (amount and the type of
> statements) after point 1.
> MARC: Very basic actually.  I'm doing a single insert into one to three
> tables.  I did notice, going through the log, that there's an additional
> 2 lines for every update where a line is removed from a key table and
> re-inserted; however this would seem to be an error in the code; I'm
> trying to track that down now.
>
> Are you saying adding data is slow, or query speed after adding data is
> slow?
> MARC: I can definitively say that both updating data and query speed is
> slow.  There are a number of different actions that can result in a data
> query (no update) on the screen, and each operation, when performed
> individually, has the same slowly increasing lag time.  Additionally, the
> save action which writes the data to the db has the same lag time, so I
> would argue that it seems to be both.
>
> Once possible cause that you do not have indexes on your tables. You can
> execute EXPLAIN PLAIN FOR SELECT [rest of your select satement] to see if
> it is reporting index use or full scan.
> MARC: This might be hard within the application itself; as I noted, I am
> using Spring + iBatis to access the DB, and the queries are set generally
> to work both with HSQL and MySQL, so I'd rather not modify them.  I tried
> doing this through the HSQL DB manager while accessing the db directly,
> but I get an error of "Unexpected token PLAIN, requires PLAN in statement
> ...".  Would I need to do this within the application, and if so, what is
> the expected result?
>
> Another possible cause is that you are performing SELECT COUNT(*) over
> and over again.
> MARC: There are some SELECT COUNT statements, but none should be being
> performed.  I could monitor the application to verify this, but I doubt
> that this is the problem.  Does HSQL have a way to record all queries so
> you can verify on that end?
>
> Yet another possible casue is Java heap limitations or use of virtual
> memory for heap.
> MARC: I'll take a look at this.
>
> Fred
>
> ----- Original Message -----
> From: "Marc Novakouski" <novakom@...>
> To: <hsqldb-user@...>
> Sent: 27 October 2009 15:35
> Subject: [Hsqldb-user] Slowdown with HSQLDB
>
>
> Hi all,
>
> I'm maintaining an eclipse RCP application and am having some issues
> integrating HSQLDB.  I'm only peripherally familiar with HSQLDB so I was
> hoping someone here could help me out.  My issue is that my application
> seems to slow down significantly as I add data to my HSQL DB instance.
> I'm currently using:
>
> Java 1.5 (it's a legacy app)
> HSQLDB 1.8.1.1
> Spring 2.0.6 + IBATIS 2.3.0.677 framework
>
> I should also note that I'm using a standalone ("In Process") HSQL
> database on the local file system; the application is a stripped down
> training app that will only ever talk to its own local db so that seemed
> appropriate.
> Data does need to be persistent however thus it cannot be a memory-only
> db.
>
> Basically the application performs queries constantly upon the db,
> presents the information, and if the user indicates any change through
> the gui, makes those changes.  These can be updates, additions, or
> deletions.
>
> I've done a few things to try to figure out what the problem is.  The
> reason why I think it is HSQL that is causing the slowdown is the fact
> that the application can be switched between a MySQL datasource and a
> HSQL datasource via a config file, and when using MySQL there are no
> slowdown problems.
> Here are the things I've tried:
> 1. Just querying the data over & over, not adding/modifying/deleting:
> doing this resulted in no slowdown issues, so it seems to be related to
> actually modifying the db.
> 2. I modified the tables, which are normally memory tables, to be cached
> tables (right now I use normal CREATE TABLE statements, so I changed that
> to CREATE CACHED TABLE).  I did observe that a .data file was created so
> I'm pretty sure I made the change correctly; however the slowdown issue
> persisted.
> 3. I added connection pooling to the datasource definition within the
> application context file; due to #1 above I didn't think that mounting
> unclosed connections was the issue, but I tried it anyways.  The slowdown
> persists.
>
> Some things to note:
> 1. My .script file is ~2000 lines.  I didn't get the sense that this was
> a particularly large db so I didn't think I needed some large-size db
> customization, but if anyone can suggest something along those lines that
> would help, that'd be great.
> 2. As I make changes in the application, I can watch as my .log file
> slowly grows.  My understanding is that this is the normal & correct
> operation, but just in case it raises any red flags I'm mentioning it
> here.
>
> I'm about out of ideas; the only other thing I thought to try would be to
> upgrade to HSQL 1.9 to see if performance improves.  I also figure that
> modifying parameters associated to the spring/ibatis framework interface
> might be helpful, but I'm not really sure where to go with that.  Any
> ideas or suggestions that anyone could provide would be very much
> appreciated.
>
> Thanks,
> Marc
>
> ------------------------------------------------------------------------------
> Come build with us! The BlackBerry(R) Developer Conference in SF, CA is
> the only developer event you need to attend this year. Jumpstart your
> developing skills, take BlackBerry mobile applications to market and stay
> ahead of the curve. Join us from November 9 - 12, 2009. Register now!
> http://p.sf.net/sfu/devconference
> _______________________________________________
> Hsqldb-user mailing list
> Hsqldb-user@...
> https://lists.sourceforge.net/lists/listinfo/hsqldb-user 
>
>
> ------------------------------------------------------------------------------
> Come build with us! The BlackBerry(R) Developer Conference in SF, CA is
> the only developer event you need to attend this year. Jumpstart your
> developing skills, take BlackBerry mobile applications to market and stay
> ahead of the curve. Join us from November 9 - 12, 2009. Register now!
> http://p.sf.net/sfu/devconference
> _______________________________________________
> Hsqldb-user mailing list
> Hsqldb-user@...
> https://lists.sourceforge.net/lists/listinfo/hsqldb-user
>
> ------------------------------------------------------------------------------
> Come build with us! The BlackBerry(R) Developer Conference in SF, CA is
> the only developer event you need to attend this year. Jumpstart your
> developing skills, take BlackBerry mobile applications to market and stay
> ahead of the curve. Join us from November 9 - 12, 2009. Register now!
> http://p.sf.net/sfu/devconference
> _______________________________________________
> Hsqldb-user mailing list
> Hsqldb-user@...
> https://lists.sourceforge.net/lists/listinfo/hsqldb-user
>
> ------------------------------------------------------------------------------
> Come build with us! The BlackBerry(R) Developer Conference in SF, CA
> is the only developer event you need to attend this year. Jumpstart your
> developing skills, take BlackBerry mobile applications to market and stay
> ahead of the curve. Join us from November 9 - 12, 2009. Register now!
> http://p.sf.net/sfu/devconference
> _______________________________________________
> Hsqldb-user mailing list
> Hsqldb-user@...
> https://lists.sourceforge.net/lists/listinfo/hsqldb-user

------------------------------------------------------------------------------
Come build with us! The BlackBerry(R) Developer Conference in SF, CA
is the only developer event you need to attend this year. Jumpstart your
developing skills, take BlackBerry mobile applications to market and stay
ahead of the curve. Join us from November 9 - 12, 2009. Register now!
http://p.sf.net/sfu/devconference
_______________________________________________
Hsqldb-user mailing list
Hsqldb-user@...
https://lists.sourceforge.net/lists/listinfo/hsqldb-user

[Fred Toussi-2]

Just for the record, I will clarify the following.

1. UPDATE statements are recorded as insert + delete in the .log file,
so this is normal.
2. I meant EXPLAIN PLAN. This should be run outside the application. The
output is like a report and shows how the query is performed by using
which indexes. It doesn't execute the query.
3. If you run a Server with silent=false property, then all the queries
and other statements are reported to the console.

Fred



On Tue, 27 Oct 2009 13:40 -0400, "Marc Novakouski" <novakom@...>
wrote:

> Hi Fred,
>
> Thanks for getting back to me so fast.  My responses are embedded below.
>
> Marc
>
> -----Original Message-----
> From: fredt [mailto:fredt@...]
> Sent: Tuesday, October 27, 2009 1:08 PM
> To: HSQLdb user discussions
> Subject: Re: [Hsqldb-user] Slowdown with HSQLDB
>
> Hi Marc,
>
> The script file (for a memory database) is pretty small.
> MARC: That's what I thought; I didn't think it would be an issue.
>
> Can you describe the data that was modified (amount and the type of
> statements) after point 1.
> MARC: Very basic actually.  I'm doing a single insert into one to three
> tables.  I did notice, going through the log, that there's an additional
> 2 lines for every update where a line is removed from a key table and
> re-inserted; however this would seem to be an error in the code; I'm
> trying to track that down now.
>
> Are you saying adding data is slow, or query speed after adding data is
> slow?
> MARC: I can definitively say that both updating data and query speed is
> slow.  There are a number of different actions that can result in a data
> query (no update) on the screen, and each operation, when performed
> individually, has the same slowly increasing lag time.  Additionally, the
> save action which writes the data to the db has the same lag time, so I
> would argue that it seems to be both.
>
> Once possible cause that you do not have indexes on your tables. You can
> execute EXPLAIN PLAIN FOR SELECT [rest of your select satement] to see if
> it is reporting index use or full scan.
> MARC: This might be hard within the application itself; as I noted, I am
> using Spring + iBatis to access the DB, and the queries are set generally
> to work both with HSQL and MySQL, so I'd rather not modify them.  I tried
> doing this through the HSQL DB manager while accessing the db directly,
> but I get an error of "Unexpected token PLAIN, requires PLAN in statement
> ...".  Would I need to do this within the application, and if so, what is
> the expected result?
>
> Another possible cause is that you are performing SELECT COUNT(*) over
> and over again.
> MARC: There are some SELECT COUNT statements, but none should be being
> performed.  I could monitor the application to verify this, but I doubt
> that this is the problem.  Does HSQL have a way to record all queries so
> you can verify on that end?
>
> Yet another possible casue is Java heap limitations or use of virtual
> memory for heap.
> MARC: I'll take a look at this.
>
> Fred
>
> ----- Original Message -----
> From: "Marc Novakouski" <novakom@...>
> To: <hsqldb-user@...>
> Sent: 27 October 2009 15:35
> Subject: [Hsqldb-user] Slowdown with HSQLDB
>
>
> Hi all,
>
> I'm maintaining an eclipse RCP application and am having some issues
> integrating HSQLDB.  I'm only peripherally familiar with HSQLDB so I was
> hoping someone here could help me out.  My issue is that my application
> seems to slow down significantly as I add data to my HSQL DB instance.
> I'm
> currently using:
>
> Java 1.5 (it's a legacy app)
> HSQLDB 1.8.1.1
> Spring 2.0.6 + IBATIS 2.3.0.677 framework
>
> I should also note that I'm using a standalone ("In Process") HSQL
> database
> on the local file system; the application is a stripped down training app
> that will only ever talk to its own local db so that seemed appropriate.
> Data does need to be persistent however thus it cannot be a memory-only
> db.
>
> Basically the application performs queries constantly upon the db,
> presents
> the information, and if the user indicates any change through the gui,
> makes
> those changes.  These can be updates, additions, or deletions.
>
> I've done a few things to try to figure out what the problem is.  The
> reason
> why I think it is HSQL that is causing the slowdown is the fact that the
> application can be switched between a MySQL datasource and a HSQL
> datasource
> via a config file, and when using MySQL there are no slowdown problems.
> Here are the things I've tried:
> 1. Just querying the data over & over, not adding/modifying/deleting:
> doing
> this resulted in no slowdown issues, so it seems to be related to
> actually
> modifying the db.
> 2. I modified the tables, which are normally memory tables, to be cached
> tables (right now I use normal CREATE TABLE statements, so I changed that
> to
> CREATE CACHED TABLE).  I did observe that a .data file was created so I'm
> pretty sure I made the change correctly; however the slowdown issue
> persisted.
> 3. I added connection pooling to the datasource definition within the
> application context file; due to #1 above I didn't think that mounting
> unclosed connections was the issue, but I tried it anyways.  The slowdown
> persists.
>
> Some things to note:
> 1. My .script file is ~2000 lines.  I didn't get the sense that this was
> a
> particularly large db so I didn't think I needed some large-size db
> customization, but if anyone can suggest something along those lines that
> would help, that'd be great.
> 2. As I make changes in the application, I can watch as my .log file
> slowly
> grows.  My understanding is that this is the normal & correct operation,
> but
> just in case it raises any red flags I'm mentioning it here.
>
> I'm about out of ideas; the only other thing I thought to try would be to
> upgrade to HSQL 1.9 to see if performance improves.  I also figure that
> modifying parameters associated to the spring/ibatis framework interface
> might be helpful, but I'm not really sure where to go with that.  Any
> ideas
> or suggestions that anyone could provide would be very much appreciated.
>
> Thanks,
> Marc
>
> ------------------------------------------------------------------------------
> Come build with us! The BlackBerry(R) Developer Conference in SF, CA
> is the only developer event you need to attend this year. Jumpstart your
> developing skills, take BlackBerry mobile applications to market and stay
> ahead of the curve. Join us from November 9 - 12, 2009. Register now!
> http://p.sf.net/sfu/devconference
> _______________________________________________
> Hsqldb-user mailing list
> Hsqldb-user@...
> https://lists.sourceforge.net/lists/listinfo/hsqldb-user 
>
>
> ------------------------------------------------------------------------------
> Come build with us! The BlackBerry(R) Developer Conference in SF, CA
> is the only developer event you need to attend this year. Jumpstart your
> developing skills, take BlackBerry mobile applications to market and stay
> ahead of the curve. Join us from November 9 - 12, 2009. Register now!
> http://p.sf.net/sfu/devconference
> _______________________________________________
> Hsqldb-user mailing list
> Hsqldb-user@...
> https://lists.sourceforge.net/lists/listinfo/hsqldb-user
>
> ------------------------------------------------------------------------------
> Come build with us! The BlackBerry(R) Developer Conference in SF, CA
> is the only developer event you need to attend this year. Jumpstart your
> developing skills, take BlackBerry mobile applications to market and stay
> ahead of the curve. Join us from November 9 - 12, 2009. Register now!
> http://p.sf.net/sfu/devconference
> _______________________________________________
> Hsqldb-user mailing list
> Hsqldb-user@...
> https://lists.sourceforge.net/lists/listinfo/hsqldb-user

------------------------------------------------------------------------------
Come build with us! The BlackBerry(R) Developer Conference in SF, CA
is the only developer event you need to attend this year. Jumpstart your
developing skills, take BlackBerry mobile applications to market and stay
ahead of the curve. Join us from November 9 - 12, 2009. Register now!
http://p.sf.net/sfu/devconference
_______________________________________________
Hsqldb-user mailing list
Hsqldb-user@...
https://lists.sourceforge.net/lists/listinfo/hsqldb-user

[Marc Novakouski-2]

Hi Fred,

Thanks again for all the info.  I've verified that this one particular select statement is the culprit.  I did try to upgrade to 1.9, but I had all sorts of issues trying to make it work; apparently I can't get the following statement to run:

Class.forName(driver).newInstance();

where driver is a string set to  "org.hsqldb.jdbcDriver" (I guess you don't have that class anymore?)

In any case, I'm going to send you directly a stripped-down database & a query which doesn't respond quickly as per your request.  I talked to my boss and I think I have a workaround so I think it shouldn't matter, but if you can resolve this that would be great too.

Thanks!
Marc


-----Original Message-----
From: Fred Toussi [mailto:fredt@...]
Sent: Tuesday, October 27, 2009 3:25 PM
To: HSQLdb user discussions
Subject: Re: [Hsqldb-user] Slowdown with HSQLDB

You can try with version 1.9 which has better query optimisation.

We are constantly improving in this area. It is therefore worth sending me a sample database together with the slow SELECT and we will speed it up.

It seem this is the issue, and the rest of the possible causes do not apply to this case.

Fred

On Tue, 27 Oct 2009 15:15 -0400, "Marc Novakouski" <novakom@...>
wrote:

> Hi Fred,
>
> A few updates:
>
> 1. The extra add/delete I mentioned in the 2nd point is actually by
> design and removing it does not improve performance, so that's not the
> issue.
>
> 2. I did try bumping heap size to 1 GB and that seemed to have no effect.
>
> 3. I tried to track down one of the lines in the code where the
> application lags.  I managed to pinpoint (at least one instance) to a
> single query.  It's actually a pretty complex query, and using
> Spring/iBatis there's actually 2 nested queries that gather additional
> information for each of the returned result rows.  It's also true that
> the query returns exactly the amount of data that has already been
> added via the insert statements (i.e., the inserts add rows to table
> x, and this complex query queries table x) so as that table grows the
> output of this query would increase in a possibly exponential manner.
> I'm currently looking at if what we're doing is actually Necessary or
> just bad design (I didn't write the original code) however the point
> still stands that HSQL chugs on this query whereas MySQL seems to
> handle it fine.  If you have any additional insight into why this sort
> of situation might be happening I'd very much appreciate it.
>
> Thanks,
> Marc
>
> -----Original Message-----
> From: Marc Novakouski [mailto:novakom@...]
> Sent: Tuesday, October 27, 2009 1:40 PM
> To: HSQLdb user discussions
> Subject: Re: [Hsqldb-user] Slowdown with HSQLDB
>
> Hi Fred,
>
> Thanks for getting back to me so fast.  My responses are embedded below.
>
> Marc
>
> -----Original Message-----
> From: fredt [mailto:fredt@...]
> Sent: Tuesday, October 27, 2009 1:08 PM
> To: HSQLdb user discussions
> Subject: Re: [Hsqldb-user] Slowdown with HSQLDB
>
> Hi Marc,
>
> The script file (for a memory database) is pretty small.
> MARC: That's what I thought; I didn't think it would be an issue.
>
> Can you describe the data that was modified (amount and the type of
> statements) after point 1.
> MARC: Very basic actually.  I'm doing a single insert into one to
> three tables.  I did notice, going through the log, that there's an
> additional
> 2 lines for every update where a line is removed from a key table and
> re-inserted; however this would seem to be an error in the code; I'm
> trying to track that down now.
>
> Are you saying adding data is slow, or query speed after adding data
> is slow?
> MARC: I can definitively say that both updating data and query speed
> is slow.  There are a number of different actions that can result in a
> data query (no update) on the screen, and each operation, when
> performed individually, has the same slowly increasing lag time.  
> Additionally, the save action which writes the data to the db has the
> same lag time, so I would argue that it seems to be both.
>
> Once possible cause that you do not have indexes on your tables. You
> can execute EXPLAIN PLAIN FOR SELECT [rest of your select satement] to
> see if it is reporting index use or full scan.
> MARC: This might be hard within the application itself; as I noted, I
> am using Spring + iBatis to access the DB, and the queries are set
> generally to work both with HSQL and MySQL, so I'd rather not modify
> them.  I tried doing this through the HSQL DB manager while accessing
> the db directly, but I get an error of "Unexpected token PLAIN,
> requires PLAN in statement ...".  Would I need to do this within the
> application, and if so, what is the expected result?
>
> Another possible cause is that you are performing SELECT COUNT(*) over
> and over again.
> MARC: There are some SELECT COUNT statements, but none should be being
> performed.  I could monitor the application to verify this, but I
> doubt that this is the problem.  Does HSQL have a way to record all
> queries so you can verify on that end?
>
> Yet another possible casue is Java heap limitations or use of virtual
> memory for heap.
> MARC: I'll take a look at this.
>
> Fred
>
> ----- Original Message -----
> From: "Marc Novakouski" <novakom@...>
> To: <hsqldb-user@...>
> Sent: 27 October 2009 15:35
> Subject: [Hsqldb-user] Slowdown with HSQLDB
>
>
> Hi all,
>
> I'm maintaining an eclipse RCP application and am having some issues
> integrating HSQLDB.  I'm only peripherally familiar with HSQLDB so I
> was hoping someone here could help me out.  My issue is that my
> application seems to slow down significantly as I add data to my HSQL DB instance.
> I'm currently using:
>
> Java 1.5 (it's a legacy app)
> HSQLDB 1.8.1.1
> Spring 2.0.6 + IBATIS 2.3.0.677 framework
>
> I should also note that I'm using a standalone ("In Process") HSQL
> database on the local file system; the application is a stripped down
> training app that will only ever talk to its own local db so that
> seemed appropriate.
> Data does need to be persistent however thus it cannot be a
> memory-only db.
>
> Basically the application performs queries constantly upon the db,
> presents the information, and if the user indicates any change through
> the gui, makes those changes.  These can be updates, additions, or
> deletions.
>
> I've done a few things to try to figure out what the problem is.  The
> reason why I think it is HSQL that is causing the slowdown is the fact
> that the application can be switched between a MySQL datasource and a
> HSQL datasource via a config file, and when using MySQL there are no
> slowdown problems.
> Here are the things I've tried:
> 1. Just querying the data over & over, not adding/modifying/deleting:
> doing this resulted in no slowdown issues, so it seems to be related
> to actually modifying the db.
> 2. I modified the tables, which are normally memory tables, to be
> cached tables (right now I use normal CREATE TABLE statements, so I
> changed that to CREATE CACHED TABLE).  I did observe that a .data file
> was created so I'm pretty sure I made the change correctly; however
> the slowdown issue persisted.
> 3. I added connection pooling to the datasource definition within the
> application context file; due to #1 above I didn't think that mounting
> unclosed connections was the issue, but I tried it anyways.  The
> slowdown persists.
>
> Some things to note:
> 1. My .script file is ~2000 lines.  I didn't get the sense that this
> was a particularly large db so I didn't think I needed some large-size
> db customization, but if anyone can suggest something along those
> lines that would help, that'd be great.
> 2. As I make changes in the application, I can watch as my .log file
> slowly grows.  My understanding is that this is the normal & correct
> operation, but just in case it raises any red flags I'm mentioning it
> here.
>
> I'm about out of ideas; the only other thing I thought to try would be
> to upgrade to HSQL 1.9 to see if performance improves.  I also figure
> that modifying parameters associated to the spring/ibatis framework
> interface might be helpful, but I'm not really sure where to go with
> that.  Any ideas or suggestions that anyone could provide would be
> very much appreciated.
>
> Thanks,
> Marc
>
> ----------------------------------------------------------------------
> -------- Come build with us! The BlackBerry(R) Developer Conference in
> SF, CA is the only developer event you need to attend this year.
> Jumpstart your developing skills, take BlackBerry mobile applications
> to market and stay ahead of the curve. Join us from November 9 - 12,
> 2009. Register now!
> http://p.sf.net/sfu/devconference
> _______________________________________________
> Hsqldb-user mailing list
> Hsqldb-user@...
> https://lists.sourceforge.net/lists/listinfo/hsqldb-user
>
>
> ----------------------------------------------------------------------
> -------- Come build with us! The BlackBerry(R) Developer Conference in
> SF, CA is the only developer event you need to attend this year.
> Jumpstart your developing skills, take BlackBerry mobile applications
> to market and stay ahead of the curve. Join us from November 9 - 12,
> 2009. Register now!
> http://p.sf.net/sfu/devconference
> _______________________________________________
> Hsqldb-user mailing list
> Hsqldb-user@...
> https://lists.sourceforge.net/lists/listinfo/hsqldb-user
>
> ----------------------------------------------------------------------
> -------- Come build with us! The BlackBerry(R) Developer Conference in
> SF, CA is the only developer event you need to attend this year.
> Jumpstart your developing skills, take BlackBerry mobile applications
> to market and stay ahead of the curve. Join us from November 9 - 12,
> 2009. Register now!
> http://p.sf.net/sfu/devconference
> _______________________________________________
> Hsqldb-user mailing list
> Hsqldb-user@...
> https://lists.sourceforge.net/lists/listinfo/hsqldb-user
>
> ----------------------------------------------------------------------
> -------- Come build with us! The BlackBerry(R) Developer Conference in
> SF, CA is the only developer event you need to attend this year.
> Jumpstart your developing skills, take BlackBerry mobile applications
> to market and stay ahead of the curve. Join us from November 9 - 12,
> 2009. Register now!
> http://p.sf.net/sfu/devconference
> _______________________________________________
> Hsqldb-user mailing list
> Hsqldb-user@...
> https://lists.sourceforge.net/lists/listinfo/hsqldb-user

------------------------------------------------------------------------------
Come build with us! The BlackBerry(R) Developer Conference in SF, CA is the only developer event you need to attend this year. Jumpstart your developing skills, take BlackBerry mobile applications to market and stay ahead of the curve. Join us from November 9 - 12, 2009. Register now!
http://p.sf.net/sfu/devconference
_______________________________________________
Hsqldb-user mailing list
Hsqldb-user@...
https://lists.sourceforge.net/lists/listinfo/hsqldb-user

------------------------------------------------------------------------------
Come build with us! The BlackBerry(R) Developer Conference in SF, CA
is the only developer event you need to attend this year. Jumpstart your
developing skills, take BlackBerry mobile applications to market and stay
ahead of the curve. Join us from November 9 - 12, 2009. Register now!
http://p.sf.net/sfu/devconference
_______________________________________________
Hsqldb-user mailing list
Hsqldb-user@...
https://lists.sourceforge.net/lists/listinfo/hsqldb-user

[Fred Toussi-2]

We still have "org.hsqldb.jdbcDriver", but now it just extends
"org.hsqldb.jdbc.JDBCDriver". Just to be sure, I added this line to one
of the test cases and it worked.

Class.forName("org.hsqldb.jdbcDriver").newInstance();

Please send the files and I will hopefully optimise it. Send to ft (at)
cluedup.com in case the sourceforge account doesn't like zips.

Fred

On Tue, 27 Oct 2009 16:55 -0400, "Marc Novakouski" <novakom@...>
wrote:

> Hi Fred,
>
> Thanks again for all the info.  I've verified that this one particular
> select statement is the culprit.  I did try to upgrade to 1.9, but I had
> all sorts of issues trying to make it work; apparently I can't get the
> following statement to run:
>
> Class.forName(driver).newInstance();
>
> where driver is a string set to  "org.hsqldb.jdbcDriver" (I guess you
> don't have that class anymore?)
>
> In any case, I'm going to send you directly a stripped-down database & a
> query which doesn't respond quickly as per your request.  I talked to my
> boss and I think I have a workaround so I think it shouldn't matter, but
> if you can resolve this that would be great too.
>
> Thanks!
> Marc
>
>
> -----Original Message-----
> From: Fred Toussi [mailto:fredt@...]
> Sent: Tuesday, October 27, 2009 3:25 PM
> To: HSQLdb user discussions
> Subject: Re: [Hsqldb-user] Slowdown with HSQLDB
>
> You can try with version 1.9 which has better query optimisation.
>
> We are constantly improving in this area. It is therefore worth sending
> me a sample database together with the slow SELECT and we will speed it
> up.
>
> It seem this is the issue, and the rest of the possible causes do not
> apply to this case.
>
> Fred
>
> On Tue, 27 Oct 2009 15:15 -0400, "Marc Novakouski" <novakom@...>
> wrote:
> > Hi Fred,
> >
> > A few updates:
> >
> > 1. The extra add/delete I mentioned in the 2nd point is actually by
> > design and removing it does not improve performance, so that's not the
> > issue.
> >
> > 2. I did try bumping heap size to 1 GB and that seemed to have no effect.
> >
> > 3. I tried to track down one of the lines in the code where the
> > application lags.  I managed to pinpoint (at least one instance) to a
> > single query.  It's actually a pretty complex query, and using
> > Spring/iBatis there's actually 2 nested queries that gather additional
> > information for each of the returned result rows.  It's also true that
> > the query returns exactly the amount of data that has already been
> > added via the insert statements (i.e., the inserts add rows to table
> > x, and this complex query queries table x) so as that table grows the
> > output of this query would increase in a possibly exponential manner.
> > I'm currently looking at if what we're doing is actually Necessary or
> > just bad design (I didn't write the original code) however the point
> > still stands that HSQL chugs on this query whereas MySQL seems to
> > handle it fine.  If you have any additional insight into why this sort
> > of situation might be happening I'd very much appreciate it.
> >
> > Thanks,
> > Marc
> >
> > -----Original Message-----
> > From: Marc Novakouski [mailto:novakom@...]
> > Sent: Tuesday, October 27, 2009 1:40 PM
> > To: HSQLdb user discussions
> > Subject: Re: [Hsqldb-user] Slowdown with HSQLDB
> >
> > Hi Fred,
> >
> > Thanks for getting back to me so fast.  My responses are embedded below.
> >
> > Marc
> >
> > -----Original Message-----
> > From: fredt [mailto:fredt@...]
> > Sent: Tuesday, October 27, 2009 1:08 PM
> > To: HSQLdb user discussions
> > Subject: Re: [Hsqldb-user] Slowdown with HSQLDB
> >
> > Hi Marc,
> >
> > The script file (for a memory database) is pretty small.
> > MARC: That's what I thought; I didn't think it would be an issue.
> >
> > Can you describe the data that was modified (amount and the type of
> > statements) after point 1.
> > MARC: Very basic actually.  I'm doing a single insert into one to
> > three tables.  I did notice, going through the log, that there's an
> > additional
> > 2 lines for every update where a line is removed from a key table and
> > re-inserted; however this would seem to be an error in the code; I'm
> > trying to track that down now.
> >
> > Are you saying adding data is slow, or query speed after adding data
> > is slow?
> > MARC: I can definitively say that both updating data and query speed
> > is slow.  There are a number of different actions that can result in a
> > data query (no update) on the screen, and each operation, when
> > performed individually, has the same slowly increasing lag time.  
> > Additionally, the save action which writes the data to the db has the
> > same lag time, so I would argue that it seems to be both.
> >
> > Once possible cause that you do not have indexes on your tables. You
> > can execute EXPLAIN PLAIN FOR SELECT [rest of your select satement] to
> > see if it is reporting index use or full scan.
> > MARC: This might be hard within the application itself; as I noted, I
> > am using Spring + iBatis to access the DB, and the queries are set
> > generally to work both with HSQL and MySQL, so I'd rather not modify
> > them.  I tried doing this through the HSQL DB manager while accessing
> > the db directly, but I get an error of "Unexpected token PLAIN,
> > requires PLAN in statement ...".  Would I need to do this within the
> > application, and if so, what is the expected result?
> >
> > Another possible cause is that you are performing SELECT COUNT(*) over
> > and over again.
> > MARC: There are some SELECT COUNT statements, but none should be being
> > performed.  I could monitor the application to verify this, but I
> > doubt that this is the problem.  Does HSQL have a way to record all
> > queries so you can verify on that end?
> >
> > Yet another possible casue is Java heap limitations or use of virtual
> > memory for heap.
> > MARC: I'll take a look at this.
> >
> > Fred
> >
> > ----- Original Message -----
> > From: "Marc Novakouski" <novakom@...>
> > To: <hsqldb-user@...>
> > Sent: 27 October 2009 15:35
> > Subject: [Hsqldb-user] Slowdown with HSQLDB
> >
> >
> > Hi all,
> >
> > I'm maintaining an eclipse RCP application and am having some issues
> > integrating HSQLDB.  I'm only peripherally familiar with HSQLDB so I
> > was hoping someone here could help me out.  My issue is that my
> > application seems to slow down significantly as I add data to my HSQL DB instance.
> > I'm currently using:
> >
> > Java 1.5 (it's a legacy app)
> > HSQLDB 1.8.1.1
> > Spring 2.0.6 + IBATIS 2.3.0.677 framework
> >
> > I should also note that I'm using a standalone ("In Process") HSQL
> > database on the local file system; the application is a stripped down
> > training app that will only ever talk to its own local db so that
> > seemed appropriate.
> > Data does need to be persistent however thus it cannot be a
> > memory-only db.
> >
> > Basically the application performs queries constantly upon the db,
> > presents the information, and if the user indicates any change through
> > the gui, makes those changes.  These can be updates, additions, or
> > deletions.
> >
> > I've done a few things to try to figure out what the problem is.  The
> > reason why I think it is HSQL that is causing the slowdown is the fact
> > that the application can be switched between a MySQL datasource and a
> > HSQL datasource via a config file, and when using MySQL there are no
> > slowdown problems.
> > Here are the things I've tried:
> > 1. Just querying the data over & over, not adding/modifying/deleting:
> > doing this resulted in no slowdown issues, so it seems to be related
> > to actually modifying the db.
> > 2. I modified the tables, which are normally memory tables, to be
> > cached tables (right now I use normal CREATE TABLE statements, so I
> > changed that to CREATE CACHED TABLE).  I did observe that a .data file
> > was created so I'm pretty sure I made the change correctly; however
> > the slowdown issue persisted.
> > 3. I added connection pooling to the datasource definition within the
> > application context file; due to #1 above I didn't think that mounting
> > unclosed connections was the issue, but I tried it anyways.  The
> > slowdown persists.
> >
> > Some things to note:
> > 1. My .script file is ~2000 lines.  I didn't get the sense that this
> > was a particularly large db so I didn't think I needed some large-size
> > db customization, but if anyone can suggest something along those
> > lines that would help, that'd be great.
> > 2. As I make changes in the application, I can watch as my .log file
> > slowly grows.  My understanding is that this is the normal & correct
> > operation, but just in case it raises any red flags I'm mentioning it
> > here.
> >
> > I'm about out of ideas; the only other thing I thought to try would be
> > to upgrade to HSQL 1.9 to see if performance improves.  I also figure
> > that modifying parameters associated to the spring/ibatis framework
> > interface might be helpful, but I'm not really sure where to go with
> > that.  Any ideas or suggestions that anyone could provide would be
> > very much appreciated.
> >
> > Thanks,
> > Marc
> >
> > ----------------------------------------------------------------------
> > -------- Come build with us! The BlackBerry(R) Developer Conference in
> > SF, CA is the only developer event you need to attend this year.
> > Jumpstart your developing skills, take BlackBerry mobile applications
> > to market and stay ahead of the curve. Join us from November 9 - 12,
> > 2009. Register now!
> > http://p.sf.net/sfu/devconference
> > _______________________________________________
> > Hsqldb-user mailing list
> > Hsqldb-user@...
> > https://lists.sourceforge.net/lists/listinfo/hsqldb-user
> >
> >
> > ----------------------------------------------------------------------
> > -------- Come build with us! The BlackBerry(R) Developer Conference in
> > SF, CA is the only developer event you need to attend this year.
> > Jumpstart your developing skills, take BlackBerry mobile applications
> > to market and stay ahead of the curve. Join us from November 9 - 12,
> > 2009. Register now!
> > http://p.sf.net/sfu/devconference
> > _______________________________________________
> > Hsqldb-user mailing list
> > Hsqldb-user@...
> > https://lists.sourceforge.net/lists/listinfo/hsqldb-user
> >
> > ----------------------------------------------------------------------
> > -------- Come build with us! The BlackBerry(R) Developer Conference in
> > SF, CA is the only developer event you need to attend this year.
> > Jumpstart your developing skills, take BlackBerry mobile applications
> > to market and stay ahead of the curve. Join us from November 9 - 12,
> > 2009. Register now!
> > http://p.sf.net/sfu/devconference
> > _______________________________________________
> > Hsqldb-user mailing list
> > Hsqldb-user@...
> > https://lists.sourceforge.net/lists/listinfo/hsqldb-user
> >
> > ----------------------------------------------------------------------
> > -------- Come build with us! The BlackBerry(R) Developer Conference in
> > SF, CA is the only developer event you need to attend this year.
> > Jumpstart your developing skills, take BlackBerry mobile applications
> > to market and stay ahead of the curve. Join us from November 9 - 12,
> > 2009. Register now!
> > http://p.sf.net/sfu/devconference
> > _______________________________________________
> > Hsqldb-user mailing list
> > Hsqldb-user@...
> > https://lists.sourceforge.net/lists/listinfo/hsqldb-user
>
> ------------------------------------------------------------------------------
> Come build with us! The BlackBerry(R) Developer Conference in SF, CA is
> the only developer event you need to attend this year. Jumpstart your
> developing skills, take BlackBerry mobile applications to market and stay
> ahead of the curve. Join us from November 9 - 12, 2009. Register now!
> http://p.sf.net/sfu/devconference
> _______________________________________________
> Hsqldb-user mailing list
> Hsqldb-user@...
> https://lists.sourceforge.net/lists/listinfo/hsqldb-user
>
> ------------------------------------------------------------------------------
> Come build with us! The BlackBerry(R) Developer Conference in SF, CA
> is the only developer event you need to attend this year. Jumpstart your
> developing skills, take BlackBerry mobile applications to market and stay
> ahead of the curve. Join us from November 9 - 12, 2009. Register now!
> http://p.sf.net/sfu/devconference
> _______________________________________________
> Hsqldb-user mailing list
> Hsqldb-user@...
> https://lists.sourceforge.net/lists/listinfo/hsqldb-user

------------------------------------------------------------------------------
Come build with us! The BlackBerry(R) Developer Conference in SF, CA
is the only developer event you need to attend this year. Jumpstart your
developing skills, take BlackBerry mobile applications to market and stay
ahead of the curve. Join us from November 9 - 12, 2009. Register now!
http://p.sf.net/sfu/devconference
_______________________________________________
Hsqldb-user mailing list
Hsqldb-user@...
https://lists.sourceforge.net/lists/listinfo/hsqldb-user