Slow response from MS access report

[MC]

Hi,
We use MS Access form (2000) to post records to SQL 7 database, and at the
end it previews report for posted transaction.
For some reason, running this access form to post transaction and preview
report, takes longer each day. When I look at the SQL tables transaction
seem to be done already, but the issue (seem to be) preview of the report
takes from 5-10minutes

record on some of the SQL tables this form updates are close to 100K rows
(about 120Meg)
ovearall SQL database file is about 8G

My question is, why running access form (would normally be functioning fine)
is becoming slow each day. Would data size on SQL server or table have
anything do where ODBC connections and Access can't handle?
(SQL server has too much storage and memory, performance logs show normal)

Any clues or suggestions are appreciated (we use symantec AV, I have
exclusion from real time scan of MDB, LDB, MDF, LDF extensions, just in
case, AV interference)

MC

 

[Sylvain Lafontaine]

Access 2000 and SQL-7: this begin to be a little old; so what I'm saying
here might be totally false. Second, this newsgroup is about Access ADP and
as such, has nothing to do MDB database file and ODBC Linked tables. I have
no idea if you are using passthrough queries against some stored procedures
or not; if not, drop anything that is not relevant to your situation. I'll
start by a general description of ADP problems but in your case, probably
that you are hurt by a full table scanning problem from Access; so you
should take a look at the end of my post.

General post:

From your description, this looks to me as a bad query plans because the
statistics have not been updated since a long time or the procedure cache is
not cleared fast enough or because you are hurt by the parameter sniffing
problem of 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

After that, reindexing everything would be a good idea.

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
or that become obsolete after some time. Usually, SQL-Server should take of
obsolescence but 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 if you have atypical parameters. You could also
rewrite your SP in order to keep these atypical parameters out of your query
plans. The use of intermediary variables is probably better than to use the
With Recompileoption because recompiling big procedures take time. Also, you
don't have to use both.). Here are some references on these topics:

http://sqlknowledge.com/index.php?op...d=65&Itemid=41

http://blog.sqlauthority.com/2007/01...ics-on-tables/

http://blogs.msdn.com/khen1234/archi...02/424228.aspx

http://www.microsoft.com/technet/pro...05/recomp.mspx


Exclusive post:

The last problem - and quite probably the one that might be hurting you
here - is the way Access is retrieving the data. It should only retrieve
that data that it needs but in many situation, it will retrieve the whole
table(s) in order to perform some local calculation; for example if you use
an Access function in a WHERE statement. When this happens, the performance
start dropping like a rock into thin air. You must be very careful about
the way you are writing your stuff but one tool that can easily show you if
this happens is the SQL-Server Profiler. This tool can show you the queries
that Access is sending to the SQL-Server. Even when you don't really know
how to use this tool, it's pretty easy to use when all you want to do is to
take a look at these queries and this way, you'll quickly see if Access is
making a full table scan when you think it shouldn't do that. You can also
spot other problems like an excessive number of requests.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[Tony Toews [MVP]]

We use MS Access form (2000) to post records to SQL 7 database, and at the
end it previews report for posted transaction.
For some reason, running this access form to post transaction and preview
report, takes longer each day. When I look at the SQL tables transaction
seem to be done already, but the issue (seem to be) preview of the report
takes from 5-10minutes

Chances are very good that some of the sorting/sequencing or criteria
fields in the report aren't indexed. Even indexing boolean fields can
sometimes make a difference.

Note that the more indexes added the longer table inserts/update can
take. Although this may not be noticeable much on SQL Server.

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/
Granite Fleet Manager http://www.granitefleet.com/

 

[MC]

As far as we see, the problem is not the query.
The same query, and same report has been running fine for years nothin has
changed.
(The only thing keep changing is the data)
When we run this form, transaction happens in SQL server, then data gets
copied to local access table, for report to use (2 seconds)
The problem is the report to preview this data from local access table,
there are page formatting on that report seem to be crazy slow. It was
working fine before. Other reports in the same access database ok


MC