MS ACCESS AND SQL SERVER 2000 ODBC TIMEOUTS
RALPHNOBLE
databases and constantly get ODBC timeouts while processing queries. Anyone
know what I can ask our technical folks to check for to solve this problem?
Thanks.
Steve Jorgensen
Whenever Access/JET (as opposed to using an ADP/ADO) submits a query
to a SQL Server back-end, it does so via ODBC, and the default time it
will wait for a result from any such query is 60 seconds. Anything
longer, and you get a time-out.
Possible causes:
1. The query is genuinely complex and legitimately required more than
60 seconds to do its job. If you were using a saved Access query or
temporary querydef for the operation, you can change the ODBC timeout
value. Otherwise, you can change the default ODBC timeout through the
options dialog.
2. The query was imporoperly written and takes too long because it's
not doing what it's supposed to at all such as a query of 2 tables
without join criteria, thus joining each row in the first table to
each row in the second (a cartesian product).
3. The query cannot be optimized well by SQL server either because it
was poorly written or SQL server just doesn't like that one. Try
rewriting the query a different way such as replacing a subquery with
a join and group/sum or vice verse.
4. When a transaction is in effect on the server, and another query
or transaction conflicts with that, it must wait for the transaction
to be committed or rolled back before it can complete. Long-running
transactions on the server can cause Access ODBC timeouts. One common
cause of this is Access error handlers that wait for user input while
leaving a transaction open. Error handlers should roll-back
transactions in progress -before- displaying an error dialog. Better
still, use stored procedures that start and commit or roll back the
transaction entirely within a single call to the server.
Sith Lord
<20021111135244...@mb-mv.aol.com>, ralph...@aol.com
(RALPHNOBLE) wrote:
Everything under the sun and a few things besides. :-)
How long is your ODBC timeout?
Do you think it's a reasonable amount of time for the query to finish?
Which program is it that executes the queries?
If it's Access, it's likely it's not doing it efficiently, it might be
dragging up complete tables from the server many times for one query
in order to perform a join. look a http://www.besty.org.uk/memory.htm
to see how inefficient Access can be on SQL Server if you're not
careful (not only is allocating 1GB RAM bad on a 64MB machine but the
amount of time needed to do that was insurmountable).
Are the queries running sub queries or complex functions for each row
returned? Or even each row in the table, e.g. functions on criteria
will do that for every row in a table even if only a few are returned
in the end.
If the queries are run directly on the server, have a look at indexing
in particular foreign keys, which aren't indexed automatically as they
are in Access when you define them.
Query Analyser is a great tool for optimising queries, it will show
you execution plans to see where most time is spent running a query
and can also do index analysis to determine if creating or
changing/deleting existing indexes will improve performance.
Profiler is also a good tool for analysing queries, you may see your
couple of line of SQL in Access be turned into thousands of stored
procedure calls.
--
Dim Salary As Currency
Dim Overtime As Double
Steve Jorgensen
wrote:
>Do you think it's a reasonable amount of time for the query to finish?
>Which program is it that executes the queries?
>If it's Access, it's likely it's not doing it efficiently, it might be
>dragging up complete tables from the server many times for one query
True, but not applicable. the ODBC timeout fails if one query from
the server takes too long. In this case, it would be the Access query
taking a vrey long time making many shorter queries to the server in
the process. The query might take an hour, but it won't generally
"time out".
>Query Analyser is a great tool for optimising queries, it will show
>you execution plans to see where most time is spent running a query
>and can also do index analysis to determine if creating or
>changing/deleting existing indexes will improve performance.
>Profiler is also a good tool for analysing queries, you may see your
>couple of line of SQL in Access be turned into thousands of stored
>procedure calls.
Doh! Yes, of course. Make sure there are appropriate indexes on the
tables. This doesn't just affect how well the server-side query will
run but also whether Access/JET will decide to have the server process
a given query at all. If Access doesn't see appropriate server-side
indexes, it may decide to do all the work locally - I'm not sure why.
Also, after changing indexes on server tables, re-link them from
Access so Access is aware of the new indexes.