- This topic has 2 replies, 1 voice, and was last updated 8 years, 3 months ago by
.
-
Posts
-
-
Hello
I am importing csv data into an SQL server table. I am using a receive pipeline to disassemble the csv data and I have a map that maps csv fields to sql table fields and an updategram runs the insert.
It works – so there is no problem there – the problem is how long it takes.
The csv file has 36,500 rows and is 2,5 MB in size and it takes 30 mins to do the insert (my resident DBA tells me that SQL server can do a 36,500 row insert in 1 sec).
It uses up all the server’s resources and slows everything down.
If I open the sql table while this is all happening then I cannot see the data – all the data is obviously cached in memory and then planted into the table as a single dump at the very end.My question is basically – how can I speed up this process? 30 mins is way too long. Where is the bottleneck? is it the pipeline or the insert? and what can I do to make it run faster?
Regards
JMan -
The mapping and converting the flat file into Xml is probably taking the most time.
Also remember that Biztalk is persisting the messages to the message box for each record. All in all, it is doing a lot more work than if you just imported the data directly into SQL.You are not even going to come close to the SQL time.
An easy thing to do to speed things up a little is make the flat file schema nodes as small as possible. So if you have a tag called <InputData> make it <InpDt> or something like that.
Hope this helps.
-
I’m curious, were you using an orchestration or just message-based routing? If you went the orchestration route, were you starting 36,000 orchestrations?
Also, SQL has something called OPENXML where you can read XML and use it in a stored proc. Maybe you could use Biztalk tools to translate the map in one big batch, then send to one single stored proc.
And lastly, I always remind people that there are two technologies:
BPM – Business Process Management – Biztalk
ETL – Extract, Translate, Load – DTS (or the new SSIS in SQL 2005).Use the right tool for the right job. Eletric cordless drills are great for making holes, but not so good for hammering in nails.
-
-
- The forum ‘BizTalk 2004 – BizTalk 2010’ is closed to new topics and replies.