ADODB vs ADO.Net or protocol difference.

[Giedrius]

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.

 

[Robbe Morris [C# MVP]]

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/micr...00819?lnk=st&q=&rnum=2&hl=en#5eca15109b700819

 

[Giedrius]

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]]

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]

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.