Do you need to query a massive SQL table with millions of rows, but there is no index on the column you care about? In this post, we’ll look at a simple technique to speed up your queries by using only the index that you do have — the primary key — to improve performance.
Including the primary key in the SQL query when no other index exists on the target table can significantly reduce the amount of database resources it takes to find the data you are looking for.
The Problem: Nested Loops and Repeated Queries Data lookups can often cause a common performance problem to be introduced into code. In this post, we’ll look at an example of that problem and one possible solution.
For this example, we’ll be using a C# console application that reads from the country table of the MySql sakila sample database. However, the refactoring solution presented is language and database agnostic.
This solution is only meant to demonstrate a common problem with nested loops.
After 25 years in software development, I’ve come to realize that every decision involves a trade-off.
Do you need to make an algorithm highly performant? Then you may need to make some sacrifices in maintainability and readability.
Do you need to make the architecture of a software system extremely flexible and resilient to change? Then you may need to make the architecture more complex.
Do you need to use a framework that is a great fit for solving specific business needs?
