Scenario :
So here is the scenario, one of our client has a requirement which demanded huge amounts of data to be read from database and transform the data into a proper XML format. All the data for a particular scenario together will form an XML structure (A complete XML Structure will be formed by reading all the rows of the query response) which has to be read completely and formed into one XML file and then this file has to be transformed to some other format.
So, In order to perform this task the team has implemented cursor which reads the data from the database chunk wise. Additionally the query whatever is used to read the content from database is unfortunately 4500 lines which is written by the client and hence is close to "alien" language to the team. Hence the team has blindly executed the query from WsApp Layer. This query of the client runs for more than 5 mins to fetch the data.
As the situation demands cursor implementation there is a "query.getObjects()" triggered multiple times based on the cursor. But fortunately/Unfortunately the java code to read the data from DB and write into XML File is taking loads of time which raises the flags on performance. So 5 loops means 5 times query.getObjects() is triggered and its taking close to 5 * (Time to fetch the results of the query). which is a performance threat to the client. Hence to overcome this performance threat the team has followed below points,
So, In order to perform this task the team has implemented cursor which reads the data from the database chunk wise. Additionally the query whatever is used to read the content from database is unfortunately 4500 lines which is written by the client and hence is close to "alien" language to the team. Hence the team has blindly executed the query from WsApp Layer. This query of the client runs for more than 5 mins to fetch the data.
As the situation demands cursor implementation there is a "query.getObjects()" triggered multiple times based on the cursor. But fortunately/Unfortunately the java code to read the data from DB and write into XML File is taking loads of time which raises the flags on performance. So 5 loops means 5 times query.getObjects() is triggered and its taking close to 5 * (Time to fetch the results of the query). which is a performance threat to the client. Hence to overcome this performance threat the team has followed below points,
- The code has been reviewed and necessary steps are taken at java level.
- It has been observed that the indexes are missing @DB level which were present in quality environment and were missing in development environment. Reduced 5 mins to around 2 mins to read a chunk of data.
- Another major point that I personally came to know here is the usage of "Refresh Interval" of "Cursor cache" in Ws-App container.
- The Refresh Interval of the container was kept by default which is 30 secs (By this means the cache of the cursor will be present only for 30 secs and next query will be a hit to DB directly).
- As the setting was 30 secs and the query in the above scenario will retrieve data almost 3 mins and there is no use of implementing query in this case as the next fetch will be done from the DB directly instead of taking from the cursor as the time has elapsed (30 secs). Hence by changing the setting of the container helped the problem of performance to vanish. We had kept a refresh interval of 5 mins by which the data would be retrieved in the first loop and will be using the cache of the cursor from the next loops again. Hence by this we could able to bring down the time to perform the operations drastically.
