Large Delays (only on some servers) for Queries of Linked SQL db's

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I have an Access 2003 .mdb/.mde that queries linked tables from a SQL db in
order to provide Access reports to the user. It also combines with the SQL
queries, data from a few very small tables within the Access db.

On most systems/servers/networks, this app works well. But there are two
systems/networks (one running Server 2003 and another running Windows 2000)
where the queries are unacceptably slow.

This is not simply a matter of database size. For example, one report
returns nearly 5000 records in <10secs at a "working" site, while the same
report returns only 48 records (correctly) in over 1.5 minutes at one of the
"problem" sites. So to clarify, the query is working properly at the
"problem" site, just painfully slow. No other problems or other performance
issues are reported on that network.

Any thoughts on what might be causing the problems, or on best ways to
troubleshoot would be very much appreciated.

Thanks,
Jeff
 
1. Are the tables properly indexed?

2. Are there differeneces in the number of records in the different databases?

3. Are you using pass-through queries to return the data or normal Access
queries? Pass-though queries are usually much faster.
 
Jerry,

Thank you for your time and your suggestions. Answers to your questions
follow. My biggest question remains, though...despite the improvements I
could make to performance as you indicate in #'s 1 and 3, shouldn't those
deficiencies affect all sites fairly equally, not just add minutes to 2 of
them?

1. The SQL tables are not mine, and are not currently indexed. There are
plans in the future to index them to improve performance.

2. There are differences in the # of records in the db's. But, I don't
believe this is causing the problems, since one of the problem db's is quite
small, and I have working sites with large db's.

3. I am not using pass-through queries, but will experiment with them.

Thanks again for your thoughts and any other ideas or information you may
have.

-Jeff
 
Hi All,

It's been a while, but thought you might want to hear the result of the
troubleshooting. Turns out it was very misleading because the two sites with
problems each had their own, very different, problems.

1) The site with the very small (but slow) db had some sort of configuration
issue. Never really did figure out what it was. But when I installed the MS
Enterprise Manager SQL tool, the problem magically disappeared.

2) At the other site, which had a larger db, it was simply the overhead of
Access causing problems. They were magnified because one table (which
happened to be a major component of my queries) was much larger than at any
other site. As soon as I moved the queries out into the SQL Query Analyzer,
or even into a pass-through query in Access, they were *much* faster.

My final solution was to move the queries into Stored Procedures in SQL, and
call them via Access pass-through queries.

Thanks again for all of the ideas.

-Jeff
 
Back
Top