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

[ Post a follow-up to this message ]

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
>

[ Post a follow-up to this message ]