Retrieval Time from DB when records are fetched in multiple calls
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?
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.
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!)
Regarding updating the additional columns,yes it will consume little extra time but it won't be having performance impact as we are updating 500/1000 records at a time.
I mentioned above steps only for Multi-node environment. If you have 4 batch nodes, the records will be shared across the nodes and the processing time will improve. [Approx 1/4th of your current processing time]
Total Records : 50000, Processing TIme: 60 Minutes
Processing Time : 60 Minutes, Updating the extra columns to avoid duplicate processing : Approx 10 Minutes
Total time : 70 -80 Minutes. Total number of Batch node : 4, Time take by multi-node processing : 70/4 = Approx 16-20 Minutes.
If it is a single node environment, as I mentioned earlier Performance cannot be improved drastically irrespective of the design.
You can check the performance by trying this approach in lower environment.
Also, check whether you have required indexes for the columns which are used in where clauses.