Pulling 500,000 records from SQL Server DB per day

, ,
1

Hi,

We have a requirement of pulling close to 500,000 records(on an average) per day from the DB and transforming it and then writing it to a flat file and SFTP the file. We tried the approach of pulling around 1000 records at one go and iterating the process until the row count is 0. But the DB team is not comfortable with us making so many calls to their system.

Is there a process that we could use which would reduce the load on both the DB and webMethods and helps us to accomplish the task ?

webMethods version :9.6

Please provide your valuable suggestions.

Thanks,
Sachin

2

Hi,
Write a storedProcedure, pull data of 10k records per each iteration and schedule the same per hr or so which ever is feasible to you.

Thanks,

3

Hi,

Thanks for the response. But the records need to be pulled at one time and further processing needs to be done. This service has to be scheduled only once per day and all processing has to happen then.

4

Hi,
from SP read all data and store in File in some server. Read file how ever you like. Here, DB hit is only once per day.

Thanks,

5

Since the service runs once a day, go for a normal select adapter service, get the data, parse it and then process it.

Any reasons for not opting the above solutions.

6

Also explore the terracotta cache capabilities. I have never practically experienced this, but I am sure it would suit your requirement if you have access to terracotta.

7

There will be on an average half million records sitting and running the select adapter service will have a serious performance issue on both the DB and webMethods IS. That is the reason I am not opting for that approach. Any suggestions to avoid loading the memory with so much data ?

8

Then write a scheduler which runs for every a few hrs, tell DBA to support on this.

9

Yes I would also suggest tune the select script/SP due to its prediction volumes and take DBA help to run the query faster for making adapter service performance optimization and make it run as a scheduled job once it is properly tested (assuming this runs in a prod cluster setup).

HTH,
RMG

10

The fact that the database team is not comfortable with you making 500 calls to their DB to pull 1000 records at a time is a bit silly. Did they back up their concerns with data? If their system can’t handle it, they should fix it so that it can or provide a better solution themselves, which leads me to the next point.

Since the database team is worried, I think MR as173d’s suggestion is perfect: push it back to them and tell them to create a stored procedure that simply dumps the file that you need. You then just have to worry about transferring it.

This solution should solve their problem and also make your life easier. Win win. :slight_smile:

Percio

11

Sachin, Don’t bang your head. Set up a call with DB team, explain the situation, also include your manager who can support you. There is a solution for every problem, let us know new comments from DB Team.

Thanks,