Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Insert Trigger and @@rowcount problem - Performance Issue Expand / Collapse
Author
Message
Posted Tuesday, November 19, 2013 12:40 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 2:28 PM
Points: 407, Visits: 1,025
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
Post #1515736
Posted Tuesday, November 19, 2013 12:45 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:59 PM
Points: 13,007, Visits: 12,426
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 Moden's 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)
Post #1515738
Posted Tuesday, November 19, 2013 12:48 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 2:28 PM
Points: 407, Visits: 1,025
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.
Post #1515741
Posted Tuesday, November 19, 2013 12:55 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:59 PM
Points: 13,007, Visits: 12,426
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 Moden's 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)
Post #1515746
Posted Tuesday, November 19, 2013 1:00 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, September 29, 2014 10:46 AM
Points: 2,734, Visits: 943
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.
Post #1515747
Posted Tuesday, November 19, 2013 1:11 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:59 PM
Points: 13,007, Visits: 12,426
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 Moden's 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)
Post #1515752
Posted Tuesday, November 19, 2013 1:25 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 2:28 PM
Points: 407, Visits: 1,025
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.
Post #1515758
Posted Wednesday, November 20, 2013 5:57 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, September 29, 2014 10:46 AM
Points: 2,734, Visits: 943
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?

Post #1516001
Posted Wednesday, November 20, 2013 6:15 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 1:14 PM
Points: 39,977, Visits: 36,340
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 2008, MVP
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

Post #1516007
Posted Wednesday, November 20, 2013 6:18 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 1:14 PM
Points: 39,977, Visits: 36,340
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 2008, MVP
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

Post #1516009
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse