Where does filtering take place? Server or Client?

  • Thread starter Thread starter XMan
  • Start date Start date
X

XMan

I'm opening a report with a filter query:

DoCmd.OpenReport "rptCustomer", , , strWhere

Does this filter of strWhere query take place at server or client machine?
This application is running against SQL2K on NT Server.

TIA.
 
It takes place on the client. Pass through queries and
stored procedures are the only ones that take place on the
server.


Chris Nebinger
 
No wonder since I upgraded to SQL2K, my Access2K has been running like a dog
over a very heavy network traffic.

What can I do here to make this app faster? Move queries to stored
procedures?
 
Chris Nebinger said:
It takes place on the client. Pass through queries and
stored procedures are the only ones that take place on the
server.
Click to expand...

That is incorrect. A Passthrough or Stored Procedure *guarantees* all
processing is done on the server. This does not mean that any query or
filter that is not a PT or SP is processed entirely on the client. In fact
the vast majority of the time the SQL is still passed to the server for
most of the processing. What percentage takes place on the server versus
the client varies depending on the complexity of the query, the number of
joins, whether VBA or custom functions are involved etc..
 
XMan said:
I'm opening a report with a filter query:

DoCmd.OpenReport "rptCustomer", , , strWhere

Does this filter of strWhere query take place at server or client machine?
This application is running against SQL2K on NT Server.
Click to expand...

Well, the client (jet) in this case will pass the sql to the server.

Only 1 record will be returned if a index is able to be used.

So, sql server, or a mdb file share..only that 1 record is sent.

If your report is based on linked tables, then of course you have a
performance problem..but that problem is not the fact of the "where" clause.
if the sql in your report has joins (more then one linked tabled), then
simply change that reports data source to a view. Then, the above "where"
clause will run just fine. It is not the where clause that is killing
you..but the use of MORE THEN ONE linked odbc tables. Change it to a
view..and things should perform fine.
 
Back
Top