Question
Evoke Technologies
IN
Last activity: 10 Nov 2017 9:04 EST
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
ING Nederland N.V.
NL
If you are using single-node system, performance cannot be improved drastically in any approach that you use.
If you have multi-node system,
1. Write an activity which can process 500/1000 records at a time and write it to the file using connect-file rule
2. Enable that agent in all batch nodes. Configure your agent to run periodically so that all the records can be processed
3. To avoid duplicate processing, add columns to the main table to contain details about the Status [Scheduled, Processing and Processed], Node ID
4. When the agent activity picks the top 500 records for processing, update the status as Processing and node ID columns. Also add logic to pick up records which are only in scheduled status.
Records will be processed faster based on the number of batch nodes.
ING Nederland N.V.
NL
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]
Current Scenario:
Total Records : 50000, Processing TIme: 60 Minutes
Above Approach:
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.