Hi All,
In general, how are triggers and constraints are going to slow down DML operations? Especially on a highly transactional system.
Thanks,
Sam
Should be relatively straightforward. A check constraint will have to perform some action to validate the data, let's keep it simple and say all values have to be > 0. So, on an insert, that's a teeny tiny bit of additional processing. Add two constraints, more processing. Add 50, a lot more processing.
Triggers are even bigger. Let's keep it simple again, the trigger captures insert data and adds it to an audit table. So, when you insert one row to your table, you're actually inserting two rows because you have to insert to the second table. This is additional processing, I/O, log use, the whole thing. More triggers, more processing. More rows on more triggers, more processing.
That's not to say that you shouldn't be using these things (although, I ALWAYS look at triggers with a bit of doubt and more than a bit of concern, can we do this some other way?). You should. Especially constraints, considering they're usually ensuring that your data is clean and appropriate for later reporting or processing. You should however, be careful in how you use them, where you use them, how many, how they're written, etc.
As so many of your questions, it's all about monitoring to understand where the pain points are and what you can do about them.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 18, 2023 at 6:33 am
Getting awareness of how these small things go bad when data increases and more concurrency and more applications access the same central database with multiple integrations. Thanks a lot Sir. Its very simple but effective for a layman to understand. Many thanks.
February 18, 2023 at 6:36 am
Trying to get different perspectives! May be I am looking at the things might not be the right. Getting awareness from wise people will definitely make me think with open mind.
February 20, 2023 at 9:19 pm
Trying to get different perspectives! May be I am looking at the things might not be the right. Getting awareness from wise people will definitely make me think with open mind.
Of course. And just because some dude on the internet said it, doesn't make it true. Validate this stuff for yourself, always.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 20, 2023 at 10:20 pm
vsamantha35 wrote:Trying to get different perspectives! May be I am looking at the things might not be the right. Getting awareness from wise people will definitely make me think with open mind.
Of course. And just because some dude on the internet said it, doesn't make it true. Validate this stuff for yourself, always.
Heh... It's not just the internet for me... I don't even trust the official Microsoft documentation anymore.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply