Question on timing of filter in front-end/back-end database

S

Steve Schacher

This is a simple question that I'm sure has been asked before:

When applying a filter in the front-end, does the back-end apply the
filter before returning rows to the front-end, or are all the rows
returned to the front-end and then the filter is applied?

Steve
 
R

Rick Brandt

Steve Schacher said:
This is a simple question that I'm sure has been asked before:

When applying a filter in the front-end, does the back-end apply the
filter before returning rows to the front-end, or are all the rows
returned to the front-end and then the filter is applied?
Click to expand...

Depends on the back end. If you mean a Jet back end (an mdb file) then ALL
processing is done on the PC running the front end. The server is merely "a
remote hard drive". That doesn't mean that the whole back end or even the whole
table is brought over the wire (as some believe), but whatever is required to be
brought over is decided in the front end, then that is brought over and the
front end processes it.
 
V

Van T. Dinh

If you refer to Access FE and Access (JET actually) BE, then all processing
is dine on the user PC since JET is a file-sharing database, not
Client-Server database. That's why it is important to set correct Indices
so allow quick filtering. OTOH, I *think* JET is smart enough to bring down
only enough data for filtering and then bring down only enough selected
"rows" to fill the (active) data object. JET doesn't bring the whole
Table(s) down in one go.
 
S

Steve Schacher

Thanks to both of you for your answers.

Yes, I did mean the Jet back-end. I was afraid that what you answered
is what was happening. I was hoping that the filter would be passed
over as part of a WHERE clause on the query instead of a filter on the
answer set at the client.

When doing an OPENFORM, doesn't it have separate parameters for
filters and WHERE clauses (OPENFORM formname, viewname, filter, where,
datamode, windowmode, openargs), or am I misinterpreting something?
Wouldn't a WHERE clause be passed to the back-end as a part of the SQL
query?

Steve
 
V

Van T. Dinh

I wrote that Access/JET database is a *file-sharing* database. It is NOT a
Client-Server database. There is no back-end database engine to process
data and everything is done by the front-end database engine on the user's
PC.

Thus, even the Filter and the WhereCondition argument are processed by the
user's PC.
 
S

Steve Schacher

I just thought of another question:

If the back-end were not a Jet database, but were SQL Server, would
the front-end now be smart enough to pass the WHERE clause or would it
still want to process the WHERE on the client?

Steve
 
R

Rick Brandt

Steve Schacher said:
I just thought of another question:

If the back-end were not a Jet database, but were SQL Server, would
the front-end now be smart enough to pass the WHERE clause or would it
still want to process the WHERE on the client?
Click to expand...

Then the answer becomes "it depends". When using ODBC linked tables to a true
server back end any query you create against those links can range anywhere from
all processing being done on the server to all processing being done on the
client or anywhere in between.

Generally, the above setup will result in most of the processing being done on
the server, but the number of joins and the use of VBA functions (particularly
user-defined) or basically any function that the server doesn't understand can
cause more processing to be local.

With today's modern hardware, "processing" really isn't the issue that it once
was. What is of paramount importance is to get the server to do the selection
criteria so that the minimum amount of data is pulled over the wire.

SELECT blah, blah FROM SomeTable WHERE blah = 'SomeValue'

....would almost certainly process on the server. If the WHERE clause used
DateDiff or a UDF, then it might not. As always, correct application of indexes
will affect performance of the query, but should not affect whether it happens
on the server or the client. You can have an efficient query that has some
aspect of it that causes client processing and you can have a server processed
query that performs terribly simply because it's a badly designed query.

With a server based engine you can guarantee that all processing is done on the
server by using a PassThrough query or a Stored Procedure. You'd be surprised
though just how infrequently that is really necessary.
 
S

Steve Schacher

Thanks, Rick. Now we're getting to where I want to go.

I have an application that is deployed to many people in San Ramon CA
(primary location), Concord CA (10 miles away), Houston TX, and
possibly Manila and Bangalore, over my company's internal WAN/LAN. The
main table only has about 300 rows in it today. There are many
dependent tables. The forms were mostly generated by wizards based on
normalized tables, and then customized slightly. Most of the queries
are constrained by key value = XXX, or unique secondary index = YYY,
or non-indexed column = ZZZ, although one uses switch(). The forms
have subforms that are linked via key fields. It may be that the main
form passes its WHERE clause to the server, but I doubt that the
subforms would constrain on the server based on the main forms
returned key.

Ultimately, I was looking for strategies that might reduce the traffic
from the back-end to the front-end so that the application might
perform better over long distances. I didn't know if it was worth the
effort to prototype a SQL Server back-end if the Access front-end
wouldn't really benefit from the sophistication.

Steve
 
R

Rick Brandt

Douglas J. Steele said:
You may want to read what Albert Kallal has at
http://www.attcanada.net/~kallal.msn/Wan/Wans.html

I think you'll find you really don't want to use Access across a WAN,
regardless of how fast it may be.
Click to expand...

Agreed. A properly designed Access app with an MDB back end works
reasonably well over a 10mb LAN and can work very well over a 100mb LAN,
but over a WAN (even a really good one) you have speed that is only a
fraction of the worst LAN setup and you are practically guaranteed
performance that is unusable and lots of file corruptions.

With a server back end you can at least remove the corruption issue, but
you would still need a highly optimized application to get good performance
and you would have to limit the scope of what the front end would even
attempt. Regardless of where it is processed there are some reports or
queries you might want to run that simply require lots of data to be pulled
over the wire and over a WAN connection you might have to wait several
minutes for such activities to finish whereas the same operation on a LAN
would take several seconds.
 
Top