Execution Plan Hinting

[Ryan]

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

[Dan Guzman]

> 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" <ry********@hotmail.com> wrote in message
news:11**********************@i39g2000cwa.googlegr oups.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

[Ryan]

Should have mentioned this is in 2000.

One problem is that we can't truncate the data as it's not ALL of it
that is being replaced, just a small chunk. The two statements are
being called sequentially and has only reared it's head recently. We do
have a way around it as I mentioned, but there must be a better
approach to this instead of calling a second method if there is an
error.

The data is not duplicated as I can run the two parts seperately and
they are fine. Manual checks also confirm this.

Ryan

[Alexander Kuznetsov]

Ryan,

another possible workaround might be to :

bulk insert into a staging table

BEGIN TRANSACTION
DELETE * FROM Whatever
INSERT INTO Whatever... select ... from staging table
COMMIT TRANSACTION

[Erland Sommarskog]

Dan Guzman (gu******@nospam-online.sbcglobal.net) writes:

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.

Would mind sharing that repro? It sounds like a pretty wild bug to me;
one of the kind that make you ask "how do they do it?"

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Dan Guzman]

> Would mind sharing that repro? It sounds like a pretty wild bug to me;

one of the kind that make you ask "how do they do it?"

Unfortunately, I don't have the original script or file I used to repro
Ryan's problem. I changed both with the intention of including simplified
versions with a bug report but I can no longer reproduce the error! I'm
fairly certain this wasn't a procedural error on my part because I copied
the script into a SQL 2005 window and it executed successfully.

I suspect the problem may be data related for some subtle difference I'm now
missing. The SQL script was similar to:

USE tempdb
GO

DROP TABLE x
CREATE TABLE x(x varchar(255) NOT NULL PRIMARY KEY)
GO

BULK INSERT x FROM 'C:\x.txt'
GO

BEGIN TRAN
--TRUNCATE TABLE x
DELETE x
BULK INSERT x FROM 'C:\x.txt'
COMMIT
GO
--
Hope this helps.

Dan Guzman
SQL Server MVP

"Erland Sommarskog" <es****@sommarskog.se> wrote in message
news:Xn**********************@127.0.0.1... Dan Guzman (gu******@nospam-online.sbcglobal.net) writes:

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.

Would mind sharing that repro? It sounds like a pretty wild bug to me;
one of the kind that make you ask "how do they do it?"

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Dan Guzman]

Ryan,

I hope Alexander's staging table work-around works for you. That's what I
would do.

I lost the script/file I used to repro your problem and can't seem to
recreate the error. Please post a script that can be used to reproduce you
problem.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Ryan" <ry********@hotmail.com> wrote in message
news:11**********************@i40g2000cwc.googlegr oups.com...

Should have mentioned this is in 2000.

One problem is that we can't truncate the data as it's not ALL of it
that is being replaced, just a small chunk. The two statements are
being called sequentially and has only reared it's head recently. We do
have a way around it as I mentioned, but there must be a better
approach to this instead of calling a second method if there is an
error.

The data is not duplicated as I can run the two parts seperately and
they are fine. Manual checks also confirm this.

Ryan

[Erland Sommarskog]

Dan Guzman (gu******@nospam-online.sbcglobal.net) writes:

Unfortunately, I don't have the original script or file I used to repro
Ryan's problem. I changed both with the intention of including simplified
versions with a bug report but I can no longer reproduce the error! I'm
fairly certain this wasn't a procedural error on my part because I copied
the script into a SQL 2005 window and it executed successfully.

I suspect the problem may be data related for some subtle difference I'm
now missing. The SQL script was similar to:

Case-insensitive collation on SQL 2000 and case-sensitive on SQL 2005?

Somehow, the only way I could see that this sequence could lead to
this error is that either the DELETE is incompletes with the incoming
data, or the incoming data itself has duplicates. I've seen many weird
bugs, but that DELETE would not have completed when the next statement
runs sounds a little fantastic to me.

But I've been wrong before.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Dan Guzman]

"Erland Sommarskog" <es****@sommarskog.se> wrote in message
news:Xn**********************@127.0.0.1...

Case-insensitive collation on SQL 2000 and case-sensitive on SQL 2005?
Both instances are SQL_Latin1_General_CP1_CI_AS.
Somehow, the only way I could see that this sequence could lead to
this error is that either the DELETE is incompletes with the incoming
data, or the incoming data itself has duplicates. I've seen many weird
bugs, but that DELETE would not have completed when the next statement
runs sounds a little fantastic to me.
Perhaps the delete completed but there was something left over
(temporarily?) by DELETE that BULK INSERT was sensitive to due to the
transaction. TRUNCATE is more thorough than delete. Maybe the problem is
data-dependent as well. I hope OP can come up with a repro for my peace of
mind.
But I've been wrong before.

Make's two of us ;-)

--
Hope this helps.

Dan Guzman
SQL Server MVP

[Ryan]

I'll get the code together and post some more meaningful detail in a
short while.

Just for further information, the collation is Latin1_General_CI_AS and
it's on SQL2000 SP3. It's running on Windows 2000AS SP4, 2 Xeon 3.2Ghz,
2GB RAM.

We've tried this step by step and have thoroughly checked the data and
there are no duplicates. As I mentioned originally, it works fine if
you delete the data, then add after a short while, which made finding
this a little tricky. Running the two together causes the violation.
What was done was the deletion, then try the bulk insert, if the bulk
insert fails, it tries to insert line by line, which in effect causes a
slight delay, which seems sufficient to allow it to work.

I'll post the code and the DDL shortly.

Ryan

[Dan Guzman]

Thanks for taking the time to put together a repro. I hope you have more
success than I did.

FWIW, I tested with SQL 2000 SP4 Developer edition
(SQL_Latin1_General_CP1_CI_AS) on a dual core XP box. The only commonality
with your system are SQL 2000 and dual CPUs. I did not experience the
problem when I omitted the explicit transaction.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Ryan" <ry********@hotmail.com> wrote in message
news:11**********************@u72g2000cwu.googlegr oups.com...

I'll get the code together and post some more meaningful detail in a
short while.

Just for further information, the collation is Latin1_General_CI_AS and
it's on SQL2000 SP3. It's running on Windows 2000AS SP4, 2 Xeon 3.2Ghz,
2GB RAM.

We've tried this step by step and have thoroughly checked the data and
there are no duplicates. As I mentioned originally, it works fine if
you delete the data, then add after a short while, which made finding
this a little tricky. Running the two together causes the violation.
What was done was the deletion, then try the bulk insert, if the bulk
insert fails, it tries to insert line by line, which in effect causes a
slight delay, which seems sufficient to allow it to work.

I'll post the code and the DDL shortly.

Ryan

[Ryan]

{ ADO.BEGINTRANS }
delete from field_values where dealer_data_id = 274;
bulk insert field_values from '274.dat' with ('fa.txt');
{ ADO.COMMIT }
// Note there is no rollback on failure.

10-13:20:33.781xxx0863 Tdm.BulkInsert field_values from
C:\WINNT\TEMP\HZSSTWZG1204493500.dat with
(C:\WINNT\TEMP\XMROGIKB1204493500.dat)
10-13:20:33.781 ExecSQL.Exception caught by TSafeADOQuery Violation of
PRIMARY KEY constraint 'PK_FIELD_VALUES'. Cannot insert duplicate key
in object 'FIELD_VALUES'
10-13:20:33.781 xxx863 Tdm.BulkInsert Exception EAccessViolation
(Access violation at address 004BE971 in module
'xxxxxxxxServer_Srvs_12.exe'

fa.txt
-------
8.0
4
1 SQLCHAR 0 0 "|" 1 dealer_data_id ""
2 SQLCHAR 0 0 "|" 2 field_code ""
3 SQLCHAR 0 0 "|" 3 field_value ""
4 SQLCHAR 0 0 "\r\n" 4 changed_type ""
sample data from 274.dat
-------------------------------------

274|S01|1.00000|0
274|S02|0.00000|0
274|S03|0.00000|0
274|S04|0.00000|0
274|S05|0.00000|0
274|S06|1.00000|0
274|S07|0.00000|0
274|S08|0.00000|0
274|S09|0.00000|0
274|S10|0.00000|0
274|S11|0.00000|0
274|S12|0.00000|0
274|S13|0.00000|0
274|S14|0.00000|0
274|S15|0.00000|0
274|S16|0.00000|0
274|S17|0.00000|0
274|S18|0.00000|0
274|S19|0.00000|0
274|S20|0.00000|0
274|S21|0.00000|0
274|S22|0.00000|0
274|S23|0.00000|0
274|S24|0.00000|0
DDL
---------

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FIELD_VALUES]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[FIELD_VALUES]
GO

CREATE TABLE [dbo].[FIELD_VALUES] (
[DEALER_DATA_ID] [int] NOT NULL ,
[FIELD_CODE] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[FIELD_VALUE] [numeric](15, 5) NULL ,
[CHANGED_TYPE] [int] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[FIELD_VALUES] WITH NOCHECK ADD
CONSTRAINT [PK_FIELD_VALUES] PRIMARY KEY CLUSTERED
(
[DEALER_DATA_ID],
[FIELD_CODE]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
Not sure if this will reproduce it, but it's all I've got. It may give
someone a hint at what is going wrong. Thanks in advance.

Ryan

[Ryan]

Forgot the formatfile= part...

bulk insert field_values from '274.dat' with (formatfile='fa.txt');

[Erland Sommarskog]

Ryan (ry********@hotmail.com) writes:

{ ADO.BEGINTRANS }
delete from field_values where dealer_data_id = 274;
bulk insert field_values from '274.dat' with ('fa.txt');
{ ADO.COMMIT }
// Note there is no rollback on failure.

Is this the actual code? Judging from the error output, it appears
that you have some sort of a wrapper on ADO. ("TSafeADOQuery").

Are you able to reproduce the problem if you run this as

BEGIN TRANSACTION
delete from FIELD_VALUES where DEALER_DATA_ID = 274;
bulk insert FIELD_VALUES from 'c:\temp\274.dat'
with (formatfile = 'c:\temp\fa.txt');
COMMIT TRANSACTION

from Query Analyzer? Or does it only happen when you run from this
ADO thing?

My suspicion is that it only happens in the latter case. One idea is to
run a trace in Profiler. Keep an eye on the SPID column.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Dan Guzman]

> Not sure if this will reproduce it, but it's all I've got. It may give

someone a hint at what is going wrong. Thanks in advance.
I couldn't recreate your problem running this script from Query Analyzer.
Create a script that demonstrates the problem in your environment and then
post it here. If the error only occurs from your application, try Erland's
suggestion to use Profiler.

truncate table field_values
bulk insert field_values from 'c:\274.dat' with (FORMATFILE='c:\fa.txt');
begin tran
delete from field_values;-- where dealer_data_id = 274;
bulk insert field_values from 'c:\274.dat' with
(FORMATFILE='c:\fa.txt');
commit
--
Hope this helps.

Dan Guzman
SQL Server MVP

"Ryan" <ry********@hotmail.com> wrote in message
news:11*********************@v46g2000cwv.googlegro ups.com...{ ADO.BEGINTRANS }
delete from field_values where dealer_data_id = 274;
bulk insert field_values from '274.dat' with ('fa.txt');
{ ADO.COMMIT }
// Note there is no rollback on failure.

10-13:20:33.781xxx0863 Tdm.BulkInsert field_values from
C:\WINNT\TEMP\HZSSTWZG1204493500.dat with
(C:\WINNT\TEMP\XMROGIKB1204493500.dat)
10-13:20:33.781 ExecSQL.Exception caught by TSafeADOQuery Violation of
PRIMARY KEY constraint 'PK_FIELD_VALUES'. Cannot insert duplicate key
in object 'FIELD_VALUES'
10-13:20:33.781 xxx863 Tdm.BulkInsert Exception EAccessViolation
(Access violation at address 004BE971 in module
'xxxxxxxxServer_Srvs_12.exe'

fa.txt
-------
8.0
4
1 SQLCHAR 0 0 "|" 1 dealer_data_id ""
2 SQLCHAR 0 0 "|" 2 field_code ""
3 SQLCHAR 0 0 "|" 3 field_value ""
4 SQLCHAR 0 0 "\r\n" 4 changed_type ""
sample data from 274.dat
-------------------------------------

274|S01|1.00000|0
274|S02|0.00000|0
274|S03|0.00000|0
274|S04|0.00000|0
274|S05|0.00000|0
274|S06|1.00000|0
274|S07|0.00000|0
274|S08|0.00000|0
274|S09|0.00000|0
274|S10|0.00000|0
274|S11|0.00000|0
274|S12|0.00000|0
274|S13|0.00000|0
274|S14|0.00000|0
274|S15|0.00000|0
274|S16|0.00000|0
274|S17|0.00000|0
274|S18|0.00000|0
274|S19|0.00000|0
274|S20|0.00000|0
274|S21|0.00000|0
274|S22|0.00000|0
274|S23|0.00000|0
274|S24|0.00000|0
DDL
---------

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FIELD_VALUES]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[FIELD_VALUES]
GO

CREATE TABLE [dbo].[FIELD_VALUES] (
[DEALER_DATA_ID] [int] NOT NULL ,
[FIELD_CODE] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[FIELD_VALUE] [numeric](15, 5) NULL ,
[CHANGED_TYPE] [int] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[FIELD_VALUES] WITH NOCHECK ADD
CONSTRAINT [PK_FIELD_VALUES] PRIMARY KEY CLUSTERED
(
[DEALER_DATA_ID],
[FIELD_CODE]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
Not sure if this will reproduce it, but it's all I've got. It may give
someone a hint at what is going wrong. Thanks in advance.

Ryan