Queries in back end?


S

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
 
Ad

Advertisements

A

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
 
Ad

Advertisements

K

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top