[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 271
  • Last Modified:

call a stored procedure from C#+VS 2008 window application

hi all,
i have a lot experience in C# and i used the stored procedure a lot of time in my projects.
but i have a problem that when i call the stored procedure from my C# application takes a lot of time about 3 minutes although when i called from the sql server it self it is takes 2 or 3 seconds maximum.

NB: the sql server is installed locally and i run the executable file  also on the same computer.

1 Solution
Anurag ThakurCommented:
I have noticed lag in executing procedures through applications but not too much lag
try to put some timestamps in the procedure
one time stamp at the start and one at the end (first line and last line of the procedure) and see if its the execution thats talking time or getting hold of the connection is talking time
Jacques Bourgeois (James Burger)Commented:
There might be a difference between calling the procedure from your code vs inside of SQL Server, but not that much.

Could you show us the C# code that you use to call the procedure. Is suspect that there might be something wrong in there.
What is the stored procedure doing? How much data does it need to send back to the calling application? What is receiving this data in you code.. dataset/datareader/entity framework/etc/etc? Sometimes, the delay is actually in sending back the results to the calling application. Investigate and perhaps play around with the Packet Size in your connection string.
Carlos VillegasFull Stack .NET DeveloperCommented:
Hello, please use the code bellow to see how your SP performs in two different scenarios:
string myConnectionString = null;

// Please uncomment to choose the correct connection string, and modify with the correct values.
// Standard Security (user, password)
//myConnectionString = "Data Source=myServerAddress; Initial Catalog=myDataBase; User Id=myUsername; Password=myPassword;";
// Trusted Connection
//myConnectionString = "Data Source=myServerAddress; Initial Catalog=myDataBase; Integrated Security=SSPI;";

if (string.IsNullOrEmpty(myConnectionString))
    throw new Exception("Please choose a correct connection string for your environment.");

using (System.Data.SqlClient.SqlConnection myConnection = new System.Data.SqlClient.SqlConnection(myConnectionString))
    System.Data.SqlClient.SqlCommand myCommand = new System.Data.SqlClient.SqlCommand();
    myCommand.Connection = myConnection;
    myCommand.CommandTimeout = 0;
    myCommand.CommandType = System.Data.CommandType.StoredProcedure;

    // Change it with your SP name.
    myCommand.CommandText = "MyStoreProcedureName";

    // Add parameters if required (examples).
    //myCommand.Parameters.Add("Param1", System.Data.SqlDbType.Int).Value = 123;
    //myCommand.Parameters.Add("Param2", System.Data.SqlDbType.VarChar, 50).Value = "123";

    System.Diagnostics.Stopwatch sw = new System.Diagnostics.Stopwatch();
    System.Diagnostics.Debug.WriteLine("Time required to complete ExecuteNonQuery: " + sw.Elapsed.ToString());

    using (System.Data.SqlClient.SqlDataReader dr = myCommand.ExecuteReader())
        while (dr.Read()) { }
    System.Diagnostics.Debug.WriteLine("Time required to complete ExecuteReader: " + sw.Elapsed.ToString());

Open in new window

After completed, copy and post here the values displayed on your VS Debug Output window.

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now