Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
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 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 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. –  Wind Raven Feb 24 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 at 22:09
    
What is the length of the ReviewNote column? (the n in VARCHAR(n)) –  ypercube Feb 24 at 22:11

1 Answer 1

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 -%')
share|improve this answer
    
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 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 at 22:16

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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