Execute Reader Command is slow

Question

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

I think you might want ExecuteNonQuery instead if the procedure isn't actually returning query results. Also you should add using statements to properly dispose of everything. — juharr
– juharr, Commented Feb 15, 2018 at 16:56
Unrelated, but you might want to read up on table valued parameters instead of using XML to pass a list. — GarethD
– GarethD, Commented Feb 15, 2018 at 17:05
You should check out Can we stop using AddWithValue() already? and stop using .AddWithValue() - it can lead to unexpected and surprising results... — marc_s
– marc_s, Commented Feb 15, 2018 at 17:08
Canonical Slow in the Application, Fast in SSMS? & as mentioned a table valued paramater (i.e. passing the procedure a DataTable) is likely going to be much more efficient. — Alex K.
– Alex K., Commented Feb 15, 2018 at 17:20

Milney · Accepted Answer · 2018-02-15 17:02:20Z

1

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

answered Feb 15, 2018 at 17:02

Milney

6,4472 gold badges21 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

CodeMan03 Over a year ago

This does prevent the timeout error but doesn't help with the speed issue. I have no idea why on SSMS its fast but through C# its slow as turtle juice

CodeMan03 · Accepted Answer · 2018-02-15 20:51:41Z

1

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.

answered Feb 15, 2018 at 20:51

CodeMan03

2474 gold badges25 silver badges51 bronze badges

Collectives™ on Stack Overflow

Execute Reader Command is slow

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related