Large MDB

  • Thread starter Thread starter JohnBu
  • Start date Start date
J

JohnBu

Large database, 120 Megs: I have a customer with an
Access 2000 database (mdb) that's large, when compressed
is 60 Megs. It's a mission critical application,
servicing three points of sales to the public. The
database is growing 30% per year. As test I've migrated
the operational part of the application to MSDE. In all
the functions I've tested the MSDE is substantially
slower, I'm talking about up to 500% slower, that's even
after increasing my RAM to 750 Megs. I've a Pentium 4
1.8hz.
Questions: Is there anybody out there with an mdb bigger
than 120 Megs, if so what is your experience, is it
stable? I'm not sure about recommending that my customer
move to the MSDE environment, any comments. Question
should I move to Microsoft Sql Server 2000 Desktop? I've
only had the database corrupt 3 times in 4 years, the
performance is good for is size. I should run into the 2-
gigabyte size limit in 25 years.
 
No you should at least move to SQL Server standard and put that on a
reasonable size server. in addition you should look critically to the
indexes, which have been defined in relation to the standard search paths or
criteria.

If you want to I have the expertise and experience to do such a job in house
and could help. This would cost money of cours.
 
From what you explain, it really should run ok on MSDE. The question "is it
mission critical enough to spend $3k+ to be more reliable" must be answered
before buying into Sql Standard and say... an entry level mirrored scsi dell
server.

If you are that much slower it has to be in the indexing or amount of data
your queries are returning. MSDE is basically a spitting image of Sql
Server without some of the tools like dts, enterprise manager. It also has
the 2 gig limit and some kind of concurrent user limit - can't remember if
it's 5 or 10... there is a great white paper on Microsoft somewhere - do a
google search or something, it's really good.

First, make sure you have indexes on the related fields between tables, then
try timing you queries by putting them in as stored procedures. Remember
that the key to making this fast is also in returning only the records you
need. Don't return 1000 when 1 is all you need. Using filters is great,
but your going to pay big time in performance because your no longer pulling
those records from a file... your pulling them from an engine over a network
that has it's own million lines of code to run to make sure corruption and
other bad things never occur.

Using "Input Parameters" is the way to go for ADP forms. Build stored
procedures, bind the forms to the stored procedures, use public functions
fetch the values into the Input Parameter property - like this...

@MyParam = getMyParameter()


--
Jerry Boone
Analytical Technologies, Inc.
http://www.antech.biz
Secure Hosting and Development Solutions for ASP, ASP.NET, SQL Server, and
Access
 
Why change it if there are not any performance issues?

Because Access is not reliable. We upgraded an mdb to SQL Server 7 Standard
a couple of years ago. The Mdb was 400mb and if we didn't compact it once
in a while, it would grow to 1gb. We also could not predict when the data
would be corrupted. It happened 2-3 times a year. There was 2 solutions
for this: restore from backup and lose the day's entries or create a new
database and import all objects and redo all security permissions. The two
solutions were time consuming and frustrating for the VPs who were waiting
for their reports. There was also all kinds of unexplicable problems and we
wanted to get rid of that too with software more suitable to our needs.

Karen
 
John,

Yes, Access is a stable desktop database. However, in a multi-user
environment, Access has limitations. It does not have a transaction log for
starters and it tends to corrupt occasionally. What I am most concerned
with in your case is that you mention that this is a customer of yours.
That implies that you are being paid to give them advice based on your
experience and expertise. This troubles me because you are asking us high
level questions that we can not answer for your case in particular and we
are giving our advice to you for free.
IF this is truly a mission critical database and you are being paid by your
customer to give them advice based on your experience and expertise then I
strongly suggest that you hire someone that understands Access and SQL
Server. Otherwise you are doing your customer a disservice and it won't
matter how much advice you get from the new groups - you will still hurt
your customer.

That being said - I do not understand what has been migrated to SQL Server.
How are the queries being processed? Did you completely re-write all the
Access queries into stored procedures or are you just using an ODBC
connection to an Access mdb front-end. Are your tables properly indexed?
What are the initial reasons for moving to SQL Server (MDSE) in the first
place? Security? Performance? Integrity? Are you planning on moving the
front-end to an ADP and re-writing all code relating to database/recordset
access?

When moving a system from Access to SQL Server - these are some of the
questions that must be answered and if you want good advice then you need to
give them to us.

Good Luck,
Daran
 
Please see below

--
Kevin Hill
President
3NF Consulting

www.3nf-inc.com/NewsGroups.htm

Karen Vaudreuil said:
Because Access is not reliable.
Click to expand...
Thousands of Access users may disagree. A properly designed and maintained
db is very reliable.
We upgraded an mdb to SQL Server 7 Standard
a couple of years ago. The Mdb was 400mb and if we didn't compact it once
in a while, it would grow to 1gb.
Click to expand...
Once in a while on a multi-user db of that size is not enough.

We also could not predict when the data
would be corrupted. It happened 2-3 times a year.
Click to expand...
Probably either a user shutting down improperly, or the lack of doing
compact and repairs. There are multiple other reasons, including network
hiccups.

There was 2 solutions
for this: restore from backup and lose the day's entries or create a new
database and import all objects and redo all security permissions.
Click to expand...
Or do regular maintenance.

The two
solutions were time consuming and frustrating for the VPs who were waiting
for their reports. There was also all kinds of unexplicable problems and we
wanted to get rid of that too with software more suitable to our needs.
Click to expand...
Can't address unspecific errors, but they do occur in all software.

I'm not beating you up, or trying to dispute your decision to move to SQL
Server, but I do want others that read this thread to know that a properly
designed and maintained database can be a very effective and cost-efficient
solution. I personally like to move to SQL Server when appropriate for a
number of reasons, such as easier security and online backups, but its not
the right solution for every situation.
 
Access is a great desktop database, but if it were my "mission critical"
database, I would move to SQL server ASAP. Microsoft recommends this.
It will take you longer to get things working with SQL Server, and to
learn it, but it will be time well spent, and great knowledge gained.

Regarding performance, make sure there is a primary key on every table.
We have had very good results with Access performance and large amounts
of data in SQL Server. We routinely work with 10GB + databases.

Are you limiting the number of rows returned from SQL to Access in
anyway? Or are you just returning the entire table? How many rows are
in your larger tables? Are your primary keys short? Are your queries
using indexed fields? Test individual Access queries to identify the
bottlenecks. Consider pass through queries or stored procedures.

And consider, when your data is in SQL Server, creating web applications
that go against the same data is a lot easier!

Henry Hansen
 
I agree, MS Access should not have anything critical on it.
Go with SQL Server, the investment in learning pays off in stability &
scalability.

-noah
 
I used Access database for several years, it is stable, but when the mdb file becomes large, it is more likely to corrupt. The best way to prevent this is backup the database from time to time. But sometimes the backup file is too old and I need the recent records, then I would like to try some professional tools. There is a tool called Advanced Access Repair. I have used it to repair many corrupt Access files successfully. Its homepage is http://www.datanumen.com/aar/ It is not free and can be used when necessary.
 
Back
Top