Jim Dettman
jimdettman at earthlink.net
Thu Apr 3 16:11:29 CST 2003
Seth, <<As I understand it, a memo field is different from a text field. It is my understanding that a memo field is really nothing more than a pointer, kind of like an OLE field. The text within a memo field is not stored directly in the table, but in its own storage area that the field points to.>> That's correct. Memo and OLE fields are stored on Long Value Pages (LVP). The "fixed" portion of the record contains a 16 byte pointer to the start of the chain (14 bytes for A97 and prior). The only exception to this is if the memo or OLE field is <30 bytes. If so, the Memo or OLE data is stored with the rest of the record. << So when you are indexing a memo field, all you are really doing is indexing the pointers, not the data.>> Could be or it could be the first 255 characters as Charlotte suggested. In either case, it doesn't sound worth while. 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 Seth Galitzer Sent: Thursday, April 03, 2003 10:44 AM To: accessd Subject: Re: [AccessD] Indexing a Memo Field. Drew, As I understand it, a memo field is different from a text field. It is my understanding that a memo field is really nothing more than a pointer, kind of like an OLE field. The text within a memo field is not stored directly in the table, but in its own storage area that the field points to. So when you are indexing a memo field, all you are really doing is indexing the pointers, not the data. I'm guessing that the Access dev team decided to add it as an index option more for completeness, rather than any practical purpose. It should be noted that "big-iron" database servers do allow you to index text (and memo fields, whatever they may be called) fields on their full contents. This adds significant size to your indexes, but makes for very fast searching on these fields. Seth On Thu, 2003-04-03 at 00:11, Drew Wutka wrote: > 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 -- Seth Galitzer sgsax at ksu.edu Computing Specialist http://puma.agron.ksu.edu/~sgsax Dept. of Plant Pathology Kansas State University _______________________________________________ AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com