Performance Issues

[zylinius1983]

I have a Customer Service database with over 70,000 customers in it. I
have Normalized the whole thing. When a Customer Calls in, a Customer
Service Representative looks up their record, and it contains all the
customer's information. In the last few weeks, it has been taking
longer and longer for the records to be found. I have index all the
common search fields. They currently open a Form that has the
Customer Accounts as a main form, and all of their "Notes" as a sub
form. We have at most 4 people in the database at 1 time. How can I
increase the speed in which the accounts are found? We are using
Access 2003 with all the latest patches installed. The database is
stored on our Windows 2003 Small Business server.

Thanks for your help.

[Rich P]

If you are using a database in a corporate environment with multiple
users - you need to use a database that was designed for corporate use
like sql server. Access is a desktop database designed for desktop
usage. In the beginning of RDBMS, Access revolutionized windows based
database programming. But it has long since been supersceded by sql
server for the corporate environment. The solution to your problem is
to step up to sql server.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[Bob Alston]

zy**********@gmail.com wrote:

I have a Customer Service database with over 70,000 customers in it. I
have Normalized the whole thing. When a Customer Calls in, a Customer
Service Representative looks up their record, and it contains all the
customer's information. In the last few weeks, it has been taking
longer and longer for the records to be found. I have index all the
common search fields. They currently open a Form that has the
Customer Accounts as a main form, and all of their "Notes" as a sub
form. We have at most 4 people in the database at 1 time. How can I
increase the speed in which the accounts are found? We are using
Access 2003 with all the latest patches installed. The database is
stored on our Windows 2003 Small Business server.

Thanks for your help.

It really depends on how your database was implemented.
1)Do you have separated front end and back end databases? If not,
highly suggest you do that.

2) How do you select a customer? do you have a selection screen where
you enter selection criteria and then display only those records
matching the criteria. From there you should select one customer and
open the customer form filtered for only that one customer.

Make sure the initial display of the customer selection form displays no
records.

Make sure the query used to display records on the customer selection
form has only the fields needed to select the customer. Preferably all
of these fields should be indiced.

Make sure you have record locking set correctly.

Others may have more suggestions but this will give you a start.

Bob

[Darryl Kerkeslager]

"Rich P" <rp*****@aol.comwrote

If you are using a database in a corporate environment with multiple
users - you need to use a database that was designed for corporate use
like sql server.

"Corporate environment"? "Designed for corporate use"?

What should I use, then, in a private (non-corporate) company with 1000
users?

The solution to your problem is to step up to sql server.

While SQL Server, Oracle etc are certainly more robust - and I might agree
that a company with 70,000 customers might want to 'step up', it is not the
answer to the posters problem. 70,000 records is trivial for Access, and
there is no reason why the results of a search should not be retrieved in a
less than noticeable time. Likely, your "solution" to a dull-bladed
pushmower would be to chuck it and buy a riding mower.

Bob has suggested a BE/FE split if not done already. The OP said he has
fully indexed the relevant fields.

The LAN itself could be an issue.

"I have index all the common search fields." - Perhaps the OP has
constructed a query that performs poorly.

Connection strings in ADO could cause a problem.

A relevant question might be, does it really take a long time to find the
customer record, or a long time to retrieve the associated notes?

But heck, the blade is dull, so let's just throw it out and upgrade.
--
Darryl Kerkeslager