SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Insert Trigger and @@rowcount problem - Performance Issue


Insert Trigger and @@rowcount problem - Performance Issue

Author
Message
John Hanrahan
John Hanrahan
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1223 Visits: 1468
In an Insert Trigger this code is sometimes quite slow: "SELECT @numrows = @@rowcount"

This code is used to determine if a row was inserted into the table. So 2 questions.
1. Isn't this redundant? How can you get to an insert trigger if a row isn't being inserted?
2. Sometimes the above line of code is has a very long duration (like 37450ms in a sql trace). Any clues to point me in a direction?

My initial thought is to just delete the code. I am working with a commercial package so I have to be a little careful.

Thanks for reading!
John
Sean Lange
Sean Lange
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62395 Visits: 17954
John Hanrahan (11/19/2013)
In an Insert Trigger this code is sometimes quite slow: "SELECT @numrows = @@rowcount"

This code is used to determine if a row was inserted into the table. So 2 questions.
1. Isn't this redundant? How can you get to an insert trigger if a row isn't being inserted?
2. Sometimes the above line of code is has a very long duration (like 37450ms in a sql trace). Any clues to point me in a direction?

My initial thought is to just delete the code. I am working with a commercial package so I have to be a little careful.

Thanks for reading!
John


Without more details about the trigger it is hard to say for sure. Are there multiple statements in this triggers each of which references @numrows? If so then you might need it. I have a bad feeling that this does some sort of looping or something, at least my experience suggests that as a high probability. When you see the trigger worrying about @@rowcount it certainly raises a red flag that you should investigate that trigger.

Can you post the code for the trigger? We may want/need more details but that is the very least amount of information we would need to get started.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
John Hanrahan
John Hanrahan
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1223 Visits: 1468
Sean,

Here's the relevant code:
ALTER TRIGGER [dbo].[tI_timCustItem] ON [dbo].[timCustItem] FOR INSERT AS
/* Copyright (c) 1995-2011 Sage Software, Inc. All rights reserved. */
BEGIN
DECLARE @numrows INT,
@nullcnt INT,
@validcnt INT,
@errno INT,
@errmsg VARCHAR(255)

SELECT @numrows = @@ROWCOUNT
IF @numrows = 0
RETURN

SET NOCOUNT ON

/* tciUnitMeasure R_2279 timCustItem ON CHILD INSERT RESTRICT */
IF UPDATE(LastPriceUOMKey)
BEGIN
SELECT @validcnt = COUNT(*)
FROM inserted, tciUnitMeasure WITH (NOLOCK)
WHERE
inserted.LastPriceUOMKey = tciUnitMeasure.UnitMeasKey
SELECT @nullcnt = COUNT(*) FROM inserted WHERE
inserted.LastPriceUOMKey IS NULL
IF @validcnt + @nullcnt <> @numrows
BEGIN
SELECT @errno = 50002,
@errmsg = 'Unable to add the timCustItem record because it is trying to reference a record in tciUnitMeasure that does not exist. Reference ID: (R_2279).'
GOTO error
END
END

Maybe my question should be isn't @@rowcount always one 1 in a trigger? You can see they use @numrows to do error checking? I guess it depends on the answer for @@rowcount.

Thx.
Sean Lange
Sean Lange
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62395 Visits: 17954
John Hanrahan (11/19/2013)
Sean,

Here's the relevant code:

...

Maybe my question should be isn't @@rowcount always one 1 in a trigger? You can see they use @numrows to do error checking? I guess it depends on the answer for @@rowcount.

Thx.


Absolutely not. In SQL server triggers DO NOT fire row by row. They respond to a statement, which in this case is an insert.

I don't what datatypes of whatever are in this table but consider the following insert statement.


insert timCustItem (LastPriceUOMKey)
select 10 union all
select 20



Assuming that statement would be valid the inserted table would have 2 rows.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
jcb
jcb
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3358 Visits: 997
Question: a select count(1) from inserted ill return the number of inserted rows?

just for the record: avoid triggers, they are evil.
avoid to put business logic in the database, let the application to handle it in the appropriate layer.
Sean Lange
Sean Lange
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62395 Visits: 17954
jcb (11/19/2013)
Question: a select count(1) from inserted ill return the number of inserted rows?

just for the record: avoid triggers, they are evil.
avoid to put business logic in the database, let the application to handle it in the appropriate layer.


I agree about triggers but as the OP stated this is a 3rd party app.


I am working with a commercial package so I have to be a little careful.


To be honest if I were going to use a trigger for this type of thing...which I would most certainly not I would totally rewrite this things.

This entire trigger looks to me to be a replacement for a foreign key. Basically the logic here is to not allow a value of LastPriceUOMKey that doesn't already exist in tciUnitMeasure. And don't get me started on NOLOCK...

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
John Hanrahan
John Hanrahan
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1223 Visits: 1468
But any idea why @@rowcount would be so slow? The line of code doing the insert is a single row insert. I can't understand why it would be so slow.

As for the foreign key constraints. The product was first written for SQL 6.0 and they only allowed 16 levels of cascading deletes (as I recall), maybe it was even 8 back then. They had to use triggers instead of RI. They never upgraded their code or design. In fact the company that writes the main product has decided to kill the product we are using.
jcb
jcb
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3358 Visits: 997
Sean, you are right! Sorry John I missed the fact its a black box.
Sean you are right, again, about the FKs. Maybe the OP can just disable/drop that trigger and create a FK but that can impact the application error handling.

John, are you using MS SQL 6 or just a 2008 with compatibilities options?
How do you get that specific line inside the trigger is the problem?
Can you create a simulation environment just to test it?
Had you tried to change that line to
SELECT @numrows = select count(1) from inserted
or just
SET @numrows = 1
and see if it changes performance?


That performance issue smells as something is waiting other things to complete (did you got any deadlock or race condition?)
Is that insert part of a big transaction?
GilaMonster
GilaMonster
SSC Guru
SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)

Group: General Forum Members
Points: 223691 Visits: 46300
jcb (11/19/2013)
just for the record: avoid triggers, they are evil.


They are a tool. They can be used well or used badly. When used badly, blame the developer, not the tool.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


GilaMonster
GilaMonster
SSC Guru
SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)

Group: General Forum Members
Points: 223691 Visits: 46300
John Hanrahan (11/19/2013)
But any idea why @@rowcount would be so slow? The line of code doing the insert is a single row insert. I can't understand why it would be so slow.


It may not be that statement that's slow.

If you can change the trigger, add this line before that SELECT and then see what statement gets the high execution

SELECT @errmsg = NULL



It does nothing, I just want to see if the long delay is in the firing of the trigger or the exact SELECT itself.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search