none
MS Access VBA Search Result Performance Improvement RRS feed

  • Question

  • Dear All,

    I need your help for MS Access VBA Search Result Performance Improvement, below is the code which i used, i want to get the result based on string matching on % basis, this code is correct but it is taking too much time to perform almost 25 Minutes To 30 Minutes for Single String.

    I have 3 tables

    (1)    MasterTable (Linked with SQL Server with 30 Lacs Records)

    1.        OM_ACCT_NBR (Text Primary Key)
    2.       Customer_Informations (Memo)

    (2)    tblSearchString (Using this table as Search String for bulk search)

    1.        Your_Search_String (Memo)

    (3)    tblResults (Store Matched Details)

    1.        OM_ACCT_NBR (Text)
    2.       Matched_% (Text)
    3.        Customer_Informations (Memo)

    Example : If my search string = “Arjun Singh Pune Maharashtra 511015 CRXPS0288X”, i want >80% string matching result on Result Table.

                    This string will check one to one & Apple to Apple on Customer_Informations (Memo) Master Table.

     

    Private Sub Command53_Click()

    Dim varRet                              'Variant Array to hold Elements of [Your_Search_String]

    Dim intCtr As Integer                   'Used to Loop thru Elements of [Your_Search_String]

    Dim intNumOfMatches As Integer          'Number of Matches (Elements in [Your_Search_String]

    Dim intOverallCtr As Integer            'Overall Counter (Number of Elements in [Your_Search_String])

    Dim conPERCENTAGE As Single

    conPERCENTAGE = CInt(Me.txtMatchingPercentage) / 100  '> this Value on [Your_Search_String] Elements indicates Match

    Dim TempTextMatched As String

    Dim con As ADODB.Connection

    Set con = Application.CurrentProject.Connection

    Dim RsResult As Object

    Set RsResult = CreateObject("ADODB.Recordset")

    Dim RsSearchString As Object

    Set RsSearchString = CreateObject("ADODB.Recordset")

    Dim RsMasterTable As Object

    Set RsMasterTable = CreateObject("ADODB.Recordset")

      

    'Clear the Results Table

    CurrentDb.Execute "DELETE * FROM tblResults", dbFailOnError

    Me.Refresh

    RsSearchString.Open "Select * from tblSearchString", con, 1, 3, dbSeeChanges

    RsMasterTable.Open "Select * from De_Dupe_Final_db_Consolidated_Final_PercentMatching", con, adOpenForwardOnly, adLockReadOnly, adCmdText 'dbSeeChanges

    RsResult.Open "Select * from tblResults", con, 1, 3, dbSeeChanges

    With RsSearchString

      Do While Not .EOF

        Do While Not RsMasterTable.EOF

          TempTextMatched = ""

              varRet = Split(![Your_Search_String], " ")

              For intCtr = LBound(varRet) To UBound(varRet)

                intOverallCtr = intOverallCtr + 1

                  If InStr(Replace(RsMasterTable![Customer_Informations], " ", ""), varRet(intCtr)) > 0 Then

                    intNumOfMatches = intNumOfMatches + 1

                    TempTextMatched = TempTextMatched & Trim(varRet(intCtr)) & " "

                  End If

              Next

                'Do > 80% of Elements in [Your_Search_String] match [Customer_Informations]

                If (intNumOfMatches / intOverallCtr) >= conPERCENTAGE Then    'Percentage Criteria for a 'MATCH'

                  RsResult.AddNew

                    RsResult![OM_ACCT_NBR] = RsMasterTable![OM_ACCT_NBR]

                    RsResult![Customer_Informations] = RsMasterTable![Customer_Informations]

                    RsResult![Matched_%] = intNumOfMatches / intOverallCtr

                  RsResult.Update

                End If

                intNumOfMatches = 0: intOverallCtr = 0      'RESET, critical

            RsMasterTable.MoveNext

        Loop

            RsMasterTable.MoveFirst

           .MoveNext

      Loop

    End With

      

    RsSearchString.Close

    Set RsSearchString = Nothing

    RsMasterTable.Close

    Set RsMasterTable = Nothing

    RsResult.Close

    Set RsResult = Nothing

    Set con = Nothing

    Me.Refresh

    End Sub

    Monday, October 24, 2016 11:27 AM

All replies

  • >  'Do > 80% of Elements in [Your_Search_String] match [Customer_Informations]

    You may want to consider a different approach, such as the Simil algorithm I wrote about here: http://accessmvp.com/TomVanStiphout/Simil.htm


    -Tom. Microsoft Access MVP

    Monday, October 24, 2016 1:39 PM
  • Hi Arjunsingh,

    you can try to use new algorithm suggested by other community member.

    but here I think that code is taking reasonable amount of time because you can see that you have 3 tables with 30 lacs data.

    and you are firing multiple queries. you are using loops at a time operation will be perform on a single record and matched also you 80 % match. so here I think that it doesn't matter you use any algorithm here.

    you will not get any extra ordinary performance by that.

    it will take this much time. and if you find any algorithm that have good performance then also it can make the difference of 1 or 2 minutes. performance will not be better then that.

    if you want to improve the performance then remove the un necessary steps from your code. do the thing that is important to achieve your requirement. so that with every record you can save certain amount of times will give you a better result.

    I have find that you had created a thread for the same issue in many other forums. so hope you also get some helpful reply from there.

    Ms Access VBA Search Result Performance Improvement, Access 2007    

    Regards

    Deepak

     


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, October 25, 2016 8:54 AM
    Moderator
  • It appears that you are familiar with ADODB.  I would write a stored procedure on the sql server, and let the sql server engine do the search work (Sql server engine has 1000+ times more horse power than the little bitty Access Jet engine -- or should I say more thrust -- like 50,000 lbs on the sql server vs 50 lbs on Jet -- and with all due respect to Access, the Jet engine is equivalent to a jet engine on a remote controlled model airplane - which is why your query takes so long). 

    Call the stored procedure from ADODB.  Use the ADODB.Command object.  The intellisense will give you a stored procedure option.  All you have to do is add the name of the stored procedure in the Command.Text param.  And You can also pass parameters with this.  ADODB.Command has a Parameter option also.


    Rich P

    Tuesday, October 25, 2016 7:05 PM