Optimising an Update statemement with a very large Where clause I have a table (TableOne) with a field called [ReviewNote], this table has more than 1.5 million record, I have to assign keywords to each record based on the content of the [ReviewNote], using another table [KeywordsTable] which contains of more than 17000 record of [SearchTerm] and relevant [Keyword].

So the objective is searching for the [SearchTerm] in [ReviewNote] and update the record and assign the appropriate [Keyword].

The problem is, it takes toooooo long to do it in conventional ways, so finally I came up with following script ( I am not sticking to this, just for you to know what I am talking about), it has been generated using the [SearchTerm] from The [KeywordsTable] (like clause).
I used where Clause, joining, temp tables.... before, all too damn slow for this... and field are indexed as as well.

So my question is, is there a way to do what I want to do in a much faster way? optimization? anything ?

Thanks, Zac

UPDATE [DatabeseOne].[dbo].[TableOne]
SET    [KeyWord] = (
        CASE 
            WHEN [ReviewNote] COLLATE Latin1_General_CS_AS LIKE('%- X-Ray -%')
                 THEN 'X Ray'
            WHEN [ReviewNote] COLLATE Latin1_General_CS_AS LIKE('%- bone densitometry -%')
                 THEN 'BMD'
            WHEN [ReviewNote] COLLATE Latin1_General_CS_AS LIKE('%- Cons Dr Reitsma -%')
                 THEN 'Consultation'
            WHEN [ReviewNote] COLLATE Latin1_General_CS_AS LIKE('%- consult -%')
                 THEN 'Consultation'

            -- 17000 more WHEN clause
            WHEN [ReviewNote] COLLATE Latin1_General_CS_AS LIKE('%- Zostavix -%')
                 THEN 'Immunization'
            WHEN [ReviewNote] COLLATE Latin1_General_CS_AS LIKE('%- Zoster -%')
                 THEN 'Immunization'
            WHEN [ReviewNote] COLLATE Latin1_General_CS_AS LIKE('%- Zoster @ Safeway Pharmacy -%')
                 THEN 'Immunization'
            WHEN [ReviewNote] COLLATE Latin1_General_CS_AS LIKE('%- Zoster Immunization -%')
                 THEN 'Immunization'
        END
    )
GO
Instead of one massive update statement, use multiple update statements one for each where clause.

UPDATE [DatabeseOne].[dbo].[TableOne]
SET    [KeyWord] = 'X Ray'
WHERE  [ReviewNote] COLLATE Latin1_General_CS_AS LIKE('%- X-Ray -%')

UPDATE [DatabeseOne].[dbo].[TableOne]
SET    [KeyWord] = 'BMD'
WHERE  [ReviewNote] COLLATE Latin1_General_CS_AS LIKE('%- bone densitometry -%')