Are the posted questions getting worse?
-
The other part about the documentation on index maintenance is that it's spread all over hell's little half acre. This IS a spot that actually describes what REORGANIZE does in a very correct manner but most people never read it because it's under a heading that talks about disk space rather than as a definition of REORGANIZE.
--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.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Having a weird case today (still under investigation). Parallel clustered index scan slower dan serial index scan (forced by MAXDOP 1)
*edit* repartition streams spilled to tempdb 🙁 Repartition stream 1=0,2=5000000,3=0,4=5000000. Oh well, query can't be modified
-
What happened to Gail Shaw ?
She used to be a regular poster on SSC.
-
Gail is still around, but less on technical sites. Life has taken a turn for her, and we chat at times, but mostly she's less engaged in this space. She still blogs at: https://sqlinthewild.co.za/
- Jo Pattyn wrote:
Having a weird case today (still under investigation). Parallel clustered index scan slower dan serial index scan (forced by MAXDOP 1)
*edit* repartition streams spilled to tempdb 🙁 Repartition stream 1=0,2=5000000,3=0,4=5000000. Oh well, query can't be modified
I believe you've correctly identified the problem and the reason why I frequently laugh at people that suggest resolving performance problems with the recommendation of "Kill it with Iron". It's also one of the reasons why understanding and correctly setting the "Cost Threshold for Parallelism" and database level MAXDOP is so very important and why using the OPTION(MAXDOP X) can be very important in code.
--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.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Thanks Jeff. Now investigating sql plan guides for additional nudges.
Summer has arrived + 25 degrees Celsius
-
Brief tangent into real SQL stuff...
Has anyone ever heard of table triggers not firing for every row if you do set-based updates to records?
Our vendor sent us code for an update and did it with a cursor to do hundreds of thousands of rows one by one (the RBAR is intense) because, and I quote the note attached to the code, "using cursor instead of multiple-row UPDATE statement so triggers will fire for each updated row".
I have never heard of triggers not firing for each row if set-based updates are used. Thoughts?
- Brandie Tarvin wrote:
Brief tangent into real SQL stuff...
Has anyone ever heard of table triggers not firing for every row if you do set-based updates to records?
Our vendor sent us code for an update and did it with a cursor to do hundreds of thousands of rows one by one (the RBAR is intense) because, and I quote the note attached to the code, "using cursor instead of multiple-row UPDATE statement so triggers will fire for each updated row".
I have never heard of triggers not firing for each row if set-based updates are used. Thoughts?
If the trigger is badly written, it will only fire for 1 record in each batch
https://www.brentozar.com/archive/2019/05/the-silent-bug-i-find-in-most-triggers/
- Brandie Tarvin wrote:
Brief tangent into real SQL stuff...
Has anyone ever heard of table triggers not firing for every row if you do set-based updates to records?
Our vendor sent us code for an update and did it with a cursor to do hundreds of thousands of rows one by one (the RBAR is intense) because, and I quote the note attached to the code, "using cursor instead of multiple-row UPDATE statement so triggers will fire for each updated row".
I have never heard of triggers not firing for each row if set-based updates are used. Thoughts?
I'll say what we're all thinking... it's because the vendor probably heard of people missing rows in triggers, didn't realize that it was because people wrote truly single row handling, and thought the only way to ensure that didn't happen was to write a Cursor. Unless they're calling a stored proc for each and every row (which would also be a huge mistake), they're making a huge mistake. Set-Based triggers don't skip rows... people's code does when written incorrectly.
Based on the, ummmm... shall we say, displayed bit of ignorance on the vendor, you might want to start looking for a better vendor because you're probably not going to be able to convince them that they're wrong. Too many nose-coners at the helm there. 😀
--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.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Brandie Tarvin wrote:
Brief tangent into real SQL stuff...
Has anyone ever heard of table triggers not firing for every row if you do set-based updates to records?
Our vendor sent us code for an update and did it with a cursor to do hundreds of thousands of rows one by one (the RBAR is intense) because, and I quote the note attached to the code, "using cursor instead of multiple-row UPDATE statement so triggers will fire for each updated row".
I have never heard of triggers not firing for each row if set-based updates are used. Thoughts?
Like the others, I have never been in a position where I've witnessed
insertednot containing all the rows that were inserted, and likewise, that the only time I have witness a trigger not processing all the rows ininsertedis because someone (foolishly) assumed that the trigger runs once per row, rather than for the entire DML statement.Either someone needs to educate the vendor on their flawed understanding so that they can roll out a priority fix (and in my view RBAR triggers being replaced with set-based ones is a huge priority) or yes you need to look elsewhere.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
I just took a glance at the trigger. The size of the thing is truly horrendous.
Not sure if it was truly built for single row processing because it'll probably take me days to break it down and verify. But I have a non-prod environment I might try rewriting the update code for set based just to test. Use a snapshot so I can roll back if it doesn't fix it.
And yes, we're already looking for a new vendor due to other reasons. Still, I'm glad to know I wasn't wrong to be shocked by that note.
- Jeff Moden wrote:Brandie Tarvin wrote:
Brief tangent into real SQL stuff...
Has anyone ever heard of table triggers not firing for every row if you do set-based updates to records?
Our vendor sent us code for an update and did it with a cursor to do hundreds of thousands of rows one by one (the RBAR is intense) because, and I quote the note attached to the code, "using cursor instead of multiple-row UPDATE statement so triggers will fire for each updated row".
I have never heard of triggers not firing for each row if set-based updates are used. Thoughts?
I'll say what we're all thinking... it's because the vendor probably heard of people missing rows in triggers, didn't realize that it was because people wrote truly single row handling, and thought the only way to ensure that didn't happen was to write a Cursor. Unless they're calling a stored proc for each and every row (which would also be a huge mistake), they're making a huge mistake. Set-Based triggers don't skip rows... people's code does when written incorrectly.
Based on the, ummmm... shall we say, displayed bit of ignorance on the vendor, you might want to start looking for a better vendor because you're probably not going to be able to convince them that they're wrong. Too many nose-coners at the helm there. 😀
I used to write those kind of triggers and procedures at my first job. Everyone did it that way. Of course, that was before I learn how to do it correctly.
- Brandie Tarvin wrote:
I just took a glance at the trigger. The size of the thing is truly horrendous.
If it's for a table with a large number of columns and it's an "audit" trigger that does the auditing by column rather than by row, that may be one of the reasons for it's size. I have code that regenerates audit triggers after you add columns to a given table and they're freakin' huge. They run nasty fast but they're huge.
--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.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Brandie Tarvin wrote:
I just took a glance at the trigger. The size of the thing is truly horrendous.
Not sure if it was truly built for single row processing because it'll probably take me days to break it down and verify. But I have a non-prod environment I might try rewriting the update code for set based just to test. Use a snapshot so I can roll back if it doesn't fix it.
And yes, we're already looking for a new vendor due to other reasons. Still, I'm glad to know I wasn't wrong to be shocked by that note.
At a former employer, we had what was simply called "THE trigger". It was so-named by previous developers. It was in Oracle and was over 1400 lines long. It balanced employee hours (scans, jobs, missing scans, etc.) in an ERP system and was the ultimate example of spaghetti code. Nobody even wanted to touch it for fear of breaking it. I tackled it, but it was no small task. It took days to just figure out the execution paths. It was eventually fixed, but it took a long time. The important part is that, while it was fixable, the code should never have existed in the first place. The ERP system lacked key functionality for the manufacturing environment, even though it was designed for manufacturing.
In the end, the company changed to a different ERP for reasons other than this missing functionality. If you're having that much trouble with a system, then you're probably right to look for a better solution. Good luck with it, Brandie. Dealing with this type of problem is a rough road to travel.
-
So fun thing I just realized, glancing at it again. The trigger is doing error handling. Yes, it's doing an audit thing for updating a history table, but … it's doing error handling as well.
To my mind, and maybe I'm off base, shouldn't error handling be handled in the actual code, not the triggers?
Viewing 15 posts - 65,491 through 65,505 (of 66,575 total)
You must be logged in to reply to this topic. Login to reply