am a DBA on a sql 2005 box which hosts Biztalk. One of the developers wrote a package/orchestration (and then left) which is killing my sql server. It takes a flat file input and compares it to records in an oracle 8 db and then either inserts or updates the record. When running profielr during this process it created over 400,000 statements like this

exec [dbo].[bts_InsertProperty] @uidBatchID=N'{BF4ED36E-EE93-4C6E-B3A0-E557D9CBC06A}',@nOrderID=0,@uidMessageID=N'{FC20E0 8C-B6C4-4810-8704-F0C6E453F5E0}',@uidPropertyID=N'{A146E00A-4AC1-4926-95E3-37A512B6644F}',@vtValue=1



The flat file has about 60,000 records. Is this normal for this type of processing. The sql server has 32 gig of RAM, but the disk gets killed during this process which takes about 15-20 minutes.

Seems quite strange, if there are only 60,000 records why 400,000 inserts are needed but who knows? Perhaps the records could be bulk inserted into the database and then processed asynchronously by the DB in "slow time"? So just effectively import the file into a temp table and then process.

--

Joe (Microsoft MVP - XML)

That's what I suggested, looks like they use Orchestration to produce the logic instead of inside sql