0

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?

  • >>>The query is written with the 15 in single quotes, thus requiring a conversion, but I cannot imagine this being a big deal<<< Yes it's a big deal, your conversion leads to index scan instead of index seek, as your book_id is int and has an index (PK) defined on it – sepupic Jan 23 '18 at 8:56
  • @sepupic are you sure that it leads to an index scan? int has higher precedence than varchar, so the '15' should be implicitly converted to an int. – ypercubeᵀᴹ Jan 23 '18 at 9:00
  • @ypercubeᵀᴹ You are right, it will be index seek anyway – sepupic Jan 23 '18 at 9:15
  • Is it possible that the reason it is taking a long time to execute is because of locking contention? Have you analysed for that? – Mister Magoo Jan 23 '18 at 9:18
  • Of course there is a locking issue, otherwise (in my opinion) there would be no way this could take more than 5 seconds. But I would like to ignore this issue in this particular question, and concentrate on the trigger and other possible sources of optimization. – carlo.borreo Jan 23 '18 at 9:35
3

book_id is int

Then your code should say:

WHERE book_id = 15

And not:

WHERE book_id='15'

This shouldn't affect performance in this case, but it is a best practice to delimit (or not) correctly. Non-Unicode strings and dates with single quotes, Unicode strings prefixed with N, binary and numerics without any quotes.

I am afraid that, since the trigger is on UPDATE, and it actually updates the column, it may trigger multiple times. Is it possible?

No, SQL Server is smart enough to not throw itself off a cliff. It recognizes that the update inside the trigger shouldn't recursively call the trigger in an infinite loop. You can prove this by running a server-side trace or an extended events session and you will see that the update inside the trigger only runs once.

You can add code to prevent an update of that column from triggering a second update (for example if someone had manually updated the last_update column, but that doesn't seem relevant here).

Do you see any optimization possible?

Generally, if you find that triggers are slowing down your code (I haven't seen any proof of that in this case), look to get rid of the triggers. I'm not sure what application code is updating this table directly, but if you can use a stored procedure to process all updates to the table, then you can add the update to this column there, instead of having to rely on the trigger. But I suspect if you are waiting 5 seconds for this update to happen, the trigger is NOT the problem.

0

If the value does not change this eliminates a lock and a trigger

UPDATE books 
SET last_read    = '2018-12-31 11:23:45' 
WHERE last_read <> '2018-12-31 11:23:45' 
  AND book_id = 15 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.