Hi,
I have a sql statement that perform bulk insert into another table.
How can I ensure that if an error occurs, maybe due to primary key
constraint, the rest of the records will be inserted.
Thanks alot.Hi
Take a look at this example, even this transaction will generate a violation
of primary key constraint the rest of the data will be inserted
Please read SET ARITHABORT commant in the BOL to get a whole picture
create table #t (col int not null primary key)
begin tran
insert into #t values (1)
insert into #t values (2)
insert into #t values (3)
insert into #t values (3)
insert into #t values (4)
insert into #t values (5)
commit
select * from #t
drop table #t
"Shelby" <shelby@.singnet.com.sg> wrote in message
news:%23X6PcA6XGHA.3868@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I have a sql statement that perform bulk insert into another table.
> How can I ensure that if an error occurs, maybe due to primary key
> constraint, the rest of the records will be inserted.
> Thanks alot.
>
>
>|||That depends on the type of error that occures. Fatal Errrors for
example will always abort the entire procedure and rollback all
transactions. There might be a way for non-fatal errors using if-blocks
like
if @.err<>0
// do something else with the datasql
Showing posts with label bulk. Show all posts
Showing posts with label bulk. Show all posts
Wednesday, March 28, 2012
How to enhance the performance when there is bulk update in trigge
Dear someone who can help,
I encounter a problem where the execution of a trigger is very slow (in
terms of minute). Below is the example of the trigger that I have been
implemented.
Assume the trigger is fired on Table1 when inserting. The trigger perform
the following
- Create a temporary table of @.Temp
- Retrieve all the records from Table2 and insert into a temporary table.
- Then loop each of the temporary-Record and perform some business logic
- Update the record back into Table2
========================================
==========================
CREATE TRIGGER TG_Table1_Ins ON Table1
DECLARE @.InsCode integer /* stored the Code value from Table1 – On
Inserted*/
DECLARE @.Code integer /*temporary variable */
DECLARE @.KeyStr char (3) /*temporary variable */
Set @.InsCode = (select Code from inserted)
/*Create temporary table and data will be retrieved from table 2*/
Create Table #Temp (KeyStr char (3), Code integer)
/* Assume the table2 contains 3 thousand records with 30 data fields*/
Insert into #Temp (KeyStr, Code) Select KeyStr, Code From From Table2
/*loop for this temporary table*/
While Exists (Select * From #Temp) Begin
Set @.KeyStr = (Select top 1 KeyStr From #Temp)
Set @.Code = (Select Code From #temp where KeyStr = @.KeyStr)
/*some logic processing here, this is only example*/
If (@.InsCode > 10)
Set @.Code = @.InsCode * 2
Else Set @.Code = @.InsCode
Update Table2 Set Code = @.Code Where KeyStr = @.KeyStr
Delete #Temp Where KeyStr = @.KeyStr
End
Drop table #Temp
End
========================================
==========================
Question:
1. Is the temporary table method correctly been used? I understand that
Cursor is not recommended in Trigger.
2. How to speed up the performance.
3. I have tried this in oracle, it is pretty fast (in 2 ~ 5 second) compare
to MS Sql.
4. I have tried with call a stored procedure with explicit
transaction, but it didn't help much. Moreover, sometime this method only
able to update few rows of record in Table2 after the trigger execution.
Thank you in advance.
regards,
StephanieStephanie wrote:
> Dear someone who can help,
> I encounter a problem where the execution of a trigger is very slow
> (in terms of minute). Below is the example of the trigger that I have
> been implemented.
> Assume the trigger is fired on Table1 when inserting. The trigger
> perform the following
> - Create a temporary table of @.Temp
> - Retrieve all the records from Table2 and insert into a temporary
> table.
> - Then loop each of the temporary-Record and perform some business
> logic
> - Update the record back into Table2
> Question:
> 1. Is the temporary table method correctly been used? I understand
> that Cursor is not recommended in Trigger.
> 2. How to speed up the performance.
> 3. I have tried this in oracle, it is pretty fast (in 2 ~ 5 second)
> compare to MS Sql.
> 4. I have tried with call a stored procedure with explicit
> transaction, but it didn't help much. Moreover, sometime this method
> only able to update few rows of record in Table2 after the trigger
> execution.
> Thank you in advance.
> regards,
> Stephanie
It's considered bad practive to use cursors and any extensive processing
in triggers as they will slow down the entire transaction, keeping locks
active in the database much longer than needed. Sometimes, if the
business rules require it, you can get away with it without affecting
performance too much.
What I don't understand is why there is no reference to reference to the
inserted virtual table in the trigger. What you have is a trigger that
affects all rows in another table every time a row is updated. I think
you really need to explain to the group what you are trying to do in the
trigger and why it is necessary.
David Gugick
Imceda Software
www.imceda.com|||Dear David,
In this example, i'm trying to show that when i update record in Table1, i
need to update the 'Code' field in Table2, in this case, will update the in
bulk. May be i should correct my example as below:
========================================
==========================
CREATE TRIGGER TG_Table1_Ins ON Table1
DECLARE @.InsCode integer /* stored the Code value from Table1 – On
Inserted*/
DECLARE @.Code integer /*temporary variable */
DECLARE @.KeyStr char (3) /*temporary variable */
Set @.InsCode = (select Code from inserted)
/*Create temporary table and data will be retrieved from table 2*/
Create Table #Temp (KeyStr char (3), Code integer)
/* Assume the table2 contains 3 thousand records with 30 data fields*/
Insert into #Temp (KeyStr, Code) Select KeyStr, Code From From Table2 where
Flag = 'Y'
/*loop for this temporary table*/
While Exists (Select * From #Temp) Begin
Set @.KeyStr = (Select top 1 KeyStr From #Temp)
Set @.Code = (Select Code From #temp where KeyStr = @.KeyStr)
/*some logic processing here, this is only example*/
If (@.Code > 10)
Set @.Code = @.InsCode * 2
Else Set @.Code = @.InsCode
Update Table2 Set Code = @.Code Where KeyStr = @.KeyStr
Delete #Temp Where KeyStr = @.KeyStr
End
Drop table #Temp
End
========================================
==========================
Hope this is clear. Thank you.
regards,
Stephanie|||Don't use cursors or loops in a trigger. Typically it isn't a good idea
to assign column values to variables in a trigger as this normally
implies you'll have to use a loop if there is more than one row
updated.
You can handle multiple updated rows by referring the INSERTED and
DELETED tables in your trigger code. Example:
UPDATE SomeTable
SET something ...
WHERE EXISTS
(SELECT *
FROM Inserted
WHERE Inserted.key_col = SomeTable.key_col)
Unfortunately your trigger code doesn't do anything useful so it isn't
really possible to give a solution specifc to your situation.
David Portas
SQL Server MVP
--|||On Wed, 16 Mar 2005 18:35:02 -0800, Stephanie wrote:
(snip)
>Question:
>1. Is the temporary table method correctly been used? I understand that
>Cursor is not recommended in Trigger.
Hi Stephanie,
This is still a cursor. Everytime you loop through a set of rows and
process one row as a time, you're performing a cursor operation, even if
you are not using the prebuilt cursor tools for it.
Warning against using cursors should be taken as warning against any
form of iterative processing. In SQL, you should write queries that
operate on whole sets at once.
Another thing (even though you don't ask) - a trigger fires once per
statement execution. All rows affected by the statement (be it one, zero
or five million) are in the inserted pseudo-table. The code you posted
will result in an error if there are more than one (but if you used
SELECT instead of SET, it would not error - it would just pick one of
the rows and process only that one). And it will produce wrong results
if no rows are affected (as @.InsCode will be set to NULL).
>2. How to speed up the performance.
Remove the cursor. Replace it with a set-based UPDATE statement.
You've posted two slighhtly different versions of your trigger, but I
believe that your real trigger is more complicated than this (if only
because the CREATE TRIGGER statement itself would cause an error). As an
exampl, I'll post a set-based equivalent of the second trigger you
posted:
CREATE TRIGGER TG_Table1_Ins ON Table1 AFTER INSERT
AS
UPDATE Table2
SET Code = (SELECT i.InsCode
FROM inserted AS i)
* CASE WHEN Table2.Code > 10 THEN 2 ELSE 1 END
go
As you can see, the set-based code is much shorter. If you test it,
you'll find that it's much faster as well.
In case you need help rewriting your real code in set-based form, you'll
need to post the table structure (as CREATE TABLE statements), some
sample data (as INSERT statements) and expected output. Posting the slow
code you currently use might help as well. See www.aspfaq.com/5006.
>3. I have tried this in oracle, it is pretty fast (in 2 ~ 5 second) compare
>to MS Sql.
I don't know Oracle myself, but based on what I've heard, it appears as
if SQL Server is heavily optimized for set-based processing (with the
obvious tradeoff in cursor operations), whereas Oracle is either more
optimized for cursorbased operation, or a bit for both. In any case,
cursors do tend to be faster in Oracle.
>4. I have tried with call a stored procedure with explicit
>transaction, but it didn't help much. Moreover, sometime this method only
>able to update few rows of record in Table2 after the trigger execution.
Do you mean that some rows in Table2 are changed and some are not? If
the rows that need to be changed are determined by the data in the
inserted pseudo-table, then this makes sense (as only one of the rows in
inserted will be processed). But with the code you posted, this should
not be possible - it should either process all rows in table2, or none
at all - regardless of transactions in the calling stored procedure (a
trigger is always part of a transaction - if not explicit, then implicit
as part of the statement that fires the trigger). If you can post some
code to reproduce this (I'd need at least the CREATE TABLE and INSERT
statements to get the starting data, the actual trigger code and the
statement that fires the trigger), I'll gladly look into it.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)sql
I encounter a problem where the execution of a trigger is very slow (in
terms of minute). Below is the example of the trigger that I have been
implemented.
Assume the trigger is fired on Table1 when inserting. The trigger perform
the following
- Create a temporary table of @.Temp
- Retrieve all the records from Table2 and insert into a temporary table.
- Then loop each of the temporary-Record and perform some business logic
- Update the record back into Table2
========================================
==========================
CREATE TRIGGER TG_Table1_Ins ON Table1
DECLARE @.InsCode integer /* stored the Code value from Table1 – On
Inserted*/
DECLARE @.Code integer /*temporary variable */
DECLARE @.KeyStr char (3) /*temporary variable */
Set @.InsCode = (select Code from inserted)
/*Create temporary table and data will be retrieved from table 2*/
Create Table #Temp (KeyStr char (3), Code integer)
/* Assume the table2 contains 3 thousand records with 30 data fields*/
Insert into #Temp (KeyStr, Code) Select KeyStr, Code From From Table2
/*loop for this temporary table*/
While Exists (Select * From #Temp) Begin
Set @.KeyStr = (Select top 1 KeyStr From #Temp)
Set @.Code = (Select Code From #temp where KeyStr = @.KeyStr)
/*some logic processing here, this is only example*/
If (@.InsCode > 10)
Set @.Code = @.InsCode * 2
Else Set @.Code = @.InsCode
Update Table2 Set Code = @.Code Where KeyStr = @.KeyStr
Delete #Temp Where KeyStr = @.KeyStr
End
Drop table #Temp
End
========================================
==========================
Question:
1. Is the temporary table method correctly been used? I understand that
Cursor is not recommended in Trigger.
2. How to speed up the performance.
3. I have tried this in oracle, it is pretty fast (in 2 ~ 5 second) compare
to MS Sql.
4. I have tried with call a stored procedure with explicit
transaction, but it didn't help much. Moreover, sometime this method only
able to update few rows of record in Table2 after the trigger execution.
Thank you in advance.
regards,
StephanieStephanie wrote:
> Dear someone who can help,
> I encounter a problem where the execution of a trigger is very slow
> (in terms of minute). Below is the example of the trigger that I have
> been implemented.
> Assume the trigger is fired on Table1 when inserting. The trigger
> perform the following
> - Create a temporary table of @.Temp
> - Retrieve all the records from Table2 and insert into a temporary
> table.
> - Then loop each of the temporary-Record and perform some business
> logic
> - Update the record back into Table2
> Question:
> 1. Is the temporary table method correctly been used? I understand
> that Cursor is not recommended in Trigger.
> 2. How to speed up the performance.
> 3. I have tried this in oracle, it is pretty fast (in 2 ~ 5 second)
> compare to MS Sql.
> 4. I have tried with call a stored procedure with explicit
> transaction, but it didn't help much. Moreover, sometime this method
> only able to update few rows of record in Table2 after the trigger
> execution.
> Thank you in advance.
> regards,
> Stephanie
It's considered bad practive to use cursors and any extensive processing
in triggers as they will slow down the entire transaction, keeping locks
active in the database much longer than needed. Sometimes, if the
business rules require it, you can get away with it without affecting
performance too much.
What I don't understand is why there is no reference to reference to the
inserted virtual table in the trigger. What you have is a trigger that
affects all rows in another table every time a row is updated. I think
you really need to explain to the group what you are trying to do in the
trigger and why it is necessary.
David Gugick
Imceda Software
www.imceda.com|||Dear David,
In this example, i'm trying to show that when i update record in Table1, i
need to update the 'Code' field in Table2, in this case, will update the in
bulk. May be i should correct my example as below:
========================================
==========================
CREATE TRIGGER TG_Table1_Ins ON Table1
DECLARE @.InsCode integer /* stored the Code value from Table1 – On
Inserted*/
DECLARE @.Code integer /*temporary variable */
DECLARE @.KeyStr char (3) /*temporary variable */
Set @.InsCode = (select Code from inserted)
/*Create temporary table and data will be retrieved from table 2*/
Create Table #Temp (KeyStr char (3), Code integer)
/* Assume the table2 contains 3 thousand records with 30 data fields*/
Insert into #Temp (KeyStr, Code) Select KeyStr, Code From From Table2 where
Flag = 'Y'
/*loop for this temporary table*/
While Exists (Select * From #Temp) Begin
Set @.KeyStr = (Select top 1 KeyStr From #Temp)
Set @.Code = (Select Code From #temp where KeyStr = @.KeyStr)
/*some logic processing here, this is only example*/
If (@.Code > 10)
Set @.Code = @.InsCode * 2
Else Set @.Code = @.InsCode
Update Table2 Set Code = @.Code Where KeyStr = @.KeyStr
Delete #Temp Where KeyStr = @.KeyStr
End
Drop table #Temp
End
========================================
==========================
Hope this is clear. Thank you.
regards,
Stephanie|||Don't use cursors or loops in a trigger. Typically it isn't a good idea
to assign column values to variables in a trigger as this normally
implies you'll have to use a loop if there is more than one row
updated.
You can handle multiple updated rows by referring the INSERTED and
DELETED tables in your trigger code. Example:
UPDATE SomeTable
SET something ...
WHERE EXISTS
(SELECT *
FROM Inserted
WHERE Inserted.key_col = SomeTable.key_col)
Unfortunately your trigger code doesn't do anything useful so it isn't
really possible to give a solution specifc to your situation.
David Portas
SQL Server MVP
--|||On Wed, 16 Mar 2005 18:35:02 -0800, Stephanie wrote:
(snip)
>Question:
>1. Is the temporary table method correctly been used? I understand that
>Cursor is not recommended in Trigger.
Hi Stephanie,
This is still a cursor. Everytime you loop through a set of rows and
process one row as a time, you're performing a cursor operation, even if
you are not using the prebuilt cursor tools for it.
Warning against using cursors should be taken as warning against any
form of iterative processing. In SQL, you should write queries that
operate on whole sets at once.
Another thing (even though you don't ask) - a trigger fires once per
statement execution. All rows affected by the statement (be it one, zero
or five million) are in the inserted pseudo-table. The code you posted
will result in an error if there are more than one (but if you used
SELECT instead of SET, it would not error - it would just pick one of
the rows and process only that one). And it will produce wrong results
if no rows are affected (as @.InsCode will be set to NULL).
>2. How to speed up the performance.
Remove the cursor. Replace it with a set-based UPDATE statement.
You've posted two slighhtly different versions of your trigger, but I
believe that your real trigger is more complicated than this (if only
because the CREATE TRIGGER statement itself would cause an error). As an
exampl, I'll post a set-based equivalent of the second trigger you
posted:
CREATE TRIGGER TG_Table1_Ins ON Table1 AFTER INSERT
AS
UPDATE Table2
SET Code = (SELECT i.InsCode
FROM inserted AS i)
* CASE WHEN Table2.Code > 10 THEN 2 ELSE 1 END
go
As you can see, the set-based code is much shorter. If you test it,
you'll find that it's much faster as well.
In case you need help rewriting your real code in set-based form, you'll
need to post the table structure (as CREATE TABLE statements), some
sample data (as INSERT statements) and expected output. Posting the slow
code you currently use might help as well. See www.aspfaq.com/5006.
>3. I have tried this in oracle, it is pretty fast (in 2 ~ 5 second) compare
>to MS Sql.
I don't know Oracle myself, but based on what I've heard, it appears as
if SQL Server is heavily optimized for set-based processing (with the
obvious tradeoff in cursor operations), whereas Oracle is either more
optimized for cursorbased operation, or a bit for both. In any case,
cursors do tend to be faster in Oracle.
>4. I have tried with call a stored procedure with explicit
>transaction, but it didn't help much. Moreover, sometime this method only
>able to update few rows of record in Table2 after the trigger execution.
Do you mean that some rows in Table2 are changed and some are not? If
the rows that need to be changed are determined by the data in the
inserted pseudo-table, then this makes sense (as only one of the rows in
inserted will be processed). But with the code you posted, this should
not be possible - it should either process all rows in table2, or none
at all - regardless of transactions in the calling stored procedure (a
trigger is always part of a transaction - if not explicit, then implicit
as part of the statement that fires the trigger). If you can post some
code to reproduce this (I'd need at least the CREATE TABLE and INSERT
statements to get the starting data, the actual trigger code and the
statement that fires the trigger), I'll gladly look into it.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)sql
How to enhance the performance when there is bulk update in tr
Dear Hugo,
Thanks for your solution. With the set-based UPDATE method, really find that
it's much faster.
I’ll have another question. Lets take (my) the same example, now I need to
add another update trigger in Table2 as below (where the insert trigger in
Table1 still remain). In this trigger, what ever record(s) updated in Table2
will insert a new record into Table3.
========================================
=============
CREATE TRIGGER TG_Table2_Upd ON Table2
DECLARE @.Msg varchar(5) /*temporary variable */
DECLARE @.KeyStr char (3) /*temporary variable */
/*Create temporary table and data will be retrieved from table 2*/
Create Table #Temp (KeyStr char (3))
/* Assume the table2 contains 3 thousand records with 30 data fields*/
Insert into #Temp (KeyStr) Select KeyStr From inserted
/*loop for this temporary table*/
While Exists (Select * From #Temp) Begin
Set @.KeyStr = (Select top 1 KeyStr From #Temp)
Set @.Msg = ‘U’ + @.KeyStr
Insert into Table3 (UpdateTime, Message) values (CURRENT_TIMESTAMP, @.Msg)
Delete #Temp Where KeyStr = @.KeyStr
End
Drop table #Temp
End
========================================
=============
In this case, how can I improve the performance?
regards,
Stephanie
"Hugo Kornelis" wrote:
> On Wed, 16 Mar 2005 18:35:02 -0800, Stephanie wrote:
> (snip)
> Hi Stephanie,
> This is still a cursor. Everytime you loop through a set of rows and
> process one row as a time, you're performing a cursor operation, even if
> you are not using the prebuilt cursor tools for it.
> Warning against using cursors should be taken as warning against any
> form of iterative processing. In SQL, you should write queries that
> operate on whole sets at once.
> Another thing (even though you don't ask) - a trigger fires once per
> statement execution. All rows affected by the statement (be it one, zero
> or five million) are in the inserted pseudo-table. The code you posted
> will result in an error if there are more than one (but if you used
> SELECT instead of SET, it would not error - it would just pick one of
> the rows and process only that one). And it will produce wrong results
> if no rows are affected (as @.InsCode will be set to NULL).
>
> Remove the cursor. Replace it with a set-based UPDATE statement.
> You've posted two slighhtly different versions of your trigger, but I
> believe that your real trigger is more complicated than this (if only
> because the CREATE TRIGGER statement itself would cause an error). As an
> exampl, I'll post a set-based equivalent of the second trigger you
> posted:
> CREATE TRIGGER TG_Table1_Ins ON Table1 AFTER INSERT
> AS
> UPDATE Table2
> SET Code = (SELECT i.InsCode
> FROM inserted AS i)
> * CASE WHEN Table2.Code > 10 THEN 2 ELSE 1 END
> go
> As you can see, the set-based code is much shorter. If you test it,
> you'll find that it's much faster as well.
> In case you need help rewriting your real code in set-based form, you'll
> need to post the table structure (as CREATE TABLE statements), some
> sample data (as INSERT statements) and expected output. Posting the slow
> code you currently use might help as well. See www.aspfaq.com/5006.
>
> I don't know Oracle myself, but based on what I've heard, it appears as
> if SQL Server is heavily optimized for set-based processing (with the
> obvious tradeoff in cursor operations), whereas Oracle is either more
> optimized for cursorbased operation, or a bit for both. In any case,
> cursors do tend to be faster in Oracle.
>
> Do you mean that some rows in Table2 are changed and some are not? If
> the rows that need to be changed are determined by the data in the
> inserted pseudo-table, then this makes sense (as only one of the rows in
> inserted will be processed). But with the code you posted, this should
> not be possible - it should either process all rows in table2, or none
> at all - regardless of transactions in the calling stored procedure (a
> trigger is always part of a transaction - if not explicit, then implicit
> as part of the statement that fires the trigger). If you can post some
> code to reproduce this (I'd need at least the CREATE TABLE and INSERT
> statements to get the starting data, the actual trigger code and the
> statement that fires the trigger), I'll gladly look into it.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>On Thu, 17 Mar 2005 21:45:02 -0800, Stephanie wrote:
>Dear Hugo,
>Thanks for your solution. With the set-based UPDATE method, really find tha
t
>it's much faster.
>Ill have another question. Lets take (my) the same example, now I need to
>add another update trigger in Table2 as below (where the insert trigger in
>Table1 still remain). In this trigger, what ever record(s) updated in Table
2
>will insert a new record into Table3.
(snip)
Hi Stephanie,
The trigger code you posted will never work. You have omitted a part of
the CREATE TRIGGER statement, and you have an unmatched END.
Anyway, instead of filling a temp table, iterating iver the rows and
inserting new rows one at a time in Table3, you can easily do this in
one statement:
CREATE TRIGGER TG_Table2_Upd
ON Table2
AFTER INSERT, UPDATE -- Wild guess
AS
INSERT INTO Table3 (UpdateTime, Message)
SELECT CURRENT_TIMESTAMP, 'U' + KeyStr
FROM inserted
go
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thank you very much! :)
regards,
Stephanie Kan
"Hugo Kornelis" wrote:
> On Thu, 17 Mar 2005 21:45:02 -0800, Stephanie wrote:
>
> (snip)
> Hi Stephanie,
> The trigger code you posted will never work. You have omitted a part of
> the CREATE TRIGGER statement, and you have an unmatched END.
> Anyway, instead of filling a temp table, iterating iver the rows and
> inserting new rows one at a time in Table3, you can easily do this in
> one statement:
> CREATE TRIGGER TG_Table2_Upd
> ON Table2
> AFTER INSERT, UPDATE -- Wild guess
> AS
> INSERT INTO Table3 (UpdateTime, Message)
> SELECT CURRENT_TIMESTAMP, 'U' + KeyStr
> FROM inserted
> go
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>
Thanks for your solution. With the set-based UPDATE method, really find that
it's much faster.
I’ll have another question. Lets take (my) the same example, now I need to
add another update trigger in Table2 as below (where the insert trigger in
Table1 still remain). In this trigger, what ever record(s) updated in Table2
will insert a new record into Table3.
========================================
=============
CREATE TRIGGER TG_Table2_Upd ON Table2
DECLARE @.Msg varchar(5) /*temporary variable */
DECLARE @.KeyStr char (3) /*temporary variable */
/*Create temporary table and data will be retrieved from table 2*/
Create Table #Temp (KeyStr char (3))
/* Assume the table2 contains 3 thousand records with 30 data fields*/
Insert into #Temp (KeyStr) Select KeyStr From inserted
/*loop for this temporary table*/
While Exists (Select * From #Temp) Begin
Set @.KeyStr = (Select top 1 KeyStr From #Temp)
Set @.Msg = ‘U’ + @.KeyStr
Insert into Table3 (UpdateTime, Message) values (CURRENT_TIMESTAMP, @.Msg)
Delete #Temp Where KeyStr = @.KeyStr
End
Drop table #Temp
End
========================================
=============
In this case, how can I improve the performance?
regards,
Stephanie
"Hugo Kornelis" wrote:
> On Wed, 16 Mar 2005 18:35:02 -0800, Stephanie wrote:
> (snip)
> Hi Stephanie,
> This is still a cursor. Everytime you loop through a set of rows and
> process one row as a time, you're performing a cursor operation, even if
> you are not using the prebuilt cursor tools for it.
> Warning against using cursors should be taken as warning against any
> form of iterative processing. In SQL, you should write queries that
> operate on whole sets at once.
> Another thing (even though you don't ask) - a trigger fires once per
> statement execution. All rows affected by the statement (be it one, zero
> or five million) are in the inserted pseudo-table. The code you posted
> will result in an error if there are more than one (but if you used
> SELECT instead of SET, it would not error - it would just pick one of
> the rows and process only that one). And it will produce wrong results
> if no rows are affected (as @.InsCode will be set to NULL).
>
> Remove the cursor. Replace it with a set-based UPDATE statement.
> You've posted two slighhtly different versions of your trigger, but I
> believe that your real trigger is more complicated than this (if only
> because the CREATE TRIGGER statement itself would cause an error). As an
> exampl, I'll post a set-based equivalent of the second trigger you
> posted:
> CREATE TRIGGER TG_Table1_Ins ON Table1 AFTER INSERT
> AS
> UPDATE Table2
> SET Code = (SELECT i.InsCode
> FROM inserted AS i)
> * CASE WHEN Table2.Code > 10 THEN 2 ELSE 1 END
> go
> As you can see, the set-based code is much shorter. If you test it,
> you'll find that it's much faster as well.
> In case you need help rewriting your real code in set-based form, you'll
> need to post the table structure (as CREATE TABLE statements), some
> sample data (as INSERT statements) and expected output. Posting the slow
> code you currently use might help as well. See www.aspfaq.com/5006.
>
> I don't know Oracle myself, but based on what I've heard, it appears as
> if SQL Server is heavily optimized for set-based processing (with the
> obvious tradeoff in cursor operations), whereas Oracle is either more
> optimized for cursorbased operation, or a bit for both. In any case,
> cursors do tend to be faster in Oracle.
>
> Do you mean that some rows in Table2 are changed and some are not? If
> the rows that need to be changed are determined by the data in the
> inserted pseudo-table, then this makes sense (as only one of the rows in
> inserted will be processed). But with the code you posted, this should
> not be possible - it should either process all rows in table2, or none
> at all - regardless of transactions in the calling stored procedure (a
> trigger is always part of a transaction - if not explicit, then implicit
> as part of the statement that fires the trigger). If you can post some
> code to reproduce this (I'd need at least the CREATE TABLE and INSERT
> statements to get the starting data, the actual trigger code and the
> statement that fires the trigger), I'll gladly look into it.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>On Thu, 17 Mar 2005 21:45:02 -0800, Stephanie wrote:
>Dear Hugo,
>Thanks for your solution. With the set-based UPDATE method, really find tha
t
>it's much faster.
>Ill have another question. Lets take (my) the same example, now I need to
>add another update trigger in Table2 as below (where the insert trigger in
>Table1 still remain). In this trigger, what ever record(s) updated in Table
2
>will insert a new record into Table3.
(snip)
Hi Stephanie,
The trigger code you posted will never work. You have omitted a part of
the CREATE TRIGGER statement, and you have an unmatched END.
Anyway, instead of filling a temp table, iterating iver the rows and
inserting new rows one at a time in Table3, you can easily do this in
one statement:
CREATE TRIGGER TG_Table2_Upd
ON Table2
AFTER INSERT, UPDATE -- Wild guess
AS
INSERT INTO Table3 (UpdateTime, Message)
SELECT CURRENT_TIMESTAMP, 'U' + KeyStr
FROM inserted
go
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thank you very much! :)
regards,
Stephanie Kan
"Hugo Kornelis" wrote:
> On Thu, 17 Mar 2005 21:45:02 -0800, Stephanie wrote:
>
> (snip)
> Hi Stephanie,
> The trigger code you posted will never work. You have omitted a part of
> the CREATE TRIGGER statement, and you have an unmatched END.
> Anyway, instead of filling a temp table, iterating iver the rows and
> inserting new rows one at a time in Table3, you can easily do this in
> one statement:
> CREATE TRIGGER TG_Table2_Upd
> ON Table2
> AFTER INSERT, UPDATE -- Wild guess
> AS
> INSERT INTO Table3 (UpdateTime, Message)
> SELECT CURRENT_TIMESTAMP, 'U' + KeyStr
> FROM inserted
> go
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>
Friday, February 24, 2012
How to do multiple rows insert?
I want to do bulk insert of data into table.
I need to do it using System.Data.SqlServerCe namespace;
I need to do it using System.Data.SqlServerCe namespace;
Already tryed 2 methods.
1)
SqlCeCommand command = Connection.CreateCommand();
command.CommandText = "Insert INTO [Table] (col1, col2) Values (val1, val2); Insert INTO [Table] (col1, col2) Values(val11, val22)";
if (Connection.State != System.Data.ConnectionState.Closed) {
Connection.Close();
}
Connection.Open();
command.ExecuteNonQuery();
Connection.Close();
Doesn't work because of parsing error. Appearantly semicolon isn't understood in commandText, although if commandText is executed in SQL Management Studio it executes flawlessly.
2)
SqlCeCommand command = Connection.CreateCommand();
command.CommandText
= "INSERT INTO [Table] (col1, col2) SELECT val1, val2 UNION ALL SELECT val11, val12";
if (Connection.State != System.Data.ConnectionState.Closed) {
Connection.Close();
}
Connection.Open();
command.ExecuteNonQuery();
Connection.Close();
Using this method i found out bug (or so i think).
I need to insert around 10000 rows of data and wouldn't want to run
Connection.Open();
Command.Execute();
Connection.Close();
cycle for 10000 times.
Any help would be appreciated. Thnx in advance.
P.S.
Sorry for bad english.
Hi,
why open, execute and close?
Why not open at the beginning, do all your executes, and only close when you exit the program?
That should be much quicker
Pete
|||U r absolutly right. Not opening and closing connection every time reduced incertion time from 10min. to ~4min.But even if opening and closing connection only once data incertion last for ~4 mins. and that is unappropriate.
If I could cut data incertion to ~2 mins than it would be OK.|||
Try using parameterized queries for bulk inserts.
// Arranging Data in an Array.
const int no_of_values = 2;
int[] val1 = new int[no_of_values];
int[] val2 = new int[no_of_values];
val1[0] = val1;
val2[0] = val2;
val1[1] = val11;
val2[1] = val22;
// Do the inserts using Parameterized queries.
Connection.Open();
SqlCeCommand command = Connection.CreateCommand();
command.CommandText = "Insert INTO [Table] (col1, col2) Values (@.val1, @.val2)";
for (int i = 0; i < no_of_values; i++)
{
command.Parameters.Clear();
command.Parameters.Add("@.val1", val1[ i ]);
command.Parameters.Add("@.val2", val2[ i ]);
command.ExecuteNonQuery();
}
Connection.Close();
|||Here is a piece of VB code that does what you want - takes 2 seconds on a slowish PC. This uses a prepared parameterised INSERT statement, with the values of the parameters changed for each insert
Dim cn As SqlCeConnection
Dim cmd As New SqlCeCommand
Dim loopCount As Integer
cn = New SqlCeConnection()
cn.ConnectionString = "Data Source = |DataDirectory|\test.sdf"
cmd.Connection = cn
Try
cn.Open()
cmd.CommandText = "INSERT INTO TestTable (col1, col2) VALUES (@.col1, @.col2)"
cmd.Parameters.Add("@.col1", SqlDbType.Int)
cmd.Parameters.Add("@.col2", SqlDbType.NVarChar, 100)
cmd.Prepare()
For loopCount = 1 To 10000
cmd.Parameters("@.col1").Value = loopCount
cmd.Parameters("@.col2").Value = "abc"
cmd.ExecuteNonQuery()
Next loopCount
Catch ex As Exception
Debug.Print(ex.ToString)
Finally
If cn.State = ConnectionState.Open Then
cn.Close()
End If
End Try
|||Since version 3.0, Microsoft provided the SqlCeResultSet class that allows for direct table insertions - bypassing the SQL query processor altogether. And this means *very fast* insertions.|||Here is the code using a recordset. In back to back tests with the 'INSERT' method, there was no significant difference for 10,000 records - both very fast. I guess at the end of the day it is down to personal preference.
Dim cn As SqlCeConnection
Dim cmd As New SqlCeCommand
Dim loopCount As Integer
cn = New SqlCeConnection()
cn.ConnectionString = "Data Source = |DataDirectory|\test.sdf"
cmd.Connection = cn
Try
cn.Open()
cmd.CommandText = "SELECT * FROM TestTable"
Dim rs As SqlCeResultSet = cmd.ExecuteResultSet(ResultSetOptions.Updatable Or ResultSetOptions.Scrollable)
Dim rec As SqlCeUpdatableRecord = rs.CreateRecord()
Debug.Print(Date.Now)
For loopCount = 1 To 10000
rec.SetInt32(0, loopCount)
rec.SetString(1, "Sample text")
rs.Insert(rec)
Next loopCount
Catch ex As Exception
Debug.Print(ex.ToString)
Finally
If cn.State = ConnectionState.Open Then
cn.Close()
End If
End Try
Debug.Print(Date.Now)
End Sub|||Thank u very much.
Using SqlCeResultSet time of bulk inserting droped from ~4min. to 35s. (240s -> 35s)
That is wonderfull.
My solution in the end.
connection.Open();
while ((buffer = reader.ReadLine()) != null) {
if (buffer.Length == 0) { continue; } //skip empty lines
strArray = buffer.Trim().Split(separator);
if (strArray[0] == "HEADER:") {
// remove any previous data
command = connection.CreateCommand();
command.CommandText = "DELETE " + strArray[1];
command.ExecuteNonQuery();
strBuilder = new StringBuilder();
strBuilder.Append("SELECT " + strArray[2]);
for (int i = 4;i < strArray.Length;i += 2) {
strBuilder.Append(", " + strArray[ i ]);
}
strBuilder.Append(" FROM " + strArray[1]);
command.CommandText = strBuilder.ToString();
resultSet = command.ExecuteResultSet(ResultSetOptions.Updatable);
}
else {
//tables data rows
resultRecord = resultSet.CreateRecord();
resultRecord.SetValues(strArray);
resultSet.Insert(resultRecord);
}
}
connection.Close();
Allso want to mention that i was afraid that such insert can intermix data, but it worked like a charm.
By intermix i mean:
CREATE TABLE SOME_TABLE (
someCol1 [int],
someCol2 [nvarchar](50)
)
And in data file values are writen:
someCol2Val1, SomeCol1Val1
someCol2Val2, SomeCol1Val2 ....
But if Select query is writen indicating cols names, then insert is using the same order of cols as in Select query.
P.S.
Again thank you very much.
|||I was surpised that it took 35 seconds - I believe that if you moved " resultRecord = resultSet.CreateRecord();" into the "HEADER" block it would run faster still as you only need to create the record once rather than 10,000 times.
|||It is not often that I find a straightforward no-nonsense sample that I do not need to battle to put to use... nice job! I wish 90% of the info in cyberspace was like that(instead of 10%)
kudos to Mohit Khullar
How to do multiple rows insert?
I want to do bulk insert of data into table.
I need to do it using System.Data.SqlServerCe namespace;
I need to do it using System.Data.SqlServerCe namespace;
Already tryed 2 methods.
1)
SqlCeCommand command = Connection.CreateCommand();
command.CommandText = "Insert INTO [Table] (col1, col2) Values (val1, val2); Insert INTO [Table] (col1, col2) Values(val11, val22)";
if (Connection.State != System.Data.ConnectionState.Closed) {
Connection.Close();
}
Connection.Open();
command.ExecuteNonQuery();
Connection.Close();
Doesn't work because of parsing error. Appearantly semicolon isn't understood in commandText, although if commandText is executed in SQL Management Studio it executes flawlessly.
2)
SqlCeCommand command = Connection.CreateCommand();
command.CommandText
= "INSERT INTO [Table] (col1, col2) SELECT val1, val2 UNION ALL SELECT val11, val12";
if (Connection.State != System.Data.ConnectionState.Closed) {
Connection.Close();
}
Connection.Open();
command.ExecuteNonQuery();
Connection.Close();
Using this method i found out bug (or so i think).
I need to insert around 10000 rows of data and wouldn't want to run
Connection.Open();
Command.Execute();
Connection.Close();
cycle for 10000 times.
Any help would be appreciated. Thnx in advance.
P.S.
Sorry for bad english.
Hi,
why open, execute and close?
Why not open at the beginning, do all your executes, and only close when you exit the program?
That should be much quicker
Pete
|||U r absolutly right. Not opening and closing connection every time reduced incertion time from 10min. to ~4min.But even if opening and closing connection only once data incertion last for ~4 mins. and that is unappropriate.
If I could cut data incertion to ~2 mins than it would be OK.|||
Try using parameterized queries for bulk inserts.
// Arranging Data in an Array.
const int no_of_values = 2;
int[] val1 = new int[no_of_values];
int[] val2 = new int[no_of_values];
val1[0] = val1;
val2[0] = val2;
val1[1] = val11;
val2[1] = val22;
// Do the inserts using Parameterized queries.
Connection.Open();
SqlCeCommand command = Connection.CreateCommand();
command.CommandText = "Insert INTO [Table] (col1, col2) Values (@.val1, @.val2)";
for (int i = 0; i < no_of_values; i++)
{
command.Parameters.Clear();
command.Parameters.Add("@.val1", val1[ i ]);
command.Parameters.Add("@.val2", val2[ i ]);
command.ExecuteNonQuery();
}
Connection.Close();
|||Here is a piece of VB code that does what you want - takes 2 seconds on a slowish PC. This uses a prepared parameterised INSERT statement, with the values of the parameters changed for each insert
Dim cn As SqlCeConnection
Dim cmd As New SqlCeCommand
Dim loopCount As Integer
cn = New SqlCeConnection()
cn.ConnectionString = "Data Source = |DataDirectory|\test.sdf"
cmd.Connection = cn
Try
cn.Open()
cmd.CommandText = "INSERT INTO TestTable (col1, col2) VALUES (@.col1, @.col2)"
cmd.Parameters.Add("@.col1", SqlDbType.Int)
cmd.Parameters.Add("@.col2", SqlDbType.NVarChar, 100)
cmd.Prepare()
For loopCount = 1 To 10000
cmd.Parameters("@.col1").Value = loopCount
cmd.Parameters("@.col2").Value = "abc"
cmd.ExecuteNonQuery()
Next loopCount
Catch ex As Exception
Debug.Print(ex.ToString)
Finally
If cn.State = ConnectionState.Open Then
cn.Close()
End If
End Try
|||Since version 3.0, Microsoft provided the SqlCeResultSet class that allows for direct table insertions - bypassing the SQL query processor altogether. And this means *very fast* insertions.|||Here is the code using a recordset. In back to back tests with the 'INSERT' method, there was no significant difference for 10,000 records - both very fast. I guess at the end of the day it is down to personal preference.
Dim cn As SqlCeConnection
Dim cmd As New SqlCeCommand
Dim loopCount As Integer
cn = New SqlCeConnection()
cn.ConnectionString = "Data Source = |DataDirectory|\test.sdf"
cmd.Connection = cn
Try
cn.Open()
cmd.CommandText = "SELECT * FROM TestTable"
Dim rs As SqlCeResultSet = cmd.ExecuteResultSet(ResultSetOptions.Updatable Or ResultSetOptions.Scrollable)
Dim rec As SqlCeUpdatableRecord = rs.CreateRecord()
Debug.Print(Date.Now)
For loopCount = 1 To 10000
rec.SetInt32(0, loopCount)
rec.SetString(1, "Sample text")
rs.Insert(rec)
Next loopCount
Catch ex As Exception
Debug.Print(ex.ToString)
Finally
If cn.State = ConnectionState.Open Then
cn.Close()
End If
End Try
Debug.Print(Date.Now)
End Sub|||Thank u very much.
Using SqlCeResultSet time of bulk inserting droped from ~4min. to 35s. (240s -> 35s)
That is wonderfull.
My solution in the end.
connection.Open();
while ((buffer = reader.ReadLine()) != null) {
if (buffer.Length == 0) { continue; } //skip empty lines
strArray = buffer.Trim().Split(separator);
if (strArray[0] == "HEADER:") {
// remove any previous data
command = connection.CreateCommand();
command.CommandText = "DELETE " + strArray[1];
command.ExecuteNonQuery();
strBuilder = new StringBuilder();
strBuilder.Append("SELECT " + strArray[2]);
for (int i = 4;i < strArray.Length;i += 2) {
strBuilder.Append(", " + strArray[ i ]);
}
strBuilder.Append(" FROM " + strArray[1]);
command.CommandText = strBuilder.ToString();
resultSet = command.ExecuteResultSet(ResultSetOptions.Updatable);
}
else {
//tables data rows
resultRecord = resultSet.CreateRecord();
resultRecord.SetValues(strArray);
resultSet.Insert(resultRecord);
}
}
connection.Close();
Allso want to mention that i was afraid that such insert can intermix data, but it worked like a charm.
By intermix i mean:
CREATE TABLE SOME_TABLE (
someCol1 [int],
someCol2 [nvarchar](50)
)
And in data file values are writen:
someCol2Val1, SomeCol1Val1
someCol2Val2, SomeCol1Val2 ....
But if Select query is writen indicating cols names, then insert is using the same order of cols as in Select query.
P.S.
Again thank you very much.
|||I was surpised that it took 35 seconds - I believe that if you moved " resultRecord = resultSet.CreateRecord();" into the "HEADER" block it would run faster still as you only need to create the record once rather than 10,000 times.
|||It is not often that I find a straightforward no-nonsense sample that I do not need to battle to put to use... nice job! I wish 90% of the info in cyberspace was like that(instead of 10%)
kudos to Mohit Khullar
Sunday, February 19, 2012
How to do bulk Delete
I have a table that has a primary key made of 3 fields (we don't want to use a surrogate key in this situation). In a particular process there is a work table that contains these 3 PK fields and we want to bulk delete them from the base table. Without looping thru the work table, how can I write a Delete statement to delete records in the base table using the work table rows as the criteria?
This syntax illustrates what I want to do, but is not allowed by SQL.
DELETE bt.* FROM basetable bt
INNER JOIN #work wk On bt.fld1 = wk.fld1 And bt.fld2 = wk.fld2 And bt.fld3 = wk.fld3
The correct DELETE statement using TSQL extension is below:
DELETE basetable
FROM basetable bt
INNER JOIN #work wk
ON bt.fld1 = wk.fld1 And bt.fld2 = wk.fld2 And bt.fld3 = wk.fld3
But best is to use the ANSI SQL syntax which doesn't have any ambiguity:
DELETE FROM basetable
WHERE EXISTS(SELECT * FROM #work as wk
WHERE wk.fld1 = basetable.fld1
AND wk.fld2 = basetable.fld2
AND wk.fld3 = basetable.fld3)
|||Ahhh!!! I tried so many variations .... except that one. Thanks.
Subscribe to:
Posts (Atom)