0

I have a cascade queries from distinct tables/views/joins/whatever from my some SQL DB to process outer requests to my service.

One request is dependent on another.

For example, to process the purchase order I have the next queries:

  1. select for order and its properties;
  2. select all bills queued for this order;
  3. select for payments for this order;
  4. select for items in order;
  5. select customer's selected item variants for each item in order;
  6. select possible shipment companies;
  7. et cetera...

How could I select data from SQL database in distinct queries, and all of them return data in one round-trip?

The simple way is to join everything and doing row-mapping denormalized data from ResultSet to DTO objects. But there is additional overhead to aggregate fetched result with distinct entities along with consistency check (and this this computational complexity increased as O(n^m) where m is equal to entities count).

Traditional approach makes all total JDBC-queries for the one request to my service to look as weird staircase on Gantt chart :(

Gantt chart for JDBC SQL requests to DBMS

Marat Buharov
  • 331
  • 5
  • 18
  • https://stackoverflow.com/a/97253/2970947 – Elliott Frisch Oct 25 '18 at 23:52
  • Performing "the lookups in memory" is not possible due to memory limits on my service. I just want to send the batch of SQL SELECT queries to DBMS. But JDBC can do batches for INSERT/UPDATE/DELETE queries only. – Marat Buharov Oct 26 '18 at 00:02
  • Read "batches" are controlled by the query fetch size. Since you haven't posted any of your queries, it's hard to say more. – Elliott Frisch Oct 26 '18 at 00:08

0 Answers0