CODEWITHSUNDEEP
c#sql-server
CodeMan03
CodeMan03

Reputation: 226

Execute Reader Command is slow

I am trying to run a stored procedure in SQL Server from C#. The stored procedure runs fine in SSMS. It takes 45 seconds to pull these records. However in C# the same call gets a timeout error. What could be the issue here? The XML is a list of Primary Keys from a table in a grid in C#. So it could have 1 line or hundreds of lines. The stored procedure parses it out fine and inserts into a temp table with other data.

SqlConnection connection = new SqlConnection(connectionstring);
ce.Database.Initialize(force: false);

connection.Open();

SqlCommand cmd = new SqlCommand("usp_GetAllAccounts", connection);
cmd.CommandType = System.Data.CommandType.StoredProcedure;

cmd.Parameters.AddWithValue("@LoanList", DBNull.Value);
cmd.Parameters.AddWithValue("@LoanIdFlagBit",detailModel.activeFlag);
cmd.Parameters.AddWithValue("@DataDtFrom", detailModel.fromDataDt);
cmd.Parameters.AddWithValue("@DataDtTo",detailModel.toDataDt)
cmd.Parameters.AddWithValue("@EffDtFrom",detailModel.fromEffDt)
cmd.Parameters.AddWithValue("@EffDtTo", detailModel.toEffDt)
cmd.Parameters.AddWithValue("@JournalDetailId", detailModel.JournalDetailId)
cmd.Parameters.AddWithValue("@JournalDetailIdList", journalDetailList).DbType = DbType.Xml;

SqlDataReader reader = cmd.ExecuteReader();

My stored procedure code is this:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

    @LoanList XML,
    @LoanIdFlagBit BIT = 1, 
    @DataDtFrom SMALLDATETIME = NULL,
    @DataDtTo SMALLDATETIME = NULL,
    @EffDtFrom SMALLDATETIME = NULL,
    @EffDtTo SMALLDATETIME = NULL,
    @JournalDetailId INT = 0,
    @JournalDetailIdList XML = NULL
AS 
    SET NOCOUNT ON

Upvotes: 3

Views: 3304

Answers (2)

CodeMan03
CodeMan03

Reputation: 226

So apparantly this bit of code I found from a project years ago still works.

SqlCommand cmd = new SqlCommand("usp_GetAllAccounts", connection);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
**SqlCommandBuilder.DeriveParameters(cmd);**

Then I can assign the values to each parameter returned in a for loop. I don't understand but this cut my time down over 8 minutes down to 45 seconds which is equal to that of the SSMS.

Upvotes: 1

Milney
Milney

Reputation: 6417

Change the CommandTimeout property on your SqlCommand object - The default is 30 seconds;

https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.commandtimeout(v=vs.110).aspx

I believe the default in SSMS is no timeout

Upvotes: 1

Related Questions