View Single Post
  #2  
Old February 8th, 2005, 08:26 PM
John Nurick
external usenet poster
 
Posts: n/a
Default

Hi Michael,

The way tables are stored in Access databases is complicated and
generally not worth bothering about. In general there will be no
advantage in putting memo fields in a separate table, and some
disadvantages.

Where memo fields really slow you down is if you need to search their
contents, because in some versions of Access they cannot be indexed at
all, while in others only the first 255 characters of the contents will
be indexed. So most searches involve scanning the entire contents of the
memo field.

If you have a lot of records and must have fast searches of the memo
fields, consider using a database that allows full-text search (either a
specialist text database such as Bekon Idealist or AskSam, or else SQL
Server or similar as a back end for Access). Otherwise, you could write
code to build your own keyword indexes in separate tables.




On Tue, 08 Feb 2005 12:17:46 -0800, Michael Shaw
wrote:

Hello!
I'm about to import a database which has four memo fields - author
biography, book review, table of contents and cover blurb.

Does having fields containing large amounts of text have a major impact
on search performance? Should I put the memo fields in a separate table?

Each record has 70 fields (not all populated) and there will be
approximately 600,000+ records.
Many thanks
Michael


--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.