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 Wednesday, November 20, 2013 9:04 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 9:56 AM
Points: 379, Visits: 902
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
Post #1516097
Posted Thursday, November 21, 2013 6:15 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 6:38 AM
Points: 2,664, Visits: 888
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).
Post #1516396
Posted Thursday, November 21, 2013 6:25 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 11:47 AM
Points: 41,525, Visits: 34,442
ignore...


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 #1516400
Posted Thursday, November 21, 2013 8:21 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 9:56 AM
Points: 379, Visits: 902
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.
Post #1516450
Posted Thursday, November 21, 2013 8:16 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Yesterday @ 10:03 PM
Points: 35,956, Visits: 30,244
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."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1516630
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse