SEARCH
Subscribe!
Archive for January, 2009
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)
Sorting a TimeZoneInfo ReadOnlyCollection in VB.NET
Posted in: Programming by Steve on January 19, 2009
So working on my latest project in ASP.NET 3.5, I use a drop down to display the different time zones for users to select. I use this as their preference in which time zone to show dates. Pretty simple.
ddlTimeZones.DataSource = TimeZoneInfo.GetSystemTimeZones
ddlTimeZones.DataBind()
So my resulting drop down looks like this:
In Windows, when you view the time zones, they are sorted by the offset. This is what users would be used to seeing, so I would want to sort the ReadOnlyCollection that is returned from the TimeZoneInfo static method. I use the OrderBy extension method:
ddlTimeZones.DataSource = TimeZoneInfo.GetSystemTimeZones.OrderBy(Function(zone As TimeZoneInfo) zone.BaseUtcOffset.TotalHours)
ddlTimeZones.DataBind()
This will sort the drop down like so:
Just another way to help the UX
TypeMock Isolator 5.20 now supports AAA in VB.NET!
Posted in: Programming by Steve on January 13, 2009
Programming Visual Basic applications?
Typemock has released a new version of their unit testing tool, Typemock Isolator 5.2. This version includes a new friendly VB.NET API which makes Isolator the best Isolation tool for unit testing A Visual Basic (VB) .NET application. Isolator now allows unit testing in VB or C# for many ‘hard to test’ technologies such as SharePoint, ASP.NET MVC, partial support for Silverlight, WPF, LINQ, WF, Entity Framework, WCF unit testing and more.
Note that the first 25 bloggers who blog this text in their blog and tell us about it, will get a Free Full Isolator license (worth $139). If you post this in a VB.NET dedicated blog, you’ll get a license automatically (even if more than 25 submit) during the first week of this announcement.
Go ahead, click the following link for more information on how to get your free license.