Question

4
Replies
665
Views
BhagyashreeC8894 Member since 2016 9 posts
Cognizant
Posted: October 30, 2017
Last activity: November 10, 2017
Closed

Retrieval Time from DB when records are fetched in multiple calls

Hi,

Does the retrieval time from DB and its corresponding copying on Clipboard improve when DB calls are made in a loop (breaking the number of items in chunks) rather than in one go?

Requirement:

We need to run a report definition from a utility on a data table that has around 1,00,000 records, and generate a csv file from this data and send as an email. The email part looks fine. But we need to finalize on the data retrieval part.

Following are the approaches:
We can either fetch these all at once (we are going to use an agent at the backend for this processing- in normal scenarios user thread dies)- copy this to the file and send email. This is taking long processing time.
OR
We can divide the query logic to run in batches in a loop and make DB calls- then append this to the file in each loop and finally send email. (Also to reduce load on DB)

What advantages does the second approach have?

1. In terms of DB performance

2. In terms of retrieval time. (Currently approach 1 is taking more than an hour for processing a file of 50,000 records!)

Also, what are the disadvantages with approach 1.

Regards,
Bhagyashree

Data Integration Java and Activities Data Integration System Administration Reporting
Moderation Team has archived post
Share this page LinkedIn