|
Home > Archive > BizTalk Server General > May 2006 > Transformation performance...
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Transformation performance...
|
|
|
| Dear All,
I need to transform a huge flat-file(15 MB) into a cross-tab target
format and after transformation a web service inserts this data into
SQL Server. But when I drop the file into a folder it just don't
proceed with the transformation, it just hangs on the transformation
stage...BizTalk Administrator shows the message to be Active even after
48 hours.
To give more insight of the transformation which is a bit complex
implementation using Table Looping functoid and other logical & custom
script functoid:
-> Source flat-file has monthly(Jan-Dec) row data that needs to be
transformed into target per month data i.e each source row needs to be
converted to 12 target rows(Jan-Dec) and I'm making use of Table
Looping functoid to do this job. Resulting in 15 MB input flat-file
data first getting converted into around 30+ MB of Xml Message after
Disassembling stage and then getting transformed into ??? MB of target
Xml Message. Each flat-file has around 75K records resulting in around
75K * 12(per month) records on the target. Conclusion is message never
gets transformed at runtime...I don't get any error in the Eventlog
viewer. Before upgrading to BizTalk 2006 RTM, while using BizTalk Beta
2 I used to get System.OutOfMemoryException for this transformation
after running for a couple of hours. And to avoid this exception I
broke my input flat-file into 8 files which helped me proceed with the
transformation. And after upgrading to RTM I was expecting it to fix
the Memory Exception problem(looks like it fixed) but still I couldn't
proceed with the transformation.
-> There are a couple of custom scripting functoids making use of C#
code with minimum logic associated.
-> There are 3-4 conditions that make use of 20+ logical functoids
spanning across in & out of Table Looping functoid doing some data
massaging.
-> The Server configuration is:
Pentium 4 CPU 3.20Gz, 1 GM RAM and BizTalk 2006 & SQL Server 2000 are
installed on the same box.
Currently I'm breaking the input flat-file into multiple files and
proceeding with end-to-end completion. Before moving to production I
need to look for a solution to tranform the file in a single execution.
Appreciate your responses.
TIA,
Kiran Pabba
| |
| Greg Forsythe 2006-05-23, 7:15 am |
| One possibility would be to use a custom Xslt to perform the transformation.
The Mapper tool is basically a GUI tool for creating Xslt; it's primary
objective is to simplify the the creation of the Xslt required for the
transformations.
It does not support all Xslt functions and does not always produce the most
efficient code.
The Mapper is fine for small simple transformations, I use it all the time.
But for larger more complicated transformations you are best to write you
own Xslt.
Are you able to email me your schemas, map and a sample input file
Another possibility is to transform the data in code. Xslt is one tool, but
not the only tool for message transformations. You could write a C# routine
or even use the database, with some temp tables and stored procs.
With a data source of nearly a million records you are likely to stress any
transformation logic.
Greg
"Pabba" <kiran.pabba@gmail.com> wrote in message
news:1148307861.076176.260770@i39g2000cwa.googlegroups.com...
> Dear All,
>
> I need to transform a huge flat-file(15 MB) into a cross-tab target
> format and after transformation a web service inserts this data into
> SQL Server. But when I drop the file into a folder it just don't
> proceed with the transformation, it just hangs on the transformation
> stage...BizTalk Administrator shows the message to be Active even after
> 48 hours.
>
> To give more insight of the transformation which is a bit complex
> implementation using Table Looping functoid and other logical & custom
> script functoid:
>
> -> Source flat-file has monthly(Jan-Dec) row data that needs to be
> transformed into target per month data i.e each source row needs to be
> converted to 12 target rows(Jan-Dec) and I'm making use of Table
> Looping functoid to do this job. Resulting in 15 MB input flat-file
> data first getting converted into around 30+ MB of Xml Message after
> Disassembling stage and then getting transformed into ??? MB of target
> Xml Message. Each flat-file has around 75K records resulting in around
> 75K * 12(per month) records on the target. Conclusion is message never
> gets transformed at runtime...I don't get any error in the Eventlog
> viewer. Before upgrading to BizTalk 2006 RTM, while using BizTalk Beta
> 2 I used to get System.OutOfMemoryException for this transformation
> after running for a couple of hours. And to avoid this exception I
> broke my input flat-file into 8 files which helped me proceed with the
> transformation. And after upgrading to RTM I was expecting it to fix
> the Memory Exception problem(looks like it fixed) but still I couldn't
> proceed with the transformation.
>
> -> There are a couple of custom scripting functoids making use of C#
> code with minimum logic associated.
>
> -> There are 3-4 conditions that make use of 20+ logical functoids
> spanning across in & out of Table Looping functoid doing some data
> massaging.
>
> -> The Server configuration is:
> Pentium 4 CPU 3.20Gz, 1 GM RAM and BizTalk 2006 & SQL Server 2000 are
> installed on the same box.
>
> Currently I'm breaking the input flat-file into multiple files and
> proceeding with end-to-end completion. Before moving to production I
> need to look for a solution to tranform the file in a single execution.
>
> Appreciate your responses.
>
> TIA,
>
> Kiran Pabba
>
| |
|
| Thanks Greg, appreciate your response.
Yesterday I tried using custom Xslt but even custom Xslt is taking
hours of time. The data is huge, as I said each source file has around
75K records and after transformation target consists of 75K * 12
approx. Finally we have decided to move the cross-tab functionality
into SQL Server procedure and now the end-end process time is 15 min.
:-)
Thnks 'n' Regards,
Kiran Pabba
|
|
|
|
|