John Hanrahan (11/19/2013)
In an Insert Trigger this code is sometimes quite slow: "SELECT @numrows = @@rowcount"This code is used to determine if a row was inserted into the table. So 2 questions.
1. Isn't this redundant? How can you get to an insert trigger if a row isn't being inserted?
2. Sometimes the above line of code is has a very long duration (like 37450ms in a sql trace). Any clues to point me in a direction?
My initial thought is to just delete the code. I am working with a commercial package so I have to be a little careful.
Thanks for reading!
John
Without more details about the trigger it is hard to say for sure. Are there multiple statements in this triggers each of which references @numrows? If so then you might need it. I have a bad feeling that this does some sort of looping or something, at least my experience suggests that as a high probability. When you see the trigger worrying about @@rowcount it certainly raises a red flag that you should investigate that trigger.
Can you post the code for the trigger? We may want/need more details but that is the very least amount of information we would need to get started.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/