如何提高在下文提到的SQL查询

问题描述：

文本列的性能有一个SQL工会与工会3所有查询的所有查询。在查询中添加了文本列CAST(c.getQuestionId AS VARCHAR(300))后，查询执行时间发生了显着变化。正在使用的数据库是SQL SERVER 2014.性能调优专家，请帮助。如何提高在下文提到的SQL查询

上有JcccustomersAssessmentProxy创造3项指标。这是一张拥有8000万条记录的表格。显式索引的使用只在第三查询中只提到了（即（NOLOCK, INDEX=IX_AssessmentProxy_myJcAssessmentContext））

有上myJcAssessmentContext

一个非聚集索引

有上getmycoordtoyplanver一个非聚集索引

有一种getMyEventItem上的非聚簇索引

SELECT  ass.P_KEY 
      ,ass.SS_CODE 
      ,CAST(evp.EVENT_SSID AS VARCHAR(11)) AS EVENT_SSID 
      ,CAST(QUESTIONNUMBER AS VARCHAR(3))        AS EVENT_NO 
      ,CAST(ISNULL(CAST(TEMPLATEVERSION AS VARCHAR), 0) AS INT)  AS TEMPLATEVERSION 
      ,ISNULL(CAST(TEMPLATENAME AS VARCHAR(50)), ' ')     AS TEMPLATENAME 
      ,evp.ASS_DATE 
      ,ISNULL(ANSWERNUMBER, 0)          AS ANSWER_NO 
      ,ass.CASE_SSID 
      ,ass.RE_SSID 
      ,ass.RE_DATE 
      ,ass.EPISODE_SSID 
      ,ass.[SERVICE] 
      ,ass.SERVICE_DESC 
      ,ass.TAM_KEY 
      ,ass.PRv_KEY 
      ,CAST(QUESTIONNUMBER AS VARCHAR(3))     AS QUESTION_NO 
      ,ISNULL(CONVERT(VARCHAR(100), REPLACE(REPLACE(CAST(QUESTIONTEXT AS VARCHAR(650)), char(10), ''), char(13), ' ')), ' ') AS QUESTION_TEXT 
      ,ISNULL(CAST(ANSWER AS VARCHAR(125)), ' ')   AS ANSWER_TEXT 
      ,a.MyAssessment          AS ASSESSMENT_SSID 
----------------------------------------------------------------------------------------- 

      ,CAST(RIGHT(a.getMyCoordtoyPlanVer, 10) AS INT)  AS toy_PLAN_VERSION_SSID  
      ,CAST(RIGHT(a.myJcAssessmentContext, 10) AS INT)  AS CONTEXT_FORM_SSID 
-----------------------------------------------------------------------------------------   
      ,ass.RECORD_DATE 
           **,CAST(a.getQuestionId AS VARCHAR(300))     AS QUESTION_ID /*NEW Text Column ,this remove the parallelism*/** 
FROM  SourceFeed.dbo.JcccustomersAssessmentProxy AS a WITH(NOLOCK) 
      INNER JOIN 
      TEMP_DATABASE.dbo.jsystemReport_AllEventItems_AllPersons evp WITH(NOLOCK) 
      ON a.getMyEventItem = evp.oid 


      INNER JOIN 
      TEMP_DATABASE.dbo.jsystemAssessment      ass WITH(NOLOCK) 
      ON evp.getcustomersId = ass.customersID 
      AND evp.EVENT_SSID = ass.EVENTID 


UNION ALL 

SELECT  ass.P_KEY 
      ,ass.SS_CODE 
      ,CAST(csp.ENT_SSID AS VARCHAR(11)) AS EVENT_SSID 
      ,CAST(jcc.QUESTIONNUMBER AS VARCHAR(3)) AS EVENT_NO 
      ,CAST(ISNULL(CAST(CAST(jcc.TEMPLATEVERSION AS VARCHAR) AS VARCHAR), 0) AS INT) AS TEMPLATEVERSION 
      ,ISNULL(CAST(jcc.TEMPLATENAME AS VARCHAR(50)), ' ') AS TEMPLATENAME 
      ,csp.ASSESSMENT_DATE 
      ,ISNULL(jcc.ANSWERNUMBER, 0) AS ANSWER_NO 
      ,ass.CASE_SSID 
      ,ass.REF_SSID 
      ,ass.RE_DATE 
      ,ass.EPISODE_SSID 
      ,ass.[SERVICE] 
      ,ass.SERVICE_DESC 
      ,ass.TEAM_KEY 
      ,ass.PROVIDER_KEY 
      ,CAST(ISNULL(jcc.QUESTIONNUMBER, 0) AS VARCHAR(3)) AS QUESTION_NO 
      ,isnull(CONVERT(VARCHAR(100), replace(replace(CAST(jcc.QUESTIONTEXT AS VARCHAR(650)), char(10), ''), char(13), ' ')), ' ') AS QUESTION_TEXT 
      ,ISNULL(CAST(jcc.ANSWER AS VARCHAR(125)), ' ') AS ANSWER_TEXT 
      ,jcc.MyAssessment AS ASSESSMENT_SSID 
----------------------------------------------------------------------------------------- 

      ,CAST(RIGHT(jcc.getMyCoordtoyPlanVer, 10) AS INT)  AS toy_PLAN_VERSION_SSID  
      ,CAST(RIGHT(jcc.myJcAssessmentContext, 10) AS INT)  AS CONTEXT_FORM_SSID 
-----------------------------------------------------------------------------------------   
      ,ass.RECORD_DATE 
           **,CAST(jcc.getQuestionId AS VARCHAR(300))     AS QUESTION_ID /*NEW Text Column ,this remove the parallelism*/** 
FROM  TEMP_DATABASE.dbo.jsystemReport_toySpell    csp WITH(NOLOCK) 
      INNER JOIN 
      TEMP_DATABASE.dbo.jsystemAssessment      ass WITH(NOLOCK) 
      ON csp.getcustomersId = ass.customersID 
      AND csp.EVENT_SSID = ass.EVENTID 
      LEFT OUTER JOIN 
      SourceFeed.dbo.JcccustomersAssessmentProxy jcc WITH(NOLOCK) 
      ON csp.OID = jcc.getmycoordtoyplanver 

WHERE  NOT EXISTS (SELECT tp1.OID 
         FROM TEMP_DATABASE.dbo.AssessmentTransferPart1 tp1 
         WHERE tp1.OID = jcc.OID) 

UNION ALL 

SELECT  ass.P_KEY 
      ,ass.SS_CODE 
      ,CAST(a.EVENT_SSID AS VARCHAR(11)) AS EVENT_SSID 
      ,CAST(CAST(C.QUESTIONNUMBER AS INT) AS CHAR(3)) AS EVENT_NO 
      ,CAST(ISNULL(CAST(C.TEMPLATEVERSION AS VARCHAR), 0) AS INT) AS TEMPLATEVERSION 
      ,ISNULL(CAST(C.TEMPLATENAME AS VARCHAR(50)), ' ') AS TEMPLATENAME 
      ,a.ASSESSMENT_DATE 
      ,ISNULL(C.ANSWERNUMBER, 0) AS ANSWER_NO 
      ,ass.CASE_SSID 
      ,ass.REL_SSID 
      ,ass.REAL_DATE 
      ,ass.EPISODE_SSID 
      ,ass.[SERVICE] 
      ,ass.SERVICE_DESC 
      ,ass.TEAM_KEY 
      ,ass.PROVIDER_KEY 
      ,CAST(ISNULL(CAST(C.QUESTIONNUMBER AS INT), 0) AS VARCHAR(3)) AS QUESTION_NO 
      ,isnull(CONVERT(VARCHAR(100), CAST(C.QUESTIONTEXT AS VARCHAR(650))), ' ') AS QUESTION_TEXT 
      ,ISNULL(CAST(C.ANSWER AS VARCHAR(125)), ' ') AS ANSWER_TEXT 
      ,a.MyAssessment AS ASSESSMENT_SSID 
----------------------------------------------------------------------------------------- 

      ,CAST(RIGHT(C.getMyCoordtoyPlanVer, 10) AS INT)  AS toy_PLAN_VERSION_SSID  
      ,CAST(RIGHT(C.myJcAssessmentContext, 10) AS INT)  AS CONTEXT_FORM_SSID 
-----------------------------------------------------------------------------------------   
      ,ass.RECORD_DATE 
           **,CAST(c.getQuestionId AS VARCHAR(300))**     AS QUESTION_ID /*NEW Text Column ,this remove the parallelism*/ 
FROM  SourceFeed.dbo.toyClusterReviewAssessment  B WITH(NOLOCK) 
      INNER JOIN 
      SourceFeed.dbo.JcccustomersAssessmentProxy  C WITH(NOLOCK, INDEX=IX_AssessmentProxy_myJcAssessmentContext) 
      ON B.myJCJccUserFormContext = C.myJcAssessmentContext 
      INNER JOIN 
      SourceFeed.dbo.JcccustomersAssessmentScoreProxy D WITH(NOLOCK) 
      ON B.myJCJccUserFormContext = D.myJcAssessmentContext 


      INNER JOIN 
      TEMP_DATABASE.dbo.jsystemReport_toyClusterReviewEvent_AllPersons A WITH(NOLOCK) 
      ON a.myAssessment = B.oid 
      INNER JOIN 
      TEMP_DATABASE.dbo.jsystemAssessment    ass WITH(NOLOCK) 
      ON a.getcustomersId = ass.customersID 
      AND a.EVENT_SSID = ass.EVENTID 
WHERE  NOT EXISTS (SELECT OID 
         FROM (SELECT  tp1.OID 
            FROM TEMP_DATABASE.dbo.AssessmentTransferPart1 tp1 
            UNION ALL 
            SELECT tp2.OID 
            FROM TEMP_DATABASE.dbo.AssessmentTransferPart2 tp2) jcc 
         WHERE jcc.OID = C.OID)

这是link to the actual execution plan。

上JccClientAssessmentProxy jcc添加索引WITH（NOLOCK，INDEX = IX_AssessmentProxy_GETMYCOORDCAREPLANVER）后这里是the new execution plan。

您添加了哪个文本列？你刚刚添加了一列到SELECT子句，它减慢了你的查询？你必须更具体，像这样，这是你的问题不容易理解。可能添加SQL小提琴与表结构等 –

getQuestionId是列 – user1254579

观察标记为粗体等在格式化的代码块中不起作用。 –

答

有问题的部分是：

,CAST(a.getQuestionId AS VARCHAR(300)) AS QUESTION_ID 
    FROM cnlPjccR_Report.dbo.JccClientAssessmentProxy AS a 
     WITH(NOLOCK,INDEX=IX_AssessmentProxy_getMyEventItem) 
    INNER JOIN ...

和问题重演在你的两个UNION ALL的子查询。

您的执行计划显示价格昂贵Key lookups。

第二种方法是看您可以创建一个“覆盖索引”是满足整个查询或者至少避免了键查找。一个 “覆盖索引”很简单，就是拥有所有必要要么满足整个查询或在我们的例子中列的非聚集索引，消除对键查找操作的需要。一个挑战是要获得列表，这些列是生成密钥查找的列。您可以通过右击关键查找运营商做到这一点在SQL Server Management Studio中（SSMS），然后选择属性。然后在属性窗口中找到输出列表行，然后单击省略号按钮。这将打开一个窗口（见下面），其中列出了密钥查找所要查找的所有列。您可以使用此列表来帮助您确定是否以及如何创建索引以“覆盖”查询或查找关键字。

在你的情况下列768,16列入创建一个覆盖索引：

[cnlPjccR_Report].[dbo].[JccClientAssessmentProxy].answer; 
[cnlPjccR_Report].[dbo].[JccClientAssessmentProxy].answerNumber; 
[cnlPjccR_Report].[dbo].[JccClientAssessmentProxy].getMyCoordCarePlanVer; 
[cnlPjccR_Report].[dbo].[JccClientAssessmentProxy].getQuestionId; 
[cnlPjccR_Report].[dbo].[JccClientAssessmentProxy].myAssessment; 
[cnlPjccR_Report].[dbo].[JccClientAssessmentProxy].myJcAssessmentContext; 
[cnlPjccR_Report].[dbo].[JccClientAssessmentProxy].questionNumber; 
[cnlPjccR_Report].[dbo].[JccClientAssessmentProxy].questionText; 
[cnlPjccR_Report].[dbo].[JccClientAssessmentProxy].templateName; 
[cnlPjccR_Report].[dbo].[JccClientAssessmentProxy].templateVersion

可能是你没有索引包括getQuestionId列，这一事实已在你的查询执行计划创建慢Key lookup或RID lookup。

见also here或this answer。

如何提高在下文提到的SQL查询

相关推荐