Optimizing Multiple SQL Queries

Question

I have 2 queries that I want to optimize but I don't know how. The execution time is not acceptable for me.

I'm working with jira database and did query to show different tasks with some attributes. The code is:

SET NOCOUNT ON
DROP TABLE IF EXISTS #TT
DROP TABLE IF EXISTS #TT1
DROP TABLE IF EXISTS #TT2

SELECT DISTINCT concat(pr.[pkey], '-', ji.[issuenum]) AS 'Ключ'
        ,concat('https://sd.finam.ru/browse/', pr.[pkey], '-', ji.[issuenum]) AS 'Ссылка'
        ,p.[pname] AS 'П'
        ,users.[display_name] AS 'Исполнитель'
        ,CAST(ji.[CREATED] AS smalldatetime) AS 'Создан'
        ,CAST(ji.[RESOLUTIONDATE] AS smalldatetime) AS 'Дата резолюции'
        ,ji.[issuenum]
INTO #TT
  FROM
    [jiraissue] AS ji
    LEFT OUTER JOIN [changegroup] AS cg ON (cg.[issueid] = ji.[id])
    LEFT OUTER JOIN [changeitem] AS ci ON (ci.[groupid] = cg.[id]) AND ci.FIELDTYPE='jira' AND ci.FIELD='status'
    LEFT OUTER JOIN [app_user] AS au ON (au.[user_key] = cg.[AUTHOR])
    LEFT OUTER JOIN [issuetype] AS itype ON (itype.[ID] = ji.[issuetype])
    LEFT OUTER JOIN [priority] AS p ON (p.[ID] = ji.[PRIORITY])
    LEFT OUTER JOIN [project] AS pr ON (pr.[ID] = ji.[PROJECT])
    LEFT OUTER JOIN [cwd_user] as users ON (users.[user_name] = ji.[ASSIGNEE])
      
  WHERE cg.[CREATED] >= CONVERT(datetime, '2021-12-01') AND cg.[CREATED] <= CONVERT(datetime, '2022-01-01') and CONVERT(NVARCHAR(MAX), ci.[NEWSTRING]) = N'Done'
  AND itype.pname = 'Incident'

SELECT DISTINCT concat(pr.[pkey], '-', ji.[issuenum]) AS 'Ключ'
        ,system_t.[NAME] AS 'Контур ИС'
        ,system_t_t.[NAME] AS 'Критичность системы'
INTO #TT1
  FROM
    [jiraissue] AS ji
    LEFT OUTER JOIN [changegroup] AS cg ON (cg.[issueid] = ji.[id])
    LEFT OUTER JOIN [changeitem] AS ci ON (ci.[groupid] = cg.[id]) AND ci.FIELDTYPE='jira' AND ci.FIELD='status'
    LEFT OUTER JOIN [app_user] AS au ON (au.[user_key] = cg.[AUTHOR])
    LEFT OUTER JOIN [issuetype] AS itype ON (itype.[ID] = ji.[issuetype])
    LEFT OUTER JOIN [project] AS pr ON (pr.[ID] = ji.[PROJECT])
    LEFT OUTER JOIN [customfieldvalue] AS customfv ON (customfv.[ISSUE] = ji.[ID])
    LEFT OUTER JOIN [AO_8542F1_IFJ_OBJ] AS system_t ON (system_t.[ID] = substring(customfv.[STRINGVALUE], PatIndex('%[0-9]%', customfv.[STRINGVALUE]), len(customfv.[STRINGVALUE])))
    LEFT OUTER JOIN [AO_8542F1_IFJ_OBJ_ATTR] AS system_attr ON (system_t.[ID] = system_attr.[OBJECT_ID])
    LEFT OUTER JOIN [AO_8542F1_IFJ_OBJ_ATTR_VAL] AS system_attr_val ON (system_attr.[ID] = system_attr_val.[OBJECT_ATTRIBUTE_ID])
    LEFT OUTER JOIN [AO_8542F1_IFJ_OBJ] AS system_t_t ON (system_attr_val.[REFERENCED_OBJECT_ID] = system_t_t.[ID])
      
  WHERE cg.[CREATED] >= CONVERT(datetime, '2021-12-01') AND cg.[CREATED] <= CONVERT(datetime, '2022-01-01') and CONVERT(NVARCHAR(MAX), ci.[NEWSTRING]) = N'Done'
  AND (customfv.[CUSTOMFIELD] = 21003 OR customfv.[CUSTOMFIELD] = 21005)
  AND (system_t.[OBJECT_TYPE_ID] = 136 OR system_t.[OBJECT_TYPE_ID] = 303 OR system_t.[OBJECT_TYPE_ID] = 143)
  AND system_attr.[OBJECT_TYPE_ATTRIBUTE_ID] = 461
  AND itype.pname = 'Incident'

SELECT ji.[issuenum]
    ,pr_pr.[pname] AS '(project) Project'

INTO #TT2
  FROM
    [jiraissue] AS ji
    LEFT OUTER JOIN [customfieldvalue] AS customfv ON (customfv.[ISSUE] = ji.[ID])
    LEFT OUTER JOIN [project] AS pr_pr ON (pr_pr.[ID] = CAST(customfv.[NUMBERVALUE] AS BIGINT))
    LEFT OUTER JOIN [changegroup] AS cg ON (cg.[issueid] = ji.[id])
    LEFT OUTER JOIN [changeitem] AS ci ON (ci.[groupid] = cg.[id]) AND ci.FIELDTYPE='jira' AND ci.FIELD='status'
    LEFT OUTER JOIN [app_user] AS au ON (au.[user_key] = cg.[AUTHOR])
    LEFT OUTER JOIN [issuetype] AS itype ON (itype.[ID] = ji.[issuetype])
  WHERE cg.[CREATED] >= CONVERT(datetime, '2021-12-01') AND cg.[CREATED] <= CONVERT(datetime, '2022-01-01') and CONVERT(NVARCHAR(MAX), ci.[NEWSTRING]) = N'Done'
  AND pr_pr.[pname] is not NULL

  SELECT CTE.[Ключ], CTE.[Ссылка], CTE.[П], CTE.[Исполнитель], CTE.[Создан], CTE.[Дата резолюции], CTE2.[(project) Project], CTE1.[Контур ИС], CTE1.[Критичность системы], CTE.[issuenum]
  FROM #TT CTE
  LEFT OUTER JOIN #TT1 CTE1 ON (CTE1.[Ключ] = CTE.[Ключ])
  LEFT OUTER JOIN #TT2 CTE2 ON (CTE2.[issuenum] = CTE.[issuenum])

DROP TABLE IF EXISTS #TT
DROP TABLE IF EXISTS #TT1
DROP TABLE IF EXISTS #TT2

There will be some information for the month before last.

summary of query

The next query is:

WITH CTE AS (
SELECT concat(p.pkey,'-',i.issuenum) AS 'Ключ', 
       cf.cfname, 
       cv.textvalue,
       ROW_NUMBER() OVER (PARTITION BY i.issuenum ORDER BY i.issuenum) AS RowNumber_SLA 
FROM   customfield cf, 
       customfieldvalue cv, 
       jiraissue i, 
       project p 
WHERE  i.project = p.id 
       AND cv.issue = i.id 
       AND cv.customfield = cf.id 
       AND cf.customfieldtypekey = 'com.atlassian.servicedesk:sd-sla-field'
       and i.issuenum IN ()
       and CHARINDEX('"succeeded":false', TEXTVALUE) >0
)

SELECT CTE.Ключ
FROM CTE
WHERE RowNumber_SLA = 1

The goal is to get tasks where SLA has been failed. So in and i.issuenum IN () my Python script is putting issue numbers that I got through previous query. The average execution time is 3 minutes. So, I want optimize this too, maybe join first query. Sorry for my English. I'm not an English-speaking person.

P. S. Changing CONVERT(NVARCHAR(MAX), ci.[NEWSTRING]) = N'Done' to ci.[NEWSTRING] = 'Done' causing the error The data types ntext and varchar are incompatible in the equal to operator. Workaround - ci.[NEWSTRING] LIKE 'Done' fixing this.

Which database system are you using the used syntax suggests SQL server — Raymond Nijland
– Raymond Nijland, Commented Feb 9, 2022 at 10:17
It seems you are also unsure what LEFT OUTER JOIN is for. In your first batch, for example, you LEFT JOIN to [changegroup], [changeitem] and [issuetype], however, you state in the WHERE that columns from those tables but not be NULL, meaning that the JOINs to [changegroup], [changegroup] and [issuetype] are all implicit INNER JOINs instead. As such, why are you using a LEFT JOIN when you require a row to be returned? Also why are you converting ci.[NEWSTRING] to an nvarchar(MAX) in your WHERE? That will be terrrible for performance. — Thom A
– Thom A ♦, Commented Feb 9, 2022 at 10:51
My experience shows me that beginner developers sometimes systematically use the LEFT OUTER JOIN whatever the join is because they have not understood nor studied the principle of joins... This is not very serious in this case because SQL Sever rectifies false OUTER to INNER on the fly as soon as a WHERE clause specifies an outer table... But this is a stupid attitude that can lead to the return of false data! — SQLpro
– SQLpro, Commented Feb 9, 2022 at 11:19

SQLpro · Accepted Answer · 2022-02-09 11:21:48Z

1

The first query must be rewrite as :

SELECT DISTINCT 
       concat(pr.[pkey], '-', ji.[issuenum]) AS 'Ключ', 
       concat('https://sd.finam.ru/browse/', pr.[pkey], '-', ji.[issuenum]) AS 'Ссылка', 
       p.[pname] AS 'П', 
       users.[display_name] AS 'Исполнитель', 
       CAST(ji.[CREATED] AS SMALLDATETIME) AS 'Создан', 
       CAST(ji.[RESOLUTIONDATE] AS SMALLDATETIME) AS 'Дата резолюции', 
       ji.[issuenum]
INTO #TT
FROM [jiraissue] AS ji
     INNER JOIN [changegroup] AS cg ON(cg.[issueid] = ji.[id])
     INNER JOIN [changeitem] AS ci ON(ci.[groupid] = cg.[id])
                                          AND ci.FIELDTYPE = 'jira'
                                          AND ci.FIELD = 'status'
     LEFT OUTER JOIN [app_user] AS au ON(au.[user_key] = cg.[AUTHOR])
     INNER JOIN [issuetype] AS itype ON(itype.[ID] = ji.[issuetype])
     LEFT OUTER JOIN [priority] AS p ON(p.[ID] = ji.[PRIORITY])
     LEFT OUTER JOIN [project] AS pr ON(pr.[ID] = ji.[PROJECT])
     LEFT OUTER JOIN [cwd_user] AS users ON(users.[user_name] = ji.[ASSIGNEE])
WHERE cg.[CREATED] >= CONVERT(DATETIME, '2021-12-01')
      AND cg.[CREATED] <= CONVERT(DATETIME, '2022-01-01')
      AND ci.[NEWSTRING] = 'Done'
      AND itype.pname = 'Incident';

False outer joins are converted in INNER and I have aslo eliminate the ugly CONVERT to NVARCHAR(max)

Same troubles appears in the second query...

answered Feb 9, 2022 at 11:21

SQLpro

5,3121 gold badge9 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

luibrain Over a year ago

Thank you a lot! OUTER to INNER didn't give me so much but eliminating converting is a very good solution, but still ci.[NEWSTRING] = 'Done' not working, changed to ci.[NEWSTRING] LIKE 'Done' and its' ok. What about SLA part? How can I attach this to the first query?

Thom A Over a year ago

ci.[NEWSTRING] = 'Done' and ci.[NEWSTRING] LIKE 'Done' are synonyms, @luibrain, as the expression on the right hand side contains no wildcards. If = 'Done' "doesn't work" (what ever that means) then neither will LIKE 'Done'.

luibrain Over a year ago

@Larnu, so then it's the magic :D Got error The data types ntext and varchar are incompatible in the equal to operator. when ci.[NEWSTRING] = 'Done' but query works when ci.[NEWSTRING] LIKE 'Done'

Thom A Over a year ago

it's not "magic" @luibrain , the problem is you are using a data type (ntext) that has been deprecated for 17 years. You should be fixing your design to be using the MAX length data types.

SQLpro Over a year ago

@Luibrain you must ask yourself why you need ci.[NEWSTRING] LIKE 'Done' ? which is a bad equivalent to =... Perhaps some blank chars ? ...

|

Collectives™ on Stack Overflow

Optimizing Multiple SQL Queries

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related