Reduce the execution of multiple SQL queries

[stephbzr]

Hello,

I would like to optimise a method that is currently my only solution. I am developing an application and in the old version, there is a sequence of SQL queries whose objective is to find a data.

The schema is like the following:

Query 1, if no result then query 2, if no result then query 3, etc... There is a sequence of queries (6 to be exact). The first one that finds a result is the one we keep. 

This is what it looks like in Talend : 

And in my tMap : 

It works well, but I think the process is not very nice. I thought that maybe it would be interesting to code a PLSQL procedure in order to reduce it to a single component. 

But I call on your ideas, you never know. 

[Anonymous]

If your data in each of those queries is contained in the same database, it makes perfect sense to carry out a single query in that DB. A DB is built to process data incredibly quickly. Taking it out of the database to join it outside and filter outside will always be less efficient in terms of performance. Sometimes it is easier to do this for people who are not familiar with SQL and it can be less complicated to understand. But in this case, I suspect that a single query would actually make the job easier to understand and far more performant.

[Anonymous]

In case you want to keep the current solution I'd consider creating a routine for the coalesce function. (i.e get the first non-null, non-empty value)

Just define a routine:

  public static String coalesce(String... inputs) {

    for(String input : inputs) {

      if(input != null && !input.equals(""))

        return input;

    }

    return null;

  }

demo: https://www.jdoodle.com/iembed/v0/AB6