Как улучшить производительность текстового столбца в приведенном ниже SQL-запросе
Существует SQL объединение всех запросов с 3 объединением всех запросов. Время выполнения запроса значительно изменилось после добавления текстового столбца CAST(c.getQuestionId AS VARCHAR(300)) в запросе. База данных SQL Server 2014. Эксперт по настройке производительности, пожалуйста, помогите.
3 индекса созданы JcccustomersAssessmentProxy, Это таблица с 80 миллионами записей. Явное использование индекса упоминается только в третьем запросе (т.е.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 jcc WITH(NOLOCK,INDEX=IX_AssessmentProxy_GETMYCOORDCAREPLANVER) - это новый план выполнения.
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 в вашем плане выполнения запроса.
Смотрите также здесь или этот ответ.