is this query optimize? [closed]

Question

UPDATE mkf
SET    mkf.Score = mkf.score - (SELECT sum(pp.ScoreInScale)
                                FROM   Progresses pp
                                WHERE  pp.MemberKnowledgeFieldId = mkf.Id
                                       AND pp.KnowledgeId IN (SELECT k.KnowledgeId
                                                              FROM   knowledges k
                                                              WHERE  k.Code IN ( 8396, 8395, 
                                                                                 7999, 7723,
                                                                                 7751, 7753, 
                                                                                 8069, 362,
                                                                                 7733, 7993, 
                                                                                 8379, 7736 )))
FROM   NewKms_MECO..MemberKnowledgeFields mkf
WHERE  mkf.Id IN (SELECT p.MemberKnowledgeFieldId
                  FROM   Progresses p
                  WHERE  p.KnowledgeId IN (SELECT k.KnowledgeId
                                           FROM   knowledges k
                                           WHERE  k.Code IN ( 8396, 8395,
                                                              7999, 7723,
                                                              7751, 7753,
                                                              8069, 362,
                                                              7733, 7993, 
                                                              8379, 7736 ))) 

Answer 1

Having much the same query repeated in both the WHERE and the SET doesn't look very optimal.

I'd be minded to write it as

WITH ScoreDeltas
     AS (SELECT SUM(pp.ScoreInScale) AS ScoreInScaleSum,
                pp.MemberKnowledgeFieldId
         FROM   Progresses pp
         WHERE  pp.KnowledgeId IN (SELECT k.KnowledgeId
                                   FROM   knowledges k
                                   WHERE  k.Code IN ( 8396, 8395, 7999, 7723,
                                                      7751, 7753, 8069, 362,
                                                      7733, 7993, 8379, 7736 ))
         GROUP  BY pp.MemberKnowledgeFieldId)
UPDATE mkf
SET    mkf.Score -= SD.ScoreInScaleSum
FROM   NewKms_MECO..MemberKnowledgeFields mkf
       JOIN ScoreDeltas SD
         ON mkf.Id = SD.MemberKnowledgeFieldId 

Whether the performance of this rewritten query could be reasonably described as "optimal" or not will depend on the indexes you have available and execution plans however.