Re: Just for kicks, try this

Thanks, I'll keep this in mind.
But it is not my case, because after analyzing the sp, I've found that
commenting/uncommenting out a simple ORDER BY Payment_Date, changes
execution time back to normal/slow. The sp queries data from 2 tables, in
one table Payment_Date cannot be null, in the other it can, the problem is
then it must get data from the second.


"Robbe Morris [C# MVP]" <info@xxxxxxxxxxxxxxx> paraðë naujienø
praneðime:%23EiGt%23YrGHA.4424@xxxxxxxxxxxxxxxxxxxxxxx
I've run across this problem twice this week and found
an obscure answer to it from a sql server mvp on
google groups.

In your stored procedure, do the following even
though it seems illogical:

CREATE PROCEDURE dbo.MyProc
(
@MyInputParameter bigint
)
as

declare @TmpMyInputParameter bigint

set @TmpMyInputParameter = @MyInputParameter

select tableName.*
from dbo.SomeTable
where SomeColumnID = @TmpMyInputParameter

You may find that your stored procedure runs much, much
faster now. I found this to be the case twice this week
both with simple stored procedures and more complex
ones. There is no "logical" reason why a developer
would ever create a variable for the sole purpose
of populating it with an input parameter. I'd have
never even considered this.

Bob Barrows talks about this:

http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_thread/thread/1f2b3d2e12d4b4ad/5eca15109b700819?lnk=st&q=&rnum=2&hl=en#5eca15109b700819



--
Robbe Morris - 2004-2006 Microsoft MVP C#
Earn money answering .NET questions
http://www.eggheadcafe.com/forums/merit.asp





"Giedrius" <unknown@xxxxxxx> wrote in message
news:uDPIoT9qGHA.3604@xxxxxxxxxxxxxxxxxxxxxxx
Hi,
I have 2 apps doing the same job, one is older and using adodb to query
sql server 2005, another is using ado.net. Something has happened and now
app using ado.net execution time of some stored procedures has increased
about ~20 times from <1s to 20s in comparison with older app.
The older app is working fast as usual. By using SQL Profiler the only
difference between adodb app and ado.net app is a Sort Warning for
ado.net.
The stored procedure is quite complex, but is fast enough using adodb or
SQL Management Studio. Database is for development purpose, takes ~10GB
but is not growing and does not have many operations. I know that
restarting SQL Server would help (because it helped before). But I want
to know why such problems occur or how to solve it without restarting
server?

Thanks for any answer.





.

Relevant Pages

  • Re: Just for kicks, try this
    ... Robbe Morris - 2004-2006 Microsoft MVP C# ... You may find that your stored procedure runs much, ... sql server 2005, another is using ado.net. ... increased about ~20 times from <1s to 20s in comparison with older app. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Timeout in .NET but not in Management Studio
    ... the ARITHABORT setting to make the handling of each row different. ... I invoke the code using the SQL Server local to my development machine. ... I invoke the Stored Procedure from SQL 2005 Mangement Studio. ... was 1 second and when it was OFF the execution time was 47 seconds. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: MSDE Slow in executing Stored Procedures
    ... App that creates an ADO connection object and execute a simple ... stored procedure and its equivalent insert statement. ... MSDE 7.0) vs. SQL Server 2000. ... The execution plan looks the same. ...
    (microsoft.public.sqlserver.msde)
  • Re: Stored procedure doesnt return dataset unless recompiled
    ... Have you changed any of the objects called by the stored procedure? ... SQL Server will not allow you to change the ... "Dewey" wrote in message ... >I have a web app that calls a SP. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Just for kicks, try this
    ... You may find that your stored procedure runs much, ... I have 2 apps doing the same job, one is older and using adodb to query ... sql server 2005, another is using ado.net. ... increased about ~20 times from <1s to 20s in comparison with older app. ...
    (microsoft.public.dotnet.framework.adonet)