MS Access VBA Search Result Performance Improvement (1 Viewer)

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)
a. OM_ACCT_NBR (Text Primary Key)
b. Customer_Informations (Memo)
(2) tblSearchString (Using this table as Search String for bulk search)
a. Your_Search_String (Memo)
(3) tblResults (Store Matched Details)
a. OM_ACCT_NBR (Text)
b. Matched_% (Text)
c. 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 cmdSearch_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

Cross posted here: http://www.accessforums.net/showthread.php?t=62595

When you cross post, tell readers you have done so and provide the associated link(s).

Here is why! http://www.excelguru.ca/content.php?184

have you considered using a DAO query and using a UDF. ADO can be really slow for this type of hopping about

difficult to follow your code without proper indentation but perhaps something like

and your UDF would be something like

Correct me if I'm wrong but your intent is to determine if 80% of your given single string matches some number of records in the table. I.e. you are looking for an inexact match of the single input string to any of the strings in the given table.

You are then worried about the performance of the comparison method. Before we give you the benefit of dealing with a complex problem, it would be nice to know the rules of the comparison.

For instance, IN ENGLISH tell us the rules to be a match or non-match. You have a target string with spaces in it. Do the fragments between the spaces have significance in the search? If you have a five-character fragment, is it an 80% match if any four of the five characters in a fragment match four of the characters from the strings in the search domain?

Dear The_Doc_Man,

You are the correct, please refer attached Access Macros for your reference.

Search string "TATA HITACHICONSTRUCTION MACHINERY COMPANY LIMITED TELCO CONSTRUCTIONEQUIPMENT LTD 4066332069 9246397641 CONSTRUCTION EQUIPMENT 5-3-338 2ND FLOOR ABOVE UTI BANK ROAD. HYDERABAD 500003 ROAD"

with 80% matching it is taking almost 22 minutes, where in my Database Table having ~11 Lacs Record.

Kindly suggest me some search engine mechanism which will give result quickly.

I think you will have to investigate a SQL Server Full Text Query for this to work at any speed. Have a read here https://www.microsoftpressstore.com/articles/article.aspx?p=2201634&seqNum=3 to see if you think it would help.

I did read a fuzzy search item on here somewhere http://www.access-programmers.co.uk/forums/showthread.php?t=270126 that might be appropriate, but I don't know about performance with huge datasets.

I downloaded your database and ran it with search term
E BAY INDIA PVT LTD

The results are shown in the attached jpg.

If there is more to your post/question/procedure, then I advise you to be extremely specific and tell readers what steps they must do; what your expected result/issue/problem is with examples.

You are familiar with the "algorithm", and the database, but I, for one, do not understand the issue in plain English. If you can't describe the issue, algorithm and why or how the observed solution doesn't match your expectation, then I doubt we can help you.

You should learn to use code tags in your posts. Many readers will move on when they see your code is not formatted and not in code tags.

I suggest you follow up on your cross posts to let people know the status.

Have you considered doing some data cleansing of the data in table De_Dupe_Final_db_Consolidated_Final?

Good luck.

arjun, I have to say that your description still leaves me seriously puzzled.

Let me clarify my question by an example.

Let us say that your SEARCH STRING (the thing you want to find in your data set) contains the sequence "ABCDE FGHIJ KLMNO PQRST WXYZ0"

Suppose that one of the records in your data set being search contained the following sequence: "ABCDX FGHYJ KPMNO P1RST AXYZ0"

In each grouping separated by spaces, four of the five letters would be matched, and I could argue that this is an 80% match because 80% of the search string matches up to the contents of the hypothetical record.

Now let's try a different sequence. The data set ALSO contains "ABCDE FGHIJ KLMNO PQRST 12345" as a sequence. I can argue that this is ALSO an 80% match since 80% of the search string matches this target string.

Third sequence: "ABCDEFGHIJKLMNOPQRSTUVWXYZ" (with no spaces). This could be a 96% match based on 24 out of 25 characters from the search string appearing in this target domain member.

Now look at these three cases and tell us IN ENGLISH which ones would be an 80% match and if any of them are disqualified as a match, why?

Without knowing the match rules clearly, there is absolutely no hope of you getting an answer from us because we can't see the logic here.

Dear The Doc Man,

I am trying to explain what exactly my requirement on Image Attachment, kindly refer the same.

I have a master table where almost 31 millions of records, there are two columns one is the Account Number which is Primary Key of this table and another is the Customer_Informations it is Memo field where we stored customer details like - customer name, billing address, installation address, mobile number, email id, passport number etc.

user wants result with some amount of matching string (% Basis) from database table they have some customer informations and they putting these information on text search box.

I have the Access VBA code for the same (Already Posted on first Thread) but it is taking too much time almost 25 to 30 minutes to get result which is sense less.

here i need your help to get the same result withing 1 minute.

your comparison is slow because you are not using any indexing, so everything is done sequentially.

if there is some logic to the way the data is stored in your information, two thoughts come to mind i.e. Title, Name, "From", Town

1. consider using regular expressions.

2. The other option is to split your information string into individual components into an indexed table

e.g.

PK ID Phrase
1...1..Mr
2...1..Arjun
3...1..Singh
4...1..Pune
etc

and do the same with your search string

CJ_London said:

your comparison is slow because you are not using any indexing, so everything is done sequentially.

if there is some logic to the way the data is stored in your information, two thoughts come to mind i.e. Title, Name, "From", Town

1. consider using regular expressions.

2. The other option is to split your information string into individual components into an indexed table

e.g.

PK ID Phrase
1...1..Mr
2...1..Arjun
3...1..Singh
4...1..Pune
etc

and do the same with your search string

Click to expand...

Thanks for your valuable advice, if i split customer details column in to multiple rows (For every strings) then my database table record-set will be reach ~ 100 Millions because every customer details will split in to average 31 rows, already in my table having 3.1 millions of records.

and if you compact your db - how big is it?

appreciate splitting will be bigger because you are adding an additional field (guess you can drop the PK for this one) and adding an index to both columns but may still fit - depends if this is a one off exercise or ongoing.

alternative is to use sql server express - can go to 10Gb

Point is your routine is slow because of no indexing, using ado and no doubt some other factors. tweaking here and there will only have marginal effect. Your cross posted thread provided an answer which appears to improved processing times by 30% - but you need to see 99% improvement.

fwiw, the OP also posted this on utteraccess, and got an acceptable reply there.

I have a master table where almost 31 millions of records, there are two columns one is the Account Number which is Primary Key of this table and another is the Customer_Informations it is Memo field where we stored customer details like - customer name, billing address, installation address, mobile number, email id, passport number etc.

Click to expand...

So bottom line is you have a "shoe box" full of things and you're trying to find out what belongs to what.

user wants result with some amount of matching string (% Basis) from database table they have some customer informations and they putting these information on text search box

Click to expand...

The user must have provided more detail requirement.

Somebody, whether you or user is unclear, has a serious lack of understanding of relational tables and database (in my view at least).

Working with a memo field can be difficult and I think what you have has to be put into a better structure that is more suited to your needs.

But, we still have to know exactly what you are trying to do; what 80% of ?? means; and what is the goal of the whole exercise?? It certainly isn't searching. Seems vaguely that you are trying to find records/information that may match some existing data held by the user.

I agree with CJ that you may also be required to move to another platform (SQL server express) because of volumes.
But, I'm not convinced the problem and goal are well understood or communicated.

Good luck.

Did you read the link from RuralGuy in post #2??Comments?

Dear jdraw,

I need query result where at-least 80% string matched. please refer Attached Image here i am trying to explained what exactly my requirement.

I now understand enough of the requirement to know that you CANNOT, with your current structure, improve anything. If you restructure this database so that the myriad of seemingly uncontrolled information addition becomes controlled and organized, you might be able to make a better result.

This problem is solved by fixing your data up front so that you can follow the advice of Julius Caesar: Divide and conquer.

As long as you have a two-field storage layout and the second field is a nightmare of things thrown in apparently at random (whether it is really random is immaterial), you will not tame this beast.

If you can't restructure, learn to live with whatever little speed improvements you can get. If this seems harsh, I apologize - but you asked for advise and sometimes the advice you get isn't necessarily what you wanted to hear.

I don't get anything new from your jpg and you've suggested it twice. Seems Doc_Man has some idea of what you are trying to do. Maybe he can explain it to the rest of us.
He also asked several questions with examples in post #7. I don't think you clearly answered him.

If you are trying to do text matching against "a mess of unformatted" text representing several "things", you might want to seriously get the "mess area" into some recognized table(s).

see posts #9/10/11 - already suggested.

I also posted a possibly faster solution in post #3 but OP has ignored, so that, plus his intransigence in trying to get his code working faster and ignoring all other advice plus (at least) triple cross posting means I'm outa here.

Good luck

I'm out also. He has not improved his requirements/description and has not responded/acknowledged his cross posting.