[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Why do my queries take so long to run

Posted on 2013-12-12
15
Medium Priority
?
2,639 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +5
15 Comments
 
LVL 93

Accepted Solution

by:
Patrick Matthews earned 200 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 48

Assisted Solution

by:Dale Fye
Dale Fye earned 200 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 - Microsoft MVP, Access and Data Platform)
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 400 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 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 200 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 200 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 39

Assisted Solution

by:PatHartman
PatHartman earned 400 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 - Microsoft MVP, Access and Data Platform)
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 400 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 31

Assisted Solution

by:hnasr
hnasr earned 400 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 39

Assisted Solution

by:PatHartman
PatHartman earned 400 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 31

Assisted Solution

by:hnasr
hnasr earned 400 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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

This article helps those who get the 0xc004d307 error when trying to rearm (reset the license) Office 2013 in a Virtual Desktop Infrastructure (VDI) and/or those trying to prep the master image for Microsoft Key Management (KMS) activation. (i.e.- C…
Microsoft has changed the look and feel of Azure AD and Microsoft account sign-in pages so that you will have a more unified look and feel when moving between the two interfaces.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
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…

656 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