Performance Tanks after Splitting Database

[Jack G]

I've been working on a database project for a few months, and now that it's
nearly complete I have split the database so that the tables reside on the
server and all other pieces are at local computers. While building it, I
had everything on my own computer.

Problem is that now the performance is terrible. What used to take about
the time it takes to click a mouse button now takes 5 to 10 seconds. For
example, each time I open or switch to a continuous form that has about 3500
records in it, it now takes 8 seconds to populate the form vs less than a
second before. The form and the query it uses are both local and the table
is on the server.

Does anyone know if there are some design guidelines for making an efficient
split database that I should know about?

Jack

 

[Jeff Boyce]

Jack

Are you saying that your form loads all 3500 records? Why?

Another approach is to add a combobox (unbound) in the header of the form.
Make the row source for the combo box the human-understandable information
to select one of the 3500. In the combobox's AfterUpdate event, requery the
form.

Base the form on a query that points to the combobox as a selection
criterion.

This way, when the form first loads, the underlying query sees nothing in
the combobox and loads "that" record (i.e., no record). When the user
selects an item from the combobox, the form is requeried and finds "that"
record.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jack G]

Jeff,

The form I'm talking about acts as a sort of index -- I can see at a glance
or quickly scroll through a long list of "projects" to choose from, and then
double-click on one to get to more detailed information. It worked fine
when everything was local -- I could scroll from the top to the bottom of
the 3500 records as fast as my hand could move the scroll bar.

But I wonder -- the query that the form is based on has dozens and dozens of
fields for each record. Do you think it would help if I made a separate
query that only included the fields I need for the "index"? Or do you take
a performance hit for having more queries?

Jack

 

[mscertified]

That will definitely help. Have the query return only the columns you need.
You should also use a saved query rather than a query created in code or a
query entered in the property. Also, if there are any joins in your query,
make sure the joined columns are indexed.
I still recommend you have a 'find' screen upfront rather than showing all
of the records. If a person is scrolling, they are looking for something and
it's more efficient to do this via a find screen.

Dorian

 

[gls858]

I've been working on a database project for a few months, and now that it's
nearly complete I have split the database so that the tables reside on the
server and all other pieces are at local computers. While building it, I
had everything on my own computer.

Problem is that now the performance is terrible. What used to take about
the time it takes to click a mouse button now takes 5 to 10 seconds. For
example, each time I open or switch to a continuous form that has about 3500
records in it, it now takes 8 seconds to populate the form vs less than a
second before. The form and the query it uses are both local and the table
is on the server.

Does anyone know if there are some design guidelines for making an efficient
split database that I should know about?

Jack

While I'm a relative newbie with split databases it's my understanding
that once the data is requested it is pulled to the client across the
network. If, in fact, you are pulling 3500 records Access moves the data
from the entire table to the local client for processing. So network
throughput could be an issue. Also make sure your database is repaired and
compacted. I seen some suggest that the database should be complied.

gls858

 

[John Vinson]

While I'm a relative newbie with split databases it's my understanding
that once the data is requested it is pulled to the client across the
network. If, in fact, you are pulling 3500 records Access moves the data
from the entire table to the local client for processing. So network
throughput could be an issue. Also make sure your database is repaired and
compacted. I seen some suggest that the database should be complied.

Your understanding is incorrect, at least for a properly designed
database and correct queries.

It will only pull all the records over if it is unable to run the
query using the Indexes; a query which can use an index to narrow the
search down to a few records will pass only the (much more compact)
indexes, not the entire table.

John W. Vinson[MVP]

 

[gls858]

Your understanding is incorrect, at least for a properly designed
database and correct queries.

It will only pull all the records over if it is unable to run the
query using the Indexes; a query which can use an index to narrow the
search down to a few records will pass only the (much more compact)
indexes, not the entire table.

John W. Vinson[MVP]

Thanks for the clarification John. That's why I stated I was a newbie.
We have a couple here at our company but they were set up by
someone else. Of course, it's always possible that the ones we have
were improperly designed Something I need to check.

gls858

 

[John Vinson]

Thanks for the clarification John. That's why I stated I was a newbie.
We have a couple here at our company but they were set up by
someone else. Of course, it's always possible that the ones we have
were improperly designed Something I need to check.

Also check out Tony Toews' performance FAQ:

http:\\www.granite.ab.ca\access\performancefaq.htm

if you haven't already done so.

John W. Vinson[MVP]

 

[Jack G]

Thanks to All for the suggestions. I incorporated several of the ideas, but
in the end, the culprit turned out to be a DLookup function that I had put
in one of my queries. I redid the query by linking to another query
instead, and now the database is snappy again. Seems like I've read many
times warnings against lookups, but I thought that only referred to using
them in tables.

Thanks,
Jack

 

[gls858]

Also check out Tony Toews' performance FAQ:

http:\\www.granite.ab.ca\access\performancefaq.htm

if you haven't already done so.

John W. Vinson[MVP]

Thanks for the link John. Good information.

gls858