1

I have a complicated problem with Java8 and Oracle. Our API with Java8 is responsible for providing 1000 records per call from Oracle DB. We are using JDBC Spring. Into DB design , we have 2 table.

  1. Merchant : Id | name | and other columns Total columns into this table is 11
  2. Merchant contact : This is table with Merchant contact information for 3 different contact type So , we have 3 rows per merchant into this table
  3. Merchant relationship : This table keep the information about Merchant Id and parent Merchant Id

Our API needs to provide all the merchant information belongs to requested parent merchant id.

We have joins into Oracle Stored Proc and we have indexes as well. When we reduce the number of columns to return from DB, this API perform well. As long as the number of columns increases, the API performance slowing down. To get 1000 records , this API is taking around 2.5 seconds in average. Whether the smaller set of information from DB with 1000 records is taking around 500 milliseconds [ good performance ]

We also have fetch-size defined during the data read operation from JDBC to Oracle. Oracle version is 12.x and API is in Java Vertx

Please suggest.

arupc
  • 356
  • 1
  • 3
  • 12
  • How redundant is your data (The JOINs can cause a lot of redundancy)? Fetching all ids and then fetching the data just once may be faster. – maaartinus Jun 21 '20 at 06:00
  • Please see [here](https://stackoverflow.com/a/34975420/4808122) what information (table structure, data sizes, execution plan) you must post. The **good news** is that if you *need 2+ seconds to get 1K rows* you have a *lot of space to improve* – Marmite Bomber Jun 21 '20 at 10:38
  • *We have joins into Oracle Stored Proc* - does it mean *loop one cursor* and *fetch rows from other table in the loop*? – Marmite Bomber Jun 21 '20 at 10:46
  • The joins are simple here, 1st join : merchant with merchant relationship and 2nd join: merchant with merchant contact. These minimal joins we need to perform. – arupc Jun 22 '20 at 05:14
  • Yes , we need to find the scope to improve here. So, I am looking for some solution design with best practices guidelines here. – arupc Jun 22 '20 at 05:16

1 Answers1

1

How do we use web search engine ?

How many result pages do you really look at ? 1, 2, ... 10, ... 50, more ?

Most users look only at first page, some 2 or 3 and then they try to refine the search to have more focused results.

Do your users really need 1000 records ? Are they really looking at 1000 records ?

See https://blogs.oracle.com/oraclemagazine/on-top-n-and-pagination-queries

pifor
  • 7,419
  • 2
  • 8
  • 16
  • Yes , as UI has dropdown and other controls to display all the merchants and it needs to be pre-populated. Also, we think about to cache this response, but since the response size is high in volume , so cache may not perform best way. Also , caching needs to be update each time as merchant added/updated. – arupc Jun 20 '20 at 18:46
  • Also, we have partitioned the data, total records are around 10K , we are getting only 1K per call, so it is around 10 calls per user , but good point is the data is common for all users. Should we do any change into API design, if yes then what why and how. – arupc Jun 20 '20 at 21:40