|
Home > Archive > BizTalk Server Orchestration > August 2004 > Poor Performance in SQL Server Inserts
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 |
Poor Performance in SQL Server Inserts
|
|
| Samar Roy 2004-08-17, 5:50 pm |
| I have an orchestration that loops through multiple CUSTOMER nodes in XML
extracting one customer information inserting into a SQL Table on every loop.
For testing purpose, I am currently inserting NULLS in all fields exept one
that has 5 characters. So it is not a big page insert. The table has no
constraints but the insert is going at about 1 transaction per second. I am
running Biztalk and SQL Server on one machine for testing purposes.
Has someone faced this issue?
| |
| Alan Smith 2004-08-17, 5:50 pm |
| Hi,
Can you get any more info on what may be causing it. Check the amount of
RAM used, the % disk access, and the % proc times for the different processes.
Things will be slow if Windows as caching RAM to the HD at the same time
as BizTalk, and your orchestration write to the database.
For a faster solution you could try creating a .net class to split the
message and
perform the inserts. Going through the SQL adapter means the messages pass
through the message box database (righ?), so there will be an overhead there.
Cheers,
Alan
"Samar Roy" wrote:
> I have an orchestration that loops through multiple CUSTOMER nodes in XML
> extracting one customer information inserting into a SQL Table on every loop.
> For testing purpose, I am currently inserting NULLS in all fields exept one
> that has 5 characters. So it is not a big page insert. The table has no
> constraints but the insert is going at about 1 transaction per second. I am
> running Biztalk and SQL Server on one machine for testing purposes.
> Has someone faced this issue?
| |
| Samar Roy 2004-08-17, 5:50 pm |
| I wrote so that I compose the XML message first and then map a many to many
XML to SQL Adapter. This way I go through the MessageBox only once. a lot
faster. 10,000 rows took close to 10 seconds now. I guess Once through the
MessageBox and then the overhead of writing so many SQLXML to the SQL Server.
Thanks for the insight.
"Alan Smith" wrote:
[vbcol=seagreen]
> Hi,
>
> Can you get any more info on what may be causing it. Check the amount of
> RAM used, the % disk access, and the % proc times for the different processes.
>
> Things will be slow if Windows as caching RAM to the HD at the same time
> as BizTalk, and your orchestration write to the database.
>
> For a faster solution you could try creating a .net class to split the
> message and
> perform the inserts. Going through the SQL adapter means the messages pass
> through the message box database (righ?), so there will be an overhead there.
>
> Cheers,
>
> Alan
>
>
> "Samar Roy" wrote:
>
|
|
|
|
|