Using ExecutorService to execute 4 db2 fetch methods in parallel. Is this right way?-CodePudding

So i have 4 java methods that fetches data from DB2, i have commented along the corresponding time it takes to fetch the result from DB2:

db2.fetchCigarProducts(id) // time taken 625 ms

db2.fetchPowderProdcuts(id) // time taken 327 ms

db2.fetchLegacyCigars(id) // time taken 314 ms

db2.fetchDairyProduct(id) // time taken 443 ms

Obviously the time taken would vary depending on the id given as input (parameter), but this is the average time for most of the calls.

This is the way the code is structured,

the pseudo-code of the calling Function :

for(String itemId: combinedItemIds) 
{
 fetchAndProcessDataFromDB2(itemId);
}

public void fetchAndProcessDataFromDB2(itemId) {

    db2.fetchCigarProducts(id) // time taken 625 ms
    db2.fetchPowderProdcuts(id) // time taken 327 ms
    db2.fetchLegacyCigars(id) // time taken 314 ms
    db2.fetchDairyProduct(id) // time taken 443 ms


}

Now in this above normal flow (without any multithreading), the time for fetchAndProcessDataFromDB2 for each itemId is around 1500 ms ~= 1.5 seconds.

So to process these 4 methods parallely to save time, I made the 4 methods synchornized and tried to use exectorService in the following way :

public void fetchAndProcessDataFromDB2(itemId) {
  ExecutorService executorService = Executors.newCachedThreadPool();

    executorService.execute(
                () -> {
                    db2.fetchCigarProducts(id);

                }
    );
    executorService.execute(
                () -> {
                     db2.fetchPowderProdcuts(id);
                }
    );
    executorService.execute(
                () -> {
                    db2.fetchLegacyCigars(id)

                }
    );
    executorService.execute(
                () -> {
                     db2.fetchDairyProduct(id);
                }
    );
   
    executorService.shutdown();

        try {
            executorService.awaitTermination(5, TimeUnit.SECONDS);

        } catch (Exception e) {
            e.printStackTrace();
        
        }

}

I was expecting 4 of the above methods would be executing in parallel, and was expecting a rought time of 400-500 ms in total. But to my surprise, the total time taken by fetchAndProcessDataFromDB2 with executorService is around 1.7 seconds, a bit more than the fetchAndProcessDataFromDB2 method without multithreading.

Can someone help me understand why is it that way? Any tips/pointers is appreciated. Thanks

CodePudding user response：

Can someone help me understand why is it that way? Any tips/pointers is appreciated. Thanks

Your code looks correct so I suspect that something in the db2 object is either serializing the database operations or maybe the server cannot perform the 4 queries simultaneously any faster.

To be more specific, the db2 object may have a single database connection that it is using to talk to the database. It may lock the connection inside the fetch calls so even though there are multiple threads calling fetch...(), only 1 of them is actually doing work at one time.

In terms of the database, each of the fetch... operations may be causing some sort of table scan of a large table. Running multiple of them at the same time wouldn't necessarily cause the fetches to run any faster and could even cause the underlying storage mechanism to run slower since it has to be reading from 4 different locations during the concurrent operations.