Good old-fashioned SQL optimzation using an index

Sometimes simple, basic stuff really works.

This query was taking too long to execute:
SELECT ISNULL(SUM(Quantity),0) as 'count'
FROM dbo.AllOrderItems WITH (NOLOCK)
WHERE CustomerID=@customerID AND ItemID=@itemID

AllOrderItems has nearly 5 million rows. I executed this query with a customerID and itemID that corresponding to 3 records. It took 23 seconds to run.

I created an index on the CustomerID and ItemID columns like this:
CREATE NONCLUSTERED INDEX [Customer_Item] ON [dbo].[ArcOrderItems]
(
[CustomerID] ASC,
[ItemID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

With this index, the execution time was reduced from 23 seconds to less than one second.

Moral of the story: sometimes the obvious fix is the right one when it comes to database optimization.

Bonus tip: When testing the speed of a query, it will often run faster the second and subsequent times, because SQL caches the results. These two commands clear the cache, ensuring a realistic measurement of the execution time:
DBCC DROPCLEANBUFFERS;
DBCC FREEPROCCACHE;

Interesting book about engineering

When I attended June's Google NYC Tech Talk on performance bugs, speaker Jon Bentley recommended the book To Engineer Is Human by Henry Petroski. I enjoyed Jon's presentation so much, I read the book.

This book is not about software. It's primarily about civil and aeronautical engineering. But the lessons it imparts about how to learn from failure and create more reliable products apply nicely to software engineering.

The writing style was a bit formal, and some of the examples -- this book is from the 1980s -- are dated. But overall I enjoyed it, and it made me think about how to be a better software developer.

Using SQL to concatenate values from multiple rows into a single string

Lots of SQL programmers probably know this trick already, but it was a new one for me and seems worth sharing. In just a few lines of code, without using a cursor, you can concatenate values from multiple rows into a single string. For example, if you have a table with a FirstName column, and a SELECT statement returns the FirstName values 'Aaron', 'Betty' and 'Carol', it's easy to form a string like this: 'Aaron, Betty, Carol'. The comments in the code snippet below explain how.

-- create a test table and insert some rows of test data
CREATE TABLE Test (
FirstName VARCHAR(10),
LastName VARCHAR(10)
)
INSERT INTO Test VALUES ('Aaron', 'Aardvark')
INSERT INTO Test VALUES ('Betty', 'Baboon')
INSERT INTO Test VALUES ('Carol', 'Condor')

-- form a comma-delimited list of the FirstName values from all records
DECLARE @myList VARCHAR(100) -- this works with a VARCHAR or NVARCHAR variable, but _not_ with a CHAR variable
SET @myList = '' -- initialize the variable; if you don't, the output string will be blank

-- The next SELECT statement is the key. It appends the FirstName from each record to whatever is in the string so far.
-- The CASE statement is just for putting commas between the names, but not in front of the first one.
SELECT @myList = @myList + CASE @myList WHEN '' THEN '' ELSE ', ' END + FirstName FROM Test
SELECT @myList AS MyList -- output the string, which should say 'Aaron, Betty, Carol'

-- delete the test table
DROP TABLE Test

Fun book about AI - "Final Jeopardy"

An interesting computer science read: "Final Jeopardy" by Stephen Baker. It's an account of IBM's massive project to build Watson, the computer system that defeated two human champions on the game show "Jeopardy." My key takeaway is that there are two approaches to artificial intelligence, and they're both hard.

You can simulate human-like reasoning with something like a neural network -- hard because it requires vast processing power

Or you can "teach" a computer system myriad rules and bits of information -- hard because it requires lots of people to spend lots of time.

Console app to send a SOAP request

For troubleshooting purposes, it can be useful to invoke a web service method by sending an XML-formatted SOAP request and receiving the response. Here's a C# console application to do that. Just replace the URL and the XML request string with your own values.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Net;
using System.IO;

namespace post
{
class Program
{
static void Main(string[] args)
{
// Replace with the URL of your web service.
string strUrl = "http://oosapi/OosApiService.asmx";

// Replace with the XML-formatted SOAP request for the web service method you wish to call.
// Don't forget to escape any double quotes.
string strRequest = @"b5a3e821-6f7d-4ad0-b5d0-b723222bc319";

// Create the Request object.
HttpWebRequest req = (HttpWebRequest)WebRequest.Create(strUrl);
req.Method = "POST";
req.ContentType = "text/xml";
req.ContentLength = strRequest.Length;

// Send the request.
StreamWriter swRequest = new StreamWriter(req.GetRequestStream(), System.Text.Encoding.ASCII);
swRequest.Write(strRequest);
swRequest.Close();

// Receive the response.
StreamReader srResponse = new StreamReader(req.GetResponse().GetResponseStream());
string strResponse = srResponse.ReadToEnd();
srResponse.Close();

// Output the response.
Console.WriteLine(strResponse);
Console.ReadKey();
}
}
}

.NET cache stores only a reference -- and what to do about it

.NET provides the System.Web.Caching.Cache object as a way to persistently store data. This is useful, for example, if you retrieve some data from a database and want to refer to it repeatedly, perhaps from several different pages, without the overhead of accessing the database again each time.

When I attempted to store an instance of a class I had created with several members -- I discovered a quirk of the Cache object: adding such an object to the cache seems to store a reference to the object, not a copy of the object. An example will make this clear.

// define a class with some members
class Animal
{
public string species;
public string name;

public Animal(string s, string n)
{
species = s;
name = n;
}
}

// create a dog named Spot
Animal animal = new Animal("dog", "Spot");

// store Spot in the cache
HttpContext.Current.Cache.Insert("MyAnimal", animal);

// change Spot's name to Rover
animal.name = "Rover";

// get Spot from the cache
Animal animal2 = (Animal)HttpContext.Current.Cache.Get("MyAnimal");

// this is the cached object; the name should be Spot, but instead it's Rover ??!!
Label1.Text = animal2.name;

I expected the above code to set the label to Spot, but it actually sets it to Rover. Cache. Insert appears to store a reference to the object. I have yet to find anyplace this is mentioned in Microsoft's documentation. Kudos to Martin Bakiev of Penn State University for figuring this out.

Here's one way to get around the problem. Add another constructor to the Animal class. Use to create a copy of the object, and then store that copy in the cache. This has the desired effect, setting the label to Spot.


// define a class with some members
class Animal
{
public string species;
public string name;

public Animal(string s, string n)
{
species = s;
name = n;
}

public Animal(Animal a)
{
species = a.species;
name = a.name;
}
}

// create a dog named Spot
Animal animal = new Animal("dog", "Spot");

// store Spot in the cache
//HttpContext.Current.Cache.Insert("MyAnimal", animal);
HttpContext.Current.Cache.Insert("MyAnimal", new Animal(animal));

// change Spot's name to Rover
animal.name = "Rover";

// get Spot from the cache
Animal animal2 = (Animal)HttpContext.Current.Cache.Get("MyAnimal");

// this is the cached object; the name should be Spot, but instead it's Rover ??!!
Label1.Text = animal2.name;

That avoids the problem, but it's not very convenient if you want to store instances of many different classes in the cache; you'd need to create a new constructor for each class. I found a better solution: I wrote methods that use the .NET BinaryFormatter class to serialize an object on its way into the cache, and deserialize it on the way back out. I may be able to share that code in a future post.

Determining whether a numeric value has at most two decimal places

I was working on a .NET website and wanted to write some C# code to validate that a value input by the user was numeric and had at most two decimal places. This is useful, for example, when validating that the input represents a dollar amount.

I first tried this, which didn't work:
try
{
// must be numeric value
double d = double.Parse(s);
// max of two decimal places
if (100 * d != (int)(100 * d)) // max of two decimal places
return false;
return true;
}
catch
{
return false;
}

The above is unreliable because, since d is a floating-point number, 100 * d isn't always exactly equal to (int)(100 * d), even when d has two or fewer decimal places. For example, 100 * 1.23 might evaluate to, say, 122.9999999.

This post on StackOverflow offers several solutions, but none of them looked right for my purpose. Instead, I came up with this:
try
{
// must be numeric value
double d = double.Parse(s);
// max of two decimal places
if (s.IndexOf(".") >= 0)
{
if (s.Length > s.IndexOf(".") + 3)
return false;
}
return true;
catch
{
return false;
}

The same thing could be accomplished using a regular expression, if you prefer.