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 14 1722
> 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********@hot mail.com> wrote in message
news:11******** **************@ i39g2000cwa.goo glegroups.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
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
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
Dan Guzman (gu******@nospa m-online.sbcgloba l.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****@sommarsk og.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
> 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****@sommars kog.se> wrote in message
news:Xn******** **************@ 127.0.0.1... Dan Guzman (gu******@nospa m-online.sbcgloba l.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****@sommarsk og.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
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********@hot mail.com> wrote in message
news:11******** **************@ i40g2000cwc.goo glegroups.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
Dan Guzman (gu******@nospa m-online.sbcgloba l.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****@sommarsk og.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
"Erland Sommarskog" <es****@sommars kog.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_Gene ral_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
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Dan |
last post by:
I am new to tuning and I am having some trouble with my Oracle
9.2.0.1. I have a database that I am trying to gain consistent
performance out of but I seem to have changing execution plans. Due
to a number of factors outside my control, I have to handle an
application that does not send bind variables, I handle them as they
come in. To do this, I have set the init.ora file to have an entry of
'cursor_sharing=similiar'. I know this is a...
|
by: Vinny |
last post by:
Can anyone help me with this strange problem please?
I have a stored procedure, with a parameter defined as a
uniqueidentifier. The procedure does a select with a number of joins,
and filters within the Where clause using this parameter.
(@orderHeader_id uniqueidentifier)
SELECT *
|
by: Will Atkinson |
last post by:
Hi All,
I'm a relative newbie to SQL Server, so please forgive me if this is a
daft question...
When I set "Show Execution Plan" on in Query Analyzer, and execute a
(fairly complex) sproc, I note that a particular query is reported as
having a query cost of "71% relative to the batch" - however, this is
nowhere near the slowest executing query in the batch - other queries
which take over twice as long are reported as having costs in...
|
by: Jenny Zhang |
last post by:
Hi,
I am running OSDL-DBT3 test against PostgreSQL. I found performance
difference between the runs even though the data and queries are the
same. I tried to study this problem by getting execution plans at the
beginning of each test. The following script gets execution plan for 22
queries, except query 15.
i=1
while
|
by: Jenny Zhang |
last post by:
The osdl-dbt3 test starts with building and vacuuming the database.
The execution plans were taken after the vacuuming.
I did two tests with the same database parameters:
1. run two osdl-dbt3 runs on one box without rebooting the stystem.
Though the execution plans are the same, the costs are different. The
system status are different for the two runs, for example, some page
cache are not released after the first one. Does that make...
| |
by: Ina Schmitz |
last post by:
Hi NG,
does IBM Universal Database 8.2 make any difference between actual and
estimated execution plans like in SQL Server ("set showplan_all on" for
estimated execution plan and "set statistics profile on" for actual
execution plan)? Does "explain plan selection for" generate the *estimated*
execution plan? Didn't find any distinction of actual or estimated execution
plans in the information center.
Thanks for help,
|
by: Tom Bradford |
last post by:
Let me first say that I'm sure that this subject has come up before,
and so forgive me for beating a dead horse. Secondly, let me say that
Python's strength is its dynamic nature, and I don't believe that it
should ever require a precondition scaffolding. With that said, I do
believe that something like type hinting would be beneficial to the
Python community, both for tool enablement and for disambiguous
programming.
Here is what I...
|
by: TheRealPawn |
last post by:
I'm trying to get the execution plan for a single stored procedure
from Profiler. Now, I've isolated the procedure but I get all
execution plans. Any ideas on how to connect the SPIDs so that I only
get the execution plan for the procedure I'm watching and not the
whole of the server?
|
by: sqlgirl |
last post by:
Hi,
We are trying to solve a real puzzle. We have a stored procedure that
exhibits *drastically* different execution times depending on how its
executed.
When run from QA, it can take as little as 3 seconds. When it is
called from an Excel vba application, it can take up to 180 seconds.
Although, at other times, it can take as little as 20 seconds from
Excel.
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
| |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |