|
|
Posted by sqlgirl on 07/16/07 22:03
Hi,
We are trying to solve a real puzzle. We have a stored procedure that
exhibits *drastically* different execution times depending on how its
executed.
When run from QA, it can take as little as 3 seconds. When it is
called from an Excel vba application, it can take up to 180 seconds.
Although, at other times, it can take as little as 20 seconds from
Excel.
Here's a little background. The 180 second response time *usually*
occurs after a data load into a table that is referenced by the stored
procedure.
A check of DBCC show_statistics shows that the statistics DO get
updated after a large amount of data is loaded into the table.
*** So, my first question is, does the updated statistics force a
recompile of the stored procedure?
Next, we checked syscacheobjects to see what was going on with the
execution plan for this stored procedure. What I expected to see was
ONE execution plan for the stored procedure.
This is not the case at all. What is happening is that TWO separate
COMPILED PLANs are being created, depending on whether the sp is run
from QA or from Excel.
In addition, there are several EXECUTABLE PLANs that correspond to the
two COMPILED PLANs. Depending on *where* the sp is run, the usecount
increases for the various EXECUTABLE PLANS.
To me, this does not make any sense! Why are there *multiple* compile
and executable plans for the SAME sp?
One theory we have is, that we need to call the sp with the dbo
qualifier, ie) EXEC dbo.sp
Has anyone seen this? I just want to get to the bottom of this and
find out why sometimes the query takes 180 seconds and other times
only takes 3 seconds!!
Please help.
Thanks much
Navigation:
[Reply to this message]
|