Invoking Stored procedure from ADO.NET is slow

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.
 
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
 
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
 
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.
 
Back
Top