none
ExecuteReader taking very long time to call my store procedure RRS feed

  • Question

  • i call my SP with ExecuteReader but it taken long time to get call my SP. from my SP i insert few test data in table and when i call my SP with ExecuteReader then i repeatedly issue a select my from table from SSMS but got no data.

    just very confused why it is taking long time. i am using sql server 2017

    see my code which is very simple

    try
                {
                    // Calling store proc to insert data into db
                    DataTable table = new DataTable();
                    SqlDataReader reader = null;
    
                    using (SqlConnection con = new SqlConnection(sConnectionString))
                    {
                        using (SqlCommand cmd = new SqlCommand("[dbo].[USP_InsertDataFromXML1]", con))
                        {
                                cmd.CommandTimeout = 3600; // Setting command timeout to 2 minutes
                                cmd.CommandType = CommandType.StoredProcedure;
                                cmd.Parameters.Add("@Ticker", SqlDbType.VarChar).Value = Ticker;
                                cmd.Parameters.Add("@PCName", SqlDbType.VarChar).Value = System.Environment.MachineName;
    
                                con.Open();
    
                                reader = cmd.ExecuteReader();
    
                                if (reader.HasRows)
                                {
                                    while (reader.Read())
                                    {
                                        
                                    }
                                }
    
    
                            //}
                        }
                    }
                }
                catch (Exception ex)
                {
                    //MessageBox.Show("Error " + ex.Message.ToString());
                }
                finally
                {
    
                }

    from this link i found a hint which i used but no luck still

                                con.Open();
                                using (SqlCommand comm = new SqlCommand("SET ARITHABORT ON", con))
                                {
                                    comm.ExecuteNonQuery();
                                }

    can you please suggest some way to call my SP very fast. what else i need to add or change in my code?

    what is SET ARITHABORT ON means ?

    please guide

    Friday, August 16, 2019 3:55 PM

All replies

  • See the following page 

    http://www.sommarskog.se/query-plan-mysteries.html


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Friday, August 16, 2019 4:46 PM
    Moderator
  • I use background worker and from its do_work event i execute my SP by data reader class... This could be a reason for which my store proc taking long time to gets called by c# code?
    Friday, August 16, 2019 7:59 PM
  • How long does it take to run that sproc from SSMS? Note that while that sproc is running there is likely a lock on the table (and certainly on the rows) being inserted. Therefore, unless you have enabled dirty reads in your query calls, you won't get any results until the data is written. However this is completely dependent upon how your sproc is written.

    You are calling ExecuteReader on the command but the sproc is called Insert. Is it really returning a SELECT clause from the sproc?

    At this point personally I think the issue is with your sproc and its transaction behavior. I recommend you post your question and sproc details in the SQL forums and let a DBA tell you what is wrong with it. The fact that you have a call to give your sproc 2 minutes to run tells me you're probably doing something wrong in the sproc.


    Michael Taylor http://www.michaeltaylorp3.net

    Friday, August 16, 2019 8:44 PM
    Moderator