Tables and memo fields

[Jeff Boyce]

Michael

I would have a concern about having a well-normalized database design
wherein a single table had more than 30 fields -- your situation with 70
raises alarms.

Access has a limit (2048, I believe) to the maximum number of characters in
one row on a table (excepting memo fields, which I believe are stored
"separately"). This may also be an issue with your 70 field design.

If I remember correctly, the kind of indexing that speeds up search work
does not apply on memo fields (but I might not be quite clear on that).

 

[John Vinson]

If I remember correctly, the kind of indexing that speeds up search work
does not apply on memo fields (but I might not be quite clear on that).

Quite correct - you cannot index memos.

John W. Vinson[MVP]

 

[John Nurick]

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.

 

[Michael Shaw]

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

 

[Jeff Boyce]

Michael

That the "70" you had mentioned earlier are actually part of a larger 140
fields only reinforces my suspicion that the data is not well-normalized.

Consider creating a data model that incorporates normalization rules, then
working out the parsing/mapping queries you'd need to get from your "dump"
to this final relational model. You'll find that Access works much better
and offers many more tools if your data is in the form Access is optimized
for.

 

[Michael Shaw]

Thanks for the advice. essentially I'm getting a flat file Oracle dump.
Its actually 140 fields. I'm cutting out fields I don't need as it gets
well over 1.2gb!