.NET Core中多SQL查询生成矩阵结果的性能优化与格式选型

The Problem #

I'm trying to generate a 10×10 matrix for a frontend, where both X and Y axes are split into 10 quantile-based ranges. Each cell in the matrix needs to count the number of distinct UserIds that fall into the corresponding X-Y range. Right now, my code runs 100 separate SQL queries (one per cell), which is terrible for performance. I'm also stuck deciding whether returning a Dictionary with 100 entries or a multi-dimensional array is better practice.

Here's my current code:

public async Task GenerateMatrix(List<double> x, string xAxis, List<double> y, string yAxis, Parameters parameters) {
    IDictionary<string, string> xDict = GenerateRanges(x, parameters.XAxis);
    IDictionary<string, string> yDict = GenerateRanges(y, parameters.YAxis);
    var innerJoin = GenerateInnerJoin(parameters);
    var whereClauses = GenerateWhereClause(parameters);
    var sql = $@"SELECT COUNT(DISTINCT [dbo].[{nameof(Table)}].[{nameof(Table.UserId)}]) FROM [dbo].[{nameof(Table)}] {innerJoin} ";
    if (whereClauses.Any()) {
        sql += " WHERE " + string.Join(" AND ", whereClauses);
    }
    for (int i = 0; i < x.Count; i++) {
        var queryToExecute = "";
        for (int j = 0; j < y.Count; j++) {
            queryToExecute = sql + " AND " + xDict.Values.ElementAt(i) + " AND " + yDict.Values.ElementAt(j);
            var userCount = await Store().QueryScalar<int>(queryToExecute);
        }
    }
    return null;
}

private IDictionary<string, string> GenerateRanges(List<double> axis, string columnTitle) {
    IDictionary<string, string> d = new Dictionary<string, string>();
    for (int i = 0; i < axis.Count; i++) {
        var rangeSql = $@" [dbo].[{nameof(Table)}].[{columnTitle}]";
        if (i == 0) {
            d.Add(axis[i].ToString(), rangeSql + " < " + axis[i]);
        } else if (i == axis.Count - 1) {
            d.Add(axis[i] + "+", rangeSql + " > " + axis[i]);
        } else {
            d.Add(axis[i-1] + "-" + axis[i], rangeSql + " > " + axis[i-1] + " AND " + rangeSql + " < " + axis[i]);
        }
    }
    return d;
}

Example single query:

SELECT COUNT(DISTINCT [dbo].[Table].[UserId]) FROM [Table] WHERE Table.[ClientId] = '2' AND [dbo].[Table].[ProbabilityAlive] < 0.1 AND [dbo].[Table].[SpendAverage] < 24.86

Solution 1: Fix Performance with a Single Query #

Running 100 queries is a huge waste of database resources (round-trips, query parsing, etc.). Instead, we can calculate all 100 cell counts in one query using CASE WHEN to map rows to their X/Y segments, then group by those segments.

Step 1: Generate Segment Mapping Logic

Modify your range generation to output CASE WHEN clauses that assign each row to a segment number (1-10) for X and Y axes:

private string GenerateSegmentCase(List<double> axis, string columnTitle) {
    var caseClauses = new List<string>();
    for (int i = 0; i < axis.Count; i++) {
        string condition;
        if (i == 0) {
            condition = $"{columnTitle} < {axis[i]}";
        } else if (i == axis.Count - 1) {
            condition = $"{columnTitle} > {axis[i]}";
        } else {
            condition = $"{columnTitle} > {axis[i-1]} AND {columnTitle} < {axis[i]}";
        }
        caseClauses.Add($"WHEN {condition} THEN {i+1}"); // Use 1-based segments for clarity
    }
    // Catch edge cases (e.g., values exactly equal to boundaries)
    caseClauses.Add("ELSE 0");
    return $"CASE {string.Join(" ", caseClauses)} END";
}

Step 2: Rewrite the Matrix Generation Method

Now build a single SQL query that groups by X and Y segments, then populate your matrix from the results:

public async Task<int[,]> GenerateMatrix(List<double> x, string xAxis, List<double> y, string yAxis, Parameters parameters) {
    // Initialize 10x10 matrix with default 0s
    var matrix = new int[10, 10];
    
    var innerJoin = GenerateInnerJoin(parameters);
    var whereClauses = GenerateWhereClause(parameters);
    
    // Generate segment case logic for X and Y axes
    var xSegmentCase = GenerateSegmentCase(x, $"[dbo].[{nameof(Table)}].[{xAxis}]");
    var ySegmentCase = GenerateSegmentCase(y, $"[dbo].[{nameof(Table)}].[{yAxis}]");
    
    // Build the optimized query
    var sqlBuilder = new StringBuilder();
    sqlBuilder.Append($@"
        SELECT 
            {xSegmentCase} AS X_Segment,
            {ySegmentCase} AS Y_Segment,
            COUNT(DISTINCT [dbo].[{nameof(Table)}].[{nameof(Table.UserId)}]) AS UserCount
        FROM [dbo].[{nameof(Table)}] {innerJoin}");
    
    if (whereClauses.Any()) {
        sqlBuilder.Append($" WHERE {string.Join(" AND ", whereClauses)}");
    }
    
    sqlBuilder.Append(" GROUP BY X_Segment, Y_Segment");
    
    // Execute once and fetch all results
    var results = await Store().QueryAsync<(int X_Segment, int Y_Segment, int UserCount)>(sqlBuilder.ToString());
    
    // Populate the matrix with results
    foreach (var (xSeg, ySeg, count) in results) {
        // Skip invalid segments from the ELSE clause
        if (xSeg < 1 || xSeg > 10 || ySeg < 1 || ySeg > 10) continue;
        // Convert to 0-based index for array access
        matrix[xSeg - 1, ySeg - 1] = count;
    }
    
    return matrix;
}

This cuts your database round-trips from 100 to 1, which will drastically improve performance.

Solution 2: Return Format Best Practices #

Let's break down the two options you're considering:

Option A: Multi-Dimensional Array (int[,])

• Pros:
  • Directly mirrors the 10×10 grid structure—intuitive for both backend code and frontend rendering.
  • Faster index-based access compared to dictionary lookups.
  • Frontend can easily iterate over rows/columns without parsing keys.
• Cons:
  • Doesn't include explicit range labels (but you can send these separately if the frontend needs them).

Option B: Dictionary (Dictionary<string, int> with keys like "1-1")

• Pros:
  • Explicitly ties counts to their segment pairs, which can help with debugging or logging.
• Cons:
  • Requires frontend code to parse keys (split on "-") to map to grid positions.
  • Less efficient than array access, and the structure doesn't visually reflect the grid layout.

Recommendation

Go with the multi-dimensional array for this use case. It's the most efficient and natural fit for a fixed-size 10×10 matrix. If the frontend needs to display the actual range values (e.g., "0-0.1" for X segment 1), you can return a separate object with axis range labels alongside the matrix.

Alternatively, if you want to bundle range info with each count, you could return a list of objects like:

public class MatrixCell {
    public string XRange { get; set; }
    public string YRange { get; set; }
    public int UserCount { get; set; }
}

But this adds extra data transfer compared to a raw array, so only use this if the frontend needs those labels directly.

内容的提问来源于stack exchange，提问作者user122222