Performance — the jumping problem — too many database (or something else) calls

var result = GetResultsFromDatabase();
foreach(var row in result)
{
var otherResult = GetOtherResultFromDatabase(row);
//Do something
}
  1. Retrieve the first 100 sales and, for each sale, retrieve it’s respective vendor;
  2. Retrieve the first 100 sales and its vendor together.
  • Jumps to the database: 101
    1 jump to retrieve firsts 100 sales and + 1 each vendor.
  • Time spent on the database: 187583 ms
  • Jumps to the database: 1
    1 jump to retrieve the joined query.
  • Time spent on the database: 45094 ms
  1. These times are on my machine;
  2. The database are fully available for my application;
  3. Numbers of columns selected can also affect this time;
  4. Could be cases where it’s slowly to retrieve all information joined, it depends on the scenario, indexes, tecnologies and so on;
  5. Could be cases where a business logic prevent the use of such thing.

Reproduce it yourself:

Copy the repository code here and stack here.
I did not connect the Docker to the Visual Studio solution because I felt comfort running the application locally and the database on a container.

cd /path/to/copy/of/repository
mkdir postgres_data
docker-compose build
docker-compose up -d
  1. The profiler display 101 records reads in the first line and 2 on the others because, I guess, the rows counts as one or more and it’s metadata counts as plus one;
  2. It’s mandatory to use Entity Framework to display the query on the profiler (or ADO.net). Source here;

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store