ADP File too bulky

  • Thread starter Thread starter VS Sreenivas
  • Start date Start date
V

VS Sreenivas

I have recently moved from Access to MSDE.
I have found the following problems with MSDE:-
1. The same data in MSDE occupies almost double the
space!. Tried shrinking the database with Enterprise
manager. Event then, for a table with about 200,000
records, the space occupied by Access project database is
double that of Access.
2. I find that working on records in Access project is
painfully slow compared to an Access database with the
same tables and quantum of data.
3. I get a 'time out' error when trying to change data
for large tables.
Are these to be accepted when moving from the comfort
of 'Acess' to MSDE?
Pl help.
 
First, make sure that you have used "char" and "varchar" instead of "nchar"
and "nvarchar". Contrary to Access, MSDE is unable to use compressed
Unicode but using Unicode in the first place is probably overkill in your
case (except if you want to store russian, arabic, japanese and other
langages along with english or even french, as in my case).

Second, MSDE (or SQL-Server) are to be used (manipulated, edited, viewed,
etc.) with stored procedures, not linked tables.

With linked tables, by comparaison with Jet, Access has no longer access to
their internal statistics and therefore can no longer make proper queries
and will usually ended up with table scans or other inefficient plans. When
using SQL-Server, the primary work of doing requests and queries must be
done by SQL-Server itself, not by some external programs using linked
tables, otherwise everything will rapidly go to a crawl.

For comparaison, over the Internet (a Wan, not a Lan), the first version of
a complex form with many subforms from a freshly upsized database was taking
more than 5 minutes to retrieve its results. Now it's only about 3 or 4
seconds.

S. L.
 
VS> 1. The same data in MSDE occupies almost double
VS> the space!. Tried shrinking the database with
VS> Enterprise manager. Event then, for a table with
VS> about 200,000 records, the space occupied by
VS> Access project database is double that of Access.

The subject of your post is "ADP file is too bulky". So what is bulky, adp
that is only the interface to the data, or the database file that you
actually don't see?

If the latter, then, in addition to what Sylvain said, make sure to use not
CHAR but VARCHAR data type. CHAR allocates fixed space for each record,
while VARCHAR allocates only what is required. I bet this is the main reason
for your problem.

VS> 2. I find that working on records in Access
VS> project is painfully
VS> slow compared to an Access
VS> database with the same tables and quantum
VS> of data.

Create indexes on the columns you search by. Since you indicated using
enterprise manager, make sure to run index tuning wizard, it will advice
which indexes are the best for your workload.

Vadim
 
Back
Top