I have logged the slowest queries on my database, and one surprises me, showing many times in the list, and taking often many seconds to execute.
UPDATE books SET last_read='2018-12-31 11:23:45' WHERE book_id='15'
book_id is int identity PK (clustered), last_read is a datetime. The query is written with the 15 in single quotes, thus requiring a conversion, but I cannot imagine this being a big deal, because the conversion would only be done once per query. There are 6 indexes on the table, but the column last_read is not involved or included in any of them. The PK is on book_id and is nothing special. The table is very central in database, contains 50 columns and 400,000 rows.
The estimated execution plan tells me:
UPDATE: 0% Clustered Index Update (on the PK constraint): 100%
The table has a trigger FOR DELETE, which I consider irrelevant here, and a trigger AFTER UPDATE, which reads:
CREATE TRIGGER books_last_update ON books AFTER UPDATE AS UPDATE books SET last_update = GETDATE() WHERE book_id IN (SELECT book_id FROM Inserted) GO
This shows me that the initial query actually performs two queries, one after the other. However, both of the updated columns have no index defined. I would like to make the initial query perform faster.
Do you see any optimization possible?
I am afraid that, since the trigger is on UPDATE, and it actually updates the column, it may trigger multiple times. Is it possible? If yes, how do I prevent it?