sewells
Posts: 13
|
| Posted: 01/16/2004, 12:22 PM |
|
I have three list boxes which are based on a large 1800+ entry query which is quite slow on my host. Each list box is based on the same query, and in theory, i should only have to do the query once and then populate each of the three list boxes with the same query result.
This challange is getting array (array of boudn values, array of text values) formatted corrrectly fromt he result of the query.
Can anyone help me execute a query which would allow populating the list box without using the CCS built in capabilites.
Thanks
---Steve
|
 |
 |
sewells
Posts: 13
|
| Posted: 01/16/2004, 2:35 PM |
|
We'll after a couple more hours, I've shaved about 50% off the time for htis page to load (from 25 seconds to 12 seconds). It still needs to be dramatically faster, but I thought I'd share the code:
'First pull the data from the database for the three list boxes
Dim RecSet, SQLCMD, ABRCatArray
SQLCMD = "SELECT Cat_ID, Cat_FullPath FROM ABRCats WHERE Cat_PID IS NOT NULL ORDER BY Cat_FullPath"
Set RecSet = DBABR.Execute(SQLCMD)
'Populate a 2d array with the records
ABRCatArray = RecSet.GetRows()
'Now create the two 1d arrays that CCS requires
Dim NumRecords, RowCounter, BoundArray(), TextArray()
NumRecords = ubound(ABRCatArray,2)
ReDim BoundArray(NumRecords)
ReDim TextArray(NumRecords)
For RowCounter = 0 to NumRecords
BoundArray(Rowcounter)=ABRCatArray(0,RowCounter)
TextArray(RowCounter)=ABRCatArray(1,RowCounter)
Next
'Now populate the fields for CCS for the three list boxes.
set Books.B_ABRC1ID.DataSource = CCCreateDataSource(dsListOfValues, Empty, Array(BoundArray,TextArray))
set Books.B_ABRC2ID.DataSource = CCCreateDataSource(dsListOfValues, Empty, Array(BoundArray,TextArray))
set Books.B_ABRC3ID.DataSource = CCCreateDataSource(dsListOfValues, Empty, Array(BoundArray,TextArray))
|
|
|
|
peterr
Posts: 5971
|
| Posted: 01/16/2004, 3:55 PM |
|
Looks good.
Also, does your ListBox contain 1800+ records/values? Or the query is searching through 1800+ records, but the ListBox contains small number of values?
In the first case, you may try this undocumented feature to use a different ListBox array build method, which is faster only for large number of values in a ListBox: place this code in "Before Show" event of the ListBox:
Books.B_ABRC1ID.PopulatingType = ccsJoins
In the second case, you may investigate further if the query itself can be sped up, for example by adding an index to your table.
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
Bil Simser
|
| Posted: 01/19/2004, 4:25 AM |
|
"sewells" <sewells@forum.codecharge> wrote in message
news:640084fe3909b6@news.codecharge.com...
> I have three list boxes which are based on a large 1800+ entry query which
is quite slow on my host. Each list box is based on the same query, and in
theory, i should only have to do the query once and then populate each of
the three list boxes with the same query result.
>
> This challange is getting array (array of boudn values, array of text
values) formatted corrrectly fromt he result of the query.
>
> Can anyone help me execute a query which would allow populating the list
box without using the CCS built in capabilites.
I would suggest you consider using a new page with a paged grid instead of a
listbox if you have that many values. No matter what you do to shave down
the time (even if you get it down to 1 second) it means the user has 1800+
items in a listbox and that's just plain wrong in my design books. There's
examples in the sample pack to use a popup window with a separate grid in it
to pick from an employee list and return the result back to the parent page.
-Bil
|
|
|
|
|
