Invoking Stored procedure from ADO.NET is slow

[Guest]

I have this stored procedure on a SQL 2005 database that when invoked from
query analyzer or SQL Server Management studio runs in 2 seconds.
However, the following code times out on the executeScalar call:

DbCommand cmd = cnn.CreateCommand();
cmd.CommandText = "spc_search_items_count";
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandTimeout = UtilFuncs.DbScaledTimeout(45,
SysTimeoutScalars.Items);

//-- Create parameters --//
DBProvider.CreateParam(cmd, "vchSearchString",
DbType.AnsiString, srchString);
DBProvider.CreateParam(cmd, "intUserID", DbType.Int32,
nUserID);
DBProvider.CreateParam(cmd, "intBatchID", DbType.Int32,
nBatchID);
DBProvider.CreateParam(cmd, "intStatusID", DbType.Int32,
nStatusID);
DBProvider.CreateParam(cmd, "intCategoryID", DbType.Int32,
nCategID);

cnn.Open();

int nCount;
object obj = cmd.ExecuteScalar();
if(obj != null)
nCount = Convert.ToInt32(obj);
else
nCount = 0;

When monitoring SQL Server I see the process ID using just CPU and the
details say: TTT.dbo.spc_search_items_count;1

Does anybody have any advise of where the problem could lie? I have never
had this happen before; where invoking something through ADO.NET is any
different than invoking the stored procedure through other means.

 

[Guest]

Hi,
The first is that if you are connecting to SQL Server from ADO.NET, you
should be using SqlClient instead of OleDb.Benefits of SQLClient over oledb
are:
1) SQLClient certainly is faster than OLEDB; the Emperor has clothes
after all
2) SQLClient has a larger managed heap footprint
3) A larger managed heap footprint does not necessarily mean a slower
performing application: in this case, it means the opposite (but this isn’t
always the case – try a String vs StringBuilder example)
4) The CLRProfiler is a powerful tool for inspecting your
application’s internal behaviour
For more details refer below link:
http://codebetter.com/blogs/grant.killian/archive/2003/08.aspx

 

[Guest]

Great! Well the first thing you mention is already taken care of. I am
using SqlClient.
I will try the CLR profiler on the client and on the server. I really
believe the problem lies on the server though because I have the same exact
database running locally and it runs just fine when connecting and querying
against that instance.

Thanks,

Chris

 

[Guest]

I would start by executing the exact same procedure call in a query window.

 

[Guest]

I did this. It only takes 2 seconds. That is what is baffling.
It is only slow when called through ADO.NET.
Another datapoint is that this stored proc is only returning a count so
there should not be a wire issue with regards to the size of the data being
returned.