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.

  • It doesn't make any difference. However, I view UNIONS as a "code smell." Their existence indicates a potential problem with your database design. – Robert Harvey Apr 24 '16 at 6:31
  • @RobertHarvey In each SQL i retrieve result for a particular functionality. And depending upon use case(separated by if else in proc) I combine set of functionalities(2 or more) using union in proc. I was thinking of removing PROC and put separate functionalities in separate SQL and depending upon use case i can call required SQL's in parallel and merge result. – Vipin Apr 24 '16 at 7:06
  • Using implicit joins is depracated. I'd advise against using them. – Pieter B Apr 24 '16 at 7:26
  • @PieterB point taken, i just wanted to express an idea here. Main aim is towards performance if i execute these sql's in parallel, can i get performance improvement or it will degrade or no change? – Vipin Apr 24 '16 at 7:47
  • @RobertHarvey why do you think it doesn't make any difference ? in stored proc both SQL's will be executed sequentially(as i understand) and then UNION will be performed, parallel execution of SQL's should be faster, isn't it ? – Vipin Apr 24 '16 at 8:09
4

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.

  • +1. A query containing unions is still a single query, and a good optimizer will be able to wring out a good execution plan from the SELECTs. – Blrfl Apr 24 '16 at 20:22
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

  • as you can see in question all SQL's are there for corresponding functionalities so I have separated them. There will not be any change in where clause of SQL so performance is not dependent on that. To answer your question I can run them in parallel using java threads and will use guvava's ListanableFuture to fetch result when all Threads(SQLs) are completed. And yes my application and DB are of separate servers. – Vipin Apr 24 '16 at 10:10
  • i think it will run faster if you conbine the where clause (if a or b) rather than (if a) + (if b) but its a close thing and depends on what the clauses and indexes are – Ewan Apr 24 '16 at 10:53
  • i agree but i want to keep SQLs separate as they belong to separate functionalities and i want to use their combinations. – Vipin Apr 24 '16 at 11:01
  • In that case I would run them individualy from code. Keeping the sprocs as simple selects. Not faster, but def more scaleable, plus neater as you say – Ewan Apr 24 '16 at 11:21
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.

  • thats what my question is why do you thing performance wise it will be better? – Vipin Apr 24 '16 at 10:19
  • say you have 2 queries. Q1 ( 5 ms ) and Q2 ( 5 ms) if you execcute them sequentially you wil get a performance of 5 + 5 = 10 ms. Union executes them sequentially. if you execute them in parallel you get say 5 ms each. But you take some time for combining results say 1 ms. so you got a performance of 6 ms vs 10 ms. this is just an example. Also it depends on number of database server CPUS. That can affect performance also. Hope you get the drift – Learner_101 Apr 24 '16 at 11:01
  • Keeping exactly this in mind i started this question. But I am looking for some concrete proofs/examples and moreover expert advice. – Vipin Apr 24 '16 at 11:10
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).

  • I agree with your first option, but I want to keep them separated in order to use their combinations. Union ALL may not work because in that case i need to delete duplicates in code and that is going to take similar time. – Vipin Apr 24 '16 at 10:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.