March 12, 2014 at 12:01 pm
Using a trigger we want to send out a notification when the top 1 record changes. Instead of running a query to get the top 1, it would be nice to store the key to the current top 1 and update it whenever that table changes. This way when there is an update or insert to the table, we can compare it to what's in the key rather than do a top 1 on the table which can be several million records.
Any suggestions?
March 12, 2014 at 12:09 pm
If the "key" is a part of your clustered index then your top 1 select statement should pose very little overhead. If that's not an option, within your trigger you should be able to select the changed key from the DELETED table and make your comparison/send email that way?
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
March 12, 2014 at 12:16 pm
The question is:
What is your definition of top 1? What is your Ordering requirements for the top 1?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 12, 2014 at 12:26 pm
MyDoggieJessie (3/12/2014)
If the "key" is a part of your clustered index then your top 1 select statement should pose very little overhead. If that's not an option, within your trigger you should be able to select the changed key from the DELETED table and make your comparison/send email that way?
Not sure what you mean about the key being part of the clustered index. Any indexing has no relevance on what the top 1 row is.
_______________________________________________________________
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/
March 12, 2014 at 12:27 pm
Say this is number of items sold. When the person with the most items sold changes, I want to be able to notify the user. ItemsSold is not in the key. The key is userid, productcatagoryId.
If the lookup is quick enough, then I can do that in the trigger. If not, I need another approach like a global variable or something.
March 12, 2014 at 12:29 pm
Sorry, I meant that if the column he's looking to find is a part of/ or is the clustered index, then the structure of the data is already sorted, so performance-wise it wouldn't be a huge deal to do a select top 1 from it.
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
March 12, 2014 at 12:30 pm
Have you tested the query option? Does it execute in acceptable time?
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
March 12, 2014 at 12:35 pm
MyDoggieJessie (3/12/2014)
Sorry, I meant that if the column he's looking to find is a part of/ or is the clustered index, then the structure of the data is already sorted, so performance-wise it wouldn't be a huge deal to do a select top 1 from it.
Depends on what the ORDER BY is for the TOP 1. If it's the index key, it won't be a huge deal. If it's not, there will be a sort.
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
March 12, 2014 at 12:39 pm
Agreed. Thanks for the clarification!
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
March 12, 2014 at 3:18 pm
If I stick with the trigger, will this slow down the actual insert or update or has that already been completed and this is more like and after call?
The reason I ask is that I need to either call a web service or use a CLR trigger because there is some special notification work that has to get done.
March 13, 2014 at 12:42 am
jbonavita (3/12/2014)
If I stick with the trigger, will this slow down the actual insert or update
Yes. Whether it will be a noticable slow down is another matter. Test!
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
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply