A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Tables and memo fields



 
 
Thread Tools Display Modes
  #1  
Old February 8th, 2005, 07:09 PM
John Vinson
external usenet poster
 
Posts: n/a
Default

On Tue, 8 Feb 2005 05:07:53 -0800, "Jeff Boyce"
-DISCARD_HYPHEN_TO_END wrote:

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]
  #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.
  #3  
Old February 8th, 2005, 09:17 PM
Michael Shaw
external usenet poster
 
Posts: n/a
Default Tables and memo fields

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
  #4  
Old February 9th, 2005, 01:34 PM
Jeff Boyce
external usenet poster
 
Posts: n/a
Default

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.

--
Good luck

Jeff Boyce
Access MVP

"Michael Shaw" wrote in message
...
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!
John Nurick wrote:
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.


  #5  
Old February 10th, 2005, 02:35 AM
Michael Shaw
external usenet poster
 
Posts: n/a
Default

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!
John Nurick wrote:
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.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Truncation of memo fields in SELECT queries - Access 2002 datawizzard Running & Setting Up Queries 1 January 27th, 2005 12:46 AM
change field properties, reflect in query SueP New Users 3 November 11th, 2004 01:46 PM
Memo fields and ODBC tables blogan General Discussion 0 July 15th, 2004 07:03 PM
Access 2000, 2002 and BTRIEVE 6.14 problems with memo fields SSGTech Tim General Discussion 0 July 1st, 2004 05:42 AM


All times are GMT +1. The time now is 08:34 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.