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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|
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 26th, 2005 11:46 PM |
change field properties, reflect in query | SueP | New Users | 3 | November 11th, 2004 12: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 |