Why do my queries take so long to run

I have inherited a MS Access database which connects to tables on a server at a different location.  I am finding that queries take a very long time to run.  Here are some questions:

Is there a name for this kind of configuration?
What are the inefficiencies of this type of configuration ?
What are my options for improving on it ?

Thanks.
ParaGlowAsked:
Who is Participating?
 
Patrick MatthewsConnect With a Mentor Commented:
I'm guessing that your queries are not set as pass-throughs.  Pass-through queries execute on the server, and as such you will probably see substantial improvement.

You should also have your DBA make sure that indexes are optimized in your back-end database.
0
 
ParaGlowAuthor Commented:
No, the queries are not set as pass-through queries. If I can ask a further question, how do I set the queries to be pass-through ?
0
 
ParaGlowAuthor Commented:
ok I just googled it.  Looks like I should be sending SQL statements to the server on an ODBC connection.
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
Dale FyeConnect With a Mentor Commented:
Para,

You indicate that your tables reside on a "server server at a different location".  Do you mean that you are using an Access backend, on a file server, or are you using SQL Server?

Pass-through queries are for SQL Server (or other ODBC data connections), not for Access backend files.

Access does not generally (unless you have a really fast connection) work well over a WAN, with linked tables.  There are exceptions to this but most companies do not have enough bandwidth and sufficient speed to operate over a WAN.  Here is a link to a very old, but still applicable article on using Access over a WAN
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Connect With a Mentor Database ArchitectCommented:
"Access does not generally (unless you have a really fast connection) work well over a WAN, with linked tables"
:-)

" There are exceptions to this but most companies do not have enough bandwidth and sufficient speed to operate over a WAN. "
I really do not agree. This is 2013, not 2003. Many companies have this capability, including mine,

" Here is a link to a very old, but still applicable article on using Access over a WAN"
Please do not read that article.  It is antiquated and outdated, compared to what CAN actually be done with today's systems and networks.  Any doubters should visit my work location her in So Cal ... and be enlightened as to what Access is really capable of.

mx
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor Infotrakker SoftwareCommented:
This is 2013, not 2003. Many companies have this capability, including mine,
And most do not, so the information in the link by fyed is relevant. I don't know how much outside consulting you're doing, but with only a few exceptions, every situation I run across with remote users does NOT have that big and wide WAN, and they experience troubles when they try to run their Access apps across that WAN.

Para: UNLESS you're working with a fast, wide and stable WAN, the suggestions of the other Experts are quite relevant.

Indexing can also be a factor in query time. In general you should index any field involved in a JOIN or WHERE clause, fields used when searching, etc. Indexing is as much art as it is science, so you may have to play around with your indexes before they are correct.
0
 
Jerry_JusticeConnect With a Mentor Commented:
I would suggest pinging the remote server.  Responses to SQL queries on a local LAN should take a few milliseconds for a small query.

If the ping is 20 ms or higher, you will never get acceptable responses to queries.  I had a client trying to run a program that did hundreds of SQL queries in a row over a WAN with 45 ms responses!

45 ms is ok for opening a spreadsheet or a Word doc, but not for queries.
0
 
PatHartmanConnect With a Mentor Commented:
Let me clarify pass-through for you.  Access natively makes every effort to pass through ALL queries so in most cases you don't need to convert your native queries to pass-though queries.  When you run a querydef against a linked SQL Server table, Access sends the entire query (or as much of it as it can) to the server for execution.  That means if you are requesting a single row out of a million, the query will run on the server and bring back only a single row.  There is much confusion regarding how Access works with ODBC data sources and this is one of them.  Where you run into trouble is with functions.  SQL Server (and other RDBMS') don't know anything about VBA and so they cannot process VBA or UDF functions.  That means that the ODBC driver needs to pull these functions out of your query before sending it off to the server.  If the functions were in the Select clause, no harm, no foul.  The functions will be applied to the resultset when it is returned from the server.  If the functions are used in the where/having clause or affect aggregation, they could force Access to send the query without any selection criteria or only partial criteria and thereby ask the server to send lots of data back.  Keep in mind, that a pass-through query won't solve this problem.  To solve this problem, you need to recreate the function on the server in T-SQL.

That said, pass-through queries do have a place and that place is bulk updates.  Because Access wraps all updates in a transaction (remember that message you get when you run them from the GUI), it gives you the option of cancelling.  This causes huge overhead when the BE is not Jet/ACE so in those cases, I use pass-through queries which are not cancellable.  That's what you give up.

I find that the easiest way to speed up queries is to create server-side views and then link to the views.  Views can be updateable but you need to be aware of how to make that happen.  Access requires a primary key or unique index on all ODBC tables or it will not allow updates, period.  When you link a view, Access gives you a dialog that asks you to choose a unique index.  Choose wisely and make sure your view includes the PK's of ALL joined tables.  You need to select the PKs from each table in the view to ensure that each row will be unique.  When you relink the tables, you loose this pseudo key and will need to rebuild it.  Therefore, I always create ALTER DDL statements as querydefs that I can run automatically when I relink the view.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Connect With a Mentor Database ArchitectCommented:
My point about that article, written 10 years ago - and I should  have clarified - is that it gives you the impression that you *can't* and *should not*  run Access over a WAN. Wherein that does apply in many cases, it does not apply in *all* cases.

Yes, not every company has a 1Gb fiber optic WAN. But when you do have access to a stable WAN of this nature, it opens up an entirely new whelm of what can be done with Access.

Five years later, over 35 mdbs in daily operation with 1 to 120 simultaneous users and zero instances of back end (mdb) data corruption ... just sayin' ....

mx
0
 
hnasrConnect With a Mentor Commented:
Linking to a server is necessary for data management.
If updating data, let server do that, and that is through pass-through queries.
Pass-through query is used if you want to retrieve few values locally.
To locally work on data from joined tables, retrieve data from a view on the server joining the relevant tables
0
 
PatHartmanConnect With a Mentor Commented:
@hnasr,
Native Access queries that get data from SQL Server linked tables are AUTOMATICALLY passed through to the server.  You do not ever need to create pass-through queries in T-SQL to make this happen.  Access passes through EVERY query as long as there are no elements that the server won't recognize.  For example, IIf() functions are converted to CASE, etc.  Most Access query functions have direct correlations with T-SQL functions.  VBA functions do not so VBA functions must be processed locally.  Access sends the rest of the query to the server and when the ONE row it asked for is returned, Access applies the VBA function on the resultset.  The only time Access can't send the where clause is if you use a VBA or UDF function in it.  Then Access must send the query without the where clause.  This issue cannot be solved by simply converting to a pass-through query.  You would also need to create the function on the server as a stored procedure.

To imply that only pass-though queries get passed through to the server is incorrect.

Native Access queries do have some additional overhead that pass-through queries don't have but it is not enough in most cases to justify converting to unbound forms just so you can use pass-through queries.
0
 
hnasrConnect With a Mentor Commented:
@PatHartman,

Thanks for info. Your comment is appreciated, although my comment did not imply that.

It is preferred to be explicit, especially when using a complex query.
0
 
ParaGlowAuthor Commented:
I have learned so much reading all the above comments.  In fact, I will be saving them to my hard drive (hope that is allowed !).  I now need my calculator to see how to divide the points equally.  Thanks again.
0
 
ParaGlowAuthor Commented:
The comments contributed by the experts are informative and shows depth of knowledge and experience.  And a willingness to share with a "beginner".
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
" In fact, I will be saving them to my hard drive (hope that is allowed !)."
You can Save them to your personal Knowledgebase right here on EE (Save on the left side menu). It's very cool and searchable.  I have 100's of Q's in my KB.

"And a willingness to share with a "beginner".
Keep asking questions here on EE and you will move out the the beginner pool in sort order :-)

mx
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.