Using session variables to reduce the number of database connections

My .NET website, which accesses a SQL Server 2005 database, has been performing poorly when usage is heavy. When processing more than about 5 - 10 orders per minute, CPU utilization on the database server approaches 100%, the website's response time becomes slow, and database timeout errors become frequent. I would have expected the website to handle hundreds of orders per minute.

The main problem seems to be that the website makes many database calls order, so many that a large number of database connections are open at any one time, straining CPU and RAM resources on the database server. This is a consequence of how the previous programmers built the web site. For example, a restaurant record can be looked up in the database by calling a stored procedure that accepts the restaurant ID as a parameter. The website was calling this stored procedure multiple times per page, even though the restaurant information never changes. (Okay, it does change sometimes, but typically only once every few months, and in ways that don't need to be taken into account during an in-progress order.)

Even caching the restaurant record in a local variable just for the duration of each page would help, but I went one step further and cached it in a .NET session variable, so that other pages can access the record without going back to the database.

In simplest terms, the pseudocode is like this:
if (session var exists) {
read restaurant record from session var
}
else {
read restaurant record from database
store restaurant record in session var
}

In practice, it was a little trickier, for two reasons. First, it's possible for a customer to place an order from one restaurant, and then without closing the browser, place an order from another restaurant. So the pseudocode really needs to be:
doLookup = true
if (session var exists) {
read restaurant record from session var
if (requested restaurant ID = restaurant ID from session var) {
doLookup = false
}
}
if (doLookup) {
read restaurant record from database
store restaurant record, including restaurant ID, in session var
}

Second, in .NET, session variables aren't accessible during very early parts of the page lifecycle. For example, in my website, a restaurant lookup can occur in the Application_PreRequestHandlerExecute handler. When writing code that uses session, and which may be called at any point during the page lifecycle, it's necessary to check whether the .NET session object is available before attempting to access it; otherwise an exception occurs. This is as simple as: if (HttpContext.Current.Session != null).

Now, the above isn't the whole story of improving my website's performance, for two reasons. First, it's not just restaurants that were being looked up in the database repetitively. The same problem applied to lots of other entities. I applied the same solution to as many of those entities as I could in the time available.

Second, by using SQL Server's SQL Profiler and Database Engine Tuning Adviser, I determined that the creation of some indexes and statistics could likely improve performance -- according to the Database Enginer Tuning Adviser, by as much as 85%. (Thanks to my friend Max Fomitchev for his excellent assistance with these SQL Server performance tools.)

So I've implemented the session variable stuff, plus the indexes and statistics. It will all go live later this week. I'll post a comment once I know the results.

Banker's rounding in C# and SQL

Today I discovered an arithmetical quirk called banker's rounding.

My .NET 3.5 website, written in C#, performs some calculations which, because they deal with currency, round to two decimal places. Here's an example:
double saleAmount = 205.75;
double commissionPercentage = 6.0;
double commission = Math.Round(saleAmount * commissionPercentage / 100.0, 2);

The result of the calculation is stored in a database. I had reason to believe there was a logic error in my application, so I wrote a database script to verify some of the calculations by directly examining values in the database. Sometimes the value calculated by the T-SQL script in SQL Server Management Studio 2005 was off by one cent when compared to the value calculated in C#.

For example, in C#, Math.Round(12.345, 2) is 12.34, while in T-SQL, Round(12.345, 2) is 12.35 -- one cent higher.

I learned in science class that, when the value to be rounded ends in 5, you round up. It turns out that .NET takes a different approach, called banker's rounding. It's based on IEEE Standard 754, section 4, and here's Microsoft's explanation of it. In short, when the last digit is a 5, it rounds up if the preceding digit is odd, and down if even. This is intended to minimize rounding errors that would accumulate from consistently rounding a midpoint value in the same direction.

SQL Server does not perform banker's rounding by default. In fact, I don't think there's any built-in function in SQL Server to perform banker's rounding. A number of people have posted their own SQL functions for banker's rounding. I'm not sure all of them are correct.

Rather than find or write a banker's rounding function in T-SQL, I worked around the problem. All I needed was to identify values that were way off from the expected value. So I used a SQL comparison like this to find values that differed by more than one cent: IF ABS(@expectedSalesCommission - @amount) > 0.01...

That turned out to not quite work. Because floating-point calculations are imprecise, sometimes values the computed value of @expectedSalesCommission was a trifle more than one cent away from the computed value of @amount. I changed it to IF ABS(@expectedSalesCommission - @amount) > 0.011..., and that did the trick.

SharePoint service uses all available CPU (owstimer.exe)

I noticed my test web server was running slowly, and when I ran perfmon, I found that CPU utilization was pegged at 100%. I ran taskmgr and looked for processes using a lot of CPU. At first I didn’t see any, but after selecting the Show processes from all users checkbox, I discovered that owstimer.exe was using 95 to 99% of CPU time. I learned that this process is used to perform scheduled tasks in SharePoint – interesting since this server doesn't host anything that uses SharePoint. I confirmed that SharePoint is installed on the test web server

To eliminate the CPU utilization problem, in services.msc, I stopped and disabled the Windows SharePoint Services Timer service.

To determine when SharePoint was installed, I launched SQL Server Management Studio on the test server and connected to the SQL Server instance on the local machine. There I found some SharePoint databases -- WSS_AdminContent, WSS_AdminConfig and WSS_Content. By right-clicking each of these and viewing the properties, I was able to see that they were created on Dec 30 2009, almost a year ago. SharePoint has apparently been present all along, and was likely installed as part of Team Foundation Services.

This doesn’t explain why a SharePoint service suddenly started performing some action that consumed all available CPU time. That remains a mystery. According to several other online posts, other people have experienced the same thing.

Missing DLL causes error "file has not been pre-compiled, and cannot be requested"

My .NET 2.0 website used an old version of Telerik's RadWindow control. I replaced this with a recent version of Telerik RadControls for ASP.NET AJAX. When I tested the website on my local machine, it worked fine. When I uploaded it to the web server, though, attempting to view any page resulted in an error of the form

The file '/step1.aspx' has not been pre-compiled, and cannot be requested.

The filename step1.aspx would be replaced with the name of whatever page I was trying to view.

I eventually determined that the error message didn't mean at all what it said. What it really meant was that Telerik.Web.UI.dll was missing from the website's bin folder. The Telerik control was referenced in a master page that was used by most pages of the site, and therefore the error occurred on almost every page.

Simply adding the missing DLL to the bin folder resolved the problem. I then got to wondering why the DLL was missing in the first place. Here's the answer.

I installed Telerik RadControls for ASP.NET AJAX using the MSI installer provided by Telerik. Among other things, this installed Telerik.Web.UI.dll to C:\Program Files (x86)\Telerik\RadControls for ASP.NET AJAX Q1 2010\Bin20.

I copied Telerik.Web.UI.dll from there to a folder named Shared Assemblies in my .NET solution. I use this folder to store some third-party DLLs to which one or more of my projects make references.

In my project, I added a reference by browsing to the copy of Telerik.Web.UI.dll in my Shared Assemblies folder. Nonetheless, Visual Studio added the reference from the GAC, and did not copy the DLL to my project's bin folder. Because my project was a website, not a web application, I had no option to select "copy local" when adding the reference.

So my only recourse was to manually copy Telerik.Web.UI.dll into the bin folder and add it to source control. Now when I publish my project, the DLL is where it needs to be, and the error no longer occurs.

By the way, this is not specific to Telerik. I've noticed other people have had similar problems with other DLLs, especially ReportViewer.

LogParser tricks

Microsoft's free command-line LogParser utility has many uses. I find it particularly helpful when I want to comb through IIS logs or Windows event logs for troubleshooting purposes. The SQL-based syntax isn't always easy to master. Whenever I have a helpful LogParser example to share, I'll add it to this post.

Today's example shows how to search the IIS logs. Here's the command line, followed by an explanation and some tips:

G:\Log Parser 2.2>logparser "select date, time, cs-uri-stem, sc-status from g:\logs\www.2l23l.com\W3SVC954531014\ex101110.log where cs-uri-stem like '/hungryboiler%' and time >= '02:00:00' and time < '03:00:00' and cs(User-Agent) like '%Gecko%' and sc-status >= 400"

The fields I wanted to output were date, time, URL (cs-uri-stem) and status (sc-status). Tip: to get a list of all available fields in a log file, use select top 1 * from...

I figured out which log file to search by looking at my website's properties in IIS Manager. In IIS6, on the Web Site tab, under the Logging section, click the Properties button. Then you just need to pick the desired log file according to date. Don't forget: by default, IIS logs use GMT, so something that occurred in the evening Eastern Time will be in the following day's log.

I knew the error occurred on a page whose name started with "hungryboiler", so I searched for cs-uri-stem like '/hungryboiler%'.

I knew a user experienced a "page not found" error, so I searched for sc-status >= 400.

I knew the error occurred between 2:00 and 3:00 AM GMT (10:00 to 11:00 PM the previous day EST), so I search for time >= '02:00:00' and time <= '03:00:00'.

One final tip for LogParser newbies like myself: The query itself goes inside double quotes. Any strings within the query go inside single quotes.

SQL unions and the text data type

Transact-SQL supports a data type named text. It's deprecated and may not be supported forever. It's similar to nvarchar(max).

I ran into an odd problem related to the text data type. I tried to do a query with a union, something like this:

CREATE TABLE [dbo].[t1](
[c1] [int] NULL,
[c2] [text] NULL
)

CREATE TABLE [dbo].[t2](
[c1] [int] NULL,
[c2] [text] NULL
)

SELECT * FROM t1 UNION SELECT * FROM t2

The SELECT statement fails with this error message: The text data type cannot be selected as DISTINCT because it is not comparable.

A bit odd, because there's no reference to DISTINCT in my query, but SQL Server 2005 must use DISTINCT behind the scenese to perform the UNION.

A workaround is to cast text to nvarchar(max), as in SELECT c1, CAST(c2 AS NVARCHAR(MAX)) AS c2 FROM t1 UNION SELECT c1, CAST(c2 AS NVARCHAR(MAX)) AS c2 FROM t2.

Two drawbacks to this workaround: First, you can't use SELECT *, since you must explicitly name any columns that need to be cast to nvarchar(max). Second, if your text columns happen to hold more data than the capacity of nvarchar(max), I'm not sure what would happen. Either an error message or data loss, I assume.

The real fix would be to change any text columns in your database to nvarchar(max) or some other data type that isn't deprecated and works with unions. That could take a fair amount of work if you have many stored procedures, etc. that refer to the existing text columns.

ReportViewer and ToolTips

Some interesting facts about ReportViewer and ToolTips:

• Yes, you can have ToolTips in a report (RDL file) created in SQL Server 2005 Business Intelligence Development Studio (BIDS) and viewed in the .NET ReportViewer control. Just set the ToolTip property for any textbox.
• You can include line breaks in the text of a tooltip. One way to do so is to set the ToolTip property to an expression that uses vbCrLf. Example: ="This is the first line" & vbCrLf & "This is the second line".
• The ToolTips don't show up when you preview the report in BIDS. But they do show up when you run the report in SQL Server Report Services or inside in a ReportViewer control in your .NET program.