I've been working with a new client to help tune the performance of their e-commerce website and SQL Server Database. Their e-commerce website and backend operations were incredibly slow and unresponsive for day-to-day operations. Throwing money at hardware upgrades just wasn't paying off and their concern was that it was either a software or architectural bottleneck causing performance problems with the website and database.
After discussing their business model and operations and what, if anything, has changed since they started having these performance issues, I started monitoring the performance of their IIS Website and SQL Server Database while looking at the ASP.NET WebForms code that drives the website. The server and ASP.NET code seem fine, but the SQL Server Statistics were showing numerous long-running queries and stored procedures. My first instinct was improper indexing of the database, but this wasn't the case and I started to notice a common pattern across all the queries and stored procedures that were hogging the SQL Server Database's Resources.
It doesn't happen often, almost never, but there was a design "flaw" in the database schema itself. I hate to use the word "flaw" here as that might conjure up images of incompetence by the original database developer. That is not the case. The database itself was nicely built and been running for over a decade with zero problems. It wasn't until recently that the client started noticing problems.
This particuar design "flaw" was causing a clustered index scan as opposed to a seek on a table that has millions of rows of data. What's worse is that this scan was common to numerous queries and stored procedures hitting the database. By fixing this one issue the SQL Server Database should see a huge performance increase across the board. And guess what? It did. Making a small, but significant change to the database schema caused a noticeable performance increase in the entire e-commerce website and SQL Server Database. And when I say noticeable, I mean notieceable :)
Although this solved their immediate needs, there was a troubling amount of database resources being used by product searches on the website. As they add more and more products to the website the problem gets worse and worse.
After discussing this problem and possible solutions in depth, we decided to remove the product search burden from the database and use Lucene.Net. Lucene.Net is a really fast search engine library built in C# and used in numerous ASP.NET web applications. Since most of their product inquiries on the website were searches, this would eliminate a huge number of queries hitting the database and improve the overall speed of the database. Since Lucene.NET is also much faster than SQL Server for searching this would also increase the performance of searching for products on the e-commerce website.
After implementing Lucene.Net into the e-commerce website the client saw another noticeable positive impact in performance. Business backend operations were so much faster and product searches on the website were faster as well. This simple change reduced the bottleneck that was sure to come!
I performed a few other changes to the website to increase scalability and performance, but the change to the SQL Server Schema and using Lucene.Net for all product searches provided the big boost to the performance of their e-commerce website, database, and visitors performing product searches. It's not everyday you can provide such a significant impact to a system like that which made this project such a joy!