is there any other alternative to memo fields

[MoinJ]

I have an mdb file which is around 1Gb in size which has a table with around
600k records and it has one memo field. The problem is that it takes lot of
time to execute just a simple select query, ti includes that memo field too.
The table has around 30 indexes and i am using MS Access 2002.
Is there any other alternative? Even if i try to compact and repair the mdb,
it takes around half an hour to do so. Please suggest me some alternative
option.

Thanks.

 

[Scott McDaniel]

A 1gb file is pretty large in Access. Generally speaking, when a client gets
around 500 mb I begin recommending they move to a more robust data storage
platform (like MS SQL Server, MySQL, etc). Of course, each case is different
and there are plenty of 500mb+ Access databases that work just fine but in
general I've found this to be good advice.

How much memory do you have on your machine? Do you have lots of harddrive
space? Access likes lots of muscle and will definitely slow if you try
running it on underpowered equipment with a db of this size.

Do you have any OLE fields in your tables? These can significantly slow
operations. What are you storing in the Memo field?

 

[Madhivanan]

As number of records increases, definitely the speed will get delayed.
Try to delete unwanted data

Madhivanan

 

[MoinJ]

Hi,

I have enough space on my hard drive, its 80GB and the memory is 380MB. I
cannot use SQl Server or MY SQL because the app which i have built is in VB
and the mdb will be on a CD. The memo field stores simple text of around 1000
chars and there are no OLE fields.

Thanks,
Moin.

 

[Dirk Goldgar]

I have an mdb file which is around 1Gb in size which has a table with
around 600k records and it has one memo field. The problem is that it
takes lot of time to execute just a simple select query, ti includes
that memo field too. The table has around 30 indexes and i am using
MS Access 2002.
Is there any other alternative? Even if i try to compact and repair
the mdb, it takes around half an hour to do so. Please suggest me
some alternative option.

I could be wrong, but I wouldn't expect a select query to take a
particularly long time, even with a large table including a memo field,
provided that you aren't grouping or sorting on the memo field, and
aren't using the DISTINCT keyword, and aren't using the memo field in
the query's criteria. Query performance will be best if all the
criteria fields are indexed and you don't use leading wild cards in the
criteria (e.g., "Like '*foo')

A large file will definitely take a long time to compact. I'm not sure
whether the amount of RAM available makes a difference to this, but it
wouldn't surprise me. 380MB is not very much these days.

 

[MoinJ]

The query that i have is as follows:
Select top 30 product_id,col2,col3 from product where product.level_2 =
11057 order by product.product_id
where col3 is the memo field and product_id, col2 and level_is are indexed.
This query took around 2-3 mins to execute.
I would definately be having queries where i would be using wild cards.

 

[Dirk Goldgar]

The query that i have is as follows:
Select top 30 product_id,col2,col3 from product where product.level_2
= 11057 order by product.product_id
where col3 is the memo field and product_id, col2 and level_is are
indexed. This query took around 2-3 mins to execute.
I would definately be having queries where i would be using wild
cards.

Hmm. I just created a database with a table matching that description,
added 600,000 records to it with 809 bytes in each record's col3 field
and every tenth record having level_2 = 11057. After I ran the append
query that added all those records, the database had ballooned to 1.29GB
in size. Then I ran that exact query, and got 30 records back in about
a second.

I suspect that isn't your actual query SQL. Are you sure that's your
exact query? It doesn't look as if you copied and pasted it from the
query design window's SQL View.

 

[John Vinson]

The query that i have is as follows:
Select top 30 product_id,col2,col3 from product where product.level_2 =
11057 order by product.product_id

This is sorting and grouping on col3, the memo: so it must parse off
the first 255 bytes of each memo field, and sort the recordset by
Product_ID, col2, and that substring of the memo. Of course this sort
cannot be indexed.

I'd suggest a query based on another query. Remove the memo field from
this query; save it; then create a second query joining the top values
query to your table by Product_ID to pick up the memo field.

where col3 is the memo field and product_id, col2 and level_is are indexed.
This query took around 2-3 mins to execute.
I would definately be having queries where i would be using wild cards.

If they are searching for content within a memo field then they will
require a full table scan, searching within the full size of each memo
field in every record for the search term. This will indeed be slower
than an indexed search. Is there any possibility of pulling search
term keywords out of the Memo field and storing them in (indexed) text
fields?

John W. Vinson[MVP]

 

[Dirk Goldgar]

This is sorting and grouping on col3, the memo: so it must parse off
the first 255 bytes of each memo field, and sort the recordset by
Product_ID, col2, and that substring of the memo. Of course this sort
cannot be indexed.

John, I don't see where it's sorting or grouping on col3. The ORDER BY
clause only specifies product_id, and there's no DISTINCT keyword. On
my not-terribly-fast PC, that specific query runs quite quickly. I do
suspect that this is not the actual query being run, and that the real
query *does* sort or group by col3, but MoinJ would have to confirm
that.

If they are searching for content within a memo field then they will
require a full table scan, searching within the full size of each memo
field in every record for the search term. This will indeed be slower
than an indexed search.
Agreed.

Is there any possibility of pulling search
term keywords out of the Memo field and storing them in (indexed) text
fields?

Good idea!

 

[Dirk Goldgar]

After I ran the append query that added all those records, the
database had ballooned to 1.29GB in size. Then I ran that exact
query, and got 30 records back in about a second.

FWIW, it took my PC -- a 1.2GHz Athlon with only 128MB of RAM -- about 9
minutes to compact that database. That didn't recover very much space,
though.

 

[moinJ]

The query which i have pasted here is the actual query from my code and its
not from query design windows's SQL View and again let me say that it has no
sorting or grouping by the memo field, as you can see its ordered by
product_id. I tried removing the memo field(col3) from my query, then too no
major improvement in the execution time. And let me tell you that this
table(product) has more than 200 fields, of which only one is memo.

 

[Dirk Goldgar]

The query which i have pasted here is the actual query from my code
and its not from query design windows's SQL View and again let me say
that it has no sorting or grouping by the memo field, as you can see
its ordered by product_id. I tried removing the memo field(col3) from
my query, then too no major improvement in the execution time. And
let me tell you that this table(product) has more than 200 fields, of
which only one is memo.

Are you *sure* the field [level_2] is indexed? Is it a numeric field?

 

[moinJ]

Are you *sure* the field [level_2] is indexed? Is it a numeric field?

Yes, it is indexed and is a numeric field.
Let me explain u the process in brief. I first created a fresh mdb by giving
the proper indexes to the required fields in the table. At this moment i have
zero records in the product table. Then i insert records into the mdb using
my vb application with sql server db as backend. First i inserted around 1500
records thru my app in the product table, then to test the performance with
large no records, i made a small vb app to duplicate the records in product
table upto 600k.
My mdb would definaltey be of such large no of records as that is what is
required.
Just to remind you once again, i am using MS Access 2002 and product table
has more than 200 fields

Thanks for your quick responses.

 

[Dirk Goldgar]

Are you *sure* the field [level_2] is indexed? Is it a numeric
field?

Yes, it is indexed and is a numeric field.
Let me explain u the process in brief. I first created a fresh mdb by
giving the proper indexes to the required fields in the table. At
this moment i have zero records in the product table. Then i insert
records into the mdb using my vb application with sql server db as
backend.

Where does the SQL Server database come into it? We are talking about
querying a local table in an Access .mdb file, aren't we? I've been
assuming that the data is stored in a Jet database (.mdb file) and the
query is being run by Access. Are these assumptions not true?

First i inserted around 1500 records thru my app in the
product table, then to test the performance with large no records, i
made a small vb app to duplicate the records in product table upto
600k.
My mdb would definaltey be of such large no of records as that is
what is required.
Just to remind you once again, i am using MS Access 2002 and product
table has more than 200 fields

That's an awful lot of fields for any one table. While I don't see how
it could have any effect on this problem, I do wonder if your table
should be normalized. If you're treating it as a data warehouse,
intended only to be queried, maybe not.

Thanks for your quick responses.

Sometimes quick, sometimes slow -- but you're welcome.

 

[moinJ]

Where does the SQL Server database come into it? We are talking about

querying a local table in an Access .mdb file, aren't we? I've been
assuming that the data is stored in a Jet database (.mdb file) and the
query is being run by Access. Are these assumptions not true?

Lets forget about SQL Server for now. Sorry for the confusion. Yes, we are
talking about MS Access(.mdb file) and all of our assumptions are true.

That's an awful lot of fields for any one table. While I don't see how
it could have any effect on this problem, I do wonder if your table
should be normalized. If you're treating it as a data warehouse,
intended only to be queried, maybe not.

Does having lot of fileds for any one table affect the performance? The
table is normalized. Our whole discussion is about just one thing, memo
field, as i faced this problem by just having only one field as memo in my
table. Before this mdb, i had created one more mdb with much more
records(750k), but it had NO memo field, i ran the same query and got the
results in seconds! Only after i created a new mdb by making one of the field
as memo and around 600k records with each memo field having 1000 chars each,
all these problems started arising.

Can there be a possibility of mdb file going corrupt?!? Is my mdb file bad?

 

[Dirk Goldgar]

Lets forget about SQL Server for now. Sorry for the confusion. Yes,
we are talking about MS Access(.mdb file) and all of our assumptions
are true.

Okay, I just wanted to make sure you're telling us everything that is
relevant to the problem.

Does having lot of fileds for any one table affect the performance?

I don't think it should, except that of course you're moving more data
around if you select all the fields. If you select just a few fields,
it shouldn't (so far as I know) make a difference how many fields there
are in the table.

The table is normalized.

I'll have to take your word for it, though it's rare that a table with
that many fields is normalized. But I'm not here to argue about it, and
I don't think it's relevant to the problem.

Our whole discussion is about just one
thing, memo field, as i faced this problem by just having only one
field as memo in my table. Before this mdb, i had created one more
mdb with much more records(750k), but it had NO memo field, i ran the
same query and got the results in seconds! Only after i created a new
mdb by making one of the field as memo and around 600k records with
each memo field having 1000 chars each, all these problems started
arising.

As I said before, I'm not having this problem at all in my test
database. So there's something different about yours, or about what
you're doing.

Can there be a possibility of mdb file going corrupt?!? Is my mdb
file bad?

I suppose it could be, but I doubt it. If it were, I think you'd get
other effects than just slow query returns.

As I understand it, the query SQL you posted is being executed in code,
not a stored query. Would you please copy and paste the exact code
you're executing? Maybe there's something there that will give us a
clue.

 

[moinJ]

If you dont mind, can u give me your email id or some ftp site address where
i can upload the mdb file as the zipped file is around 30 mb. That way you
will be able to suggest me some things.

 

[Dirk Goldgar]

If you dont mind, can u give me your email id or some ftp site
address where i can upload the mdb file as the zipped file is around
30 mb. That way you will be able to suggest me some things.

No, that's not practical, as I have only a dial-up Internet connection.
Occasionally I ask for a copy of the relevant parts of someone's
database, but only when there's no other way. Doing it that way puts a
burden on me that (considering I'm not getting paid for this) I feel
really should be borne by the other person. Also, one of the big values
of these newsgroups is that others can come along later and see what the
problem was and what solution was found. Working "offline" doesn't
contribute to that.

On the other hand, since my test database has such different results
from yours, it might be enough if you send me a compacted, zipped copy
of your database that had almost all of the records, and all
non-essential objects, removed. Then I could see what you're actually
doing, and how it differs from what I've tested. If you can get the
size of the file down to 1MB or less -- preferably much less -- you may
e-mail it to the address you'll find on my website, which is listed in
my sig, below.

 

[moinJ]

The mdb which i am talking of sending you has only one table and has no
non-essential objects. The mdb is compacted and its zipped size is around 30
MB.

 

[Dirk Goldgar]

The mdb which i am talking of sending you has only one table and has
no non-essential objects. The mdb is compacted and its zipped size is
around 30 MB.

Sorry, I can't handle a 30MB download. If you want to take the time to
make a copy of the database, delete almost all the records, compact it
and zip it so that it's 1MB or smaller in size, then you may send it to
me.