Resolve Timeout & Max Pool Size Issue in .NET Queue-Based Methods

My website is an e-commerce platform featuring hundreds of SKUs for sale. My manager has requested that I track the total number of clicks for each SKU.

This includes clicks from members who are logged into our website as well as from guests who are not logged in.

To facilitate this, I have created a table named SkuTracking to record the total clicks for each item.

CREATE TABLE [dbo].[SkuTracking](
 [Email] [varchar](100) NULL,
 [SkuID] [varchar](50) NULL,
 [Hit] [int] NULL,
 [ActionDate] [datetime] NULL
) ON [PRIMARY]
GO

For every click on the SKU front-end page, a function named InsertSKUCount in C# is triggered. This function uses ADO.NET to insert data into the database and the code looks like this.

public void InsertSKUCount(Guid productId, string email)
{

    using (SqlConnection conn = new SqlConnection(strConnString))
      {
          conn.Open();

          using (SqlConnection conn = new SqlConnection(strConnString))
           {  
               cmd.Connection = conn;
               cmd.CommandText = "Insert Into SkuTracking(Email, SkuID, Hit, 
                ActionDate,)  Values(@Email, @SkuID, @Hit, @ActionDate)"
               cmd.Parameters.AddWithValue("@Email", email);
               //...rest of the code

Since my website is extremely popular, with numerous SKUs being viewed by a large number of customers every minute, my InsertSKUCount method is continuously called, along with the database insertion function. Eventually, this results in an error due to the high frequency of calls

“ Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached”

Even though I had used the using keyword to manage the DB connection, make sure all the connection will be disposes after use, the high traffic on my popular website still leads to the exhaustion of available connections.

So, one of the strategy I implemented to tackle the problem is using ConcurrentQueue.My strategy is like this

Instead of insert to DB for every click , I set a threshold, say 100, I will only insert to DB once the click is reach 100.
Use ConcurrentQueueto store the click info until it reached 100
Use asynchronous method to insert data to DB

Here is how I solve the issue. Firstly, I created a class HitUpdateto store the click information of my SKUs.

 class HitUpdate {
            public Guid ProductId { get; set; }
            public string Email { get; set; }
            public int Hit{ get; set; } 
        }

Then, create a ConcurrentQueue variable. This variable is use to store the incoming information of SKU clicks.

 private static ConcurrentQueue<HitUpdate> hitUpdatesQueue = new ConcurrentQueue<HitUpdate>();

Under our existing InsertSKUCount method, add following code to add those SKUs clicks info to our queue.

public void InsertSKUCount(Guid productId, string email)
{
   var hitUpdate = new HitUpdate { ProductId = productId, Email= email, Hit= 1};
    hitUpdatesQueue.Enqueue(hitUpdate);
}

Create another update to DB function, named UpdateDatabase and make this method asynchronous. Since we will add data in bulk (in this case it is 100 rows), so our DB insert statement will include bulk insertion.

   private async Task UpdateDatabase(List<HitUpdate> updates)
        {
            string strConnString = StandardLibrary.Database.Config.GetDBConnectionString("MyConnectionString");

            DataTable dt = new DataTable();
            dt.Columns.Add("Email", typeof(Guid));
            dt.Columns.Add("SkuID", typeof(Guid));
            dt.Columns.Add("Hit", typeof(int));
            dt.Columns.Add("ActionDate", typeof(DateTime)); 

            foreach (var update in updates)
            {
                dt.Rows.Add(
                    Guid.Parse(update.Email),
                    update.SkuID,
                    1, // HitRate is always 1 for each update
                    DateTime.Now);
            }

            using (SqlConnection conn = new SqlConnection(strConnString))
            {
                await conn.OpenAsync();
                using (SqlBulkCopy bulkCopy = new SqlBulkCopy(conn))
                {
                    bulkCopy.DestinationTableName = "SkuTracking";

                    try
                    {
                        await bulkCopy.WriteToServerAsync(dt);
                    }
                    catch (Exception e)
                    {
                        //error log
                    }
                }
            }
        }

I have also created another function for checking the queue before insert to the database named ProcessHitUpdatesQueue, and made this method asynchronous.

 public async Task ProcessHitUpdatesQueue()
        {
            List<HitUpdate> batch = new List<HitUpdate>();
            while (true)
            {
                try
                {
                    while (hitUpdatesQueue.TryDequeue(out HitUpdate hitUpdate))
                {
                    batch.Add(hitUpdate);
                    if (batch.Count >= 100) //queue will be updated if reach 100
                    {
                        await UpdateDatabase(batch);
                        batch.Clear();
                        GC.Collect(); // Force garbage collection
                    }
                }
               
                if (batch.Count > 0)
                {
                    await UpdateDatabase(batch);
                    batch.Clear();
                    GC.Collect(); // Force garbage collection
                }
                }
                catch (Exception ex)
                { 
                   //log the error
                }
                try
                {
                    await Task.Delay(TimeSpan.FromMinutes(10));
                }
                catch (TaskCanceledException ex)
                {
                   //log the error
                }
            }
        }

As you can see, I add one line await Task.Delay(TimeSpan.FromMinutes(10)); this mean the system will check the queue if it exceeds 100 of queues number.

One more thing, in the Global.asax.cs, you need to add the following code to make sure the ProcessHitUpdatesQueue()) will automatically run when the project is run.

   protected void Application_Start()
        {
Task.Run(() => new Product().ProcessHitUpdatesQueue());
}

With this approach, we can effectively resolve the timeout error caused by reaching the maximum pool size for database connections. We now update the SKU click tracking every 10 minutes or whenever the queue reaches 100 entries. You can configure the maximum queue size or the time interval for checking the queue based on the specific needs of your application.