Window problems in Base

[Lorinda]

I installed text patch 10 today, and now I'm having trouble with certain windows in Base.

You can download the database I'm using at:

http://homepage.mac.com/hoovmoss/.Public/NeoBirdDatabase.odb

Here's how to reproduce it:

Open the database file
Click on Queries in the left column'
Double Click on User 1 Sightings List
Wait for a bit while the Query builds
Click on the Red close "button" at the upper left hand corner of the Query window
All the menus except "NeoOffice" disappear, but the window doesn't close.
It does close eventually...the one time I timed it, it took 50 seconds from clicking the red circle to the actual close.

Now double click on User1LifeList
You should get an error window, specifying an error involving ORDER BY 9I thought this query was working, but that's another story)
When I do this, the first time the window appears and disappears quickly--too fast to read. But double click on User1LifeList again, and it should appear and stick until you click OK.
If you click OK, the error warning will disappear, and a blank, unnamed Query window will open
Click on the red close "button" in the upper left, and all the menus except NeoOffice will disappear, and but the window won't close until 45-50 seconds have passed.

Hmmm..it occurred to me mid-writing that the delay might be related to the fact that I was access the file directly from my iDisk. Unfortunately I'm having even more trouble with my local copy of the database; it hands when I try to run the query.

I'm out of time to fiddle with it tonight, but I'll leave this note in case someone else is inclined to download the file and check the steps. I'll try to get back to it when I can.

Lorinda

[pluby]

Lorinda,

The URL does not work. Is it password protected or not public?

Patrick

[pluby]

FYI. I tried the steps that you listed using your database in bug 2705 but the windows closed very quickly and the menus were refreshed immediately after the window closed.

Patrick

[Lorinda]

I'm sorry, Patrick. I didn't look carefully at my notes on urls for items in my iDisk. I meant to test the url before posting, but I got distracted by the hanging issue that occurred when I paused to try to reproduce this on a local version of the database. I have corrected the url in my first post.

For the moment, though, it looks like it's an issue on my end. I'll try to find some time in the next few days to fiddle some more and see if I can reproduce the issue with a local copy. It may be several days, as the rest of the week and the weekend are quite busy.. In the mean time, I'm happy to consider the matter closed.

Lorinda

[pluby]

If you want, open a bug in Bugzilla and attach it there. I suspect that the menu resetting is causing some hanging so I definitely want to look at your file tomorrow.

Edit: I forgot to ask the following:

- Are you using Intel or PowerPC?
- Are you using Mac OS X 10.4.x or 10.5.x
- Do you have any floating palette windows open? If so, which ones? (I had none open when I used the file in bug 2705)

Patrick

[Lorinda]

Thanks for the questions. More details I forgot to post. I do know better.

10.4.11 PPC

No floating pallette windows that I knowof were open, although I did run into a loop problem during one of the hangs: the error message window kept popping up (turns out it was moving behind, that's why it disappeared.) I would close it, then try to close the empty window, then the error message would appear, etc.

I'll see if I can't find time to post a repeatable list of steps in Bugzilla. I think part of my problem with my local copy is that I had to force quit and the .lck file is still there. I'm going to experiment with some workarounds for that.

Lorinda

[Lorinda]

Some of the hangs appear to actually just be very long "computing" times. One seems to be a real hang, though. I've posted it as bug 3052. As I said there, I'll try to leave the query running over night to see if it just takes that long. But for now I need Neo for other purposes.

Lorinda

[pluby]

I updated bug 3052 but to summarize here, the hanging occurs in OOo 2.2.1 and 2.3.1 and crashes in the latest OOo Aqua build.

I have posted some suggestions for optimizing your queries as I think that the number of joins between non-keyed fields is rather large. In general, to get efficient queries you need to join on key fields. Such optimization may also help you avoid the OpenOffice.org bug as well.

For example, in your database's Sightings table, you have a Location text field and then you join that field the Location text field in your Field Trips table. Since the Location field in the Field Trips table is not the primary key, joining these two fields causes a huge amount of processing. One way to restructure this is to make the Location field in the Sightings table store the ID in the Field Trips table. Then, join on that field to display the text fields in the Field Trip table for their matching Sightings record.

Patrick

[Lorinda]

Thanks Patrick. I was beginning to suspect there were some problems with the structure of the query, although I thought it was working properly earlier.

When I have a chance I'll look over your suggestions and overhaul the database. If I have any further questions about how to do that, I'll ask at the OOo forums.

Lorinda

[Lorinda]

In the process of starting to figure out how to overhaul my database file, I got some excellent help from Drew Jensen over in the OpenOffice Community forums.

He doens't think changing the relationships will help, as the primary key vs. other field issue isn't related to optimization, at least not with HSQLdb. But he did confirm that the hanging issue was caused by an unresolved issue in OOo involving multiple OR statements, as Patrick has said.

His initial suggestion was to create a view and use the UNION command. While I can get the views he made to work in Neo, I can't recreate the views myself; I get an error message that the Command is not a Select statement. I'm working under the assumption that this is because Neo uses an older version of OOo's code than what Drew is using.

I'll continue to work on it with Drew and hopefully I'll find a workable resolution.

Lorinda

[pluby]

[Lorinda]

As Drew rewrote the queries for me, there wasn't a UNIQUE command. And it works now; I had missed his instruction to turn on the "SQL Direct" button.

Now I've just got changes to make to the wiki tutorial!

Lorinda