Revisiting the slow SqlDataAdapter.Fill method

Posted in: Programming by Steve on January 30, 2009

In my previous post, Do Not Taunt sp_updatestats, I described a problem where a query was taking a lot longer than expected.  I followed my own advice and ran sp_updatestats on the database and tried to run my query from the website again. 

What happened? Timeout. The same thing was happening where my query was running immediately in Sql Server Management Studio, but not when using ADO.NET.

I used Red Gate ANTS Profiler to see which method was slowing down.  It determined that the calling of the SqlDataAdapter.Fill(DataTable) method was causing the issue.

I then refactored to use a DataReader to rule out any weirdness and the same issue occurred.  I then looked for any issues with long queries in .NET, but should be shorter in SSMS.

I came across a couple of articles mentioning that if you want to replicate this behavior in SSMS, run this statement before you execute your proc:

SET ARITHABORT OFF

This will mimic the behavior of ADO.NET. 

So how do you fix this?  There are 3 things I found that you could do.  First, make sure your database indexes are reindexed in some kind of maintenance plan.  I didn’t have any kind of maintenance on my database.  If you need to reindex immediately, execute this SQL:

-- Execute this to rebuild all for a given database. Replace the <databasename> after EXEC.
EXEC <databasename>..sp_MSforeachtable @command1='DBCC DBREINDEX (''*'')', @replacechar='*'

The next solution would be to execute “SET ARITHABORT ON” before running your stored procedure.

Using cn As New SqlConnection(Me.ConnectionString)
    Dim cmd As New SqlCommand("web_LongIntenseQuery", cn)
    cmd.CommandType = CommandType.StoredProcedure
    cmd.Parameters.Add("@UserID", SqlDbType.BigInt).Value = UserID
    cn.Open()
    'this will allow the sproc to run
    Dim arithabortCmd As New SqlCommand("SET ARITHABORT ON", cn)
    arithabortCmd.ExecuteNonQuery()

    Dim ret As Integer = ExecuteNonQuery(cmd)
    Return (ret = 1)
End Using

Or the last option would be to add the SET ARITHABORT ON in your sproc.

More on stored procedure performance from Steven Smith (ASPAdvice.com, SteveSmithBlog.com)