|
|
Posted by Dan Guzman on 03/09/06 15:47
> 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@hotmail.com> wrote in message
news:1141905599.836545.225980@i39g2000cwa.googlegroups.com...
> 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
>
Navigation:
[Reply to this message]
|