Do I understand form optimization correctly?

[John Welch]

I'm developing my first multi-user application, and have been reading a lot
about the basics of multi-user optimization (ADH vol 2, this newsgroup's
archives, etc.). I've decided to go with a file server system (mdb backend
on network), and I want to design it so that it runs efficiently. There will
be 5-10 concurrent users and ~30,000 records. I'm trying to optimize it by
addressing the most important issues first. I've been focusing on the idea
of having forms 'serve up' only a few records at a time, rather than a whole
table's worth, and I wonder if some of you pros can help me confirm that I'm
understanding it correctly. So here goes:

1) If I bind a form to a query that filters the records with a 'WHERE'
clause, and the field in the 'WHERE' is indexed, the backend will send the
whole index across the wire, but not "the whole table" as some people seem
to say, right? Then the front end will search the index and request the
records that match the WHERE clause, and then only those records will be
sent. Is that right?

If this is correct, then a lot less network traffic will be created, but
there will be a lot of searching of indexes: every time the user 'navigates'
to a new record a new query is run. So does that mean that searching large
indices is faster than sending lots of data? Or does it depend on the
details of the network?

2) In the WHERE clause that limits the recordsource, does using "LIKE" slow
things down a lot? For example, to get all customers whose last names start
with "smi". How about if I do something like "SELECT top 10 ... order by
myIndexedField" ? Will that be a lot slower than limiting records with
'WHERE'?

3) For subform recordsources, I usually use saved queries with "WHERE
myfield = forms!parentform.control_bound_to_PK_field". Is this a good way to
go, and is it a lot better than using the "link master/child" fields
properties, for the same reason as in (1) above?

Thanks very much in advance. This newsgroup is wonderful!
-john

[Salad]

John Welch wrote:

I'm developing my first multi-user application, and have been reading a lot
about the basics of multi-user optimization (ADH vol 2, this newsgroup's
archives, etc.). I've decided to go with a file server system (mdb backend
on network), and I want to design it so that it runs efficiently. There will
be 5-10 concurrent users and ~30,000 records. I'm trying to optimize it by
addressing the most important issues first. I've been focusing on the idea
of having forms 'serve up' only a few records at a time, rather than a whole
table's worth, and I wonder if some of you pros can help me confirm that I'm
understanding it correctly. So here goes:

1) If I bind a form to a query that filters the records with a 'WHERE'
clause, and the field in the 'WHERE' is indexed, the backend will send the
whole index across the wire, but not "the whole table" as some people seem
to say, right? Then the front end will search the index and request the
records that match the WHERE clause, and then only those records will be
sent. Is that right?

If this is correct, then a lot less network traffic will be created, but
there will be a lot of searching of indexes: every time the user 'navigates'
to a new record a new query is run. So does that mean that searching large
indices is faster than sending lots of data? Or does it depend on the
details of the network?
Someone here has that knowledge at their fingertips. But I wouldn't
worry about it. If you have the right indexes (links from key to
foreign key and fields on where filtering will occur) then your results
should be near instantaneous. 30K records is not very many.
2) In the WHERE clause that limits the recordsource, does using "LIKE" slow
things down a lot? For example, to get all customers whose last names start
with "smi". How about if I do something like "SELECT top 10 ... order by
myIndexedField" ? Will that be a lot slower than limiting records with
'WHERE'?
For 30K records, I should think everything will be as near instantaneous
as to be considered instanteneous by users.

3) For subform recordsources, I usually use saved queries with "WHERE
myfield = forms!parentform.control_bound_to_PK_field". Is this a good way to
go, and is it a lot better than using the "link master/child" fields
properties, for the same reason as in (1) above?
Just more work. You have to keep remember to requery the recordset when
you move from record to record. I figure, with your example, why
bother? I'd use master/child properties. There are times you'd want to
do what you want...for example if you plug a subform into an unbound form.

Thanks very much in advance. This newsgroup is wonderful!
-john

[chris.nebinger]

To answer your question, no, it won't matter how you execute the
queries on an Access database. Access is file based, not server based.
If you run any query on the table, the entire table is brought down to
your machine, and the query is executed. With 30,000 records this
won't be a big deal, just wanted you to know for future projects and/or
scalability.
Chris Nebinger
John Welch (remove +'s) wrote:

I'm developing my first multi-user application, and have been reading a lot
about the basics of multi-user optimization (ADH vol 2, this newsgroup's
archives, etc.). I've decided to go with a file server system (mdb backend
on network), and I want to design it so that it runs efficiently. There will
be 5-10 concurrent users and ~30,000 records. I'm trying to optimize it by
addressing the most important issues first. I've been focusing on the idea
of having forms 'serve up' only a few records at a time, rather than a whole
table's worth, and I wonder if some of you pros can help me confirm that I'm
understanding it correctly. So here goes:

1) If I bind a form to a query that filters the records with a 'WHERE'
clause, and the field in the 'WHERE' is indexed, the backend will send the
whole index across the wire, but not "the whole table" as some people seem
to say, right? Then the front end will search the index and request the
records that match the WHERE clause, and then only those records will be
sent. Is that right?

If this is correct, then a lot less network traffic will be created, but
there will be a lot of searching of indexes: every time the user 'navigates'
to a new record a new query is run. So does that mean that searching large
indices is faster than sending lots of data? Or does it depend on the
details of the network?

2) In the WHERE clause that limits the recordsource, does using "LIKE" slow
things down a lot? For example, to get all customers whose last names start
with "smi". How about if I do something like "SELECT top 10 ... order by
myIndexedField" ? Will that be a lot slower than limiting records with
'WHERE'?

3) For subform recordsources, I usually use saved queries with "WHERE
myfield = forms!parentform.control_bound_to_PK_field". Is this a good way to
go, and is it a lot better than using the "link master/child" fields
properties, for the same reason as in (1) above?

Thanks very much in advance. This newsgroup is wonderful!
-john

[John Welch]

To answer your question, no, it won't matter how you execute the
queries on an Access database. Access is file based, not server based.
If you run any query on the table, the entire table is brought down to
your machine, and the query is executed.
I don't think this is correct, actually. That's why I was asking: because a
lot of people say that each query has to retreive the 'whole table', but I
think if the fields are indexed, then only the index has to be sent. Can
someone who really knows settle this?


Chris Nebinger
John Welch (remove +'s) wrote:

I'm developing my first multi-user application, and have been reading a
lot
about the basics of multi-user optimization (ADH vol 2, this newsgroup's
archives, etc.). I've decided to go with a file server system (mdb
backend
on network), and I want to design it so that it runs efficiently. There
will
be 5-10 concurrent users and ~30,000 records. I'm trying to optimize it
by
addressing the most important issues first. I've been focusing on the
idea
of having forms 'serve up' only a few records at a time, rather than a
whole
table's worth, and I wonder if some of you pros can help me confirm that
I'm
understanding it correctly. So here goes:

1) If I bind a form to a query that filters the records with a 'WHERE'
clause, and the field in the 'WHERE' is indexed, the backend will send
the
whole index across the wire, but not "the whole table" as some people
seem
to say, right? Then the front end will search the index and request the
records that match the WHERE clause, and then only those records will be
sent. Is that right?

If this is correct, then a lot less network traffic will be created, but
there will be a lot of searching of indexes: every time the user
'navigates'
to a new record a new query is run. So does that mean that searching
large
indices is faster than sending lots of data? Or does it depend on the
details of the network?

2) In the WHERE clause that limits the recordsource, does using "LIKE"
slow
things down a lot? For example, to get all customers whose last names
start
with "smi". How about if I do something like "SELECT top 10 ... order
by
myIndexedField" ? Will that be a lot slower than limiting records with
'WHERE'?

3) For subform recordsources, I usually use saved queries with "WHERE
myfield = forms!parentform.control_bound_to_PK_field". Is this a good way
to
go, and is it a lot better than using the "link master/child" fields
properties, for the same reason as in (1) above?

Thanks very much in advance. This newsgroup is wonderful!
-john