CODEWITHSUNDEEP
sql-server
raja atif
raja atif

Reputation: 188

Long running UPDATE query

I want to update some values in a table but the query takes more than two hours and it continues to run. Is there anything obviously wrong with my query?

 UPDATE tblQuestionnaires
 SET    nAddBy = ( SELECT   tblanswers.nolduserid
                   FROM     tblAnswers
                   WHERE    tblanswers.strOldMobileModelQuestionairenumber 
                          = tblQuestionnaires.strQuestionnaireCode
                   GROUP BY tblanswers.nolduserid ,
                            tblanswers.strOldMobileModelQuestionairenumber
                 )
 WHERE  tblQuestionnaires.nId BETWEEN 610815 AND 614988

Upvotes: 0

Views: 97

Answers (1)

Chris Steele
Chris Steele

Reputation: 1381

Doing it that way means that the subquery in the SET clause is likely being executed for every questionnaire. As a general rule, I like to build the query as a select that outputs the id of the row you want to update, as well as the calculated value that you need. For your case, it'd be something like this:

select  q.nId,
        a.nolduserid
    from tblQuestionnaires q
    join tblAnswers a on
        a.strOldMobileModelQuestionairenumber = q.strQuestionnaireCode
    where
        q.nId between 610815 and 614988
    group by a.nolduserid, q.nId

Then change that query into an update:

update q
    set 
        nAddBy = a.nolduserid
    from tblQuestionnaires q
    join tblAnswers a on
        a.strOldMobileModelQuestionairenumber = q.strQuestionnaireCode
    where
        q.nId between 610815 and 614988
    group by a.nolduserid, q.nId

Please note that I haven't tested this (I don't have your data), but this should work a little better.

Upvotes: 4

Related Questions