GCI72.asciidoc

Don’t execute SQL queries in loops.

Why is this an issue ?

Executing SQL queries in loops induces unnecessary calculation by the CPU, RAM usage and network transfer. This can potentially lead to severe performance issues, especially when the loop is executed a large number of times. Instead, consider using a single bulk query to massify the call.

When can it be ignored ?

This warning shouldn’t be ignored.

Non compliant example

const string baseQuery = "SELECT name FROM users WHERE id = ";

using var conn = new SqlConnection(MyConnectionString);
using var cmd = conn.CreateCommand();
for (int i = 0; i < 20; i++) {
    cmd.CommandText = $"{baseQuery}{i}";
    using var reader = cmd.ExecuteReader(); // Noncompliant
    while (reader.Next())
        Console.WriteLine(reader.getString(0));
    cmd.Close();
}

Compliant example

string query = $"SELECT name FROM users WHERE id IN ({string.Join(",", Enumerable.Range(0, 20))})";

using var conn = new SqlConnection(MyConnectionString);
using var cmd = new SqlCommand(query, conn);
using var reader = cmd.ExecuteReader(); // Compliant
while (reader.Next())
    Console.WriteLine(reader.getString(0));