We require to fetch all the form records from TM with tracking codes for a month to generate a report. It may be around 50k or more. What is best and optimize way to achieve that?
What kind of data to you need in your reports? Transaction metadata or PII data ?
For transaction metadata reports on finished transactions, I would recommend you use the REST Transaction History API to pull the data into your system (Transaction History API), or use the Transaction History Publishing services to push this data into a data warehouse data injection pipeline.
For reports which require a mixture of transaction meta data and PII data I would recommend you create a report using the submission data extracts facility. Note however if you are doing monthly reports you this may not line up with your systems PII data retention policies, with the PII data being deleted before the monthly report. To manage this either increase the reporting frequency, or review the the PII data retention policy.
You can use BIRT. Refer to the guide at Developing Reports