|
Home > Archive > BizTalk Server Tools > July 2004 > SQL Adapter for BizTalk is very slow
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 |
SQL Adapter for BizTalk is very slow
|
|
| morgana@nospam.amerinetcentral.org 2004-07-21, 5:49 pm |
| Hello
I'm using BizTalk 2004 to update a sql server database using the SQL
adapter, and I am getting very slow response.
I have a very large dataset that I am trying to load (300,000+
records). I mapped this into a BizTalk SQL adapter generated stored
procedure map, and sent the data thought the adapter. The next
morning, it still had not finished (14 hours later).
When I cut the dataset down, it finishes very quickly. 30,000 records
in 5 minutes.
I wrote a fast vb.net app, to read the XML and load data using the
same stored procedure. It ran in under 10 minutes. When I switch it
back to the sql adapter I get the same slowdown.
I noticed that the SQL adapter does not commit any records as it is
inserting records, it waits till the end, this has to be creating a
big rollback section and could be causing my a problem. When I ran my
vb program above, I get records inserted as the procedure runs. Is
there a way to get BizTalk to commit the records as they are written?
This may help my performance.
I really do not want to write my own adapter, but it is starting to
look like I might have to. Does anybody have any ideas how to improve
the speed on large datasets?
Thanks
| |
| Patrick Wellink 2004-07-26, 5:52 pm |
| Yep...
You gave the answer yourself...
Split it up in manageable pieces.
<morgana@nospam.amerinetcentral.org> wrote in message
news:40vsf0lpghsidla66mmnjle18qc8s2ar2v@
4ax.com...
> Hello
>
> I'm using BizTalk 2004 to update a sql server database using the SQL
> adapter, and I am getting very slow response.
>
> I have a very large dataset that I am trying to load (300,000+
> records). I mapped this into a BizTalk SQL adapter generated stored
> procedure map, and sent the data thought the adapter. The next
> morning, it still had not finished (14 hours later).
>
> When I cut the dataset down, it finishes very quickly. 30,000 records
> in 5 minutes.
>
> I wrote a fast vb.net app, to read the XML and load data using the
> same stored procedure. It ran in under 10 minutes. When I switch it
> back to the sql adapter I get the same slowdown.
>
> I noticed that the SQL adapter does not commit any records as it is
> inserting records, it waits till the end, this has to be creating a
> big rollback section and could be causing my a problem. When I ran my
> vb program above, I get records inserted as the procedure runs. Is
> there a way to get BizTalk to commit the records as they are written?
> This may help my performance.
>
> I really do not want to write my own adapter, but it is starting to
> look like I might have to. Does anybody have any ideas how to improve
> the speed on large datasets?
>
> Thanks
>
|
|
|
|
|