How to improve the text column performance in below mentioned SQL query

Question

There is an SQL union all query with 3 union all queries. The query execution time changed significantly after I had added a text column CAST(c.getQuestionId AS VARCHAR(300)) in the query. The database being used is SQL SERVER 2014. Performance tuning expert, please help.

There are 3 indexes created on JcccustomersAssessmentProxy. This is a table with 80 million records. Explicit use of index is only mentioned in the 3rd query only (ie (NOLOCK, INDEX=IX_AssessmentProxy_myJcAssessmentContext))

There is a non clustered index on myJcAssessmentContext

There is a non clustered index on getmycoordtoyplanver

There is a non clustered index on 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)

Here is the link to the actual execution plan.

After adding an index on JccClientAssessmentProxy jcc WITH(NOLOCK,INDEX=IX_AssessmentProxy_GETMYCOORDCAREPLANVER) here is the new execution plan.

Which text column you have added? You just added one column to the SELECT clause and it slowed your query? You must be more specific, like this it is your question not very comprehensible. Possibly add SQL Fiddle with table structures etc. — Vojtěch Dohnal, Aug 14 '17 at 11:26
Watch that markup for bold etc. does not work within a formatted code block. — Vojtěch Dohnal, Aug 14 '17 at 11:33
You should post an execution plan here, there is [a website that allows for posting complicated plans](https://www.brentozar.com/pastetheplan/) without a need to post an image. — Vojtěch Dohnal, Aug 14 '17 at 11:35
This is the plan for the original query without the mentioned column? — Vojtěch Dohnal, Aug 14 '17 at 13:34
You have several Table Scans and one RID Lookup on `JccClientAssesmentProxy`, that takes 30,8% of time. You should improve indexing on that table. There are index suggestions given by SSMS Execution plan, take a look at them a modify your indexes with care. — Vojtěch Dohnal, Aug 14 '17 at 13:48
Thans a lot for your valuable suggestion and the website.I am going to include all those index hint (eg:-WITH(NOLOCK, INDEX=IX_AssessmentProxy_myJcAssessmentContext) ) on all those JccClientAssesmentProxy. — user1254579, Aug 14 '17 at 13:52
SSMSexecution plan was/is not showing any suggestions.The website is veryuseful.One of the tables is showing TABLE SCAN with 0% cost.Does it matter ? Do we need to create an index and make it an index scan !!? — user1254579, Aug 14 '17 at 13:54
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/151889/discussion-between-vojtch-dohnal-and-user1254579). — Vojtěch Dohnal, Aug 14 '17 at 13:54

score 1 · Accepted Answer · answered Aug 15 '17 at 06:31

The problematic part is:

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

and the problem repeats itself in two of your UNION ALL subqueries.

Your execution plans shows expensive Key lookups.

The second method is to see if you can create a “covering index” that satisfies the entire query or at least eliminates the key lookups. A “covering index” is simply a non-clustered index that has all of the columns needed to either satisfy the entire query or in our case, eliminate the need for a key lookup operation. One challenge is to get a list of columns that are generating the key lookup. You can do this in SQL Server Management Studio (SSMS) by right-clicking on the key lookup operator, and then choosing Properties. Then find the Output List row in the Properties window, and click on the ellipsis button. This will open a window (see below) with a list of all of the columns that the key lookup is looking for. You can use this list to help you decide whether and how to create an index to “cover” the query or key lookup.

In your case following columns shoud be INCLUDEd to create a covering index:

[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

Probably your index did not include getQuestionId column and that fact has created slower Key lookup or RID lookup in your query execution plan.

How to improve the text column performance in below mentioned SQL query

1 Answers1