[SOLVED] Slow Database tried fixes but, no help 3.3 RC 10

Mobidoy · Last edited by Mobidoy on Thu Jan 27, 2011 9:36 am; edited 2 times in total

I have a database with a table that has over 6000 entries. when I do a search, the first time, it takes about 5 minutes for it to get to the end. Once that is done, the database is lightning fast.

I have tried checkpoint defrag, the database is embedded HSQL but, when I do this, on next reboot, it will crash so I have to delete the .openoffice directory in my home.

What should I do/try out ?

Sliderule · Posted: Sat Jan 22, 2011 4:02 pm Post subject:

Permission to ask . . . on the field(s) that you are doing your search . . . is there an INDEX on the field(s)?

If not, you might want to create an index on the field(s) and see if that helps.

Also, what might be helpful for me is if you could post here the SQL you are using in the search . . . if you created the Query with the GUI ( Graphic User Interface ) . . . you can press on the Switch Design View On/Off icon on the toolbar. Smile

I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your first post Title ( edit button ) if your issue has been fixed / resolved.

Mobidoy · Posted: Sat Jan 22, 2011 5:14 pm Post subject:

How can I tell if there is an Index ?

and here is the query:

Sliderule · Posted: Sat Jan 22, 2011 5:36 pm Post subject:

Mobidoy:

You wrote / asked:

Mobidoy · Posted: Sat Jan 22, 2011 6:14 pm Post subject:

I do indeed have an index in each db for the two fields

1. "piecesfabricant"."Numéro_de_pièce"
2. "pieces"."Numéro_de_pièce"

but, I have also noticed that there is a second index in

"piecesfabricant"."Numéro_de_pièce"

first which is unique and second is not... Is the second needed and could it be part of the slowdown ?

Sliderule · Posted: Sat Jan 22, 2011 6:37 pm Post subject:

Mobidoy:

You said / asked:

Mobidoy · Posted: Sat Jan 22, 2011 7:08 pm Post subject:

First thing I tried was to delete the extra index, which is not unique but, I get an error message:

Sliderule · Posted: Sat Jan 22, 2011 7:15 pm Post subject:

OK, you must have created a RELATIONSHIP between the two tables.

You can confirm this from the Menu:

Tools -> Relationships...

and, it ( the foreign key ) refers to the FOREIGN KEY INDEX ( relation between the two tables ), you do NOT want to change / drop it. Smile

At this point, please try what the I identified above . . .

Mobidoy · Posted: Sat Jan 22, 2011 7:31 pm Post subject:

I do have a relationship so, I wont touch it.

I have done the

Sliderule · Posted: Sat Jan 22, 2011 7:40 pm Post subject:

OK, one more question . . . if you go to EDIT your table ( right click on the Table, select Edit from the drop down menu ) . . . I want to know how the TEXT fields are defined.

That is, are they defined as:

CHAR
VARCHAR
VARCHAR_IGNORECASE

AND what is the LENGTH of the two fields:

piecesfabricant"."Numéro_de_pièce"
"pieces"."Numéro_de_pièce"

I am asking because I wonder if you defined these as CHAR and they and the indices are taking up alot more space than needed, compared to if they had been defined with VARCHAR_IGNORECASE, or VARCHAR ( I recommend the former rather than the later ) if it might help to change the field definition.

Sliderule

Thanks to add [Solved] in your first post Title ( edit button ) if your issue has been fixed / resolved.

Mobidoy · Posted: Sat Jan 22, 2011 7:56 pm Post subject:

In fact, they are both integer length 10 ... Should I change them varchar_ignorecase ?

It does not need to be Integer even thougth it is "part Number" (in french).

Sliderule · Posted: Sat Jan 22, 2011 8:04 pm Post subject:

I see, they are INTEGER 10.

No, in that case, I would keep them that way. Sorry, I was thinking they might look like ( for example ): 103-30407

or something ( characters not ONLY numbers ).

The only other suggestion I can thnk of, is to upgrade to HSQL 2.0.1 . . . a much more efficent database engine that makes better use of indices in Queries.

Posts by dacm on this web site, AND, at:

http://user.services.openoffice.org/en/forum/

describe the technique to do this.

Sliderule

Thanks to add [Solved] in your first post Title ( edit button ) if your issue has been fixed / resolved.

Mobidoy · Posted: Sat Jan 22, 2011 9:43 pm Post subject:

I am still trying to figure out how to do the upgrade under Ubuntu 10.10 64bits, will let you know as soon as I am done.

Mobidoy · Posted: Sat Jan 22, 2011 10:10 pm Post subject:

I have solved it but, from another procedure:

http://ubuntuforums.org/showpost.php?p=10251666&postcount=10

but I would like to follow the 2.0.1 upgrade with multi mode as this DB, once over, will be share by about 10 users.

http://www.oooforum.org/forum/viewtopic.phtml?p=396523#396523

I just cant figure out how to do this under Ubuntu 10.10 64 bits. Can you help ?

Sliderule · Posted: Sat Jan 22, 2011 10:25 pm Post subject:

Ok, so, it was an Operating System issue, NOT an OpenOffice Base issue. Fine.

You said / asked"