SqlDataAdapter.Fill method is slow

Question

Why does 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 runs on the local computer with so little / no network latency, the machine runs fast

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 

An array of parameters is typed (for this SQL is only one parameter)

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

A stored procedure is just a select statement:

 ALTER PROC [dbo].[web_GetMyStuffFool] (@UserID BIGINT) AS SELECT Col1, Col2, Col3, Col3, Col3, Col3, Col3, Col3, Col3 FROM [Table] 

Answer 1

First, make sure you profile performance correctly. For example, run the query twice from ADO.NET and see if the second time is much faster than the first time. This eliminates the overhead by waiting for the application to compile and the debugging infrastructure to deploy.

Then check the default settings in ADO.NET and SSMS. For example, if you run SET ARITHABORT OFF in SSMS, you may find that it runs as slowly as when using ADO.NET.

What I once discovered was that SET ARITHABORT OFF in SSMS caused a recompiled saved copy and / or other statistics to be used. And suddenly SSMS and ADO.NET reported about the same runtime.

To verify this, review the execution plans for each run, in particular the syscacheobjects table. They will probably be different.

Running "sp_recompile" in a specific stored procedure will remove the associated execution plan from the cache, which allows SQL Server to create a possibly more appropriate plan the next time the procedure is executed.

Finally, you can try the “ nuke it from orbit ” approach to flushing the entire cache of procedures and memory buffers using SSMS:

 DBCC DROPCLEANBUFFERS DBCC FREEPROCCACHE 

By doing this before you check your request, it is forbidden to use cached execution plans and the cache of previous results.

Answer 2

Here is what I did:

I executed the following SQL statement to rebuild indexes in 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 proc as follows:

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

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

 MyConnection.Execute "SET ARITHABORT ON" 

Answer 3

I ran into the same problem, but when I rebuilt the indexes in the SQL table, it worked fine, so you may need to rebuild the index on the sql server side

Answer 4

Why not make a DataReader instead of a DataAdapter, it looks like you have a single result set, and if you are not going to make changes to the database and do not need the restrictions applied in the .NET code, you should not use an adapter.

EDIT:

If you need it to be a DataTable, you can still extract data from the database through the DataReader, and then use the DataReader in the .NET code to populate the DataTable. It should be faster than relying on a DataSet and DataAdapter

Answer 5

I had the same problem and fixed it with this article: http://www.databasejournal.com/features/mssql/article.php/3841271/T-SQL-Best-Practices--Parameter-Sniffing.htm

Answer 6

I don’t know why it is so slow, but, as Marcus points out, comparing Mgmt Studio with filling out a dataset is apples for oranges. Datasets contain a lot of overhead. I hate them and NEVER use them if I can help.

You may have problems with inconsistencies between older versions of the SQL stack or some of them (for example, you are clearly stuck in .NET 1.1). The framework is probably trying to use the equivalent of the "Reflection" database to output the scheme etc etc etc

One thing to consider with your unfortunate limitation is accessing the database using a datareader and creating your own dataset in the code. You can easily find patterns through google.