How can I optimize the SQL query?

Question

I have a query an SQL query as follows, can anybody suggest any optimization for this; I think most of the effort is being done for the Union operation - is there anything else can be done to get the same result ? Basically I wanna query first portion of the UNION and if for each record there is no result then the second portion need to be run. Please help. :

SET dateformat dmy; 

WITH incidentcategory 
     AS (
     SELECT 1 ord, i.IncidentId, rl.Description Category FROM incident i
        JOIN  IncidentLikelihood l ON i.IncidentId = l.IncidentId
        JOIN IncidentSeverity s ON i.IncidentId = s.IncidentId
        JOIN LikelihoodSeverity ls ON l.LikelihoodId = ls.LikelihoodId AND s.SeverityId = ls.SeverityId
        JOIN RiskLevel rl ON ls.RiskLevelId = rl.riskLevelId

     UNION 

     SELECT 2 ord, i.incidentid, 
                rl.description Category 
         FROM   incident i 
                JOIN incidentreportlikelihood l 
                  ON i.incidentid = l.incidentid 
                JOIN incidentreportseverity s 
                  ON i.incidentid = s.incidentid 
                JOIN likelihoodseverity ls 
                  ON l.likelihoodid = ls.likelihoodid 
                     AND s.severityid = ls.severityid 
                JOIN risklevel rl 
                  ON ls.risklevelid = rl.risklevelid

                  ) ,
ic AS (
        SELECT ROW_NUMBER() OVER (PARTITION BY i.IncidentId ORDER BY (CASE WHEN incidentTime IS NULL THEN GETDATE() ELSE incidentTime END) DESC,ord ASC) rn,
                        i.incidentid, 
                        dbo.Incidentdescription(i.incidentid, '', 
                        '', 
                        '', '') 
                               IncidentDescription, 
                        dbo.Dateconverttimezonecompanyid(closedtime, 
                        i.companyid) 
                               ClosedTime, 
                        incidenttime, 
                        incidentno, 
                        Isnull(c.category, '') 
                               Category, 
                        opencorrectiveactions, 
                        reportcompleted, 
                        Isnull(classificationcompleted, 0) 
                               ClassificationCompleted, 
                        Cast (( CASE 
                                  WHEN closedtime IS NULL THEN 0 
                                  ELSE 1 
                                END ) AS BIT) 
                               IncidentClosed, 
                        Cast (( CASE 
                                  WHEN investigatorfinishedtime IS NULL THEN 0 
                                  ELSE 1 
                                END ) AS BIT) 
                               InvestigationFinished, 
                        Cast (( CASE 
                                  WHEN investigationcompletetime IS NULL THEN 0 
                                  ELSE 1 
                                END ) AS BIT) 
                               InvestigationComplete, 
                        Cast (( CASE 
                                  WHEN investigatorassignedtime IS NULL THEN 0 
                                  ELSE 1 
                                END ) AS BIT) 
                               InvestigatorAssigned, 
                        Cast (( CASE 
                                  WHEN (SELECT Count(*) 
                                        FROM   incidentinvestigator 
                                        WHERE  incidentid = i.incidentid 
                                               AND personid = 1588 
                                               AND tablename = 'AdminLevels') = 0 
                                THEN 0 
                                  ELSE 1 
                                END ) AS BIT) 
                               IncidentInvestigator, 
                        (SELECT dbo.Strconcat(osname) 
                         FROM   (SELECT TOP 10 osname 
                                 FROM   incidentlocation l 
                                        JOIN organisationstructure o 
                                          ON l.locationid = o.osid 
                                 WHERE  incidentid = i.incidentid 
                                 ORDER  BY l.locorder) loc) 
                               Location, 
                        Isnull((SELECT TOP 1 teamleader 
                                FROM   incidentinvestigator 
                                WHERE  personid = 1588 
                                       AND tablename = 'AdminLevels' 
                                       AND incidentid = i.incidentid), 0) 
                               TeamLeader, 
                        incidentstatus, 
                        incidentstatussearch 
        FROM   incident i 
               LEFT OUTER JOIN incidentcategory c 
                            ON i.incidentid = c.incidentid 
        WHERE  i.isdeleted = 0 
               AND i.companyid = 158 
               AND incidentno <> 0 
               --AND reportcompleted = 1 
               --AND investigatorassignedtime IS NOT NULL 
               --AND investigatorfinishedtime IS NULL 
               --AND closedtime IS NULL
              ),
ic2 AS ( 
SELECT * FROM ic WHERE rn=1
)
SELECT *  FROM ic2

--WHERE rownumber >= 0 
--       AND rownumber < 0 + 10 
--WHERE ic2.incidentid in(53327,53538)
--WHERE ic2.incidentid = 53338

ORDER  BY incidentid DESC 

Following is the execution plan I got: https://www.dropbox.com/s/50dcpelr1ag4blp/Execution_Plan.sqlplan?dl=0

Answer 1

Try alternate approach by removing

   (SELECT dbo.Strconcat(osname) 
                         FROM   (SELECT TOP 10 osname 
                                 FROM   incidentlocation l 
                                        JOIN organisationstructure o 
                                          ON l.locationid = o.osid 
                                 WHERE  incidentid = i.incidentid 
                                 ORDER  BY l.locorder) loc) 
                               Location, 
                        Isnull((SELECT TOP 1 teamleader 
                                FROM   incidentinvestigator 
                                WHERE  personid = 1588 
                                       AND tablename = 'AdminLevels' 
                                       AND incidentid = i.incidentid), 0) 
                               TeamLeader

from the SELECT. Avoid using complex functions/sub-queries in select.

Answer 2

Since the first inner query produces rows with ord=1 and the second produces rows with ord=2, you should use UNION ALL instead of UNION. UNION will filter out equal rows and since you will never get equal rows it is more efficient to use UNION ALL.

Also, rewrite your query to not use the WITH construct. I've had very bad experiences with this. Just use regular derived tables instead. In the case the query is still abnormally slow, try to serialize some derived tables to a temporary table and query the temporary table instead.

Answer 3

There are several issues:

1) use UNION ALL instead of UNION ALL to avoid the additional operation to aggregate the data.

2) try to modify the numerous function calls (e.g. dbo.Incidentdescription() ) to be an in-lie table valued function so you can reference it using CROSS APPLY or OUTER APPLY. Especially, if those functions referencing a table again.

3) move the subqueries from the SELECT part of the query to the FROM part using CROSS APPLY or OUTER APPLY again.

4) after the above is done, check the execution plan again for any missing indexes. Also, run the query with STATISTICS TIME, IO on to verify that the number of times a table is referenced is correct (sometimes the execution plan put you in the wrong direction, especially if function calls are involved)...