Posted by Ryan on 03/09/06 13:59

We've got as slightly unusual scenario happening whereby a statement is
passed to SQL which consists of two parts.

BEGIN TRANSACTION
DELETE * FROM Whatever
BULK INSERT INTO Whatever...(etc)
COMMIT TRANSACTION

The first is a deletion of the data and the second is the bulk insert
of replacement data into that table. The error that we see is a
violation of the primary key (composite).

The violation only happens if we run both processes together. If we run
one, then the other, it works fine. If we set a line by line insert, it
works fine.

My suspicion is that the execution plan that is being run is most
likely working the two parts in parallel and that the records still
exist at the point that the insert is happening. Truncate is not an
option. The bulk insert was added for performance reasons. There is an
option of trying the bulk insert, and if that fails, do the line by
line insert, but it's far from ideal.

I think we can probably wrap this into two individual transactions
within the one statement as follows :

BEGIN TRANSACTION
DELETE * FROM Whatever
COMMIT TRANSACTION

BEGIN TRANSACTION
BULK INSERT INTO Whatever...(etc)
COMMIT TRANSACTION

Will this give sufficient hint to SQL about the order it processes it
so that it completes as we intend and not as it sees being the most
efficient method ?

Or, is there a better approach to this ?

I've seen that some hints can be passed to SQL for optimizing, but my
understanding was that it was always better to trust the optimiser and
re-work the query as needed.

With the server having two processors, is it feasible that one is doing
one part and the other processor the other part in parallel ? Will
telling it to use a single processor be worthwhile looking at ? MAXDOP
1 ?

Finally, I'd imagine that the insert is quicker to process than the
deletion. Is this correct ?

Thanks

Ryan

[Back to original message]