Timeout when applying a filter

[Yarik]

Hello,

We have an MS Access 2003 application (ADP). There is a view in SQL
Server 2000 database that takes quite some time to run (2-3 minutes or
so). When our application opens this view using DoCmd.OpenView
function, everything is fine. However, when user starts playing with
filtering on the resulting form, sometimes an attempt to apply a
filter results in "Timeout" error (and sometimes it even crashes MS
Access afterwards). This happens even with very primitive filtering
criteria - like ">0" for some numeric column.

Does anyone know why this might be happening and how it could be
fixed.

Perhaps it is somehow connected to the fact that the view itself is
very complex and takes a lot of time to open. By default,
DoCmd.OpenView could not open it at all, so we had to change the OLE/
DDE timeout option programmatically before invoking OpenView (see the
code snippet below). However, this trick helped only with initial
opening of the view; the timeout specified programmaticall does not
have any effect on the problem described above.

Please help!


Thanks,
Yarik.


P.S. FWIW, here is the code snippet demonstrating how the view is
being opened in our application:


Dim oldTimeout As Integer: oldTimeout = 0
Const sTimeoutOption = "OLE/DDE Timeout (sec)" As String


oldTimeout = Application.GetOption(sTimeoutOption)
Call Application.SetOption(sTimeoutOption, MyTimeout)


Call DoCmd.OpenView("MyView", acViewNormal, acReadOnly)


If oldTimeout <> 0 Then
Call Application.SetOption(sTimeoutOption, oldTimeout)
End If

 

[Sylvain Lafontaine]

The most likely explanation is the use of bad query plans on SQL-Server.
This first thing to do would be to update the statistics using the
sp_updatestats stored procedure (or use UPDATE STATISTICS is you want to
work on a more detailed
level) and clean the caches after that:

DBCC FLUSHPROCINDB
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

You should always make these steps (especially updating the statistics)
after upsizing a database. Reindexing everything could also be a good idea.
If this still doesn't work after that, then you should think about creating
and using Views or passthrough queries or to use ADO objects to call stored
procedures on the server.

In the case of SP, you must take care of not having the SP compiled with a
bad query plan because of the use of atypical parameters on the first call;
so you may have to use the WITH RECOMPILE option or to use intermediate
variables in order to deactivate the parameter sniffing procedure from
SQL-Server. (The use of intermediary variables is probably a better thing
than the use of the With Recompile option because recompiling big procedures
take time. Also, you don't have
to use both.). Here are some references on these various topics discussed
in this post:

http://sqlknowledge.com/index.php?option=com_content&task=view&id=65&Itemid=41

http://blog.sqlauthority.com/2007/0...abase-tables-and-update-statistics-on-tables/

http://blogs.msdn.com/khen1234/archive/2005/06/02/424228.aspx

http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx

As you are with SQL-Server 2005, you can also play with the OPTIMIZE FOR
option instead of deactivating the parameters sniffing or using the With
Recompile option:

http://www.sql-server-performance.com/articles/per/using_plan_guides_p1.aspx

and:
http://www.sql-server-performance.com/articles/per/new_query_hints_p1.aspx

Small things like always stating the schema (dbo) can also do a lot. A
review of the indexes used won't hurt your. By making sure that you have
all the required indexes is still the best way to improve the performance of
your sql-server.

Finally, personally, I never use the filters provided by Access because I
always been suspicious of them; particularly the client filters (by
opposition to the "server" filters which are sent directly to the SQL-Server
instead of being done locally by Access). You should take with the
SQL-Server Profiler to see what's Access is doing with these filters. I
won't be surprised if your problem is caused by their use. If you have a
complex query, I don't understand why you don't use a parameterized stored
procedure (SP). ADP has been created exactly for that, so that you could
use SP instead of Views and Filtered Views; like you must do too often with
ODBC linked tables.

 

[Sylvain Lafontaine]

Correction: as you are using filters on Access side, the most likely
explanation is not ncessarily the use of bad query plans on SQL-Server; you
should take a look with the SQL-Profiler to see what really happens between
Access and SQL-Server.