Daniele worked at a pharmaceutical firm that had an old web application that allowed commercial customers to look up information. Since the data was quite complicated, there were numerous fields that needed to be queried in order to populate the form.

Unfortunately, as the amount of data in the system grew, the time to load the form grew as well. And grew. And grew.

Fortunately, the DBA in charge of setting up the underlying tables was actually quite capable at setting up tables with the proper relationships. For example, an address consists of street, city, zip, province and country. A country can contain multiple provinces which can contain multiple cities which can contain multiple zip codes, and so forth. As it was well organized, the database was not the problem; the source of the slowness was likely in the code.

And what code it was. The programmer that engineered this had to have revered this piece of brillance as well. They decided that they would support substantial data growth by querying the data in parallel. Yes, there would be a separate query for each field - run in a separate thread - in parallel. In other words, all of the queries had essentially the same where-clause (except for the joins); only the fields that were selected were different. For cases where one field depended upon another, the dependency was handled like this in the corresponding query classes, which all followed the same pattern:

   class StreetQuery implements Thread {
      // Street names can be duplicated. We need to know in which 
      // city this street resides in order to query for it.
      private CityQuery city;
	  
      private boolean finished = false;
	  
      public StreetQuery(CityQuery city) {
        this.city = city;
      }
	  
      public boolean isRunning() {
        return !finished;
      }
	  
      public void run() {
        // Wait until query on which we depend finishes
        while (city.isRunning());
		
        // do query here, using any results from dependent queries as needed
        finished = true;
      }
    }

Daniele replaced all of that with a single stored procedure and the delays were gone.

One can't help but wonder if the author of the original code might have been helping themselves to a few too many sample products...

[Advertisement] BuildMaster allows you to create a self-service release management platform that allows different teams to manage their applications. Explore how!