Solved

Why do my queries take so long to run

Posted on 2013-12-12
15
1,909 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
ID: 39715626
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
ID: 39715634
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
ID: 39715647
ok I just googled it.  Looks like I should be sending SQL statements to the server on an ODBC connection.
0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 50 total points
ID: 39715667
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
ID: 39715835
"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
ID: 39716261
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
ID: 39716673
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
 
LVL 35

Assisted Solution

by:PatHartman
PatHartman earned 100 total points
ID: 39717005
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
ID: 39717381
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
ID: 39717669
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 35

Assisted Solution

by:PatHartman
PatHartman earned 100 total points
ID: 39717881
@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
ID: 39717982
@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
ID: 39718819
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
ID: 39718823
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
ID: 39718976
" 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

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…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

777 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