Tag Archives: Performance
Posted in: Programming by Steve on October 21, 2010
In our current web app, we are running into an issue where if too many users are selected for a function, the website times out. When we originally were looking at the design of this feature, the developers weren’t sure about the performance of this function. We were concerned that if a lot of people were selected, that the feature would get bogged down and possibly timeout on the server.
At that time, we didn’t know how we were going to implement the function, we just went ahead with what we knew. We knew the options available for us, but we decided that it was in the interest of time that we do it a certain way.
Fast forward 9 months after we designed, developed, QA’d and released the feature; we are now at a point where the feature performance will gradually start to degrade with each passing day. Ideally, we should have done it the right way, but time and money prevented us from doing it. Now we are using work arounds for the customer trying to explain that if they use more than X number of users for this feature, that the application will timeout.
What should we have done?
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)