1

I have below in my stored proc

if
 then

    SELECT TABLE1.COLUMN1,TABLE1.COLUMN2,TABLE1.COLUMN3,TABLE1.COLUMN4 FROM TABLE1,TABLE2 where criteria1 (covers functionality 1)
    UNION
    SELECT TABLE1.COLUMN1,TABLE1.COLUMN2,TABLE1.COLUMN3,TABLE1.COLUMN4 FROM TABLE1,TABLE3 where criteria2 (covers functionality 2)
    UNION
    SELECT TABLE1.COLUMN1,TABLE1.COLUMN2,TABLE1.COLUMN3,TABLE1.COLUMN4 FROM TABLE1,TABLE4 where criteria3 (covers functionality 3)
elseif
 then

    SELECT TABLE1.COLUMN1,TABLE1.COLUMN2,TABLE1.COLUMN3,TABLE1.COLUMN4 FROM TABLE1,TABLE5 where criteria4 (covers functionality 4)
    UNION
    SELECT TABLE1.COLUMN1,TABLE1.COLUMN2,TABLE1.COLUMN3,TABLE1.COLUMN4 FROM TABLE1,TABLE6 where criteria5 (covers functionality 5)
    UNION
    SELECT TABLE1.COLUMN1,TABLE1.COLUMN2,TABLE1.COLUMN3,TABLE1.COLUMN4 FROM TABLE1,TABLE7 where criteria6 (covers functionality 6)

is it good idea to execute these SQL's separately (in parallel) and then put results in set (to get only unique) ?

I want to understand theoretically it is good idea or there is something I am not able to see right now?

Because SQL's are separate DB2 stored procedure internally might be running them in parallel already.

Vipin
  • 141

4 Answers4

5

Running your queries in parallel from your application, and then combining the output will never be as fast as running a tuned query from the database.

Do the work where it is most appropriate

Database engines are designed and optimized to work with data sets. Certainly some database engines are more efficient at this than others, but this is their primary task. Just by using a foreign key reference, your database can increase the performance connecting two tables (JOINing them) by multiple orders of magnitude.

Even when you can show me something that executes faster on the client than the database, you will still have to factor in the time it takes to move the data down the network pipe. It is naive to move 100K records to the client just to filter it down to the 1 or 2 that you need.

Sending large sets of data across the network will also negatively affect performance of other applications that use the network.

Adam Zuckerman
  • 3,725
  • 1
  • 21
  • 27
0

Since all your data comes from the same table this is an odd use of union.

Instead you could do a single select with a more complicated where statement.

Now! whether one is more performant than the other I think will depend on the where clause.

I'm not sure how you mean to run them in parralell? call them from code and iterate the results? This will be more scaleable if you are running your code on a differnt server to the database

Ewan
  • 83,178
0

Performance wise you will get the parallel run better but code wise it might be better to put everything in the stored procedure in a union.

In future if you need a modification ( say an update of the data fetched by joining some other table ) or say an other 3-4 unions added to the query . Now you will have to refactor your code which calls the stored procedure/queries heavily.

Learner_101
  • 280
0

I believe that using a single SELECT with a more complex WHERE clause is a better alternative, in terms of design.

I would also expect that, somewhat by chance, the UNION approach is slower because the database needs to eliminate duplicates. If you were using UNION ALL instead, I wouldn't be able to guess which alternative would have better performance (I'd have to measure it).

James Youngman
  • 3,192