Giedrius
Mon Jul 24 02:00:28 CDT 2006
Actually it does not matter if it has (result is one row), just try reading
my first post:
adodb vs ado.net executing the same stored proc, with the same parameters,
performance difference is so huge that I cannot find any explanation why
there should be any.
"Robbe Morris [C# MVP]" <info@eggheadcafe.com> paraðë naujienø
praneðime:eQn%23LxorGHA.4324@TK2MSFTNGP03.phx.gbl...
> Does Payment_Date have an index?
>
> --
> Robbe Morris - 2004-2006 Microsoft MVP C#
> Earn money answering .NET questions
>
http://www.eggheadcafe.com/forums/merit.asp
>
>
>
>
>
> "Giedrius" <unknown@spam.lt> wrote in message
> news:eq6EtmirGHA.5028@TK2MSFTNGP04.phx.gbl...
>> 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@eggheadcafe.com> paraðë naujienø
>> praneðime:%23EiGt%23YrGHA.4424@TK2MSFTNGP05.phx.gbl...
>>> 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@spam.lt> wrote in message
>>> news:uDPIoT9qGHA.3604@TK2MSFTNGP02.phx.gbl...
>>>> 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.
>>>>
>>>
>>>
>>
>>
>
>