multiple rows insert: execution

Showing posts with label execution. 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

How to enforce SQL Server 2005 to use Worktable?

I have a problem in SQL Server 2005. In some cases SQL Server produces an execution plan of complex query (8 joins of views, some of views contains couple of joins) which does not contain a woktable creation in tempdb. As a result time of query execution increasion for about 5 seconds to about 4 minutes. All necessary indexes are created. It sims all data located in cache. Is there any way to enforce SQL Server to create worktable?

Query

SELECT [a0].[id],[a0].[Priority],[a0].[Heading],[a0].[DocumentDate],[a0].[LastName],[a0].[FirstName],[a2].[Name],[a1].[Position],[a4].[ContactTime],[a4].[Subject],[a0].[WorkPhone],[a0].[MobilePhone],[a0].[FaxNumber],[a0].[PrimaryEmail],[a5].[ContactTime],[a6].[Value],[a7].[id],[a7].[id_class],[a8].[id],[a8].[id_class],[a0].[FIO]
FROM [Bkc_EBM_Person_View] [a0]
LEFT JOIN [Bkc_EBM_ContactsInfo_View] [a3] ON ( a0.ContactsInfo_id = a3.[id] )
LEFT JOIN [Bkc_EBM_Contact_View] [a4] ON ( a3.LastContact_id = a4.[id] )
LEFT JOIN [Bkc_EBM_Contact_View] [a5] ON ( a3.NextContact_id = a5.[id] )
LEFT JOIN [Bkc_EBM_PersonType_View] [a6] ON ( a0.PersonType_id = a6.[id] )
LEFT JOIN [Bkc_EBM_Employment_View] [a1] ON ( a0.PrimaryEmployment_id = a1.[id] )
LEFT JOIN [Bkc_EBM_Client_View] [a2] ON ( a1.Client_id = a2.[id] )
LEFT JOIN [Bkc_EBM_Person_View] [a7] ON ( a0.Responsible_id = a7.[id] )
LEFT JOIN [Bkc_EBM_Department_View] [a8] ON ( a0.Department_id = a8.[id] )

Statistics

(2454 row(s) affected)

Table 'ReadRights'. Scan count 2455, logical reads 109411, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'ProcessAccounts'. Scan count 11102, logical reads 22266, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Bkc_EBM_Department'. Scan count 0, logical reads 4908, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Bkc_EBM_Person'. Scan count 0, logical reads 38986, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Bkc_EBM_Client'. Scan count 0, logical reads 4826, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Bkc_EBM_Employment'. Scan count 0, logical reads 83632238, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Bkc_EBM_PersonType'. Scan count 0, logical reads 4908, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Bkc_EBM_Contact'. Scan count 0, logical reads 9816, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Bkc_EBM_ContactsInfo'. Scan count 0, logical reads 4908, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

SQL Server Execution Times:

CPU time = 231719 ms,elapsed time = 253491 ms.

Execution plan

http://rsdn.ru/File/22090/plan1.rar

Please post the view definitions.

It looks like your view definition for BKc_EBM_Employment has a bad join in it, You are processing 640Gb of data from the BKcEBM_Employment table

|||try forcing a recompile.

e.g.
select *
from ...
option (recompile)|||

Below is the view definition

Bkc_EBM_Employment is a table which connects Bkc_EBM_Person and Bkc_EBM_Client in a many-to-many relation.

ReadRights is a table which defines rights of user account to view particular document in system

processaccounts is a table, to which Application server writes corrspondence between current spid and user account id, before executing a query

USE [Oblik_CRM]

/****** Object: View [dbo].[Bkc_EBM_Employment_View] Script Date: 10/04/2006 10:18:38 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE VIEW [dbo].[Bkc_EBM_Employment_View] WITH SCHEMABINDING AS

(SELECT [id], 1370 AS [id_class], cast([id] as varchar(64))+'_1370' AS [id_record], t.[EmployedPerson_id], t.[EmployedPerson_class_id], t.[Client_id], t.[Client_class_id], t.[Department_id], t.[Department_class_id], t.[Position], t.[PlaceOfWorkType_id], t.[PlaceOfWorkType_class_id], t.[RoleEmployee_id], t.[RoleEmployee_class_id], t.[FRC_id], t.[FRC_class_id], t.[InnerPhone], t.[IsPrimary], t.[IsFired], t.[EmploymentDate], t.[FiredDate], t.[FiredReason_id], t.[FiredReason_class_id], t.[Description], t.[RightToSign], t.[Heading], t.[Version] FROM dbo.Bkc_EBM_Employment t

inner join dbo.ReadRights r on (t.EmployedPerson_id = r.object_id) inner join dbo.ProcessAccounts pa WITH (NOLOCK) on (r.account_id = pa.account_id) where pa.spid=@.@.spid

)

|||

I rewrite the query without using viws. It helps a little because in new query data, that not needed by this query not queried by the views. But time of query execution is still to long. About 2 minutes

declare @.userID int

set @.userID = 104356

declare @.qp_0 int

set @.qp_0 = 0

DELETE ProcessAccounts WITH (ROWLOCK) WHERE spid=@.@.spid INSERT ProcessAccounts WITH (ROWLOCK) (account_id, spid) VALUES (@.userID, @.@.spid)

SELECT

[a0].[id],[a0].[Heading],[a0].[DocumentDate],[a0].[DocumentId],[a0].[Name],[a0].[PrimaryPhone],[a0].[PrimaryEmail],

[a1].[FIO],

[a3].[ContactTime],

[a4].[WorkPhone],[a4].[WorkEmail],[a4].[FIO],

[a5].[Value],

[a6].[Position],

[a7].[ContactTime]

FROM

(

SELECT [a0].[id],[a0].[Heading],[a0].[DocumentDate],[a0].[DocumentId],[a0].[Name],[a0].[PrimaryPhone],[a0].[PrimaryEmail], a0.Responsible_id, a0.ClientType_id, a0.ContactsInfo_id, a0.IsTemplate FROM [Bkc_EBM_Client] [a0]

INNER JOIN [ReadRights] [a0r] ON ([a0].[id] = [a0r].[object_id] )

INNER JOIN ( SELECT [account_id] FROM [ProcessAccounts] WITH (NOLOCK) WHERE [spid] = @.@.SPID ) [a0pa] ON ( [a0r].[account_id] = [a0pa].[account_id] )

) [a0]

LEFT JOIN [Bkc_EBM_ClientType] [a5] ON ( a0.ClientType_id = a5.[id] )

LEFT JOIN

(

SELECT [a1].[id], [a1].[FIO] FROM [Bkc_EBM_Person] [a1]

INNER JOIN [ReadRights] [a1r] ON ([a1].[id] = [a1r].[object_id] )

INNER JOIN ( SELECT [account_id] FROM [ProcessAccounts] WITH (NOLOCK) WHERE [spid] = @.@.SPID ) [a1pa] ON ( [a1r].[account_id] = [a1pa].[account_id] )

) [a1] ON ( a0.Responsible_id = a1.[id] )

LEFT JOIN [Bkc_EBM_ContactsInfo] [a2] ON ( a0.ContactsInfo_id = a2.[id] )

LEFT JOIN

(

SELECT [a3].[id], [a3].[ContactTime] FROM [Bkc_EBM_Contact] [a3]

INNER JOIN [ReadRights] [a3r] ON ([a3].[id] = [a3r].[object_id] )

INNER JOIN ( SELECT [account_id] FROM [ProcessAccounts] WITH (NOLOCK) WHERE [spid] = @.@.SPID ) [a3pa] ON ( [a3r].[account_id] = [a3pa].[account_id] )

) [a3] ON ( a2.LastContact_id = a3.[id] )

LEFT JOIN

(

SELECT [a4].[id], [a4].[WorkPhone],[a4].[WorkEmail],[a4].[FIO], [a4].[PrimaryEmployment_id] FROM [Bkc_EBM_Person] [a4]

INNER JOIN [ReadRights] [a4r] ON ([a4].[id] = [a4r].[object_id] )

INNER JOIN ( SELECT [account_id] FROM [ProcessAccounts] WITH (NOLOCK) WHERE [spid] = @.@.SPID ) [a4pa] ON ( [a4r].[account_id] = [a4pa].[account_id] )

) [a4] ON ( a2.LastContactPerson_id = a4.[id] )

LEFT JOIN

(

SELECT [a6].[id], [a6].[Position] FROM [Bkc_EBM_Employment] [a6]

INNER JOIN [ReadRights] [a6r] ON ([a6].[EmployedPerson_id] = [a6r].[object_id] )

INNER JOIN ( SELECT [account_id] FROM [ProcessAccounts] WITH (NOLOCK) WHERE [spid] = @.@.SPID ) [a6pa] ON ( [a6r].[account_id] = [a6pa].[account_id] )

) [a6] ON ( a4.PrimaryEmployment_id = a6.[id] )

LEFT JOIN

(

SELECT [a7].[id], [a7].[ContactTime] FROM [Bkc_EBM_Contact] [a7]

INNER JOIN [ReadRights] [a7r] ON ([a7].[id] = [a7r].[object_id] )

INNER JOIN ( SELECT [account_id] FROM [ProcessAccounts] WITH (NOLOCK) WHERE [spid] = @.@.SPID ) [a7pa] ON ( [a7r].[account_id] = [a7pa].[account_id] )

) [a7] ON ( a2.NextContact_id = a7.[id] )

WHERE ([a0].[IsTemplate] = @.qp_0 OR [a0].[IsTemplate] IS NULL )

DELETE ProcessAccounts WITH (ROWLOCK) WHERE spid=@.@.spid

Statistics

(1750 row(s) affected)

Table 'ReadRights'. Scan count 5891, logical reads 222578, physical reads 425, read-ahead reads 283, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'ProcessAccounts'. Scan count 6, logical reads 5891, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Bkc_EBM_Contact'. Scan count 0, logical reads 7000, physical reads 98, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Bkc_EBM_Employment'. Scan count 0, logical reads 33686288, physical reads 33, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Bkc_EBM_Person'. Scan count 0, logical reads 7000, physical reads 60, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Bkc_EBM_ContactsInfo_View'. Scan count 0, logical reads 3500, physical reads 5, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Bkc_EBM_ClientType_View'. Scan count 0, logical reads 3500, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Bkc_EBM_Client'. Scan count 0, logical reads 33252, physical reads 43, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

SQL Server Execution Times:

CPU time = 111797 ms, elapsed time = 123889 ms.

Execution Plan

http://rsdn.ru/File/22090/plan4.rar

|||

Please post the scripts for all the tables involved and indexes.

To get performance you need to reduce the amount of data being read from Bkc_EBM_Employment

|||Do you have a where clause on your query ?|||

SimonS_ wrote:
Do you have a where clause on your query ?

Yes "WHERE ([a0].[IsTemplate] = @.qp_0 OR [a0].[IsTemplate] IS NULL ) " (see query in the answer above) but there is no records in database filtered by this condition.

About database

'ReadRights' ~ 500000 records
'ProcessAccounts' 1 record
'Bkc_EBM_Person' ~ 2500 records
'Bkc_EBM_Client' ~ 1700 records
'Bkc_EBM_ClientType' ~ 20 records
'Bkc_EBM_ContactsInfo' ~ 4000 records
'Bkc_EBM_Contact' ~ 5000 records
'Bkc_EBM_Employment' ~ 2500 records

Very strange, but it sims RECOMPILE option helps. Worktable is created even right after SQL Server restart. If RECOMPLIE not used after server restart Worktable not created.

|||

The problem is query plans. Your query won't change from one user to the next or from one template to the next however this means the same query plan will be used. However the same query plan will not be optimal for all situations.

What can happen is that a plan is put in the cache based on the first set of parameters supplied, if this plan is not suitable for all queries you can end up with the problem above.

The recompile will address this at the expense of having to compile the query every time.

|||

Thank you for your help Simon. RECOMPILE is really helps.

Maybe in my case using of parameters in a query is not the best choise? Especially @.userID ?

|||Can you still post the CREATE table statements and CREATE View statements so I can understand your query better, its quite difficult with your use of views to understand what is joining to what.|||

Script will be quite large. Maybe by email?

|||try SQLForumsATonarcDOTcom|||I send the script. Please check.|||Another problem. In SQL Server 2000 option (recompile) is not supported. Is there any way to enforce SQL Server 2000 to recompile execution plan every time?