- This topic has 6 replies, 1 voice, and was last updated 9 years, 3 months ago by
.
-
Posts
-
-
Hi,
I’ve built an orcestration that uses the Oracle Adapter and the SQL adapter. The orcestration pulls data from an oracle DB, does a transform with a map, then places the data ino a SQL table.
The orcestration was a snap to build and I first tested with 100 records and everything ran fine. I then tried 1,000, 10,000 and finally 100,000 records. 1,000, and 10,000 work, but when I get up to 100,000 the orcestration breaks and I receive a error message on my orchestartion that says ‘Error transmitting message: Object reference not set to an instance of an object.’
I know there’s lots of tuning and tweeking I can do to get this thing to work but I don’t know where to start. Any ideas?
-Clay
-
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
0false
false
falseMicrosoftInternetExplorer4
<!–
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{mso-style-parent:””;
margin:0in;
margin-bottom:.0001pt;
mso-pagination:widow-orphan;
font-size:12.0pt;
font-family:”Times New Roman”;
mso-fareast-font-family:”Times New Roman”;}
p
{mso-margin-top-alt:auto;
margin-right:0in;
mso-margin-bottom-alt:auto;
margin-left:0in;
mso-pagination:widow-orphan;
font-size:12.0pt;
font-family:”Times New Roman”;
mso-fareast-font-family:”Times New Roman”;}
@page Section1
{size:8.5in 11.0in;
margin:1.0in 1.25in 1.0in 1.25in;
mso-header-margin:.5in;
mso-footer-margin:.5in;
mso-paper-source:0;}
div.Section1
{page:Section1;}
–>/* Style Definitions */
table.MsoNormalTable
{mso-style-name:”Table Normal”;
mso-tstyle-rowband-size:0;
mso-tstyle-colband-size:0;
mso-style-noshow:yes;
mso-style-parent:””;
mso-padding-alt:0in 5.4pt 0in 5.4pt;
mso-para-margin:0in;
mso-para-margin-bottom:.0001pt;
mso-pagination:widow-orphan;
font-size:10.0pt;
font-family:”Times New Roman”;
mso-ansi-language:#0400;
mso-fareast-language:#0400;
mso-bidi-language:#0400;}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 original message will need to be converted to an escaped string and placed in the attribute that represents that stored procedure parameter. If you are dealing with a big message this is not going to please BizTalk at all.
Maybe the new WCF sql adapter may offer more resilience, but I would count on it 🙂
So the only place this leaves you (and is the route I followed in the end) is to experiment with the solutions suggested where you
1. read the data from oracle in smaller chubnks and push those through to sql
Not sure how the oracle adapter works, but you could get a race condition where the sql write is not complete by the next oracle read making the pressure build in biztalk and on the target DB.
2. read all the data from oracle and debatch in a pipeline
This assumes you can read in all this data from oracle without issue. If you can you can either split the batch in a pipeline and have multiple orchestrations pushing data to sql, or process the batch sequentially or semi sequentially where you control the flow of data to the sql server.Your SQL box will also be being kicked to kingdom come and other services running on the biztalk \ sql platform may experience performance issues.
Hope this helps, and I feel your pain!
Dino
-
-
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 original message will need to be converted to an escaped string and placed in the attribute that represents that stored procedure parameter. If you are dealing with a big message this is not going to please BizTalk at all.
Maybe the new WCF sql adapter may offer more resilience, but I would count on it 🙂
So the only place this leaves you (and is the route I followed in the end) is to experiment with the solutions suggested where you
1. read the data from oracle in smaller chubnks and push those through to sql
Not sure how the oracle adapter works, but you could get a race condition where the sql write is not complete by the next oracle read making the pressure build in biztalk and on the target DB.
2. read all the data from oracle and debatch in a pipeline
This assumes you can read in all this data from oracle without issue. If you can you can either split the batch in a pipeline and have multiple orchestrations pushing data to sql, or process the batch sequentially or semi sequentially where you control the flow of data to the sql server.Your SQL box will also be being kicked to kingdom come and other services running on the biztalk \ sql platform may experience performance issues.
Hope this helps, and I feel your pain!
Dino
-
-
-
-
- The forum ‘BizTalk 2004 – BizTalk 2010’ is closed to new topics and replies.