slow-running sql query

Question

I have a sql like this, but it will run 17 seconds in the sqlserver.
100 users logined my web to execute this sql at the same time just a moment ago (80% cpu usage), and it's ok.But it will over 1000 users tomorrow. so, my question is :
1: how to optimized this sql ?
2: 100 users 80% cpu usage, what will be happened if 1000users?

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRANSACTION;
WITH tempQuestion AS
(
  SELECT 
    DISTINCT(CASE
      WHEN qut.IsComplex=1 THEN qf.Id
      WHEN qut.IsComplex=0 THEN qs.Id END) AS QuestionId,
    (CASE
      WHEN qut.IsComplex=1 THEN qf.Content
      WHEN qut.IsComplex=0 THEN qs.Content END) AS QuestionContent,
    (CASE
      WHEN qut.IsComplex=1 THEN qf.Content
      WHEN qut.IsComplex=0 THEN qs.Content END) AS QuetionContentOri,
    qut.IsComplex AS IsComplex, 
    qut.Id AS QuestionTemplateId,
    qut.Name AS QuestionTemplateName,
    qp.KnowDotCode AS KnowDotCode,
    (CASE
      WHEN qs.QuestionFaceCode IS NULL THEN qp.Analysis
      WHEN qs.QuestionFaceCode IS NOT NULL THEN '' END) AS Analyze,
    (CASE
      WHEN qs.QuestionFaceCode IS NULL THEN qp.SuitNumber
      WHEN qs.QuestionFaceCode IS NOT NULL THEN '' END) AS SuitNumber,
    (CASE
      WHEN qs.QuestionFaceCode IS NULL THEN qp.Difficult
      WHEN qs.QuestionFaceCode IS NOT NULL THEN '' END) AS Difficult,
    (CASE
      WHEN qs.QuestionFaceCode IS NULL THEN qp.Levels
      WHEN qs.QuestionFaceCode IS NOT NULL THEN '' END) AS Levels,
    (CASE
      WHEN qs.QuestionFaceCode IS NULL THEN qp.Exposure
      WHEN qs.QuestionFaceCode IS NOT NULL THEN '' END) AS Exposure,
--qp.Analysis AS Analyze,--浼氬鑷村鍚堥閲嶅
    qs.Tag,
    qc.Name AS QuestionCategoryName,
    qu.Name AS UnitName,
    qu.Name AS QuestionUnitName,
    qu.Id AS UnitId,
    qp.Fettle,
    (CASE
      WHEN qut.IsComplex=1 THEN ''
      WHEN qut.IsComplex=0 THEN qp.CreatedDateTime END) AS CreatedDateTime,
    (CASE
      WHEN qut.IsComplex=1 THEN ''
      WHEN qut.IsComplex=0 THEN qp.LastUseDateTime END) AS LastUseDateTime,
    (CASE
      WHEN qut.IsComplex=1 THEN ''
      WHEN qut.IsComplex=0 THEN u.Name END) AS CreateName,
    pqs.Id,
    pqs.OrderInPaper,
    pqs.Score as Point,
    pqs.PaperStrategyId,
    pqs.QuestionCategoryId,
    pqs.QuestionUnitId,
    pqs.KnowDotIds,
    (CASE
      WHEN qs.QuestionFaceCode IS NULL THEN   qs.Answer
      WHEN qs.QuestionFaceCode IS NOT NULL THEN('') END) AS CorrectAnswer 
  FROM EL_QuestionBank.QS_QuestionStem AS qs
    LEFT JOIN EL_QuestionBank.QS_QuestionFace AS qf
      ON qs.QuestionFaceCode=qf.Id
    INNER JOIN EL_QuestionBank.QS_QuestionProperty AS qp
      ON qp.QuestionStemCode=qs.Id
    LEFT JOIN EL_Organization.[User] AS u 
      ON qp.CreatorCode=u.Id
    INNER JOIN EL_QuestionBank.QuestionCategory AS qc
      ON qc.Id = qp.SubjectCode
    INNER JOIN EL_QuestionBank.QS_QuestionUnit AS qu
      ON qu.Id = qp.QuestionUnitCode
    INNER JOIN EL_QuestionBank.QS_QuestionUnitTemplate AS qut 
      ON qut.Id = qu.QuestionUnitTemplateCode
    INNER JOIN EL_Paper.PaperQuestionStrategy AS pqs
      ON ((qut.IsComplex=0 AND pqs.QuestionId=qs.Id) OR (qut.IsComplex=1 AND pqs.QuestionId=qf.Id))
   WHERE pqs.PaperStrategyId='576222efa335483680e4e2e6e1c3d254' 
)
SELECT * FROM tempQuestion
COMMIT TRANSACTION;

Answer 1

You could try replacing

 INNER JOIN EL_Paper.PaperQuestionStrategy AS pqs 
    ON ((qut.IsComplex=0 AND  pqs.QuestionId=qs.Id) OR ( qut.IsComplex=1 AND pqs.QuestionId=qf.Id))

with

CROSS APPLY (SELECT * FROM EL_Paper.PaperQuestionStrategy pqs1
             WHERE (qut.IsComplex=0 AND  pqs1.QuestionId=qs.Id)
             UNION ALL
             SELECT * FROM EL_Paper.PaperQuestionStrategy pqs2
             (qut.IsComplex=1 AND pqs2.QuestionId=qf.Id)
             ) AS pqs

This isn't guaranteed to speed it up, but I have run into cases in the past where I've seen performance of non-trivial queries improved by replacing OR conditions with use of UNION ALL. If this could have fit as a comment, I would have preferred to suggest it that way, since it's not a definitive answer. If you try it, add a comment telling whether it helped, hurt, or made no difference. Also, this is off the top of my head, so be sure to test that results are identical as well.

For folks applying this technique to similar situations, note that you may need to use UNION rather than UNION ALL to give the exact same results as the code you're replacing. In this case, the two selects being unioned are mutually exclusive since one can only contain rows with IsComplex=0, and the other can only contain rows with IsComplex=1, so UNION ALL works well. UNION ALL will typically be faster than UNION, but you can't choose based on the criteria of performance alone. You have to pick the the one that will produce the results you expect.