Performance across a LAN

[Bob Alston]

I know that it is best to limit the amount of data you bring across the
LAN, from BE database to front end.

If I have an app with 10000 records in the BE database:

Is it equally efficient to use a query - based on the table - and a
selection clause in the form

or

use the table as the record source - and a selection clause in the form?

Or should the selection criteria be in the record source definition?

Or something else?

Bob

[storrboy]

On Feb 15, 1:51 pm, Bob Alston <[e-mail]:

I know that it is best to limit the amount of data you bring across the
LAN, from BE database to front end.

If I have an app with 10000 records in the BE database:

Is it equally efficient to use a query - based on the table - and a
selection clause in the form

or

use the table as the record source - and a selection clause in the form?

Or should the selection criteria be in the record source definition?

Or something else?

Bob

I prefer to use a SQL statement as the record source. I find them
easier to manipulate than form-based filters. I would also think the
SQL would be faster then Access having to process filters and other
clauses to. Instead the SQL just gets passed to the Jet engine.

[Bob Alston]

storrboy wrote:

On Feb 15, 1:51 pm, Bob Alston <[e-mail]:

>I know that it is best to limit the amount of data you bring across the
LAN, from BE database to front end.

If I have an app with 10000 records in the BE database:

Is it equally efficient to use a query - based on the table - and a
selection clause in the form

or

use the table as the record source - and a selection clause in the form?

Or should the selection criteria be in the record source definition?

Or something else?

Bob

I prefer to use a SQL statement as the record source. I find them
easier to manipulate than form-based filters. I would also think the
SQL would be faster then Access having to process filters and other
clauses to. Instead the SQL just gets passed to the Jet engine.

Thanks. I was starting to think that way but found a Microsoft
knowledgebase entry that says:

"Save the SQL statement as a query

If the RecordSource property for a form or for report is set to an SQL
statement, save the SQL statement as a query and then set the
RecordSource property to the name of the query."

http://support.microsoft.com/kb/209126/

Bob

[Albert D. Kallal]

Well, the key concept here is to restrict records.

It makes no sense to open a form, and THEN filter it...

So, you can well base a form on the table, or query...and then use a "where"
clause to restrict records. The use
of a query on the table does not help in this case.

So, most important is to simply "ask" a user for the record they need to
work on, and then load the form to that one record. This approach tend to
work the best...
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com

[Bob Alston]

Albert D. Kallal wrote:

Well, the key concept here is to restrict records.

It makes no sense to open a form, and THEN filter it...

So, you can well base a form on the table, or query...and then use a "where"
clause to restrict records. The use
of a query on the table does not help in this case.

So, most important is to simply "ask" a user for the record they need to
work on, and then load the form to that one record. This approach tend to
work the best...

More info on what I do. I open up a record selection form with a
selection key equal to a record key that does not exist. the record key
is the primary key. so the form opens with no data.

I allow the user to enter the selection criteria for the user or users
desired. Options are last name, first name, and several others. From
that I dynamically create a where clause that I put in the form spec and
then refresh the query.

This seems to work well.

Previously I have using a query as the data source. I tried changing it
to the table itself and added a sorting clause in the form; that seems
slightly faster on my PC but hard to tell cause I am not testing it
across the network.

My selection criteria are indexed so there should be minimal reading of
the records just to select the record or not.

Anything I should be doing differently?

P.S. I did read again some of the performance stuff in this newsgroup.

I had already turned off the name autocorrect.

I need to set the subdatasheet property from auto to None

I checked and this query does not include a user defined function (which
can make it run 5x slower)

Thanks

Bob

[Bri]

Bob Alston wrote:

storrboy wrote:

>On Feb 15, 1:51 pm, Bob Alston <[e-mail]:

>>I know that it is best to limit the amount of data you bring across the
LAN, from BE database to front end.

If I have an app with 10000 records in the BE database:

Is it equally efficient to use a query - based on the table - and a
selection clause in the form

or

use the table as the record source - and a selection clause in the form?

Or should the selection criteria be in the record source definition?

Or something else?

Bob

I prefer to use a SQL statement as the record source. I find them
easier to manipulate than form-based filters. I would also think the
SQL would be faster then Access having to process filters and other
clauses to. Instead the SQL just gets passed to the Jet engine.

Thanks. I was starting to think that way but found a Microsoft
knowledgebase entry that says:

"Save the SQL statement as a query

If the RecordSource property for a form or for report is set to an SQL
statement, save the SQL statement as a query and then set the
RecordSource property to the name of the query."

http://support.microsoft.com/kb/209126/

Bob

Yes, that is more efficient because the query is compiled and optimized
when saved. However, if you start having lots of different saved queries
it can get confusing later on. Also, if you need to have variations on
the query (ie filtering data, applying criteria on the fly) then you
would either have to have a query for every possible filter value, or
use a parameter query (which isn't optimized since the value changes, so
you lose the advantage anyway) or filter the form. All of these cause
the optimization to be no advantage. Also, on modern equipment, the
overhead of optimizing an SQL string before it executes is so small you
won't notice it.

Bottom line; use what ever method makes the coding easier to use and
understand as the execution time saved by pre-optimization of a saved
query is extremely marginal. The MS KB articles include it since it used
to be significant on older PCs and it does give a small boost to speed,
but the other points they make in that article for improving efficiency
are a lot more important.

--
Bri