Menu
I am executing stored procedure using ExcuteReader() command. If I execute Stored Procedure in SQL server it is taking 2 secs. But in code taking around 2 mins. I tried DataAdapter.Fill(). Still the same.
What is wrong in the code?
spString = "usp_graph"
sqlcmd_q.Connection = sqlCnn
sqlcmd_q.CommandText = spString
sqlcmd_q.CommandType = CommandType.StoredProcedure
sqlcmd_q.Parameters.AddWithValue("@clientid", clientId)
sqlcmd_q.Parameters.AddWithValue("@store", storeID)
sqlcmd_q.Parameters.AddWithValue("@attributes", attributeNumber)
sqlcmd_q.Parameters.AddWithValue("@attri1_idx", attribute1_idx)sqlCnn.Open()
sqlcmd_q.CommandTimeout = 300
sqldr = sqlcmd_q.ExecuteReader() // taking time here
dt.Load(sqldr)
dsGrid.Tables.Add(dt)
841
ExecuteReader() Sends the CommandText to the Connection and builds a SqlDataReader. ExecuteReader(CommandBehavior) Sends the CommandText to the Connection, and builds a SqlDataReader using one of the CommandBehavior values.
ExecuteReader: Use this operation to execute any arbitrary SQL statements in SQL Server if you want the result set to be returned, if any, as an array of DataSet.
It means SQL Server is caching and reusing query plans to make your queries run faster, saving the CPU and memory that would be used to compile the execution plan again. Parameter sniffing is only bad when your data values are unevenly distributed and cached query plans are not optimal for all values.
Read More) ExecuteReader will be used to return the set of rows, on execution of SQL Query or Stored procedure using command object. This one is forward only retrieval of records and it is used to read the table values from first to last. ( Read More)
Slow in the Application, Fast in SSMS?. Everything you need to know about this subject, and more.
191
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
