searching fields speed 
Author Message
 searching fields speed

hi
I have written an app in vb6 that gets data from an access 97 database. My
question is how can I fine tune my code to speed up the search facility in
the app. My code (listed below) works fine when you are searching through
1000 or 2000 records, but when you apply the code to a database that has
60,000 or 70,000 records the search is very slllloooooowwwwwwwwwwwww. Is
there an alternative I can use or how can I modify my code to speed up the
search facility on large databases?

Can someone please help me thanks
Michael

background info
============
the form is used to search for a particular client
I am using a data control (unbound) to populate a dbgrid with the results
from the recordset
I am them searching for the text that is entered into a text box.

Code
====

Option Explicit
Dim rsNameSearch As Recordset

Private Sub Form_Load()
    dataNameSearch.DatabaseName = DataFilesDir & DBFile
    Call SelectRecords
End Sub

Private Sub Form_Unload(Cancel As Integer)
    rsNameSearch.Close
    Set rsNameSearch = Nothing
End Sub

Private Sub txtSearch_Change()
    Dim strSearch As String
    Dim strLookFor As String

    If Trim$(txtSearch.Text) = "" Then
        dataNameSearch.Recordset.MoveFirst
        Exit Sub
    Else
        strSearch = txtSearch.Text
        strLookFor = "[Warranty Name] like '" & strSearch & "*'"
    End If

    dataNameSearch.Recordset.FindFirst "" & strLookFor
End Sub

Private Sub SelectRecords()
    Dim qdNameSearch As QueryDef
    Dim sqlSearch

    sqlSearch = "SELECT Warranty.[Warranty Number], Warranty.[Warranty Name]
"
    sqlSearch = sqlSearch + "FROM Warranty "
    sqlSearch = sqlSearch + "ORDER BY Warranty.[Warranty Name];"

    Set qdNameSearch = db.CreateQueryDef("")
    qdNameSearch.SQL = sqlSearch
    Set rsNameSearch = qdNameSearch.OpenRecordset()
    Set dataNameSearch.Recordset = rsNameSearch

    qdNameSearch.Close
    Set qdNameSearch = Nothing
End Sub



Sun, 29 Dec 2002 03:00:00 GMT  
 searching fields speed

Quote:

> hi
> I have written an app in vb6 that gets data from an access 97 database. My
> question is how can I fine tune my code to speed up the search facility in
> the app. My code (listed below) works fine when you are searching through
> 1000 or 2000 records, but when you apply the code to a database that has
> 60,000 or 70,000 records the search is very slllloooooowwwwwwwwwwwww. Is
> there an alternative I can use or how can I modify my code to speed up the
> search facility on large databases?

> Can someone please help me thanks
> Michael

> background info
> ============
> the form is used to search for a particular client
> I am using a data control (unbound) to populate a dbgrid with the results
> from the recordset
> I am them searching for the text that is entered into a text box.

> Code
> ====

> Option Explicit
> Dim rsNameSearch As Recordset

> Private Sub Form_Load()
>     dataNameSearch.DatabaseName = DataFilesDir & DBFile
>     Call SelectRecords
> End Sub

> Private Sub Form_Unload(Cancel As Integer)
>     rsNameSearch.Close
>     Set rsNameSearch = Nothing
> End Sub

> Private Sub txtSearch_Change()
>     Dim strSearch As String
>     Dim strLookFor As String

>     If Trim$(txtSearch.Text) = "" Then
>         dataNameSearch.Recordset.MoveFirst
>         Exit Sub
>     Else
>         strSearch = txtSearch.Text
>         strLookFor = "[Warranty Name] like '" & strSearch & "*'"
>     End If

>     dataNameSearch.Recordset.FindFirst "" & strLookFor
> End Sub

> Private Sub SelectRecords()
>     Dim qdNameSearch As QueryDef
>     Dim sqlSearch

>     sqlSearch = "SELECT Warranty.[Warranty Number], Warranty.[Warranty Name]
> "
>     sqlSearch = sqlSearch + "FROM Warranty "
>     sqlSearch = sqlSearch + "ORDER BY Warranty.[Warranty Name];"

>     Set qdNameSearch = db.CreateQueryDef("")
>     qdNameSearch.SQL = sqlSearch
>     Set rsNameSearch = qdNameSearch.OpenRecordset()
>     Set dataNameSearch.Recordset = rsNameSearch

>     qdNameSearch.Close
>     Set qdNameSearch = Nothing
> End Sub

Michael;
I've a couple of thoughts for you.

   * Do anything you can to get the "like" out of the process.  It ends up doing
     a sequential read of every record.
   * You did any "order by" that I don't think serves any use given the "like".
   * Put an index on the key field. "Warranty.[Warranty Name]"
   * Cut down on the number of records that are "fetched" by the query by
     collecting a smaller set of records for a final search.

Let us know how you make out.

--
Regards
/Don Ames
GUU Software Inc.

Please visit our web site at...
http://www.mindspring.com/~guu_sftw



Sun, 29 Dec 2002 03:00:00 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. searching fields speed

2. searching fields speed

3. Tab to search field on search engines

4. Speed, Speed, and Speed!

5. Speed Speed Speed - Cutting down on wasted cycles

6. Fields, how to search for a specific type of field

7. Contact Search Speed - To damn slow

8. search speed on large access db using jet

9. RDO Speed Search

10. search speed on large access db using jet

11. SPEED UP SEARCHES, here's how

12. Character-search in string, speeding up?

 

 
Powered by phpBB® Forum Software