Processing very very large messages (3gigs) HELP!

Hi Clay,

There is a performance hit when trying to insert that many records (100,000?) to a SQL table using the SQL adapter in an orchestration.

Having said that, short of architecting your BizTalk environment (version 2006) to take advantage of mutiple hosts and host instances configuration to make it more scalable and have better performance (this is more of a long-term design and architecture issue), you can use the following two short-term approaches, in conjunction or on its own:

1) Limit the number of records fetched back from Oracle adapter (relatively easy).

2) Break up the message (fetched back from Oracle adapter) into more manageable pieces that you can feed to the SQL adapter (relatively harder).

Best of luck!

Daniel.

Normal 0 false false false MicrosoftInternetExplorer4

Thanks Daniel,

When all is said and done I need to retrieve 1.6 million records from Oracle and get them into SQL.  I like the idea of limiting the number or records fetched from Oracle.  I could retrieve 10,000 record chunks and pass them to SQL.  Is there a simple way to do this, or documentation?  I'm not exactly sure where to start.

Regards,

Clay

Hi Clay,

In your Oracle database, create an auxiliary table with the same data structure as your main table where you will be pulling your records from, and add an extra column to that auxiliary table as an integer type column that serves as a batch increment indicator (which will be useful later on).

Still in Oracle, when creating your stored procedure, do the following:

- Define a parameter variable as integer type that will serve as the batch increment indicator

- In the body of the Stored procedure, define an INSERT statement to insert rows into your auxiliary table using the TOP predicate to select a range of records from your main table. In your SELECT statement clause, use a WHERE clause to filter against those existing records in the auxiliary table based on a set of criteria that defines the uniqueness of each record. Remember to include the above parameter variable in the SELECT statement.

- After the above INSERT statement, define a SELECT statement to extract those rows from your auxiliary table whose batch increment indicator value matches the value of the parameter variable.

Back in your BizTalk orchestration, after you have generated the SQL schema that matches the signature of the aforementioned Oracle stored procedure, in the flow of the orchestration, define a Loop shape where you will be first calling your Oracle stored procedure in request/response style through an adapter, passing a batch increment value (defined in a scoped variable at orchestration level), then you will transform the response message from the Oracle SP, and use its output message to pass into your SQL adapter call. Each time your orchestration goes through the loop, you will increment the batch increment value. The condition to terminate the loop is to check on the rows returned from the Oracle SP call: if there are no more rows in the last call to Oracle SP, then set a flag that will break out of the loop and terminate the orchestration.

Following up on my original reply to your post, there is another way to send all your 1.6 million records from Oracle to SQL Server in one shot: you will have to define your stored procedure in SQL Server to have a parameter variable of type Xml, which will then receive the BizTalk message in serialized format; however, you will have to parse the data in your SQL SP, using XQuery, to populate the SQL table on that end, which doesn't impact the SQL engine performance-wise, but I have not tested this kind scenario with this volume of records and I don't know what your level of skill is with regards to XML and XQuery parsing, in order to implement this.

I hope all this information helps.

Best,

Daniel.

In my experience this is not a great way of performing this sort of task.

If you are only shifting data from oracle to SQL and there is minimal mapping and no further interaction with the data I would strongly recommend a more suited technology such as SSIS. 

I have had a very similar problem with significantly smaller file sizes (100MB) that I had to pick up a batch file and stick it in sql.

Almost every angle I turned to I got an out of memory exception or very erratic behaviour from BizTalk (a symptom of the low memory i think)

I tried all sorts of tricks, including the aforementioned xquery and xml parameters.  While useful in principle, the practicality of doing this means your

In my experience this is not a great way of performing this sort of task.

If you are only shifting data from oracle to SQL and there is minimal mapping and no further interaction with the data I would strongly recommend a more suited technology such as SSIS. 

I have had a very similar problem with significantly smaller file sizes (100MB) that I had to pick up a batch file and stick it in sql.

Almost every angle I turned to I got an out of memory exception or very erratic behaviour from BizTalk (a symptom of the low memory i think)

I tried all sorts of tricks, including the aforementioned xquery and xml parameters.  While useful in principle, the practicality of doing this means your

You all are correct, BizTalk is not the best tool for this type of message.  I ended up building a little SSIS package.  And man is it fast!  1.68 million rows in a bit under 30 seconds on non production hardware.

 

Thanks all