Base record access unacceptably slow

users
#1

This has been a problem from the beginning, and it's becoming intolerable.

The principal table in my database has about 550 records, with about 20 fields each. A number are binary in type, and two are memo, with the rest being text or foreign keys. Almost all the memo fields are empty.

Just to move the record pointer from the first to the last record takes almost exactly 20 seconds. To do a search of the beginning of the main text field for a 6 character string that isn't there (i.e., the engine searches to the very end of the table) takes 2 minutes and 5 seconds. You can imagine how long it takes to locate and update 10 records. This is the worst performance I've ever seen in a database, period.

Indexes exist on the primary key and on the primary text field. It seems to make no difference. As the engine reports its progress during the search, it appears to be doing a serial search through every record.

At this rate, I'll be unable to make meaningful use of this database in about a week.

Is there anything at all I can do about this appalling situation?

I hope I just missing something that can be pointed out to me. But information on indexes is essentially nonexistent. There's none in the LO "book", or the Help file, or the underlying HYPERSQL engine's documentation. Amazing, but true.

I do hope someone has some ideas for me.

Thanks in advance.

Tom

#2

Hi Tom,

Just to move the record pointer from the first to the last record takes
almost exactly 20 seconds. To do a search of the beginning of the main
text field for a 6 character string that isn't there (i.e., the engine
searches to the very end of the table) takes 2 minutes and 5 seconds.
You can imagine how long it takes to locate and update 10 records. This
is the worst performance I've ever seen in a database, period.

Unfortunately, you are not the first, and no doubt the last either, to
report performance issues using the Base with integrated hsqldb. The
problem is not hsqldb, which actually on its own performs quite well,
but the manner in which Base loads everything into memory to be able to
work.

You could try issuing a SHUTDOWN COMPACT command from the Tools > SQL
dialog, then saving and closing your ODB file before re-opening it again.

Alex

#3

I've used base ever since it was first offered. About 6 months ago a Java update (I'm running Ubuntu 11.04) slowed it down to a pathetic speed. The latest Java update didn't really help much. I was hoping it would be fixed that update, or that the LibreOffice folks would find a solution

#4

That command appears to have cut 5 seconds off the record pointer move test, and also off the full db search test I ran previously.

Tom

#5

Don,

So, for 6 months, OO (I presume) and now LO (is it that old?) have been promoting a db which doesn't work. I have over 10 full time days invested in this, and I'm watching it fall apart in front of me, apparently irretrievably. I understand LO has NO programmer working on BASE, and with the lack of protest see on the list no one's using it anyway.

I'm baffled by all this. This thing is actually far more stable and reliable than I ever experienced Access to be when I used it daily for about 5 years. The interface designer works well. There's just not much to be distressed by and a lot to like, but...it looks like a dead dog. This is sad, and perplexing, and ultimately irrational.

For me personally, it's also desperate - or rather *I* am. I'm doing this thing to support my work with a non-profit educational group associated with the Wikimedia Foundation (the folks who manage Wikipedia). I only have so much time, and I've invested a lot in this project. Now I essentially have little or nothing to show for it, right when we're working hard against an utterly inflexible deadline.

I see no reason to hope for a fix with Base, and when my record count doubles again, later this week, it'll take almost 5 minutes to locate a record that's at the other end of the db, I need an alternative.

Does anyone have any suggestions?

More specifically,

1. Are there any alternative graphic interface tools that even approximate Base's functionality (other than Access)? The last time I looked - a number of weeks ago, I didn't come up with any equivalents.

2. How feasible is it simply to use SQL? I never done this, so I have no idea.

Since I'm reasonable comfortable with Ruby, I'm trying to think of a way to port this thing to a db engine that has a ruby driver (which HYPERSQL doesn't - tells us something, doesn't it?), and do it all from a command line. Painful thought, but better than grinding to a complete halt.

I'm eager to read people's thoughts on all this.

And thanks, Alex and Don, for your response.

Tom

#6

The Base documentation:
http://openoffice.org/projects/documentation/downloads/directory/Base/Mid%20level%20Base%20tutorial

My chrystal ball tells me that you run LibreOffice with a recent Java version under Linux, therefore this is the solution to your problem:
http://www.oooforum.org/forum/viewtopic.phtml?t=125253

#7

Hello,
I have been using OpenOffice and now LibreOffice with BASE for a long
time. My MySQL database "serves" a windband and all the issues related
to such an animal (eg. repertoire, uniforms, members, contacts, concert
programmes, etc., etc.).
Of course my tables are small compared to what I used to see when working
with DB2 at large IBM-customers. "Big" tables might have some 30.000
tuples in them, so just looking for a particular item using a filter
such as "like name*" never takes long.
On the other hand, I have been complaining about problems using the
report writer for serial letters. Conditions based on variables in a
table are not handled correctly in the Linux 3.3.3 version (they seem
th work under Windows!!).
I REALLY depend on BASE not falling behind or on its face altogether.
So I also think that some emphasis should be place on it. Unfortunately
I neither have the knowledge nor the time to devote myself to such an
effort...
Regards from Austria
H. Stoellinger

#8

Hi :slight_smile:
We need to attract some devs to this project. Preferably paid devs because
there is a bit of a quagmire trying to work out which patches have which
licences and so which cannot be incorporated into the LGPL and which can. It
needs people that work like devs but also people that like to research like
documenters. Could we get Wikipedia, Google, RedHat, Cannonical and others to
stump up some cash for this? Could TDF itself afford to pay for it? I think we
need a couple of full-time paid researchers and a paid dev to start things
moving. I think those researchers could move into coding or documentation after
even perhaps just 3 - 6 months with any luck. How could we get this going
forwards before the whole Suite falls over due to the 1 app's failures?

Regards from
Tom :slight_smile:

#9

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

The principal table in my database has about 550 records, with about 20

fields each. A number are binary in type, and two are memo, with the
rest being text or foreign keys. Almost all the memo fields are empty.

Just to move the record pointer from the first to the last record takes

almost exactly 20 seconds. To do a search of the beginning of the main
text field for a 6 character string that isn't there (i.e., the engine
searches to the very end of the table) takes 2 minutes and 5 seconds.
You can imagine how long it takes to locate and update 10 records. This
is the worst performance I've ever seen in a database, period.

It would help to know what OS you're on and whether the file is a
self-contained ODB file or if it's accessing an externally-served database.

I run a fairly large database (much larger than the one you're talking
about) on Windows XP without any problems, and have for years. However,
I do not have it as a self-contained ODB file; I use LO/OOo as the
front-end and serve the database using hypersql. I found that doing it
this way dramatically speeded (sped?) up the process. You might try
converting over to that model.

I have heard that H2 DB is actually a bit better for this purpose and
that it's not tremendously difficult to convert over to H2 from
hypersql, but I haven't had the guts to try it yet.

- --
Steven Shelton

#10

Hi Tom,

We need to attract some devs to this project. Preferably paid devs because
there is a bit of a quagmire trying to work out which patches have which
licences and so which cannot be incorporated into the LGPL and which can.

  Ho hum; the legal / ownership angle is not so difficult to sort out;
usually finding & fixing the bugs is more problematic :wink:

  Your suggestion to get lots of companies to fund more developers is a
great one - but can be organisationally problematic. Ultimately I
suggest the most reliable way is to find and/or encourage new developers
to do the work. There is a great spot for someone to love & 'own' base
in the project, it's a responsible role, and we'd really appreciate
someone to do it.

  I think those researchers could move into coding or documentation after
even perhaps just 3 - 6 months with any luck. How could we get this going
forwards before the whole Suite falls over due to the 1 app's failures?

  This is like RMS' amusing 'myth of the starving genius' :slight_smile: If there is
a serious bug that annoys enough people: particularly people that are
able to understand and build databases (which are near being programmers
anyway) - then *surely* if it matters enough, one or other of them will
start to dig into the code to fix it.

  There is no magic bullet here, or other white knights coming to fix
bugs in LibreOffice I'm afraid. If we want it done, we have to do it
ourselves. If you know what a database is, and how to use it, then you
are probable quite able to invest some time in building the latest code
and having a poke at it.

  Sorry,

    Michael.

#11

Your joking, right?

Users aren't programmers, and usually aren't even documenters. Whatever language Base is written in (C, I presume, and probably some C++?) I sure don't know it and am not going to learn it in the 20 minutes of spare time I have each day (on good days). And even if I did, you surely don't want a non-professional muddling around THIS code base, surely.

If we've come to this it's time to start looking for the lifeboats, which is basically what I'm doing today.

Tom

#12

[,]

It would help to know what OS you're on and whether the file is a self-contained ODB file or if it's accessing an externally-served database.

OS = Kubuntu Linux 11.04
The db is a self-contained ODB file. I just fire up LO Base and use what it give me.

I run a fairly large database (much larger than the one you're talking about) on Windows XP without any problems, and have for years. However, I do not have it as a self-contained ODB file; I use LO/OOo as the front-end

OK. this I understand, but...

and serve the database using hypersql.

This is do not understand. I thought HYPERSQL was the default db engine. You say you're using it to server ANOTHER db? why not access that directly through Base? Am I misunderstanding something (entirely possible)?

I found that doing it this way dramatically speeded (sped?) up the process.

You don't way what your external db is, and I'm curious about this.

You might try converting over to that model. I have heard that H2 DB is actually a bit better for this purpose and that it's not tremendously difficult to convert over to H2 from hypersql

This is an interesting option, and I'm thinking that later today I'll install H2 and set up a test to see is the problem I'm experiencing vanishes. If not, then it's MYSQL, I guess.

Thanks for your thoughts.

Tom

#13

Thanks, Andreas.

To summarize what I learned from that second link: I need to install sun-java6-jre_6.22-0ubuntu1~10.04 into a directory of my choosing, then direct LO to use this special older version (which, it is reported, does not cause the awful slowdown we're experiencing).

Look like a short-term solution I can live with. Give me hope that I get back to work, later today

The long-term solution appears entirely opaque, however. Is the problem being reported to Oracle (who I presume is behind the latest sun-java updates). Is this an Oracle plot to blow OO and LO opensource dbs out of the water?

Is there any hope we can cut this dependancy upon a java version that is now associated with Oracle?

Sigh.

Tom

#14

An obvious alternative, as well: move data tables to a non-java db, and use LO as front end for that - sqlite might be a reasonable first choice.

t.

#15

Tom,

The GUI is one element but how do you want too access the data and use the output? If you need integration with LO then your only option at the moment is to trial using base with another backend; something like MySQL, SQlite, Postgre, etc?

If you are happy using any data storage system, alternatives are around but it depends a lot on what you want to do.

To investigate options further you need to clarify exactly what you are doing, what you are trying to achieve and how permanent a solution you are after.

#16

Am 28.07.2011 00:00, Tom Cloyd wrote:

This is do not understand. I thought HYPERSQL was the default db engine.
You say you're using it to server ANOTHER db? why not access that
directly through Base? Am I misunderstanding something (entirely possible)?

I found that doing it this way dramatically speeded (sped?) up the
process.

You don't way what your external db is, and I'm curious about this.

You might try converting over to that model. I have heard that H2 DB
is actually a bit better for this purpose and that it's not
tremendously difficult to convert over to H2 from hypersql

This is an interesting option, and I'm thinking that later today I'll
install H2 and set up a test to see is the problem I'm experiencing
vanishes. If not, then it's MYSQL, I guess.

Again: Under Linux any Java database is crawling unless you use one particular Java version which is 1.6.22.
I use Base documents with embedded HSQLDB 1.8, HSQLDB 2 server and H2 as well. The latter runs well with half a million records, a dozend relations and no Java other than 1.6.22
http://www.oooforum.org/forum/viewtopic.phtml?t=125253#436698

#17

Am 28.07.2011 00:47, Tom Cloyd wrote:

An obvious alternative, as well: move data tables to a non-java db, and
use LO as front end for that - sqlite might be a reasonable first choice.

t.

Yes, MySQL, SQLite, PostrgreSQL can circumvent your Linux/Java problem while providing first class database engines. I run a HSQLDB server because the databases transformed seamlessly from embedded HSQLDB drafts and the performance problem does not occur on Windows systems where the databases are in productive use.
H2 was an experiment when the performance issue was not apparent. H2 is a great database engine, very easy to set up for exclusive use on the local machine (drop files and use URL).
I expect Base to fall apart, and it will be a big relief as long as the mere database connectivity and form controls persist. If not, I can not use this office suite anymore since all our spreadsheet models and most of the Writer templates are connected to some database one way or the other.

Thanks, Andreas.

You're welcome,

#18

Hi Tom,
Just my - maybe naive - 2 pennies worth of comments:
As I mentioned before, I have been using OO/LO for years together with
BASE with MySQL (which - also "sigh") now "belongs" to Oracle as well.
As a connector to the DB I have tried out the Java version (always had
trouble with it one way or the other!) as well as ODBC/UNIXODBC (which
is what I am now using under Linux and which works sort of!). With
MySQL there is also the native connector which I would actually prefer
to use - but, yes you guess right - does NOT work on either LO 3.3.3 or
3.4.1 under Linux. A bit confusing, isn't it!
I am not giving up hope yet for LO 3.4.2 - especially since it targets
business...
Isn't there a OpenSource fork of MySQL called MariaDB?!?
Regards
H

#19

Thanks Andreas. I think I'm clear about this now. Linux + java db is currently a problem, so regression to the 1.6.22 is the way to, unless one utterly leave Java dbs behind.

As an aside, have you thoughts to share about HSQLDB vs H2? Any good reason to migrate to H2 (a question entirely separate from the db speed question). I'd be interested to hear your thoughts if you have time to share them.

#20

Well better than nothing I suppose, but I do sympathise. Did the JDK/JRE
change suggested by someone else help any further ?

Alex