Deleted and Inserted On A Trigger

Question

Deleted and Inserted On A Trigger

Viewing 15 posts - 16 through 30 (of 42 total)

You must be logged in to reply to this topic. Login to reply

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 1

On the more general point, I wouldn't expect 30,000 rows in inserted and deleted to cause a problem

I agree on it.

Have you captured an 'actual' execution plan for the trigger? If so, please share it. If not, perhaps that would be a good idea...

It's definitely a good idea. 🙂

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 2

1. Right now it is a theoretical question on wat is the best practice

For a therotical question (with 30K rows), whatever you do is fine. I wouldn't suggest the same if you say rows are 300+ K.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

@dev

Recruiters usually don't troll these forums. You're better off trying to contact them directly.

Also posting your personal info here is just asking for spam.

Have you tried linkedin?

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 4

GilaMonster (11/8/2011)
Dev @ +91 973 913 6683 (11/8/2011)
Per my understanding, Compilation just verifies syntaxical errors & object dependencies (permissions & availability etc.)
Syntax and object existence is done by the parser and algebriser. That's not a compile.
Optimizer decides to use indexes based on their availability & usefulness.
Generating a plan is what's referred to as 'compilation', so compilation is the point at which indexes are considered.

Gail, I will accept your comments if I find any supporting documents on MSDN. I don't find any (I googled it as well). Can you please guide me to related documents?

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 5

Ninja's_RGR'us (11/8/2011)
@Dev
Recruiters usually don't troll these forums. You're better off trying to contact them directly.
Also posting your personal info here is just asking for spam.
Have you tried linkedin?

I understand it but I am not spamming here. Also, I am not targeting any recruiter (especially HR dept resource) here. I am just targeting a technical person who might guess my skill set & might refer to HR dept later on.

It’s just a signature text. If I am violating any of SSC norms, I apologize. I will correct it.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

Dev @ +91 973 913 6683 (11/8/2011)
Ninja's_RGR'us (11/8/2011)
@Dev
Recruiters usually don't troll these forums. You're better off trying to contact them directly.
Also posting your personal info here is just asking for spam.
Have you tried linkedin?
I understand it but I am not spamming here. Also, I am not targeting any recruiter (especially HR dept resource) here. I am just targeting a technical person who might guess my skill set & might refer to HR dept later on.
It’s just a signature text. If I am violating any of SSC norms, I apologize. I will correct it.

I'm not saying you're spamming. I'm saying that bots that collect e-mails will start sending YOU spam.

Have you started a thread specifically asking for help on this topic. I'm sure there are plenty of folks here that would help you out with specific contacts.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Dev @ +91 973 913 6683 (11/8/2011)
Gail, I will accept your comments if I find any supporting documents on MSDN. I don't find any (I googled it as well).

http://msdn.microsoft.com/en-us/library/ms190439.aspx

As a database is changed by such actions as adding indexes or changing data in indexed columns, the original query plans used to access its tables should be optimized again by recompiling them.

http://msdn.microsoft.com/en-us/library/ee343986%28v=sql.100%29.aspx

Before a query, batch, stored procedure, trigger, prepared statement, or dynamic SQL statement (henceforth, "batch") begins execution on a SQL Server, the batch gets compiled into a plan.

Also, see the compiles/sec and recompiles/sec perfmon counters which count how many times a sec a query plan is generated or regenerated

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

Paul White SSC Guru Points: 150467 More actions · Answer 8

Dev @ +91 973 913 6683 (11/8/2011)
Gail, I will accept your comments if I find any supporting documents on MSDN. I don't find any (I googled it as well). Can you please guide me to related documents?

One could argue that compilation includes everything from parsing all the way through to the final copy-out that generates something truly executable by the query processor. Debating the semantic of the word is probably of dubious worth.

That said, I would probably disagree with your statement that "compilation just verifies syntax and object dependencies", though perhaps you have a particular definition of 'compilation' in mind. Shrug.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

SQL Kiwi (11/8/2011)
Dev @ +91 973 913 6683 (11/8/2011)
Gail, I will accept your comments if I find any supporting documents on MSDN. I don't find any (I googled it as well). Can you please guide me to related documents?
One could argue that compilation includes everything from parsing all the way through to the final copy-out that generates something truly executable by the query processor. Debating the semantic of the word is probably of dubious worth.

You could argue validly that compile is parse + bind (algebriser) + optimise, or that compile = optimise. I don't think I've ever seen any article/help file that uses compile for just the parsing portion of query execution though.

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

Paul White SSC Guru Points: 150467 More actions · Answer 10

GilaMonster (11/8/2011)
You could argue validly that compile is parse + bind (algebriser) + optimise, or that compile = optimise. I don't think I've ever seen any article/help file that uses compile for just the parsing portion of query execution though.

Oh I don't know, I could probably argue that trivial plans don't go near the optimizer...but I would have to be in a particularly 'difficult' mood to do that, perhaps :-). On the other hand, perhaps SQL text is 'compiled' into a sequence tree by the parser? Ha ha.

No...seriously, overall I would agree the common understanding of 'compilation' in SQL Server is the process of generating an executable plan. The references you found (I would not have bothered, so good on you for that!) back that up.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 11

I'm not saying you're spamming. I'm saying that bots that collect e-mails will start sending YOU spam.

I don't have any option except to accept this spam.

Have you started a thread specifically asking for help on this topic. I'm sure there are plenty of folks here that would help you out with specific contacts.

I agree that plenty of folks here would be able to help here and this is what I am hoping for. But I would like to be practical here. If I start a thread it would be active for few days ONLY.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 12

How long do you plan to waste before finding what you want?

I've never been jobless more than a few days. That's what contacts are for... => linkedIN.

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 13

Thanks Gail but the part that I didn't find was parser. I corrected my understanding on compilation immediately when I found compilation articles.

Thanks again!

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 14

How long do you plan to waste before finding what you want?

First, I will never say it waste... I would rather say invest. Waste hurts.:crying:

I've never been jobless more than a few days. That's what contacts are for... => linkedIN.

I am not jobless... I am just looking for better... For some reason, I don't like LinkedIn (or any networking site for that matter). I am connected to my friends / colleagues only through mails / calls. (Yeah, I know, I am little old-fashioned :-P)

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 15

Dev @ +91 973 913 6683 (11/8/2011)
How long do you plan to waste before finding what you want?
First, I will never say it waste... I would rather say invest. Waste hurts.:crying:
I've never been jobless more than a few days. That's what contacts are for... => linkedIN.
I am not jobless... I am just looking for better... For some reason, I don't like LinkedIn (or any networking site for that matter). I am connected to my friends / colleagues only through mails / calls. (Yeah, I know, I am little old-fashioned :-P)

Waste hurts because that's what you're doing and against what you want.

I don't waste time not working because in a single click I can contact EVERY recruiter / job HR I ever spoke to.

Same goes with Dice or other big hiring sites. 1 on 1 is a waste of time. Put yourself in front of people who need you want you'll have to turn offers down instead of hoping for one.

Be old fashioned all you want. I still preffer my way.