ADO - ADO.NET Performance Tuning & Profiling

1. What performance tuning actually means

Improving latency, throughput, and resource usage of data access by:

Profiling = measuring before fixing.

Issue	Impact
Opening connections repeatedly	High latency
Missing parameters	No plan reuse
Row-by-row operations	Extremely slow
Over-fetching columns	Memory waste
Long transactions	Blocking & deadlocks

using (SqlConnection conn = new SqlConnection(cs))
{
    conn.Open();
}

✔ Always close connections
✔ Never keep global connections
✔ Standardize connection strings

cmd.Parameters.Add("@id", SqlDbType.Int).Value = id;

Benefits:

Wrong API = wasted resources

❌

SELECT * FROM Orders

✔

SELECT OrderId, Amount FROM Orders

✔ Faster network
✔ Less memory
✔ Faster GC

Never loop inserts unless unavoidable.

✔ Keep transactions short
✔ Choose correct isolation level
✔ Avoid long-running transactions

Bad:

tx.Begin();
// UI + DB logic
tx.Commit();

Rule:

If you don’t measure it, you’re guessing.

Checklist:

❌ Premature optimization
❌ Blaming DB without profiling
❌ Using ORM defaults blindly
❌ Increasing timeouts instead of fixing root cause