JDBC has been supporting bulk updates for a long time using addBatch
and executeBatch
. Why isn't there any support for adding a bunch of prepared statements and getting an array of result sets as response?
For example, if I wanted to load customer details, basic account details, basic card details, basic loan details etc. for a single view, I would prefer to create a bunch of prepared statements and append the prepared statements to an ArrayList and execute them as a batch. I would then loop through the result sets and process the data. Hopefully, several network round trips would be saved (assuming my queries are performant).
Sample bunch of queries:
SELECT custid, first, last, age FROM Customer where custid = ?
SELECT custid, acno, accountname, accounttype, status FROM Account where custid = ?
SELECT custid, cardno, cardname, cardtype, status FROM CreditCard where custid = ?
SELECT custid, loanno, principal, rate FROM Loan where custid = ?
I can imagine several hypothetical reasons why it could be a bad idea. But, I am not sure which is most likely true in the real world.
Hypothetical reasons against having bulk-fetch:
- There is some fundamental networking/db stack/memory related issue which prevents a bunch of select queries to be executed on the same connection and result-sets kept open.
- Response handling code would be too cumbersome, as there could be exceptions at call level and individual statement level. And, several statements would have to be closed correctly.
- There is no significant performance gain in reducing the number of network-calls. Query execution is the main bottleneck and network round-trip cost is insignificant.
- There could be misuse of such a feature. A single non-performant query batched up like this with other queries could cause application to perform poorly.
The reason I ask this is because often I see a lot of Join queries which merge parent-child relationships into a single SQL query, just for the sake of completing the loading in a single call.
However, as the number of tables grows, the query becomes complex. Also, the parent table information is repeated in every row of every child. So, there is huge amount of data redundancy in the single join-ed result set.
Sample join query:
SELECT custid, first, last, age, acno, accountname, accounttype, a.status, cardno, cardname, cardtype, c.status, loanno, principal, rate
FROM Customer cc, Account a, CreditCard c, Loan l
WHERE a.custid=CC.custid(+) and c.custid=CC.custid(+) and l.custid=CC.custid(+)
Aucun commentaire:
Enregistrer un commentaire