Jim Dettman
jimdettman at earthlink.net
Thu Apr 3 16:08:08 CST 2003
Charlotte, That would be my guess. I also think it is a bug. Jim Dettman President, Online Computer Services of WNY, Inc. (315) 699-3443 jimdettman at earthlink.net -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Charlotte Foust Sent: Thursday, April 03, 2003 11:38 AM To: accessd at databaseadvisors.com Subject: RE: [AccessD] Indexing a Memo Field. I wonder if it's only applying the indexing on the first 255 chars of the memo field? Charlotte Foust -----Original Message----- From: Drew Wutka [mailto:DWUTKA at marlow.com] Sent: Wednesday, April 02, 2003 10:12 PM To: 'AccessD at databaseadvisors.com' Subject: [AccessD] Indexing a Memo Field. Okay folks, I was chatting in email with Susan, and I brought up Indexing a Memo field. I don't know if everyone knows what an index really does, but essentially it is an internal 'table' within a database, that allows for faster searchs. How does that work? The best way I know of, to describe indexing is explaining something I did once for a Palm OS project. I had a phone list, where I wanted to be able to search by First Name, Last Name and Phone Number. The Palm app was exceptionally slow when trying to find a record within a table, so instead of letting the data control 'find' a record, I wrote an index routine. Essentially I duplicated the data table three times. One sorted by FirstName, one sorted by LastName, and one sorted by Extension/PhoneNumber. I create three seperate tables, which would be the 'indexes' for the three duplicates. Two of the 'index tables' were pretty much the same. (All three had the same structure). The two relating to first and last name had the first field as the 'letter' (which would be the first letter in the last or first name), the second field was the record position that was the start of that letter, and then the last field was a 'string' that contained the position of the next letter after the first. The Number table was similar, but it tracked the position of the numbers. Thus, when a user entered 'D', the code would jump to the fourth record in the index (since D is the fourth letter), then it would look at the second field and find that D started at position 50. So it would then jump to position 50 on the main table (which was sorted by first name), and beginning displaying the records starting with D. If the user entered R, after the D, then the code would again jump to the fourth record on the index table, get the starting position for the D's, then it would look in the last field, and determine where the R's started within the D's. Again, it now had the starting point for all first names starting with DR. Now, the AccessD archives are at roughly 140 megs (somewhere close to that). Doing a search on a subject field is pretty quick. So if you search the subject field for *test*, it pulls up almost instantaneously. However, if you do the same search on the memo field (which holds the body of the email), then it takes a while. What I built for the new archives (which are almost ready, I am just adding the capability to show a certain number at a time), what I did was create a VB program that 'indexes' the memo fields. It reads each memo field, splits it into the words that make up the message, then it puts each word and what message that word belongs to into a seperate table (in another database). So what I end up with is a table for each letter (a through z), and a table for non-letters. Thus 'Drew' is listed in IndexD.mdb, in tblWords, and the key for that record has records within tblWordsToMessages, which then have the key's to each record in the main database that has the word 'Drew' in it. Thus, when searching the archives with this new process, searching time is cut immensely since every word is indexed. I mentioned 'indexing' a memo field to Susan, and she replied that she 'could' index a memo field. That kind of shocked me, because I had spent several hours on my 'indexing' service. So I looked into this a bit. Access 97 does not let you do it at all. It doesn't give you an Index option in the field properties, no does it list the Memo Fields when you go to the 'indexes' window. A2k, however, also doesn't list Indexed as a property, but it does let you select Memo fields within the Indexes window. I then tested the speed of searching. Doing a search for *Drew* in the 97's memo field (on an old copy of the Archive database), took about 10 to 11 seconds to display all of the records. I converted that database to A2k (and set Unicode compression...which was already set), and the same query took about 22 seconds. (Go A2k!!! <grin>). I then set the Index for the memo field in A2k. No speed increase. Also, when I set that index, it took about 5 seconds for Access to 'create' the index. So I immediately knew it wasn't indexing everything within the memo field. I then modified my query to search for Drew*. In 97 that took about 4 to 5 seconds. In A2k, it took about the same amount of time. With the index 'on' in A2k, it seemed to take a second or two longer.....now that I found VERY odd. So my question is, why in the world does A2k (and later versions) even give you the option to 'index' a memo field. It does seem to actually do anything?!?! Drew _______________________________________________ AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com _______________________________________________ AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com