如何在下面提到的SQL查询中提高文本列性能

Question

有一个SQL联合所有查询，其中3个联合所有查询。在查询中添加文本列CAST(c.getQuestionId AS VARCHAR(300))后，查询执行时间发生了显著变化。正在使用的数据库是 SQL SERVER 2014。性能调优专家，请帮忙。

在JcccustomersAssessmentProxy上创建了 3 个索引。这是一个包含 8000 万条记录的表。索引的显式使用仅在第 3 个查询中提及 (即(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)

这是实际执行计划的链接。

---

在JccClientAssessmentProxy jccWITH(NOLOCK，INDEX=IX_AssessmentProxy_GETMYCOORDCAREPLANVER( 上添加索引后 这是新的执行计划。

Answer 1

有问题的部分是：

,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 子查询中重复出现。

您的执行计划显示昂贵的密钥查找。

第二种方法是查看是否可以创建一个"覆盖索引" 满足整个查询或至少消除键查找。一个 "覆盖索引"只是一个非聚集索引，它具有所有 需要满足整个查询的列，或者在我们的例子中， 无需密钥查找操作。一个挑战是获得 生成键查找的列的列表。你可以这样做 在 SQL Server Management Studio (SSMS( 中，通过右键单击该项 查找运算符，然后选择"属性"。然后找到输出 在"属性"窗口中列出行，然后单击省略号按钮。 这将打开一个窗口(见下文(，其中包含所有列的列表 密钥查找正在寻找的内容。您可以使用此列表来帮助您 决定是否以及如何创建索引以"覆盖"查询或键 查找。

在您的情况下，应包含以包含以创建覆盖索引：

[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。

另请参阅此处或此答案。