December 2007 - Posts
I was confronted with a slow performing stored procedure in SQL server this week. The procedure was to retrieve one row from a table. But before that row could be retrieved, an update procedure was called to make sure all rows in that table had the correct status. That update procedure first checked the number of rows in a table to see if there were rows to be be updated. It used a piece of SQL similar to this:
CREATE PROCEDURE dbo.spMyProcedure
AS
SET NOCOUNT ON
IF (SELECT count(UserID) from UserTable) > 0
BEGIN
-- Process the items in the table
END
GO
This will obviously work. But on a table that contains more than 150,000 rows this can take some time. But why count the number of rows if all we need to know if there is at least one row present. I changed the IF statement a little and gave the procedure a new name so that I could compare both:
CREATE PROCEDURE dbo.spMyProcedureNew
AS
SET NOCOUNT ON
IF EXISTS(SELECT top 1 UserID from UserTable)
BEGIN
-- Process the items in the view
END
GO
I then called both with the Execution plan on and found this result:
A logical conclusion, just checking if there is one row in the table is much faster than checking of the number of records is more than zero. When you compare the subtree cost of the old procedure (0.726) with the subtree cost of the new procedure (0.00641), you can see a performance gain of a factor 113 . But how does SqlProfiler look at both queries? Well, here's that result:
As you can see, the duration of the old procedure averages at 34.4 and the new procedure averages at 12.2. The new proceduer is still a factor 2.8 faster than the old procedure. But you'll also need to look at the difference in CPU time (0 for the new procedure and 31 for the old procedure) and the number of reads (19 for the new compared to 709 for the old procedure) to conclude that checking for at least one row is a lot more efficient than counting the number of rows.
I think a lot of people need to do this, and we do in our project. We created a method that will check if URLs specified in our database (a couple of hundred) actually exist. Our support team uses the result of that check to update incorrect URLs in our application. I extracted the method and removed some project specific stuff to show it here:
/// <summary>
/// Checks the status of the specified URL.
/// </summary>
/// <param name="url">The URL that needs to be checked</param>
/// <param name="statusText">The status text associated with the specified URL</param>
/// <returns>An Integer value which is the result of the check action</returns>
public int CheckUrl(string url, out string statusText)
{ int status = -1;
statusText = "Unknown status";
try
{
ServerXMLHTTP30Class http = new ServerXMLHTTP30Class();
http.setTimeouts(5000, 5000, 5000, 5000);
http.open("HEAD", url, Missing.Value, Missing.Value, Missing.Value); http.send(Missing.Value);
status = http.status;
// Status 200 specifies the http.send was successful
if (status != 200)
{ statusText = http.statusText;
}
else
{ statusText = "OK";
}
}
catch (COMException com)
{ statusText = com.Message;
}
return status;
}
For most URLs, this method works fine for us. There are some cases where the method fails. For example, some urls (for intranet sites) require a user with sufficient access rights to be logged on. One problem kind of worries us. The http.Send method sometimes throws the following exception:
System.Runtime.InteropServices.COMException (0x80072EE2):
Exception from HRESULT: 0x80072EE2
at MSXML2.ServerXMLHTTP30Class.send(Object varBody)
I'm wondering if someone out there can tell me why, or maybe someone knows of a better (and faster) way to check if a URL exists.
I just spent 4 hours of figuring out why the reports we developed using Crystal Reports would not show the graphs after being deployed to our production server. We checked everything from access rights to proper installation of Crystal reports on the server. Below are a few things you may want to check when you need to deploy a .Net 2.0 web application that integrates Crystal Reports:
Deploy Crystal components
First, and rather obvious, is to make sure the correct version of Crystal components are installed on the server. If you have the Visual Studio SDK installed, you can look for CRRedist2005_x86.msi, which is in this folder C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\BootStrapper\Packages\CrystalReports on your machine. Running that installation on the server will make sure all components required for Crystal Reports are properly installed on your machine.
If you have a setup project for your web application, you can add a merge module to that project. Crystal Reports provides four merge modules for download at this location. Depending on your needs, you may need to add any combination of these to your setup project. Which module is required for your application can be found in this document on the Business Objects support site.
Web.Config settings
In order for a Crystal Reports viewer to work, the following settings must be added to the appSettings section in the web.Config file:
<add key="CrystalImageCleaner-AutoStart" value="true"/>
<add key="CrystalImageCleaner-Sleep" value="60000"/>
<add key="CrystalImageCleaner-Age" value="120000"/>
This will ensure that the temporary images for Crystal are removed when they are no longer required. The following setting must be added the the httpHandlers section.
<add verb="GET" path="CrystalImageHandler.aspx" type="CrystalDecisions.Web.CrystalImageHandler,
CrystalDecisions.Web, Version=10.2.3600.0, Culture=neutral, PublicKeyToken=692fbea5521e1304"/>
This is required, because otherwise all you will see is an empty box with a red cross in it.
aspnet_client files
Last, but certainly not least, check the aspnet_client folder in the root folder of your web site. This folder should contain a folder with the name CrystalReportWebFormViewer3. That folder must exist in the following path: system_web\2_0_50727. If that folder is not there, copy it from your local machine. It should be in the folder C:\WINNT\Microsoft.NET\Framework\v2.0.50727\ASP.NETClientFiles.
I guess most of us are really happy with the release of Visual Studio 2008. I know I am, although I realise that I won't be able to use it professionally. I have only recently been able to convince my current projectowner to upgrade our suite of applications to .Net 2.0. The reason behind that decision was not driven by functionality in VS 2005 and .Net, but by the decision of the company to upgrade from Windows XP to Vista. And as we all know, VS 2003 won't run properly on Vista. So for now, I won't even consider to try and persuade him to do another migration.
But Microsoft is already working on a new version of Visual Studio, code-name Rosario. It promisses to be an integrated Application Life-Cycle Management (ALM) solution comprising tools, processes, and guidance. It enables members of your team to:
- Collaborate and communicate more effectively with other team members and business stakeholders
- Ensure software quality using advanced quality tools at every step of the application life cycle
- Gain visibility into project activity and priorities to make informed decisions based on real-time data
Now don't get me wrong, I have no idea yet on what the new version will deliver. But those of you that are interested in this new version. a set of specifications is available for download here. But that's still only a (lot of) piece(s) of paper, right. But you can also download the November 2007 CTP version. Yes, that's right. a CTP is already available for download here. It's as a VPC image so it won't screw-up your system to much. You're system should at least match the following requirements:
- Supported Operating Systems: Windows Server 2003; Windows Vista; Windows XP
- Required Software: Virtual PC 2007.
- Processor: 1.6GHz Pentium III+
- RAM: 1 GB of available physical RAM