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.

regards,
MKItaniAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Anurag ThakurTechnical ManagerCommented:
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
0
Jacques Bourgeois (James Burger)PresidentCommented:
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.
0
MlandaTCommented:
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.
0
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";
                
    myConnection.Open();

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

    sw.Reset();
    sw.Start();
    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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.