33

Why would a stored procedure that returns a table with 9 columns, 89 rows using this code take 60 seconds to execute (.NET 1.1) when it takes < 1 second to run in SQL Server Management Studio? It's being run on the local machine so little/no network latency, fast dev machine

Dim command As SqlCommand = New SqlCommand(procName, CreateConnection())
command.CommandType = CommandType.StoredProcedure
command.CommandTimeout = _commandTimeOut
Try
   Dim adapter As new SqlDataAdapter(command)
   Dim i as Integer
   For i=0 to parameters.Length-1
      command.Parameters.Add(parameters(i))
   Next
   adapter.Fill(tableToFill)
   adapter.Dispose()
Finally
   command.Dispose()
End Try

my paramter array is typed (for this SQL it's only a single parameter)

parameters(0) = New SqlParameter("@UserID", SqlDbType.BigInt, 0, ParameterDirection.Input, True, 19, 0, "", DataRowVersion.Current, userID)

The Stored procedure is only a select statement like so:

ALTER PROC [dbo].[web_GetMyStuffFool]
   (@UserID BIGINT)
AS
SELECT Col1, Col2, Col3, Col3, Col3, Col3, Col3, Col3, Col3
FROM [Table]
Steve Wright
  • 2,481
  • 3
  • 26
  • 35
  • 1
    This is an old question and many people find it. Before you start cleaning the cache and playing with ARITHABORT settings, read the comprehensive article by Erland Sommarskog that explains what might be going on: [Slow in the Application, Fast in SSMS? Understanding Performance Mysteries](http://www.sommarskog.se/query-plan-mysteries.html) Quite often this behaviour is caused by the feature of SQL Server called "parameter sniffing". – Vladimir Baranov May 26 '16 at 23:59
  • I had the same issue and got it fixed using this article: http://www.databasejournal.com/features/mssql/article.php/3841271/T-SQL-Best-Practices--Parameter-Sniffing.htm – sujeewa Sep 29 '10 at 00:12
  • This worked for me since my stored procedure does some branching based on parameters. – Kekoa Sep 02 '11 at 16:54

5 Answers5

52

First, make sure you are profiling the performance properly. For example, run the query twice from ADO.NET and see if the second time is much faster than the first time. This removes the overhead of waiting for the app to compile and the debugging infrastructure to ramp up.

Next, check the default settings in ADO.NET and SSMS. For example, if you run SET ARITHABORT OFF in SSMS, you might find that it now runs as slow as when using ADO.NET.

What I found once was that SET ARITHABORT OFF in SSMS caused the stored proc to be recompiled and/or different statistics to be used. And suddenly both SSMS and ADO.NET were reporting roughly the same execution time. Note that ARITHABORT is not itself the cause of the slowdown, it's that it causes a recompilation, and you are ending up with two different plans due to parameter sniffing. It is likely that parameter sniffing is the actual problem needing to be solved.

To check this, look at the execution plans for each run, specifically the sys.dm_exec_cached_plans table. They will probably be different.

Running 'sp_recompile' on a specific stored procedure will drop the associated execution plan from the cache, which then gives SQL Server a chance to create a possibly more appropriate plan at the next execution of the procedure.

Finally, you can try the "nuke it from orbit" approach of cleaning out the entire procedure cache and memory buffers using SSMS:

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

Doing so before you test your query prevents usage of cached execution plans and previous results cache.

Charlieface
  • 52,284
  • 6
  • 19
  • 43
HTTP 410
  • 17,300
  • 12
  • 76
  • 127
  • 2
    DBCC DROPCLEANBUFFERSDBCC FREEPROCCACHE solved my problem. Does this need to be ran on a recurring basis, or only when changes are made to the stored procedure? – Merritt Aug 13 '09 at 17:59
  • 2
    It's on a recurring basis, as these are cached results and plans that will gradually accumulate. – HTTP 410 Sep 15 '09 at 14:52
  • 1
    Great answer, my SP execution time went from 117s to 3s! +1 – Carl Jul 02 '10 at 19:58
  • The "SET ARITHABORT OFF" was a great answer! Now I get a consistent experience between ADO.NET as SSMS. In my case I just needed to rebuild one index which I did from the SSMS IDE. – Carter Medlin Jul 21 '10 at 21:31
  • Yes.. DBCC DROPCLEANBUFFERS DBCC FREEPROCCACHE solve my problem.. but I dont want to clear cache for all objects in ssms... can I use 'exec sp_recompile' instead to solve this problem. @RoadWarrior – Munavvar Jul 21 '16 at 07:36
  • 1
    @Munavvar, sorry, late to see your comment. Yes, 'sp_recompile' will clear the cache of the execution plan for a specified stored procedure. – HTTP 410 Jan 09 '17 at 14:25
  • DBCC DROPCLEANBUFFERS approach did not work for me. – Jitendra Pancholi Dec 11 '21 at 12:36
5

Here is what I ended up doing:

I executed the following SQL statement to rebuild the indexes on all tables in the database:

EXEC <databasename>..sp_MSforeachtable @command1='DBCC DBREINDEX (''*'')', @replacechar='*'
-- Replace <databasename> with the name of your database

If I wanted to see the same behavior in SSMS, I ran the proc like this:

SET ARITHABORT OFF
EXEC [dbo].[web_GetMyStuffFool] @UserID=1
SET ARITHABORT ON

Another way to bypass this is to add this to your code:

MyConnection.Execute "SET ARITHABORT ON"
Steve Wright
  • 2,481
  • 3
  • 26
  • 35
2

I ran into the same issue, but when I've rebuilt indexes on SQL table, it worked fine, so you might want to consider rebuilding index on sql server side

Aurasphere
  • 3,841
  • 12
  • 44
  • 71
user5534139
  • 21
  • 1
1

Why not make it a DataReader instead of DataAdapter, it looks like you have a singel result set and if you aren't going to be pushing changes back in the DB and don't need constraints applied in .NET code you shouldn't use the Adapter.

EDIT:

If you need it to be a DataTable you can still pull the data from the DB via a DataReader and then in .NET code use the DataReader to populate a DataTable. That should still be faster than relying on the DataSet and DataAdapter

Marcus King
  • 1,667
  • 3
  • 15
  • 20
0

I don't know "Why" it's so slow per se - but as Marcus is pointing out - comparing Mgmt Studio to filling a dataset is apples to oranges. Datasets contain a LOT of overhead. I hate them and NEVER use them if I can help it.

You may be having issues with mismatches of old versions of the SQL stack or some such (esp given you are obviously stuck in .NET 1.1 as well) The Framework is likely trying to do database equivilant of "Reflection" to infer schema etc etc etc

One thing to consider try with your unfortunate constraint is to access the database with a datareader and build your own dataset in code. You should be able to find samples easily via google.

fuzzbone
  • 2,020
  • 14
  • 14