Background: The Challenge Unfolds

The e-commerce platform, built on nopCommerce, was growing rapidly, catering to a dynamic user base. With 8,000 unique users daily and a product catalog of 25,000 items, the platform was thriving—until increasing traffic began to expose underlying performance issues.

While the platform initially handled normal sales volumes without trouble, peak traffic periods soon led to frequent site downtimes. These issues were compounded by the strain caused by thousands of anonymous crawlers visiting the site daily, intensifying the database load and overwhelming system resources. As database connection pools maxed out at 500 connections, performance deteriorated, frustrating both users and businesses alike.

The situation demanded immediate attention and a strategic overhaul to ensure the platform could scale effectively under increasing user demand.

Identifying the Root Causes

The platform’s troubles were many, each compounding the other:

• 1. Queries Gone Wild: Some queries ran thousands of times without caching, overloading the system.
• 2. Indexing Mysteries: Key queries lacked proper indexing, leading to slow processing and high CPU usage.
• 3. A Search Term Trap: The Search Term table, designed to log user searches, became a burden with its inefficient structure and processes.
  • o The Search Term table, which logs user search keywords, was highly CPU-intensive.
  • o Keywords were stored in an NVARCHAR(MAX) column, preventing effective indexing.
  • o The workflow involved searching for the term before inserting or updating, and adding to the load.
• 4. Mapping Faults: Bots and crawlers triggered unnecessary database operations due to flawed customer-company mapping logic.
  • o Custom logic assigns a default company to every customer upon visiting the site.
  • o Crawlers exacerbated the issue by triggering database operations for each visit.
• 5. Campaign Chaos: Marketing campaigns brought traffic spikes the system couldn’t handle, causing frequent crashes.
• 6. Guest Overload : A new customer entry was created in the database for each new user session, adding unnecessary load.

Strategic Resolutions

The team dove deep into the platform’s architecture, armed with tools like SQL Server Management Studio and Profiler. Here’s how they tackled the challenges:

• 1. Taming the Queries:
  • o Frequently executed queries were identified and optimized with caching mechanisms.
  • o Indexes were added to reduce CPU load and improve execution times.
• 2. Retiring the Search Term Table:
  • o Recognizing redundancy, the team ceased data insertion into the Search Term table, relying instead on a third-party search engine.
• 3. Fixing the Mapping Logic:
  • o Customer-company mapping was limited to B2B customers, while bots and general visitors were excluded.
• 4. Introducing Campaign Mode:
  • o A caching mechanism for product and category data was developed, allowing selective caching with configurable expiration settings.
• 5. Reining in Guest Records:
  • o General visitors were assigned to a common guest profile, and new records were created only for specific actions like adding to the cart or registering.

Results: A Transformed Platform

The results were transformational. The platform emerged stronger, ready to face the challenges of high-traffic days with ease:

• Stability Restored: The reduced query execution time and count directly contribute to the platform's ability to handle marketing surges without downtime.
• Efficiency Achieved: The optimizations lead to decreased CPU and memory usage, which aligns with the reduced query execution load shown in the table.
• Scalability Secured: The improvements demonstrate how the platform can efficiently manage more concurrent users, as fewer resources are consumed per query.
• Delighted Users: The faster execution times enhance overall performance, leading to quicker load times and better user satisfaction.

Performance Optimization Results

A thorough analysis was conducted to evaluate the impact of recent optimizations on the Top 5 Resource-Consuming Queries, based on data gathered over a one-hour period using SQL Server Management Studio’s Query Store. The following table highlights the significant improvements in query execution efficiency and overall scalability.

Note: Data is measured over one hour for the average of the top 5 queries.

Image: Query execution duration before and after optimization

Image: Query execution count before and after optimization

Key Takeaways for Optimization

Every adventure brings wisdom, and this one was no different. The team identified key practices to ensure sustained success:

• Monitor Proactively: Regularly analyze query performance and system bottlenecks.
• Cache Smartly: Implement caching for frequently accessed data and resource-heavy queries.
• Design Thoughtfully: Use appropriate data types and indexing strategies to optimize database schemas.
• Tame the Crawlers: Implement throttling and rate-limiting for bots to reduce unnecessary load.
• Prepare for Campaigns: Pre-cache critical content to handle surges seamlessly.
• Adapt and Improve: Regularly review and refine platform configurations to address known limitations.

Image: Strategies for sustained system success

Conclusion

This story of transformation is a testament to the power of proactive diagnosis and targeted optimizations. By addressing its performance bottlenecks, the platform survived and thrived, providing users with a seamless shopping experience and supporting the business’s growth ambitions. May it inspire similar success stories for e-commerce platforms everywhere.