conditional split for insert or update cause dead lock on database level

Hi

I am using conditional split Checking to see if a record exists and if so update else insert. But this cause database dead lock any one has suggestion?

Thanks

Don't try and insert and update a table from the same data flow.

-Jamie

|||

My read from db is very expensive. We can't afford to read same data twice. So I use another merge join to force waiting on updating records to finish before I insert. This solve my problem. Thanks anyway.

aproaching Before:

conditional Split on newRecords and changedRecords, OLE DB Command was used to update changedRecords, OLE DB Destination was used to insert newRecords

aproaching Now:

Conditional Split on newRecords and changedRecords, OLE DB Command was used to update changedRecords,

Merge Join is used to left outer join newRecords with output of OLE DB Command ( this leave only newRecords is availible in output, but still wait for db update command finish), then

OLE DB Destination was used to insert output from merge join.

|||

Jun Fan wrote:

My read from db is very expensive. We can't afford to read same data twice. So I use another merge join to force waiting on updating records to finish before I insert. This solve my problem. Thanks anyway.

Why do you need to read data twice? Just push one of the data paths into a raw file and then insert/update/whatever that data from another dataflow.

-Jamie

|||

Thanks for sugestion. Pushing data into temp location (file or temp table) has been too slow for large amount new records. Another merge join to force wait on update finishing seems work great at this point.

Thanks again!

Jun Fan

|||

Jun Fan wrote:

Thanks for sugestion. Pushing data into temp location (file or temp table) has been too slow for large amount new records. Another merge join to force wait on update finishing seems work great at this point.

Thanks again!

Jun Fan

Have you tried raw files? They're lightning fast.

By the way, merge join does not ensure anything. It slows up one datapath, sure, but that in no way guarantees that you will prevent your locking problem.

-Jamie

|||

If performance is a concern, I'm suprised that using the OLEDB Command is OK, as it tends to be pretty slow. I have had much better success using a Conditional Split to direct new rows (Inserts) to an OLEDB Destination that writes directly to the target table, and directs the update rows to a permanent temp table. Then I use an Execute SQL Task to issue a batch Update statement after the data flow. During performance testing in the environments I work in, this has proven to be the fastest approach.

This is a pattern that many of the regular posters on this forum use very successfully.