Slow data access for a search form list

[TC99]

Hi,

I have a search form that displays a list of all records in a table. There are 5 combo's on the form that the user can select a value from to filter the list to only show records that match that value. (Each combo relates to a different field.)

The problem I have is when there are in excess of 10,000 records. I cannot find a way to successfully update the list in a short amount of time with this number of records. I have tried using a listview control and using code to add a line for each record and I have also tried filtering an ADODB recordset that a Datagrid is linked to. Can anyone help with a suggestion or has had this problem themselves?? It can take up to 20 seconds to requery the list when running on a local machine. I hate to think how slow this would be across a network!!

Thanks for your help.

TC

Technical stuff:
VB 6.0 form using ADO to connect to an Access database via a file DSN. Passing a SQL string to the ADO connection each time a selection is made (In tests this has proved quicker than have a stored procedure and passing parameters). I intend to upgrade the back end to SQL Server but it must be able to work on both for now. The data being shown and filtered is all text.

[Ephesians]

What about applying your filters to the currently loaded recordset rather than passing it back via ado? If you manipulate the recordset in memory and narrow it down via filters it should go a little quicker. The initial recordset is still going to take a few seconds though but repeated filters shouldn't take nearly as long.

[TC99]

Thanks for the reply.

I have tried creating a recordset and then attaching it to a Datagrid to allow me to apply filters but it still takes about the same amount of time for the filter to apply.

I am currently testing on approx 11,500 records but this could easily go up to 30,000-40,000 in the future. As it takes about 20 seconds at the moment I can see users having to wait over a minute in the future.

I was hoping someone had already come across this and had a resolution to hand.

Thanks again for the suggestion though.

TC

[vb_dba]

How are you populating the listview? Are you using the recordset object itself to populate the listview? I've found it's faster to use the GetRows Method of the recordset object to populate an array and build the list. You might try that and see. Below is some sample code:

Dim arrList() As Variant
Dim iLBound As Int
Dim iUBound As Int
Dim i As Int
 
'Initialize Lower/Upper bound values
iLBound = -1
iUBound = -1
 
Set oRS = New Recordset
oRS.Open SQL, Conn, adOpenStatic, adLockReadOnly
 
'If Rows Returned, populate the array
If Not oRS.EOF Then
    arrList = oRS.GetRows()
    iLBound = LBound(arrList, 2)
    iUBound = UBound(arrList, 2)
End If
 
'Close Recordset
oRS.Close
Set oRS = Nothing
 
'If iUBound = -1 then no records returned, so don't populate listview
If iUBound > -1 Then
    For i = iLBound To iUBound
      With ListView1.ListItems.Add(, , arrList(0, i))
          .ListSubItems.Add , , arrList(1,i)
          .ListSubItems.Add , , arrList(2,i)
          ...
          .ListSubItems.Add , , arrList(x, i)
      End With
    Next
    Erase arrList
End If

Regardless of the method, populating a listview with over 10,000 records is not going to be a real quick process.

[TC99]

Thanks Chris.

That was really helpfull. I have now got the list populated in around 7 seconds.

Although I am now going to use this practice, could I ask anyone else to still post their comments in the hope of reducing this time further still. As long as there is a list that can be sorted by columns and it is quick to populate I don't mind what control I use to get the results.

Once again, thanks to Chris for the code.

TC