[Solved] How do I speed up a very slow listbox?

[JDHeinzmann]

I have created a list box in a Wine tracking form from which I can pick one of about 12,000 wineries. It then successfully puts the associated WineryID into the Wine record. But it takes about 35 seconds to populate the listbox's list. And when I move to the next wine record, it takes another 35 seconds to display that next record. This makes navigating through my database intolerably slow.

I have read about the virtual listbox for other DBMSs: http://wiki.tcl.tk/10035. Is there a way to create that kind of functionality in a Base form? Or any other way to speed things up? My Winery column is of type tinytext and I have indexed the first 30 characters of it. I am connecting to MySQL via ODBC.

[JDHeinzmann]

I just discovered that if I specify the "Type of list contents" to be SQL [Native], the listbox populates about 7 times faster! Why would that be?

Even though this is a nice speed up (no longer glacially slow), navigation is still slow. Anybody have any other ideas?

[JDHeinzmann]

A ha! I solved my own problem! I had created a Winery subform on the Wine mainform to place the Winery listbox on. But a subform is not needed to link the Winery table to the Wine mainform. The listbox can be placed directly on the mainform. Then the listbox and its embedded SQL statement takes care of the linkage between the Wine and Winery tables. Now, it still takes about 8 seconds to open the form but it only takes about a second to navigate from one record to the next. (I think it would go even faster if it weren't for another two subforms on the Wine mainform, each with a Table Control for displaying the grapes and bottles associated with each wine.) This is sweet! It's simple and it's elegant. This listbox is turning out to be quite the useful little control. For those of you interested in the details:

The name of the mainform is frmWine. It is associated with the Wine table which has these columns:

• WineID (int) - PK
  WineryID (int) - FK
  etc

The Winery table has these columns:

• WineryID (int) - PK
  Winery (tinytext) - INDEX of length 30 (I bet this does not need to be indexed)
  etc

frmWine has a Listbox on it with:

• Data field: WineryID
  Type of list contents: SQL [Native]
  List content: "SELECT Winery, WineryID FROM Winery ORDER BY Winery"
  Bound field: 1

The way this works is that Bound Field = 1 means that the 2nd column, WineryID, in the SELECT statement is bound to WineryID as specified by "Data field". WineryID is a column in the Wine table that lies under frmWine. Bound field is an index into the columns returned by the SELECT statement starting with 0. (i.e., Bound field = 0 would have referred to the first column, Winery.)

If I specify "SQL" instead of "SQL [Native]" for the "Type of list contents", the form takes 35 seconds to load initially, but still navigates quickly through Wine records.

I'd still be interested to know if there is some way to speed up the initial loading of the listbox, but for now, this works well enough for me.

[ufalke]

(i.e., Bound field = 0 would have referred to the first column, Winery.)

Hm, did you check that out? I could not achieve such a behaviour before (see http://user.services.openoffice.org/en/ ... =39&t=5038, post from Apr 25th, 08:30 therein), and I just rechecked: The control properties revert always back to 1 if I set "bound field=0" for a listbox. It seems not possible to transfer the first field to the control's data field. Therefore the SQL statement has to contain at least two field names (which may be identical however), like

Code: Select all

SELECT FIELD1, FIELD2 FROM SOMETABLE;

or

Code: Select all

SELECT FIELD1, FIELD1 FROM SOMETABLE;

In the second case, having the possibility to set bound field to 0 enabled to use "SELECT FIELD1 FROM SOMETABLE; " which I have not seen anywhere and which is seemingly impossible (at least with the current version of OO Base).

[JDHeinzmann]

You are right, ufalke, if I set Bound field = 0, it reverts back to 1. However, as stated in OOo help on "Bound Fields":

If you delete the contents of the Bound field cell in the property browser, the first field of the result set is used to display and to exchange data.

I checked that out and it works as advertised.

Good catch. Thanks.