Queries in back end?

[Stapes]

Hi

A competitor has told our customer that the database we supplied is
slow because the queries are on the client (front end) database and
not on the server (back end), there is a lot of work being done across
the network making the system much slower.

I have not come across this architecture in MS Access before, but am
familar with the use of Stored Proecdues in SQL

What do you think?

Stapes

 

[Allen Browne]

The competitor did not understand Access.

Although Access is a file server database (not a true client server
database), it is not dumb. JET (in the front end) is intelligent enough to
request only the primary key in some cases, only enough to fill the screen
before displaying, and so on. It doesn't have the power of stored procedures
(or views), but it's not dumb either. In fact, if you take a typical Access
form with lots of subforms, combos and list boxes, and try connecting it to
SQL Server linked tables instead without making other adjustments, it will
probably run slower than it did with JET tables.

Having said that, there are intelligent things you can do to make your
database more efficient. A starting point might be ShowPlan:
http://builder.com.com/5100-6388-5064388.html

Other suggestions:
http://msdn.microsoft.com/archive/d...MicrosoftAccessMicrosoftJetDatabaseEngine.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacbk02/html/odc_4009c15.asp
http://www.granite.ab.ca/access/performancefaq.htm

 

[Klatuu]

Allen's Answer is correct assuming your back end is an mdb or accdb if 2007.
If it is SQL Sever, then there may be some validity to the competitor's
statement; however, I have found in some cases that a pass through query is
faster than a view, but for action queries, you would get better performance
with stored procedures in an SQL environment.