Question Regarding Invocation of Stored Procedures in Database Writer

[snarayanan99]

Our application has a Database Writer destination, where we invoke Stored procedures in Microsoft SQL server.
There are a number of Stored Procedues that we invoke in the database Writer destination. Approximately, there are around
400,000 Stored procedure invocations. The Channel seems to be running fine with parsing an input attachment Hl7 file of around 40MB in the Source Connector.
We currently have 8192MB in the mcService and mcServer VMOptions file. We find that it takes approximately 2.5 hours for all the database operations to be completed.
One of the options that we are considering is to consolidate some of the Stored Procedures to minmize the number of database calls.
Apart from that, are there any optimizations that can be done on the Mirth side to facilitate faster database operations.
We do only SELECT and INSERT operations mostly through the Stored Procedure calls.

[rjkroll]

Need more context. Whats in the Hl7 file? Are you parsing records one by one, then executing 4000 stored procedure calls? Are you connecting to the server for each call? How long does it take if you didn't do it through Mirth? Could you just put the data you are using for the stored procedures into a staging table, then execute all the procedures again the whole table instead of having Mirth do all the processing?

For us, when we process large CSV's we used to do it through MIrth but it took awhile, so we ended up just converting everyting to batch imports via Powershell scripts and just calling the script via a transformer step.