Splitting a large table in to

[Louis Levine]

I have a very large table (100 fields) and was considering breaking it up
into two that are connected by a 1 to 1 relationship. I could then use a
query to join the tables, and view it as if it were one big table.

My main reason for doing this is that speed (more available indexes) and I
don't want to rework and check thousands of lines of code. This seemed like
a relatively simple way to do it.

Does anyone have any experience with or thoughts on this?

Thanks,
Louis

 

[Albert D. Kallal]

The problem here is that you will TAKE VERY MUCH LARGE performance hit when
you need to join the data back together.

Further, JET is rather quite smart in how it retrieves data (it does not
always read in the whole record). As for more indexes, well, if any of your
quires has one field that can narrow things down, then you may be able to
dump other indexed fields.

So, I can't really recommend this approach. With 100 fields, you will still
have some code to work/re-write. I would thus consider normalizing your
data, as that will solve the indexing problems for you.

Further, with thousands of lines of code, some code may/will beak when you
add a new record, as the "child", or other side record will NOT be
automatically added. Thus, you still will incur coding work.

I can only offer the above advice on my experience, but perhaps others may
chime in with a more positive note on your idea.

 

[GVaught]

It is possible that the table can be split into a one to many rather than
two as a one to one relationship. Run the table through the Analyzer that is
built-in to Access and it will suggest a possible split. You have the option
to accepting the split or exiting. Before you do this make sure you make a
copy of the table within your database or a copy of your database so you can
recover back to the original, if necessary.