I slipped past this one earlier, but now I fully understand the nightmare.
You CANNOT provide indexes on Memo fields, so none of the typical methods of annotation will help. Memo fields are an intractable mess for anything except brute-force parsing. That is why you are having the problem. To get any speed out of this miserable dog of a problem, you MUST break up that memo field in some way.
It also would appear that the original design of the problem did not include any information on the nature of the information that would be gathered and so was not able to break THAT up during data capture. Now, during data analysis, we have a saying about "closing the barn door after all the horses have escaped." It is far too late to do anything constructive with this data set. You MUST find a way to restructure the data in some useful way or you will NEVER EVER get any speed out of this.
I say this while still not being sure whether I fully understand what constitutes a match for the search string that has spaces in it, though your picture DID show some partial matches that let me get as far as I did. One unclear situation is this: does "ABCDE" count as an 80% match if there is an "ABCDF" string in a particular record in the search domain?
Let me tell you technically why you are in a deep hole of your own digging:
A fast way to match up text fields would be if they are capable of being indexed - but a Memo field cannot be indexed.
Doing a search on multiple input strings at once when the only separators appear to be spaces means that you have to parse the input first AND THEN parse the search domain records one at a time, which you won't do with SQL. That means you have to do recordset operations ONE MISERABLE RECORD AT A TIME to match up parts to the whole, and recordset operations in VBA are EMULATED, not EXECUTED. I.e. VBA is a semi-compiler and will not produce x86 (or IA64) code. It will produce interpretable pseudo-code for a virtual machine hosted inside of Access itself.
Your unclear descriptions are driving others away and I'm not sure how much longer I can offer ideas if you can't be more CLEARLY responsive. Parroting the same answers on each interaction (which is how it appears to us) is not productive for us OR for you.
I'm going to give you one last chance to actually LOOK at what WE are telling YOU and try to formulate a better answer. Otherwise, I'm out of the game too.
I really appreciate your efforts and suggestions, sorry for late reply.
Previously I was not aware of cross posting rules that's why i was posted this requirement on multiple Forums for quick result.
Now i read and understand cross posting rules and next time i will take care about this.
here is the one of my posting where some discussion is happening "utteraccess.com"
Note : To be able to post links or images your post count must be 10 or greater. You currently have 9 posts. Please remove links from your message, then you will be able to submit your post.
(Error Message While Submitting This Reply with Link thats why here i just copy and past that website name)
On above link: David was given me VBA code for the same issue and it's reduced searching time up-to 6 minutes, but it is still not acceptable we need result quickly max up-to 1 minutes (for more details please refer above link)
I accepting my all mistakes whatever you are said, i was not replied timely on every threads that is my mistake.
I request you guys please help me to solve my problem.
I have the same details in another table where all details are spited in to different columns like
Account No (PK) - Text
Customer Name - Text
Email ID - Text
Billing Address - Text
Mobile Number - Text
Passport Number - Text
Contact Person Name - Text
Installation Address - Text
.
.
.
etc.
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 cannot build what you really need, but I can give you a verbal guideline. Oh, and for the record we could tell that English is not your first language. Doesn't bother me except that sometimes you don't "think" in English so your explanations have to get translated and sometimes, it is possible that something gets lost.
First, that "other" table with split fields might be helpful, but YOU have to change the way this is approached at the search level.
If your "other" table has no more than 9 fields that you want to search, then you can index the text fields. If more than 9, there are (ugly) ways around the problem.
Build a form with a text box for each of the fields you want to search. You listed
Account No (PK) - Text
Customer Name - Text
Email ID - Text
Billing Address - Text
Mobile Number - Text
Passport Number - Text
Contact Person Name - Text
Installation Address - Text
If it happens that you have more than 9 fields you want to search, this is one of those VERY RARE times when it would be proper to split that table into as many tables as it takes to have no more than 9 (searchable) fields plus the prime key - and then make a 1-to-1 table relationship between each search table and the table I'm going to describe below. Splitting would be needed because you would want to be able to index every field for which you wanted to search and there is a limit of 10 indexes to a single table, and the PK consumes one of those 10 indexes, leaving you 9 per table.
Build a "score" table, let's call that table Score (because we don't need to be imaginative for the names). It should have perhaps three or four fields. The account number (PK), a Hits field (LONG), and a PctScore field which could be LONG if all you want is percent from 0 to 100.
So you would build a form with a text box for customer name, another for e-mail, another for Billing Address, etc. etc., one text box for every field you might wish to search. Put a couple of buttons on it to do some VBA things. When you start a new search, have a button that just erases every text box on that form that can be used for searching. Your user then uses the mouse to select the boxes to be used in the search and puts NOTHING in the other boxes. This "clear the form" button would include code as simple as
It is critical that you expressly set each text box to the empty string represented by the "" sequence, not a null.
After filling in the boxes with data to be searched, you click the button that launches some VBA code that visits each box. For the sake of this discussion, let us say that you DID split the table into SrchTblA and SrchTblB and that each such table has the PK and up to 9 other fields.
OK, this part will be tedious, but this is what you need to do. To make this faster, I recommend using a DAO database object because that gives you access to the DAO .Execute method. The "Dim" statements might not be the only ones you need for the code, but this might give you some ideas.
Code:
Dim TotFldsSrchd as LONG
Dim SrhQry as STRING
Dim DBobj as DAO.Database
...
TotFldsSrchd = 0
SET DBObj = CurrentDB
DBObj.Execute "UPDATE SCORE SET HITS = 0, PCTSCORE = 0 ;"
...
This next part is an EXCERPT of what you might have to do in the same routine, but I wanted to break out of that code to explain that you will probably have to repeat this kind of code once for every searchable field that is supported by your form.
Code:
IF [MobileNumber] <> "" THEN
SrhQry = "UPDATE SCORE INNER JOIN SrchTblA " & _
ON SCORE.AccountNum = SrchTblA.AccountNum " & _
SET SCORE.HITS = SCORE.HITS + 1 " & _
WHERE SrchTblA.MobileNumber = '" & [MobileNumber] & "' ;"
DBObj.Execute SrhQry
TotFldsSrchd = TotFldsSrchd + 1
END IF
You'll have a segment that looks like this for EVERY SEARCHABLE FIELD. If you had to split the tables into A and B (or however many, it doesn't matter), you will do the INNER JOIN on the correct table that holds that searchable field. We're almost there. After you have done the last search because you have searched all the non-blank fields (and skipped all the blank ones), you have to compute the total.
Now at this point you could write a query to do the INNER JOIN between the SCORE table and whatever table or tables you wanted to see. You would have a number from 0 to 100 in PCTSCORE in the SCORE table and that table ALSO contains the PK of the record for which that would be the score. If you wanted to get fancy, you would write the query to not show any joined records for which the PCTSCORE was below some lower limit. Up to you for that fine point.
NOTE that this method DOES NOT perform "close" matches, in that if you search for Company Name "ABCDE" and the table's entry for company name is "ABCDF", that will NOT be a hit. But this is the best you will get otherwise.
Why could this be faster?
First, if you can get an index on every field to be searchable, that makes it a lot faster.
Second, you are running one search at a time on one field at a time, but ONLY for the things selected to be searched. I doubt you would search more than 5 or 6 fields at a time, so you have limited the scope of your search to only those fields for which you had search values.
Third, using SQL in this way is faster than ANY recordset operation because SQL code is based on compiled code engine, not an interpretive one.
Things you could do to slow it down again: Use LIKE instead of = (equals) as a search criterion. Wildcard searches are a total PIG, speed wise.
If your "other" table has no more than 9 fields that you want to search, then you can index the text fields. If more than 9, there are (ugly) ways around the problem.
Build a form with a text box for each of the fields you want to search. You listed
Thank you The_Doc_Man for your suggestion, here i just prepared a table as below details
In my other table have 11 fields as below
1. Account Number (PK) - Text
2. COMPANY_NAME - Text
3. Customer_Name - Text
4. AUTH_SIGNATORY_NAME - Text
5. AUTH_EMAIL_ID - Text
6. AUTH_CONTACT_NO - Text
7. Cust_DAY_EVE_Phone - Text
8. Cust_EMail_ID - Text
9. Billing_Address - Text
10. Installation_Address - Text
11. Proof_ID_Number - Text
Post “CONCATENATE” above fields, now in my table have 9 fields as below mentioned
1. Account Number (PK) - Text
2. COMPANY_NAME - Text
3. Customer_Name - Text
4. AUTH_SIGNATORY_NAME - Text
5. EMAIL_ID - Text
6. CONTACT_NO - Text
7. Billing_Address - Text
8. Installation_Address - Text
9. Proof_ID_Number - Text
If it happens that you have more than 9 fields you want to search, this is one of those VERY RARE times when it would be proper to split that table into as many tables as it takes to have no more than 9 (searchable) fields plus the prime key - and then make a 1-to-1 table relationship between each search table and the table I'm going to describe below. Splitting would be needed because you would want to be able to index every field for which you wanted to search and there is a limit of 10 indexes to a single table, and the PK consumes one of those 10 indexes, leaving you 9 per table.
So you would build a form with a text box for customer name, another for e-mail, another for Billing Address, etc. etc., one text box for every field you might wish to search
Here you suggested, i have to create search text box for every fields but in this way we my management is not considering, they wants a single search box only, user will never past searching string on many search boxes.
I'll offer the OPINION that concatenating fields is not a good idea because that would force you to do a LIKE operation for your string queries, and a LIKE operation kills the use of the indexes. It is the indexes that would give you the speed you need.
Let me clarify that before anyone else chimes in:
Doing a "WHERE fieldx = 'text' ;" is VERY fast and uses indexes to great advantage. Doing a "WHERE fieldx LIKE 'text*' ;" is fast but not quite as fast as exact matching.
Doing a "WHERE fieldx LIKE '*text' ;" or "WHERE fieldx LIKE '*text*' ;" will be exceedingly slow because the leading * with the LIKE prevents the use of the index and leads to what is called a 'relation scan' - which is why your original approach was so slow.
However, it is your app, and if you take this approach, you will probably have to tweak it more than once or twice anyway. This is my best advice, and beyond this point, I doubt I can offer much more. If you find that you are still having slow searches with your concatenated fields, I advise revoking the concatenation and splitting the tables into the 1-to-1 relationship so that you can declare more indexes than could be supported by a single table.
Doing a "WHERE fieldx = 'text' ;" is VERY fast and uses indexes to great advantage. Doing a "WHERE fieldx LIKE 'text*' ;" is fast but not quite as fast as exact matching.
Doing a "WHERE fieldx LIKE '*text' ;" or "WHERE fieldx LIKE '*text*' ;" will be exceedingly slow because the leading * with the LIKE prevents the use of the index and leads to what is called a 'relation scan' - which is why your original approach was so slow.
.
I just let you know what exactly is my requirement.
We are service provider and we have N number of customers where some customers are still using our services where Account Status is Active and some customers are left where Account Status is Disconnected.
we are operational guys and we have a separate database where all customer information's are available in our Database Server.
When we get New Service Request from a customer then we are checking that customer history in our database.
(1) If Customer is exist in our database table then
(A) IF it is Active (Already He Using Our Other Services) then we will check their back history (Like : Outstanding Amount, Cheque Bounce History......etc.)
(B) IF it is Disconnected (Previously He Was Used Our Services) then we will check their back history (Like : Outstanding Amount, Cheque Bounce History, Settlement Amount, Wright-Off Amount..........etc)
(2) If Customer is not exist in our database table it means it is New for us and we will cross check their AVCV. Post all clearance we will give the service.
Challenges : Example :
Customer in not traceable in our database table while he will again comes with us for new services, while he change their company name, address, contact number etc.
Suppose : ITM Company is a Customer and Modafone Company is a Service Provider.
ITM Chairman was using Modafone Post-paid services for their employees after few days he will left Modafone services without paid any bill amount, Then Modafone Tagged as ITM is defaulter company in their database with Wrigh-off amount.
ITM Details : ITM Pvt Ltd Magarpatta City Hadapsar Lane No 3 Pune 511015 arjunsingh@itm.com MOB 8149090300 PAN CRXPS099X Contact Person Arjun Singh Personal Email arjun@gmail.com TAN RSS90PPT
After few days ITM Chairman will come once again with Modafone and asked a new WIFI connection for their new office with new Name and New Address.
ITM New Details : ISS Pvt Ltd Hardware Park Gyaan Peeth Plot No 1789 Hyderabad 500005 arjunsingh@iSS.com MOB 7149090399 TAN POPOT90T PAN CRXPS099X Contact Person Arjun Singh Personal Email arjun@gmail.com
Here is the some contains are matched Like PAN CRXPS099X Contact Person Arjun Singh Personal Email arjun@gmail.com
On behalf of some text matching (like above matched strings) we are able to identified defaulter customers.
I only read the first few posts and looked at the sample file so excuse me if things have moved on a bit.
This seems to give matching results to your own code. Whether its quicker or not ...
Code:
Private Sub cmdSearch_Clickold()
Dim ar() As String, s
ar = Split(txtSearch, " ")
For Each s In ar
CurrentDb.Execute ("insert into tblResult ( mtchid, srch ) select OM_ACCT_NBR,'" & s & "' FROM De_Dupe_Final_db_Consolidated_Final where Customer_Informations like '*" & s & "*'")
Next
End Sub
it adds only the id field 'OM_ACCT_NBR' and the text being searched for to table tblResult
You can then use other queries to extract data from that result.
tblResult
id COUNTER PRIMARY KEY
mtchid long
srch text
Arjun, the requirement is simply that you need to search for a minimum number of common items for someone who might be in your database in more than one way (due to possible name changes or service provider changes or... who knows, the phase of the moon.) The point is that you supply what you can and try to find the closest match to the totality of what you KNEW, some of which might now be changed.
My original comment stands. To get performance out of your search, you CANNOT rely on a brute force scan of memo fields because they involve some of the most highly CPU-intensive operations you could possible name. That table where each field is broken up into individual factors is your salvation, even if you have to redesign the details somewhat - because you need to be able to use indexes to get this done, AND you need to know the nature of the item you seek (i.e. an address, a phone carrier, a contact name, etc.) so that you can (a) provide information for the search and (b) USE that information to narrow down the search to a single indexed field.
If you are going to stick with Access, I see no other way to do this to boost speed. A brute force attack WILL NOT SUCCEED.
Static, you suggested a way of parsing out the search criteria that included:
where Customer_Informations like '*" & s & "*'")
Next
Please note that the construct "LIKE '*something*' " is perfectly legal - but totally negates the possibility of using an index. It is OF COURSE up for debate and experimentation, but Arjun's problem isn't doing the search - it is how FAST he is doing the search, and given that he was searching a memo field, that's a guaranteed no-index situation.
I completely agree, but the structure of the data isn't always perfect and writing code to restructure it can be more time consuming and problematic and error prone.
My code is shorter, simpler and, I think, more flexible. Whether or not it is quicker to execute I'll leave to arjun.
I haven't been following this thread closely but he thinks his search of x millions of records against unknown amounts of search criteria should execute in under a minute. Which seams a bit optimistic to me anyway... but we can only try..
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.
For sake of argument, let's say the memo fields average 256 bytes, so 31 Million x 1/4 Thousand = (31 / 4 ) Thousand million = just under 8 BILLION characters for the memo fields alone. This leads to an impossibility. The databases for Access cannot exceed 2 Gb for a single file, and this one table is 4 x the capacity of the biggest file Access could ever manage. Even if we are talking a memo field of only 64 bytes, which is fairly puny by memo-field standards, that makes the table he described 2 Gb in size right there and no room for another table.
Arjun, you didn't give us details about FE/BE - it might have helped us to know that this is a split database and that the BE is some sort of server. Because if it is not, you are talking about something that isn't Access at all. It is clear from this and other comments in the thread that you are not familiar with the ethics or practices of forums, but this kind of information is CRUCIAL for us to define a solution to your problem.
What is the structure of this database with regard to front-end, back-end hosting. How many files are we talking about as components? What is the typical number of characters in the Memo field? Are we looking at 32 bytes typically? 64? 128? What size is this data set you are searching?
Among other things, it is Access that imposes the "10 indexes per table" rule, but another back end such as SQL server or ORACLE or Sybase might have a different limit. My suggesting about splitting the tables to grant full indexing might not apply for a foreign back end. That means I spent a lot of time shooting at an ill-defined target, which is how you get a bad reputation here.
PLEASE consider that we are volunteers who do our level best to help folks with knotty problems. When you don't tell us things that we might need to know to solve this problem or at least point the right way, we get very frustrated because it seems that we are wasting our time trying to help you. The ONLY reason we don't cut you off at the knees is that it is obvious you are new to this environment.
Let me give you a medical analogy: If you go to a doctor complaining about a bad stomach pain and he gives you medicine after medicine to help but it does not, and you finally tell your doctor after four or five visits that you didn't think he would need to know that you suffered from the "pica" disease that causes you to eat foreign objects like tacks and pencils, do you think HE might be perturbed for not telling you possible causes for your complaint?
I am pretty sure it's in SQL Server or similar, On the UA thread someone suggested running a stored procedure to analyse the substrings. (I think - definitely some server side process anyway)
What is the structure of this database with regard to front-end, back-end hosting. How many files are we talking about as components? What is the typical number of characters in the Memo field? Are we looking at 32 bytes typically? 64? 128? What size is this data set you are searching?.
FE - MS Access 2007 & BE - SQL Server 2008, we are looking details from single table where 2 columns are there. where size of Memo field [nvarchar(MAX)], we are using 32 bytes system.
Sorry dear i'm travelling, we are celebrating our big festival Diwali, please give me time till tomorrow morning. once i will be reach my destination i will reply you. thanks a lot for your tolerance.
Here is where I have to defer to any others about the details of indexing for SQL Server databases. However, my original advice to use the individual fields in a multi-pass search still stands, since you will only search on inputs you have, not on an amorphous table.
Hope your festival brings you enjoyment, Arjun. In the USA we would say "Happy Diwali" but I don't know the appropriate greeting for your homeland.
As with Access, you can't index a varchar(max) field. You can make a full-text index on the field and then run a full-text search (https://msdn.microsoft.com/en-us/library/ms142571.aspx), but that kind of search looks for words and phrases rather than doing the kind of comparison the OP wants.
I don't know any way offhand to use a FT search to just compare two fields for a % match, but I'm still a relative beginner with SQL Server's intricacies, so that doesn't mean a whole lot.
I'd have to go with Doc and the rest: the data needs to be cleaned up and broken out before it can be used effectively.
As I mentioned in post #6 there is some very powerful text based functionality built into 2008 sql server. This should be done passing the query text through to a stored procedure and let SQL do the hard work.
