SqlDataAdapter.Fill method slow

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.

To check this, look at the execution plans for each run, specifically the syscacheobjects 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 DROPCLEANBUFFERSDBCC FREEPROCCACHE

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

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 OFFEXEC [dbo].[web_GetMyStuffFool] @UserID=1SET ARITHABORT ON

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

MyConnection.Execute "SET ARITHABORT ON"

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