Performance Optimization Guide

Database optimization, caching strategies, and monitoring for high-performance operations

Overview

This guide provides comprehensive performance optimization strategies for the Shirinzad E-Commerce Platform, covering database optimization, caching strategies, query optimization, and monitoring.

Performance Goals: Response times under 300ms for most endpoints, support for 1000+ concurrent users, and 99.9% uptime.

Database Optimization

1. Index Strategy (46+ Indexes)

The platform implements a comprehensive indexing strategy with over 46 indexes for optimal query performance.

Products Table Indexes

-- Single Column Indexes
CREATE INDEX IX_AppProducts_Slug ON AppProducts(Slug);
CREATE INDEX IX_AppProducts_Code ON AppProducts(Code);
CREATE INDEX IX_AppProducts_IsActive ON AppProducts(IsActive);
CREATE INDEX IX_AppProducts_IsFeatured ON AppProducts(IsFeatured);
CREATE INDEX IX_AppProducts_BrandId ON AppProducts(BrandId);
CREATE INDEX IX_AppProducts_CreationTime ON AppProducts(CreationTime);

-- Composite Indexes for Better Performance
CREATE INDEX IX_AppProducts_IsActive_IsFeatured
ON AppProducts(IsActive, IsFeatured) INCLUDE (Price, AverageRating);

CREATE INDEX IX_AppProducts_BrandId_IsActive
ON AppProducts(BrandId, IsActive) INCLUDE (Name, Price, MainImageUrl);

CREATE INDEX IX_AppProducts_Price_IsActive
ON AppProducts(Price, IsActive) INCLUDE (Name, Slug);

CREATE INDEX IX_AppProducts_AverageRating_IsActive
ON AppProducts(AverageRating DESC, IsActive) INCLUDE (Name, Price);

Categories Table Indexes

CREATE INDEX IX_AppCategories_Slug ON AppCategories(Slug);
CREATE INDEX IX_AppCategories_IsActive ON AppCategories(IsActive);
CREATE INDEX IX_AppCategories_DisplayOrder ON AppCategories(DisplayOrder);
CREATE INDEX IX_AppCategories_ParentId ON AppCategories(ParentId);

Orders Table Indexes

CREATE INDEX IX_AppOrders_OrderNumber ON AppOrders(OrderNumber);
CREATE INDEX IX_AppOrders_UserId ON AppOrders(UserId);
CREATE INDEX IX_AppOrders_Status ON AppOrders(Status);
CREATE INDEX IX_AppOrders_CustomerEmail ON AppOrders(CustomerEmail);
CREATE INDEX IX_AppOrders_CreationTime ON AppOrders(CreationTime);

-- Composite index for user order history
CREATE INDEX IX_AppOrders_UserId_Status_CreationTime
ON AppOrders(UserId, Status, CreationTime DESC);

Product Reviews Table Indexes

CREATE INDEX IX_AppProductReviews_ProductId ON AppProductReviews(ProductId);
CREATE INDEX IX_AppProductReviews_UserId ON AppProductReviews(UserId);
CREATE INDEX IX_AppProductReviews_IsApproved ON AppProductReviews(IsApproved);
CREATE INDEX IX_AppProductReviews_CreationTime ON AppProductReviews(CreationTime);

-- Composite index for approved reviews
CREATE INDEX IX_AppProductReviews_ProductId_IsApproved_CreationTime
ON AppProductReviews(ProductId, IsApproved, CreationTime DESC);

2. Database Statistics & Query Store

Enable Auto-Update Statistics

-- SQL Server
ALTER DATABASE ShirinzadShop SET AUTO_UPDATE_STATISTICS_ASYNC ON;
ALTER DATABASE ShirinzadShop SET AUTO_CREATE_STATISTICS ON;

-- Rebuild statistics weekly
UPDATE STATISTICS AppProducts WITH FULLSCAN;
UPDATE STATISTICS AppOrders WITH FULLSCAN;
UPDATE STATISTICS AppProductReviews WITH FULLSCAN;

Enable Query Store for Performance Monitoring

ALTER DATABASE ShirinzadShop SET QUERY_STORE = ON;
ALTER DATABASE ShirinzadShop SET QUERY_STORE (
    OPERATION_MODE = READ_WRITE,
    DATA_FLUSH_INTERVAL_SECONDS = 900,
    INTERVAL_LENGTH_MINUTES = 60,
    MAX_STORAGE_SIZE_MB = 1024,
    QUERY_CAPTURE_MODE = AUTO
);

3. Partitioning Strategy

For high-traffic scenarios, implement table partitioning on large tables like Orders:

-- Partition Orders by creation date (monthly)
CREATE PARTITION FUNCTION PF_OrdersByMonth (DATETIME2)
AS RANGE RIGHT FOR VALUES (
    '2025-01-01', '2025-02-01', '2025-03-01',
    '2025-04-01', '2025-05-01', '2025-06-01',
    '2025-07-01', '2025-08-01', '2025-09-01',
    '2025-10-01', '2025-11-01', '2025-12-01'
);

CREATE PARTITION SCHEME PS_OrdersByMonth
AS PARTITION PF_OrdersByMonth
ALL TO ([PRIMARY]);

-- Apply to Orders table
ALTER TABLE AppOrders DROP CONSTRAINT PK_AppOrders;
ALTER TABLE AppOrders ADD CONSTRAINT PK_AppOrders
PRIMARY KEY CLUSTERED (Id, CreationTime) ON PS_OrdersByMonth(CreationTime);

Query Optimization

1. N+1 Query Prevention

Bad - N+1 Queries

var products = await _productRepository.GetListAsync();
foreach (var product in products)
{
    // This triggers N additional queries!
    product.Brand = await _brandRepository.GetAsync(product.BrandId);
}

Good - Eager Loading

var queryable = await _productRepository.GetQueryableAsync();
var products = await AsyncExecuter.ToListAsync(
    queryable
        .Include(p => p.Brand)
        .Include(p => p.ProductCategories)
            .ThenInclude(pc => pc.Category)
        .Where(p => p.IsActive)
);

2. Projection to DTOs

Project directly to DTOs instead of loading entire entities to reduce memory usage and improve performance.

Bad - Load Entire Entity

var products = await _productRepository.GetListAsync();
return products.Select(p => ObjectMapper.Map<Product, ProductDto>(p)).ToList();

Good - Direct Projection

var queryable = await _productRepository.GetQueryableAsync();
var productDtos = await AsyncExecuter.ToListAsync(
    queryable
        .Where(p => p.IsActive)
        .Select(p => new ProductDto
        {
            Id = p.Id,
            Name = p.Name,
            Price = p.Price,
            MainImageUrl = p.MainImageUrl,
            BrandName = p.Brand != null ? p.Brand.Name : null
        })
);

3. AsNoTracking() for Read-Only Queries

Use AsNoTracking() for read-only queries to skip change tracking and improve performance.

var queryable = await _productRepository.GetQueryableAsync();
var products = await AsyncExecuter.ToListAsync(
    queryable
        .AsNoTracking() // Don't track changes - faster reads
        .Where(p => p.IsActive)
        .Include(p => p.Brand)
);
Performance Gain: AsNoTracking() can improve query performance by 20-30% for read operations.

4. Pagination Best Practices

Always use pagination for list queries to prevent loading too much data at once.

public async Task<PagedResultDto<ProductDto>> GetListAsync(PagedAndSortedResultRequestDto input)
{
    var queryable = await _productRepository.GetQueryableAsync();

    // Apply filters first
    queryable = queryable.Where(p => p.IsActive);

    // Get count BEFORE pagination
    var totalCount = await AsyncExecuter.CountAsync(queryable);

    // Apply sorting and pagination
    queryable = queryable
        .OrderByDescending(p => p.CreationTime)
        .Skip(input.SkipCount)
        .Take(input.MaxResultCount);

    var products = await AsyncExecuter.ToListAsync(queryable);

    return new PagedResultDto<ProductDto>(
        totalCount,
        ObjectMapper.Map<List<Product>, List<ProductDto>>(products)
    );
}

5. Compiled Queries

Use compiled queries for frequently executed queries to avoid re-parsing and re-compiling.

private static readonly Func<ShopDbContext, Guid, Task<Product>> GetProductByIdQuery =
    EF.CompileAsyncQuery((ShopDbContext context, Guid id) =>
        context.Products
            .Include(p => p.Brand)
            .FirstOrDefault(p => p.Id == id));

public async Task<Product> GetProductByIdAsync(Guid id)
{
    var dbContext = await _productRepository.GetDbContextAsync();
    return await GetProductByIdQuery(dbContext, id);
}

API Performance

1. Response Compression (Brotli & Gzip)

Response compression is configured to reduce payload sizes by up to 80%.

builder.Services.AddResponseCompression(options =>
{
    options.EnableForHttps = true;
    options.Providers.Add<BrotliCompressionProvider>();
    options.Providers.Add<GzipCompressionProvider>();
});

builder.Services.Configure<BrotliCompressionProviderOptions>(options =>
{
    options.Level = CompressionLevel.Fastest;
});

2. Response Caching Middleware

// In Program.cs
builder.Services.AddResponseCaching();

app.UseResponseCaching();

Apply to Controllers

[ApiController]
[Route("api/app/product")]
public class ProductController : AbpController
{
    [HttpGet]
    [ResponseCache(Duration = 600)] // 10 minutes
    public async Task<PagedResultDto<ProductDto>> GetListAsync(PagedAndSortedResultRequestDto input)
    {
        // ...
    }

    [HttpGet("{id}")]
    [ResponseCache(Duration = 300, VaryByQueryKeys = new[] { "id" })]
    public async Task<ProductDto> GetAsync(Guid id)
    {
        // ...
    }
}

3. Output Caching

builder.Services.AddOutputCache(options =>
{
    options.AddBasePolicy(builder => builder
        .With(c => c.HttpContext.Request.Path.StartsWithSegments("/api/app/product"))
        .Expire(TimeSpan.FromMinutes(10)));
});

app.UseOutputCache();

4. Async/Await Best Practices

Bad - Blocking Call

public async Task<ProductDto> GetProductAsync(Guid id)
{
    // .Result blocks the thread!
    var product = _productRepository.GetAsync(id).Result;
    return ObjectMapper.Map<Product, ProductDto>(product);
}

Good - Proper Async

public async Task<ProductDto> GetProductAsync(Guid id)
{
    var product = await _productRepository.GetAsync(id);
    return ObjectMapper.Map<Product, ProductDto>(product);
}

5. Background Jobs for Heavy Operations

Queue heavy operations as background jobs to avoid blocking API responses.

public class OrderAppService : ApplicationService
{
    private readonly IBackgroundJobManager _backgroundJobManager;

    public async Task<OrderDto> PlaceOrderAsync(CheckoutDto input)
    {
        var order = await CreateOrderAsync(input);

        // Queue background jobs instead of blocking
        await _backgroundJobManager.EnqueueAsync(new SendOrderConfirmationEmailArgs
        {
            OrderId = order.Id
        });

        await _backgroundJobManager.EnqueueAsync(new UpdateInventoryArgs
        {
            OrderId = order.Id
        });

        return ObjectMapper.Map<Order, OrderDto>(order);
    }
}

Connection Pooling

Database Connection Pooling

Configure connection pooling to reuse database connections and reduce overhead.

"ConnectionStrings": {
    "Default": "Server=localhost;Database=ShirinzadShop;User Id=sa;Password=***;TrustServerCertificate=True;Pooling=true;Min Pool Size=10;Max Pool Size=100;Connection Lifetime=0;"
}
Parameter Value Description
Pooling true Enable connection pooling
Min Pool Size 10 Minimum number of connections in pool
Max Pool Size 100 Maximum number of connections in pool
Connection Lifetime 0 Connection lifetime in seconds (0 = no limit)

Performance Benchmarks

Target Performance Metrics

Endpoint Target Response Time Max Concurrent Users Error Rate
GET /api/app/product (list) < 300ms 1000 < 0.1%
GET /api/app/product/{id} < 100ms 2000 < 0.1%
POST /api/app/cart/add-item < 200ms 500 < 0.5%
POST /api/app/order/checkout < 500ms 200 < 1%
GET /api/app/category < 150ms 1500 < 0.1%

Database Query Targets

Query Type Target Time
Simple SELECT by ID < 5ms
Product list with filters < 50ms
Complex joins (products + categories + brand) < 100ms
Full-text search < 200ms
Order history < 150ms

Load Testing

Using k6 for Load Testing

Install k6

# Windows
choco install k6

# Linux
sudo apt-get install k6

# Docker
docker pull grafana/k6

Create Load Test Script (load-test.js)

import http from 'k6/http';
import { check, sleep } from 'k6';

export const options = {
  stages: [
    { duration: '2m', target: 100 }, // Ramp up to 100 users
    { duration: '5m', target: 100 }, // Stay at 100 users
    { duration: '2m', target: 200 }, // Ramp up to 200 users
    { duration: '5m', target: 200 }, // Stay at 200 users
    { duration: '2m', target: 0 },   // Ramp down
  ],
  thresholds: {
    http_req_duration: ['p(95)<500'], // 95% of requests under 500ms
    http_req_failed: ['rate<0.01'],   // Error rate under 1%
  },
};

export default function () {
  // Test product list endpoint
  const listRes = http.get('https://localhost:5001/api/app/product');
  check(listRes, {
    'product list status 200': (r) => r.status === 200,
    'product list response time < 500ms': (r) => r.timings.duration < 500,
  });

  sleep(1);

  // Test product detail endpoint
  const detailRes = http.get('https://localhost:5001/api/app/product/some-product-id');
  check(detailRes, {
    'product detail status 200': (r) => r.status === 200,
    'product detail response time < 200ms': (r) => r.timings.duration < 200,
  });

  sleep(1);
}

Run Load Test

k6 run load-test.js

Monitoring & Application Performance Management

Application Insights Integration

Configuration

{
  "ApplicationInsights": {
    "ConnectionString": "InstrumentationKey=xxx",
    "EnableAdaptiveSampling": true,
    "EnablePerformanceCounterCollectionModule": true
  }
}

Track Custom Metrics

public class ProductAppService : ApplicationService
{
    private readonly TelemetryClient _telemetryClient;

    public async Task<ProductDto> GetAsync(Guid id)
    {
        var stopwatch = Stopwatch.StartNew();

        var product = await _productRepository.GetAsync(id);

        stopwatch.Stop();
        _telemetryClient.TrackMetric("ProductLoad.Duration", stopwatch.ElapsedMilliseconds);

        return ObjectMapper.Map<Product, ProductDto>(product);
    }
}

MiniProfiler for Development

builder.Services.AddMiniProfiler(options =>
{
    options.RouteBasePath = "/profiler";
}).AddEntityFramework();

app.UseMiniProfiler();

Access profiler at: https://localhost:5001/profiler/results-index

Health Checks

builder.Services.AddHealthChecks()
    .AddSqlServer(configuration.GetConnectionString("Default"))
    .AddRedis(configuration["Redis:Configuration"])
    .AddCheck<ProductCatalogHealthCheck>("product-catalog");

app.MapHealthChecks("/health");

Recommended APM Tools

  • Application Insights - Microsoft's APM solution, native Azure integration
  • New Relic - Comprehensive APM with distributed tracing
  • Datadog - Full-stack monitoring and analytics
  • Elastic APM - Open-source APM built on Elastic Stack
  • Seq - Structured logging and analysis

Scalability Strategies

Horizontal Scaling

graph LR LB[Load Balancer] API1[API Server 1] API2[API Server 2] API3[API Server 3] Redis[(Redis Cache)] DB[(SQL Server)] LB --> API1 LB --> API2 LB --> API3 API1 --> Redis API2 --> Redis API3 --> Redis API1 --> DB API2 --> DB API3 --> DB
  • Deploy multiple API server instances behind a load balancer
  • Use Redis for distributed caching across servers
  • Store session state in Redis (not in-memory)
  • Use distributed background job queue
  • Implement sticky sessions if needed

Database Read Replicas

Set up read replicas to distribute read load across multiple database instances.

"ConnectionStrings": {
    "Default": "Server=primary-db;Database=ShirinzadShop;...",
    "ReadOnly": "Server=read-replica;Database=ShirinzadShop;ApplicationIntent=ReadOnly;..."
}

CDN for Static Assets

  • Store product images in Azure Blob Storage or AWS S3
  • Serve static assets through CDN (Azure CDN, CloudFlare, AWS CloudFront)
  • Optimize images (resize, compress, WebP format)
  • Implement lazy loading for images
  • Use image thumbnails for list views

Performance Checklist

Application Level

  • Enable response compression (Brotli/Gzip)
  • Implement distributed caching (Redis)
  • Use async/await consistently
  • Enable response caching for static endpoints
  • Queue background jobs for heavy operations
  • Implement rate limiting
  • Use output caching

Database Level

  • Create appropriate indexes (46+ indexes implemented)
  • Enable Query Store for monitoring
  • Enable auto-update statistics
  • Use AsNoTracking() for read-only queries
  • Implement pagination everywhere
  • Avoid N+1 queries (use Include/ThenInclude)
  • Use projection to DTOs
  • Consider table partitioning for large tables

Infrastructure Level

  • Use CDN for static assets
  • Enable HTTP/2 or HTTP/3
  • Optimize images (resize, compress)
  • Set up connection pooling
  • Configure database connection limits
  • Use load balancer for horizontal scaling
  • Enable database read replicas

Monitoring

  • Set up Application Insights or APM tool
  • Configure health checks
  • Enable performance profiling
  • Set up alerts for slow queries
  • Monitor error rates
  • Track response times
  • Monitor database CPU/memory