2

I have the following query that is taking a long time to execute (nearly 20 seconds).

SELECT Count(*)  FROM   "workorder" "wo" 
       LEFT JOIN "workorder_fields" "wof" 
              ON "wo"."workorderid" = "wof"."workorderid" 
       LEFT JOIN "servicecatalog_fields" "scf" 
              ON "wo"."workorderid" = "scf"."workorderid" 
       LEFT JOIN "wotoprojects" "wtp" 
              ON "wo"."workorderid" = "wtp"."workorderid" 
       LEFT JOIN "sitedefinition" 
              ON "wo"."siteid" = "sitedefinition"."siteid" 
       LEFT JOIN "sdorganization" 
              ON "sitedefinition"."siteid" = "sdorganization"."org_id" 
       INNER JOIN "workorderstates" "wos" 
               ON "wo"."workorderid" = "wos"."workorderid" 
       LEFT JOIN "categorydefinition" "cd" 
              ON "wos"."categoryid" = "cd"."categoryid" 
       LEFT JOIN "aaauser" "ti" 
              ON "wos"."ownerid" = "ti"."user_id" 
       LEFT JOIN "aaauser" "aau" 
              ON "wo"."requesterid" = "aau"."user_id" 
       LEFT JOIN "prioritydefinition" "pd" 
              ON "wos"."priorityid" = "pd"."priorityid" 
       LEFT JOIN "statusdefinition" "std" 
              ON "wos"."statusid" = "std"."statusid" 
       INNER JOIN "workorder_queue" "wo_queue" 
               ON "wo"."workorderid" = "wo_queue"."workorderid" 
       INNER JOIN "queuedefinition" "qd" 
               ON "wo_queue"."queueid" = "qd"."queueid" 
       LEFT JOIN "departmentdefinition" "dpt" 
              ON "wo"."deptid" = "dpt"."deptid" 
       LEFT JOIN "leveldefinition" "lvd" 
              ON "wos"."levelid" = "lvd"."levelid" 
       LEFT JOIN "modedefinition" "mdd" 
              ON "wo"."modeid" = "mdd"."modeid" 
       LEFT JOIN "urgencydefinition" "urgdef" 
              ON "wos"."urgencyid" = "urgdef"."urgencyid" 
       LEFT JOIN "impactdefinition" "impdef" 
              ON "wos"."impactid" = "impdef"."impactid" 
       LEFT JOIN "requesttypedefinition" "rtdef" 
              ON "wos"."requesttypeid" = "rtdef"."requesttypeid" 
       LEFT JOIN "subcategorydefinition" "scd" 
              ON "wos"."subcategoryid" = "scd"."subcategoryid" 
       LEFT JOIN "itemdefinition" "icd" 
              ON "wos"."itemid" = "icd"."itemid" 
       LEFT JOIN "servicedefinition" "serdef" 
              ON "wo"."serviceid" = "serdef"."serviceid" 
       LEFT JOIN "aaauser" "cbau" 
              ON "wo"."createdbyid" = "cbau"."user_id" 
       LEFT JOIN "aaauser" "oboaau" 
              ON "wo"."oboid" = "oboaau"."user_id" 
       LEFT JOIN "sduser" 
              ON "wo"."requesterid" = "sduser"."userid" 
       LEFT JOIN "sduser" "SDUSER_ONBEHALFOF" 
              ON "wo"."oboid" = "SDUSER_ONBEHALFOF"."userid" 
       LEFT JOIN "workorder_fields" 
              ON "wo"."workorderid" = "workorder_fields"."workorderid"  WHERE  ( ( ( "wos"."statusid" IN ( 1, 2, 301 ) ) 
           AND ( "wo_queue"."queueid" IN (SELECT "queuedefinition"."queueid" 
                                          FROM   "queuedefinition" 
                                          WHERE  ( "queuedefinition"."queuename" 
                                                   COLLATE 
                                                   latin1_general_cs_as = 
                                                   N'Zm ICT' 
                                                 )) ) ) 
         AND ( ( "wo"."isparent" = 1 ) 
               AND ( ( ( "wos"."ownerid" = 90954 ) 
                        OR ( "wo"."requesterid" = 90954 ) ) 
                      OR ( ( "wo"."siteid" IN (SELECT "usersitemapping"."siteid" 
                                               FROM   "usersitemapping" 
                                               WHERE  ( 
                                              "usersitemapping"."userid" = 
                                              90954 )) 
                           ) 
                            OR ( "wo"."siteid" IS NULL ) ) ) ) );

All the FK columns and the columns in criteria are already indexed.

I collected the actual query plan but unable to make anything out of this. How do I find what is wrong with this execution plan? How do I improve it?

Please find the execution plan at https://www.brentozar.com/pastetheplan/?id=rkAHTW0Qm

This count query is supposed to show the total number of items matching a particular filtered list view. So the application joins the same set of tables always and applies the criteria based on which view is selected. Query with the same joins but with much simpler criteria are executing just fine. I want to know if there is any way to improve this, or whether I have hit some kind of ceiling based on the execution plan.

The type conversion warning in the plan is due to the use of an explicit COLLATE in the query. I checked that out by removing the collate operation from the query and found that was not a factor for the slowness.

  • Only three tables have any filtering in the where clause. Maybe try taking that logic and dumping it into a temp table? – Erik Darling Jul 20 '18 at 12:43
  • looking at the query plan 2 things caught my attention, Low cardinality "wo"."isparent" = 1 so the index is inefficient and a missing index in "workorderstates" "wos" – jean Jul 20 '18 at 12:51
  • 1. I have seen a key-lookup operator. i would suggest to create a non cluster index on those columns making it to covering index. 2. There is a index seek with 66%, please check on which columns index were created, if it is varchar are you converting or cast operator then index will not be used. instead re-create the index on the integer columns, based on the integer columns get the relevant varchar or character data – Smart003 Jul 20 '18 at 13:17
  • 1
    My first response is to not expect good performance from queries that join 30 tables (yes, I counted). Since you're simply doing a COUNT(*), removing all LEFT JOIN-ed tables (that aren't used in the WHERE clause) should return the same result. – BradC Jul 20 '18 at 14:58
  • 1
    @BradC good idea but those tables have been removed from the plan, I think. Still, it's a good idea. No reason to give the optimizer a 30-table join when the query can be written with a 6-table. – ypercubeᵀᴹ Jul 20 '18 at 15:08
0

Change the line where you explicitly do COLLATE, and switch it so that you do COLLATE on the literal, not on the table column.

This will help the optimizer use the index.
Also, this could help solve the issue with wrong estimation in node22.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.