none
Calling store procedure taking long time from C# VS2013 IDE RRS feed

  • Question

  • i have store procedure when i am calling it from SSMS then it is taking 4/5 second but when calling the same store procedure by C# code then it is taking 20 Second. reason not clear. i use stop watch class to record time taken by calling store procedure

    below code where i am calling SP and record the time

    stopwatch_sp.Start();
    
    CompSS.Business.DashboardData.FetchData("USP_Data_withCommentFlag", tickerMain, clientcode, "", domain);
    
    stopwatch_sp.Stop();
    TimeSpan timeSpan1 = stopwatch_sp.Elapsed;
    string timetaken = string.Format("{0}h {1}m {2}s {3}ms", timeSpan1.Hours, timeSpan1.Minutes, timeSpan1.Seconds, timeSpan1.Milliseconds);
    ViewBag.TimeElapsed = timetaken;
    ViewBag.FromCache = "Calling SP";

    public static System.Data.DataTable FetchData(string spName, string ticker, string Code, string fullbroker, string type)
            {
                string connetionString = null;
                SqlConnection connection;
                SqlDataAdapter adapter;
                SqlCommand command = new SqlCommand();
                SqlParameter param;
                DataTable dt = new DataTable();
    
                connetionString = ConfigurationSettings.AppSettings["XXXX"];
                string irmodels = ConfigurationSettings.AppSettings["irmodeltype"];
                string zirt = ConfigurationSettings.AppSettings["zirttype"];
                string zhft = ConfigurationSettings.AppSettings["zhfttype"];
                connection = new SqlConnection(connetionString);
                SqlConnection conn = new SqlConnection();
                connection.Open();
                command.Connection = connection;
                command.CommandType = CommandType.StoredProcedure;
                command.CommandText = spName;
                command.CommandTimeout = 0;
                param = new SqlParameter("@Ticker", ticker);
                param.Direction = ParameterDirection.Input;
                param.DbType = DbType.String;
                command.Parameters.Add(param);
    
                param = new SqlParameter("@ClientCode", Code);
                param.Direction = ParameterDirection.Input;
                param.DbType = DbType.String;
                command.Parameters.Add(param);
    
                if (fullbroker.Trim() == "")
                {
                    param = new SqlParameter("@withAllBrokers", DBNull.Value);
                    param.Direction = ParameterDirection.Input;
                    param.DbType = DbType.String;
                    command.Parameters.Add(param);
                }
                else
                {
                    param = new SqlParameter("@withAllBrokers", fullbroker.Trim());
                    param.Direction = ParameterDirection.Input;
                    param.DbType = DbType.String;
                    command.Parameters.Add(param);
                }
    
                adapter = new SqlDataAdapter(command);
    
                //var dataReader = command.ExecuteReader();
                //var dataTable = new DataTable();
                //dataTable.Load(dataReader);
    
                adapter.Fill(dt);
                connection.Close();
                DataTable filteredData = new DataTable();
                if ((dt.Rows.Count > 0) && (type == irmodels))
                {
                    filteredData = WebApplication1.Business.CommonFunction.GetActualPeriods(dt);
                }      
                else
                {
                    filteredData = dt;
                }
                return dt;
    
            }

    i do not understand the reason why SP calling taking 5 second when called from SSMS but it take 20 second when calling same SP from c# code. please guide me where i am making the mistake. thanks

    Friday, October 16, 2020 9:27 AM

Answers

  • It is unlikely to be the network. Start with the database server. Your admin is going to have to help with that. They have tools available and know what they are.

    If you have to profile the network then you'll need to do so ideally from your gateway. Your network admins know how to do that. If not then use wireshark or equivalent.

    Although just testing what happens if you switch to using a data reader to quickly read the data will indicate whether the issue is with the dataset or not. That would be fastest, even if you temporarily don't actually load the data into your UI.


    Michael Taylor http://www.michaeltaylorp3.net

    • Marked as answer by Sudip_inn Monday, October 19, 2020 7:03 AM
    Sunday, October 18, 2020 7:43 PM
    Moderator

All replies

  • The sproc isn't taking that long. Your stopwatch is timing a lot more than just a simple sproc execution.

    1. Reading a configuration file to extract application settings.

    2. Creating a connection to the database.

    3. Setting up a command with parameters.

    4. Filling a dataset which will include the schema.

    5. Making a call to some GetActualPeriods method that does whatever.

    Out of all this only 4 is actually making a sproc call and it is doing more than that since you're using Dataset and Dataset attempts to figure out the schema. This is going to be slower than doing that in SSMS which doesn't care. 

    Personally 1 should be done at app startup. 2 and 3 should be pretty fast but you're not disposing of the connections so the more this code runs the more likely you are to run out of resources. You need to write this code to properly clean up the resource. There are 100s of questions on these forums about how to do that and the MSDN documentations have very clear examples on how to properly call and clean up a database connection using SqlConnection. Refer to those examples.

    Time the actual Fill call and I suspect it'll be closer to the timing you are seeing in SSMS but it will still be a little slower given the Dataset overhead. If you want the fastest possible results then use a data reader instead.


    Michael Taylor http://www.michaeltaylorp3.net

    Friday, October 16, 2020 1:53 PM
    Moderator
  • I think using a datatable can be the problem contributing to the slow response. 

    https://dzone.com/articles/reasons-move-datatables

    Friday, October 16, 2020 2:41 PM
  • later i dispose the connection and command but still slow. when i execute the SP in SSMS then it take 4 second but when i call the same SP from c# then adapter.Fill(dt); taken 12 second.

    SP return 310 columns which is creating in datatable at run time. it is not clear why adapter.Fill(dt) taking long time. please i stuck in this issue from morning and search lot google but found nothing still

    please give me some way out by which adapter.Fill(dt) should take minimum time.

    i can not use DataReader because i cache datatable at server side. another problem is once data read from datareader then datareader  becomes empty. so that is why i use datatable and cached it. so later i do not call SP rather take data from cache.

    looking for further guidance.

    Friday, October 16, 2020 5:18 PM
  • later i dispose the connection and command but still slow. when i execute the SP in SSMS then it take 4 second but when i call the same SP from c# then adapter.Fill(dt); taken 12 second.

    SSMS is a DBA tool and any raw T-SQL or stored procedure run from the query pane on  SSMS is going to execute faster, becuase it is executing at the database level,   and the data does not have to travel anywhere.

    SP return 310 columns which is creating in datatable at run time. it is not clear why adapter.Fill(dt) taking long time. please i stuck in this issue from morning and search lot google but found nothing still

    The data has to travel from MS SQL Server over named pipes or TCP/IP  connection back to the client computer and to the program that is requesting some kind of CRUD operations with the database. And then you are using a table adapter, a dataset and a datatable that contributes to poor performance.

    If you can't get rid of old technology being used, then it is what it is,  and you're stuck with it.




    • Edited by DA924x Friday, October 16, 2020 5:37 PM
    Friday, October 16, 2020 5:35 PM
  • If the fill takes 12 seconds then it is because of your massive dataset. If you want to use a dataset then you'll have to live with the perf issue. This is a known problem with datasets. There is no way to optimize the Fill call as it simply calls your command and fills the data in. There are no extensibility points if the query itself is fast.

    Perseonally I disagree completely that data reader cannot be used here. It is for situations like this that it is designed for. It doesn't matter that you can only read it once. You're missing the point of a data reader. You're using a dataset as your business object and then complaining about it being slow. Datasets are slow and they get slower as your data size increases. Datasets were never designed for this. Datasets are designed for small sets of data that need to be updated directly in memory without the need for a business object/layer.

    Stop using the dataset and your perf issue goes away. Take the time to build an actual business class(es) that represents your data and then use a data reader to read it in. It'll be as fast as possible. Yes you'll have to update all your code to use your custom business object instead of a dataset but that is the price to pay for ease of use over performance.

    You have to decide which is more important to your project. Note that if you're only reading this data in once then personally 12 seconds isn't that bad if you do it at startup before the data is needed then nobody may even notice. But it completely depends upon your architecture.


    Michael Taylor http://www.michaeltaylorp3.net

    Friday, October 16, 2020 5:39 PM
    Moderator
  • Hello,

    Adding to the advice given so far, set a breakpoint on this line adapter.Fill(dt);  run SQL-Profiler. When the breakpoint is hit the profiler will provide information including time to execute to understand how long it takes for the Stored Procedure to run rather than timing from a stop watch component.

    Also learn about using Activity Monitor in SSMS besides SQL-Profiler will let you see that the problem is after your SP executes.


    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

    Saturday, October 17, 2020 11:11 AM
    Moderator
  • Sir before one day i was running my application in a server pc where data adapter fill method was taking 12 sec some time more.

    today when i was running my application in a USA server pc then data adapter fill method was taking 1 sec. so i just do not understand what is the problem in server 1 in our office.

    please advise me that how could i find out the problem exist in our server which causing the data adapter fill method taking long time.

    on what are parameter exist based on which slow and fast performance depend.

    thanks

    Saturday, October 17, 2020 3:01 PM
  • Madam before one day i was running my application in a server pc where data adapter fill method was taking 12 sec some time more.

    today when i was running my application in a USA server pc then data adapter fill method was taking 1 sec. so i just do not understand what is the problem in server 1 in our office.

    please advise me that how could i find out the problem exist in our server which causing the data adapter fill method taking long time.

    on what are parameter exist based on which slow and fast performance depend.

    thanks

    Saturday, October 17, 2020 3:02 PM
  • Sir before one day i was running my application in a server pc where data adapter fill method was taking 12 sec some time more.

    today when i was running my application in a USA server pc then data adapter fill method was taking 1 sec. so i just do not understand what is the problem in server 1 in our office.

    please advise me that how could i find out the problem exist in our server which causing the data adapter fill method taking long time.

    on what are parameter exist based on which slow and fast performance depend.

    thanks

    Saturday, October 17, 2020 3:02 PM
  • If you didn't change any code and your query runs fast one day and slow the next then there is no such parameter that would control this behavior. It could be a network issue. Or it could be you aren't cleaning up you DB connections. Or perhaps deadlock issues.

    You need to have your DBA help you with this. There is no way for us to diagnose this for you and it like is intermittent. Have your DBA profile the DB connections and query while your code is running slow. This will give them an idea of a perf issue in the DB. If they identify that the query is coming back immediately then the problem may be on the network side. You need to profile the specific call to Fill to determine if that is actually where the issue is. If the network call is slow then bring in your infrastructure team.

    At this point, if your code is fast one day and slow the next it likely isn't your code unless you have a resource leak. The C# forums isn't going to be able to help you with this.


    Michael Taylor http://www.michaeltaylorp3.net

    Saturday, October 17, 2020 4:21 PM
    Moderator
  • Sir can you please mention any tool name which help me to find out network issue. thanks
    Sunday, October 18, 2020 8:04 AM
  • It is unlikely to be the network. Start with the database server. Your admin is going to have to help with that. They have tools available and know what they are.

    If you have to profile the network then you'll need to do so ideally from your gateway. Your network admins know how to do that. If not then use wireshark or equivalent.

    Although just testing what happens if you switch to using a data reader to quickly read the data will indicate whether the issue is with the dataset or not. That would be fastest, even if you temporarily don't actually load the data into your UI.


    Michael Taylor http://www.michaeltaylorp3.net

    • Marked as answer by Sudip_inn Monday, October 19, 2020 7:03 AM
    Sunday, October 18, 2020 7:43 PM
    Moderator