ADP vs QA Queries

  • Thread starter Thread starter Ruggiero Lauria
  • Start date Start date
R

Ruggiero Lauria

I have really a strange issue.
Same Query "select......" runned via ADP goes in timeout, from profiler i
saw it took about 30000 ms, runned under Query Analyzer took 300 ms!
Any Idea, I have a serious performace issue.
thanks
Ruggiero Lauria
 
The most likely explanation is a use of bad query plans because the
statistics are out of date or the first times the SP are called - and
compiled -, they are with atypical parameters. The first step 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, if you still have problems, you can think about reindexing and -
very important -, you can
take some precautions about the use of atypical parameters and
recompilations in order to eliminate the reuse of bad query plans. See:
http://sqlknowledge.com/index.php?op...d=65&Itemid=41

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

Good article on the use of the With Recompile option and of intermediary
variables in order to deactivate the parameters sniffing from SQL-Server
(the use of intermediary variables is probably better than to use the With
Recompile option because recompiling big procedures take time):
http://blogs.msdn.com/khen1234/archi...02/424228.aspx . (It's useless to use
both technics at the same time).

If 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; see: http://www.sql-server-performance.co...guides_p1.aspx

and: http://www.sql-server-performance.co..._hints_p1.aspx

Finally, here's a good reference on recompilation:
http://www.microsoft.com/technet/pro...05/recomp.mspx
 
We reindex once a week and update statistics every night, my be could
reindex some more, but the question is how is possible some query runs so
different in Access and QA enviroment, at the same time?
I suppose they should use the some query plan, or not?
Thanks again
Ruggiero Lauria
 
Than you are probably hit by the parameter sniffing problem. Add the WITH
RECOMPILE option as a quick way of testing this if you are using a SP.

If you are using a simple sql string and not a SP, use the EXEC statement
with the WITH RECOMPILE option for executing your select statement (or use
prepared statements by making different preparations). BTW, I'm not sure
about this one because it has been a long time since I used this option with
the EXEC statement.

The Access and the QA environments behave differently, don't expect them to
work in the same way and no, you cannot suppose that they will necessarily
(re-)use the same query plan. If they were using the same query plan, you
would got the same result in both cases.
 
Back
Top