Re: Execution Plan Hinting

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

These statements run consecutively and not in parallel. You shouldn't get a
PK violation unless the file contains duplicate data so it looks to me like
you found a bug. I can repro this under SQL 2000 but no problem under SQL
2005.

There seem to be couple of work-arounds. One is to use TRUNCATE:

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

Another is to avoid the explicit transaction so that each statement is in an
individual transaction:

DELETE FROM Whatever
BULK INSERT INTO Whatever...(etc)

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Ryan" <ryanofford@xxxxxxxxxxx> wrote in message
news:1141905599.836545.225980@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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



.

Quantcast