Solved

Why do my queries take so long to run

Posted on 2013-12-12
15
1,667 Views
Last Modified: 2013-12-14
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.
0
Comment
Question by:ParaGlow
  • 4
  • 3
  • 2
  • +5
15 Comments
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 50 total points
Comment Utility
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
 

Author Comment

by:ParaGlow
Comment Utility
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
 

Author Comment

by:ParaGlow
Comment Utility
ok I just googled it.  Looks like I should be sending SQL statements to the server on an ODBC connection.
0
 
LVL 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 50 total points
Comment Utility
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
 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Access MVP)
DatabaseMX (Joe Anderson - Access MVP) earned 100 total points
Comment Utility
"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
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 50 total points
Comment Utility
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
 
LVL 3

Assisted Solution

by:Jerry_Justice
Jerry_Justice earned 50 total points
Comment Utility
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 34

Assisted Solution

by:PatHartman
PatHartman earned 100 total points
Comment Utility
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
 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Access MVP)
DatabaseMX (Joe Anderson - Access MVP) earned 100 total points
Comment Utility
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
 
LVL 30

Assisted Solution

by:hnasr
hnasr earned 100 total points
Comment Utility
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
 
LVL 34

Assisted Solution

by:PatHartman
PatHartman earned 100 total points
Comment Utility
@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
 
LVL 30

Assisted Solution

by:hnasr
hnasr earned 100 total points
Comment Utility
@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
 

Author Comment

by:ParaGlow
Comment Utility
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
 

Author Closing Comment

by:ParaGlow
Comment Utility
The comments contributed by the experts are informative and shows depth of knowledge and experience.  And a willingness to share with a "beginner".
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
" 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

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now