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
SSC Eights!
SSC Eights! (801 reputation)SSC Eights! (801 reputation)SSC Eights! (801 reputation)SSC Eights! (801 reputation)SSC Eights! (801 reputation)SSC Eights! (801 reputation)SSC Eights! (801 reputation)SSC Eights! (801 reputation)

Group: General Forum Members
Points: 801 Visits: 1464
Gail,

I can't do that (change the code). That could materially change how the trigger works. While in this case it is a single insert (via a values ()), I'm not sure about the others. I do suspect this is not the problem (@@rowcount) but thought maybe someone had run into this before.

As for SQL 6.0. They system now runs up to SQL 2012 but the vendor has not upgrade their database design or much of their code to take into account new standards and features. I have found this to be very common with the accounting packages I have worked with (that they don't upgrade working code).

John
jcb
jcb
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2870 Visits: 993
GilaMonster (11/20/2013)
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, you are right (as usual).
But triggers, cursors, denormalization and other DB tools are misused most of time by developers.

Cannot remember last time I found a trigger is not wrong used to implement a business logic better to be left in the application.
As a developer I try hard to put that tools to good use, only.

John, if you must stick with that application as it was made you are right to take extra caution before changing anything.
I also suspect that line is not the problem.

Make some profiling and keep a eye in that inserts query plan.
A spare server and a DB backup can be great to explore and test some solutions.

I hope you find the culprit and fix it (and share it at the forum).
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86484 Visits: 45239
ignore...

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


John Hanrahan
John Hanrahan
SSC Eights!
SSC Eights! (801 reputation)SSC Eights! (801 reputation)SSC Eights! (801 reputation)SSC Eights! (801 reputation)SSC Eights! (801 reputation)SSC Eights! (801 reputation)SSC Eights! (801 reputation)SSC Eights! (801 reputation)

Group: General Forum Members
Points: 801 Visits: 1464
Well I'm not 100% sure but I think we've found the problem. The system we use is interconnected with several other systems some of which are a bit old. It looks like the Windows Heap was somehow causing issues, technically the software that uses it. We solved it by running a Microsoft Heap analyzer product which saw it getting full. I don't understand how that could effect SQL but somehow it looks like it was. The Heap problem is on another machine but communicates to SQL.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)

Group: General Forum Members
Points: 84947 Visits: 41071
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.


Just for the record and to emphasize what Gail has already posted, I strongly disagree with both notions.

Poorly written triggers are evil... not all triggers.

Putting certain types of business logic in the database ensures the business logic is carried out when actions are initiated through other means than the application. Sometimes the "appropriate layer" is actually the data layer.

But triggers, cursors, denormalization and other DB tools are misused most of time by developers.


Misuse by people not qualified to use the tools doesn't make the tools bad. A chain saw can cut your arm off but some know how to carve intricate works of art with one.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
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