ADO.NET 2.0 Slows SP Execution By Large Factor

I'm hoping someone will be able to point me in the right direction for solving this problem as i've come a bit stuck.

The Sql Server 2005 Stored Procedure runs in about 3 secs for a small table when run from SQL Management Studio (starting withdbcc freeproccache before execution) but times out when run through ADO.NET on .NET (45 sec timeout).

I've made sure the connection was closed prior to opening and executing the adapter. I'm a bit stuck as where to check next though.

Any ideas greatfully received, Thanks

Can you give us something more to go on? If the stored procedure text is relatively short, can you post it with related tables/indexes and such?

|||

Could you please make sure that you are connected to the Sql Server before making call to that stored procedure... Just try to execute a small query with a top clause.

The problem seems with Connection

|||

Unfortunately the SP is fairly complicated at about 200 lines in all. In summary it comprises of two joins. One is a highly indexed Account table and the other is a YearToDate table.

The output is basically a list of regions and all the Revenue that they've generated over period X to Y.

Here's my data tier code. The bottleneck seems to be the last line as that is the part that takes ages when i'm stepping through in debug mode

ssCommand.CommandType =CommandType.StoredProcedure;
ssCommand.CommandText = "my_stored_proc";
ssCommand.Connection =newSqlConnection("my_con_str");
ssCommand.CommandTimeout = 45;
foreach (SqlParameter ssParamin mySqlParmCollection){
ssCommand.Parameters.Add(ssParam);
}
ssCommand.Connection.Open();
ssDataAdapter =newSqlDataAdapter(ssCommand);
ssDataAdapter.Fill(ssDataSet, sDataTableName);

Many thanks,

|||

The nice thing would be to check the Sql Profiler... By this way you will be able to know whether its hitting the Sql Server or not.. and it will narrow down the problem. Then you can see why its taking so long

|||

I've just done a trace and the execution took almost 45 secs to complete using ADO.NET

I then ran it through query analyser and it took 3 secs.

I'm very puzzled by all of this, I could understand the execution plans not being used though .NET but they were cleared whilst executing them using sql management..

Your help is much appreciated,

Matt

|||

This is a excerpt from one of the msdn forum ..

I had the same problem. Seems like the SQL execution plan was corrupted for the stored procedure. I created a new SP and and copied the code to the new SP from the old and ran the application and the data was returned in no time. This proves that this was a SQL server issue and probably the execution plan for the SP was not efficient when it is run from ADO.NET. The reason that the SP worked from the query analyser and not the application using ADO.NET is QA directly executes the SP on the server but ADO.NET internally executes the sp by calling sp_executesql. The dropping and recreating the SP from the database should be a quick fix for the future. It may be worth while to find out WHY this condition happens after a while on the database.

Here are some information from the research I did on this for your reference:
1) "SqlCommand.ExecuteReader executes all commands in the context of the sp_executesql stored procedure." (http://msdn2.microsoft.com/en-us/library/aa720629(VS.71).aspx)
2) "Stored procedure will recompile if there is a sufficient number of rows in a table referenced by the stored procedure has changed. SQL Server will recompile the stored procedure to be sure that the execution plan has the up-to-date statistics for the table. You will notice this problem quite often when you are working with temporary tables in SQL Server 7.0 as SQL Server will determine that after 6 modifications to a temporary table any stored procedure referencing that table will need to be recompiled."
(http://www.sql-server-performance.com/rd_optimizing_sp_recompiles.asp)
3) "You may have heard about a system stored procedure called sp_executesql. It lets you evaluate dynamic SQL, but it happens to also cache its execution plan."
(http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/controlling-stored-procedure-caching-with-dyanmic-sql.aspx)
4) "The WITH RECOMPILE option prevents reusing the stored procedure execution plan, so SQL Server does not cache a plan for this procedure and the procedure is always recompiled at run time. Using the WITH RECOMPILE option can boost performance if your query will vary each time it is run from the stored procedure, because in this case the wrong execution plan will not be used. "
(http://www.databasejournal.com/features/mssql/article.php/1565961)

Check the link

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1102019&SiteID=1

and try the solutions mentioned