How to Shorten Form Load Time?

[joseph9876]

I have a database (in Base, of course) with tables that contain 40,000+ records (each). I programmed a simple form that uses those tables. It seems to be working OK but...

Whenever I open this form, it takes VERY long time for it to be ready for the first entry/display (the Windows hourglass is spinning...). Obviously this is not very convenient or friendly to the user. I would like to cut down that time.

After posting the description of my problem in http://www.oooforum.org/forum/viewtopic ... highlight=
I found out that this is related to tying a List Box to one of those tables (containing 40,000+ records).

It is now obvious that upon opening the form, Base tries to pre-fill that ListBox control with ALL the records in the tables before displaying the first record... Nice, but unacceptable in my case.

How do I tell Base to start with smaller portion of the database in that ListBox (and load the rest upon demand only)?

I hope that this is not a bug in Base that can only be fixed by the OOo programmers. I would like to find a workaround like tweaking the SQL for that ListBox such that it will read only a portion of the table upon load and will "scroll" down upon clicking the listbox.

I noticed that Base itself is doing something similar (when not using a ListBox) upon load: It reads only about 10 records, then when you try to go past that it reads another 10 records.

Any suggestion as to how to go about this?

Thanks!
Joe

[DrewJensen]

Hi Joe,

Good question..

You can limit the number of records returned by a query easily enough with the modifier LIMIT <start> <count>, and this would be a way to speed up the load time.

Then the question becomes how to move through the list via 'chunkcs' or a 'window'. Not sure I have an answer on that...ok I am sure...I don't. Question - is the list box bound to a field or just there for use on the form, maybe as a filter setting?

For example - you could use a limited result set for the initial form load and only switch to the full result set when and if the user actually uses the control. But thinking about that it really only works well if the list box is NOT bound to a column. If it bound then you can't know in advance which group of records you need to have in the controls items property on load...hmmm...like I said 'Good question'...darn it this is gonna nag me for a while...I'll be back..

[joseph9876]

Drew, thank you very much for your reply.

I tried the LIMIT modified in the SQL statement that is bound to the ListBox but for some reason it's giving me trouble. I checked the HSQLDB reference and tried all combinations to make that I am not making a silly syntax error - to no avail... I think that in the context of ListBox perhaps LIMIT cannot be used?

As for your question, I am not sure what you mean by "bound to a column". I can try to describe to you what I currently have:

2 tables, TABLEFK and TABLEPRI. TABLEPRI is the one containing the 40,000+ records. each record has a primary key ID and could be referenced by a foreign key in TABLEFK, which currently has about 100 records only (the intent of the form is to actually populate TABLEFK with many many more records).

TABLEFK is actually the one accessed by the MainForm. There is no subform. The ListBox, although displaying content from the TABLEPRI (i.e. not from the MainForm's table) is on the MainFrom, not SubForm. It accomplishes this via the following arrangement in the control's Data tab:

Code: Select all

Data field.......             tablepri_foreign_key
Type of list contents......   Sql
List content......            SELECT "uniqfield", "ID" FROM "TABLEPRI"
Bound field.......            1

tablepri_foreign_key is the foreign key in TABLEFK (MainForm's table). The idea is that when you select an item in the ListBox, tablepri_foreign_key is automatically updated in TABLEFK.

Any idea how to continue from here?
What is "bound to a column"?

Thanks,
Joe

[DrewJensen]

Sorry

- sometimes I am much less precise with language then I could be

• Bound to a column is the same as "Data field.." and "Bound field" in the controls property you show above. I tend to use the word "column" while I should use "field" to aligned with the OpenOffice.org UI terminology.

- and at times I tend to leave out kind of important pieces of information ( no excuses for this one )

• For the modifier LIMIT and TOP ( I think this might really be the one you want ) the Base query analyzer does not recognize them. ( with one exception ..but let's leave that off the table for now ).
  
  This means that you need to turn the analyzer off to run this type of SQL statement and to do that with a list box you pick "SQL [Native]" in the property "Type of list contents".
  
  However - ( there is always a however isn't there ) - with "SQL [Native]" as the content type there comes a new issue. Column aliases in the SQL statement. There are some versions of OO.o that will not return any data unless they are explicitly supplied so a good rule of thumb is that if you are running "Native" SQL command you should alias all columns, unless you are returning "*".

Ok - so to be safe simply do this.
Select the type of content "SQL [Native]"
Change the SQL statement to:

Code: Select all

SELECT TOP 100 "uniqfield" AS "uniqfield", "ID" AS "ID" FROM "TABLEPRI"

You should be good to go.

Edit: TOP does just what it sounds like, take the first "top" 100 records only and seems to run a bit faster under HSQLdb then LIMIT 0 100

[joseph9876]

Drew, you are amazing. Once again, thank you very much for your help.

How do you know that the Base query analyzer does not recognize those modifiers? or, more importantly, how am I supposed to know that? Is it documented somewhere and I missed it? Or this is one of those things that must be learned through hard earned experience?

Your suggested fix (using "Native Sql") works very well.

Now, as you indicated in your first reply, I have to find a way to tell the ListBox to fetch the next 100 records upong hitting the last record in the list...

Any idea how to do that?

Perhaps using events?

Thanks,
Joe