Thanks for the question, Pui.
Asked: February 08, 2016 - 5:03 pm UTC
Last updated: February 09, 2016 - 10:06 am UTC
Version: 11g
Viewed 1000+ times
You Asked
Which of the following approach for fetching data from two DBs is desirable?
(1) Application (connection pooled)
call stored procedure x on DB1 data
DB1 stored procedure x query data on DB2 through DB link
DB1 stored procedure x assemble data from DB1 and DB2 returning to application
(2) Application (connection pooled)
call stored procedure x on DB1 data
call stored procedure y on DB2 data
application assemble data from DB1 and DB2
There is claimed that approach (1) will cause lot of expensive opening/closing db connections.
Need a opinion on this. Thanks.
and Chris said...
I'm assuming that by "assemble" you mean join the two data sets together.
If so, option 1 - joining through a database link - is the better solution.
Why?
Because with option 2 you need to write the join at the application tier. This means you have more code to write. So your application will take longer to develop.
Managing performance is also harder. If the underlying data change, you may need to rewrite your application joins so they execute "fast enough". If you do the join in the database the optimizer can adapt without you needing to do anything.
Database links keep the connection to the remote database open until you end the session or manually close the link. Whether this results in more opening/closing db connections depends upon how you're managing your application pools.
If the two data sets are completely separate (you're not going to join them in any way) then option 2 may be better. This because you can run both queries at the same time.
Is this answer out of date? If it is, please let us know via a Comment