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] = (
            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'
  • 1
    This isn't a full on answer, but using CASE statements and open ended wild card statements can't leverage indexes. Read this post by Grant Fritchey: scarydba.com/2014/02/18/the-case-statement-and-performance – Mike Fal Feb 24 '14 at 20:29
  • Thanks for your comment, I definitely learned something, unfortunately it doesn't solve my problem as there is no way for me to get rid of '%', the ReviewNote does not follow a predictable pattern and there are too many of them, thanks again – ZacG Feb 24 '14 at 20:35
  • One thing to watch out for, The first case the big long nasty statement comes across will be the tag since Sql Server will break out of the case. If every ReviewNote can have only 1 tag then this works. Otherwise you will need to adjust your method. IE: if - X-Ray - matches and - Zoster - would match only X Ray would be set. – RubberChickenLeader Feb 24 '14 at 21:21
  • there is no (easy) way to be sure, 15million+ records! but our understanding is that each ReviewNote corresponds to one tag – ZacG Feb 24 '14 at 22:09
  • What is the length of the ReviewNote column? (the n in VARCHAR(n)) – ypercubeᵀᴹ Feb 24 '14 at 22:11

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 -%')
  • I tried that first, same problem, it would be 17000 Update statements against a table of more than 1.5 million records Thanks – ZacG Feb 24 '14 at 21:13
  • Post the full table definition including indexes as well as an explain plan for a single update statement. Also you should look at rebuilding the index on [ReviewNote] before running the updates. – Zack Feb 24 '14 at 22:16

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.