Greatly different executing times for EXACT same query

  • Thread starter Thread starter Chris Aitchison
  • Start date Start date
C

Chris Aitchison

Hello,

I have two tables that I am needed to generate reports from, one has 10
million rows+ and the other has less than 200,000. These number of rows
will be increasing fairly steadily over time.

I have some queries that do counts and group bys along with some sums on
these two tables and join them based on what the query requires. I have
these queries running very quickly inside the query analyzer and have let
the index tuning wizard decide on the best indexed views and table indexes.
These indexes and views dramatically reduced the query times down to less
than a second.

However, these queries are called from a .net web application and when they
are called through the sqlconnection objects inside this web app the
execution timesout. I can copy the EXACT same t-sql code that timed out
into the query analyzer and they run quickly again.

The queries are all inside stored procedures, and to test I have been
calling them with exactly the same parameters from the web app and the query
analyzer.

Has anyone got any ideas as to why this is happening? I assume the
execution plan is not calculating correcty when it is being called by the
web application however this is very hard to confirm because as soon as I
paste the t-sql from the profiler into query anaylzer it runs quickly again.

Many thanks for any help that can be given on this issue,

Kind regards,
Chris Aitchison
 
Hi Chris,

The reason it runs quickly the second time is most of the data is likely
already in the cache.

To see if the query will be performed the way you expect, you could paste it
into Query Analyzer and use the "Display Estimated Execution Plan" option to
find out.

Sounds like you may also need to check your indexing strategy and/or
increase your command & asp timeouts.

HTH,
 
I listened to Kimberly Tripp (the SQL goddess) this week at the Dev
Connections conference and was reminded that this (fairly common) complaint
is often because SQL Server is using a cached query plan. When SQL Server
executes a stored procedure it checks to see if the query plan is in cache.
If it's not, the SP is recompiled and the plan is cached. The plan is
generated based on the parameters passed to the SP so when you execute the
SP again with different parameters, the optimal plan might not be the one in
the cache--but it's used anyway. To address this problem, Kimberly (and I)
suggest you add WITH RECOMPILE to the SP so it's recompiled each time it's
executed. If this works, it might make sense to simplify the SP so that it
can be executed efficiently regardless of the parameters passed to it.

hth
--
____________________________________
Bill Vaughn
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
Bill,

I have tried quite a few things since posting my message for help, and one
of them was using the "with recompile" syntax on the stored procedures used
for this query. Unfortunately it made no difference to the execution time
when being called from the web app compared to the query analyzer. It is
still a few seconds compared to 30 secs+

This was also happening when I tried to run the query using execute_sql
instead of a sproc.

My execution plan when run through the query analyzer shows what I would
expect - that is an index scan on the indexed view I have created for this
commonly used query type.

I have run out of ideas to try, so any more help or pointers would be
greatly appreciated.

Kind regards,
Chris Aitchison
 
Hello Greg,

I have been playing with this query all day, trying the "with recompile"
option and also index tuning and so forth.

I am not sure that it is running faster because the data is in the cache,
because if I run the page twice in quick succession with an extended
time-out it still takes ages to load (more than 30 secs)

My indexes are based on what the index tuning wizard suggested, and have
really got a query that draws data from millions of rows to execute in under
a second. Would this still suggest a badly designed index?

My execution plan is what I would have expected as it shows an index scan on
my created index on the fast executions of the query. I can not tell what
the execution plan is of the slowly running query because I do not know of
a way to reproduce the issue where I can generate the execution plan.

Thank you for your reply!

Kind regards,
Chris Aitchison
 
Chris,

Just a shot in the dark ... but I have seen mysterious stuff like this
happen if your statistics are bad. Try rebuilding the statistics on the
involved table.

True, it shouldn't matter about the transaction, but maybe the transaction
causes the optimizer to take a different path to the solution for some
reason, and that path is working from bad assumptions.

--Bob
 
Back
Top