We manage a RTC repository containing 435.000 work items and 1.6 million entries in the links.auditable_links table. One of our users formulated a work item query similar to

Work Item Type is ...
AND
Parent > ID is ...

The first condition selects a single work item type, but the list of work item IDs of the second condition contains about 60 work item IDs. Execution of this query takes minutes.

So we looked deeper into what's happening and found that the query engine transforms the above conditions into SQL similar to this:

select distinct t1.ITEM_ID, t1.STATE_ID, t1.INTERNAL_SEVERITY, t1.MODIFIED
from MODEL.WORK_ITEM t1
where ((t1.PROJECT_AREA_ITEM_ID = ?) and (t1.WORK_ITEM_TYPE =
 ?) and ( exists (

        select 1
        from LINKS.AUDITABLE_LINK x1 inner join MODEL.WORK_ITEM x2 on
         (x1.TARGET_REF_REFERENCED_ITM_TM_D = x2.ITEM_ID)
        where x1.SOURCE_REF_REFERENCED_ITM_TM_D = t1.ITEM_ID     and ((x1.NAME_COL
         = 'com.ibm.team.workitem.linktype.parentworkitem')      and (x2.ID
         = ?))
) or exists (

        select 1
        from LINKS.AUDITABLE_LINK x1 inner join MODEL.WORK_ITEM x2 on
         (x1.TARGET_REF_REFERENCED_ITM_TM_D = x2.ITEM_ID)
        where x1.SOURCE_REF_REFERENCED_ITM_TM_D = t1.ITEM_ID     and ((x1.NAME_COL
         = 'com.ibm.team.workitem.linktype.parentworkitem')      and (x2.ID
         = ?))
) or exists (

.... n blocks ...

))

For each of the ID numbers given on the condition a separate exists query block is generated. We assume that the long execution time of the query is due to the large number of such blocks.

After we had the generated SQL statement in front of us, we gave it a little thought and came up with this slightly different SQL query:

select distinct t1.ID, t1.ITEM_ID, t1.STATE_ID, t1.INTERNAL_SEVERITY                       \
 , t1.MODIFIED                                                                          \
 from MODEL.WORK_ITEM t1                                                                \
 where ((t1.PROJECT_AREA_ITEM_ID = '_IiG0wLfJEeK8V9aYCMsFHg') and (t1.WORK_ITEM_TYPE =  \
  'com.ibm.stg.workItemType.changeRecord') and (                                        \
exists (                                                                               
         select 1                                                                      
         from LINKS.AUDITABLE_LINK x1 inner join MODEL.WORK_ITEM x2 on                 
          (x1.TARGET_REF_REFERENCED_ITM_TM_D = x2.ITEM_ID)                             
         where x1.SOURCE_REF_REFERENCED_ITM_TM_D = t1.ITEM_ID     and ((x1.NAME_COL    
          = 'com.ibm.team.workitem.linktype.parentworkitem')      and (x2.ID           
          in (529417,530701,533012,532628,533782,533654,530071,530458,532123,531997,531998,531746,531747,528547,533540,533541,532518,532519,533928,533929,533550,533551,530735,533040,533428,533815,533817,534082,530247,529226,534091,533835,532944,533842,533843,533844,533845,526550,533846,532312,526556,532957,533853,532958,533858,532963,533859,533348,518246,533865,530286,532976,534001,534002,533107,532595,532596,530421,533113,532989,
) ))  ) ))

Instead of having 60 separate exists blocks, we only use a single such block and enumerate the work item IDs as values of an IN predicate.

This query executes 100x faster!!!

To conclude, we have two questions:

1) Could the query engine be improved to generate SQL according to our proposal?
2) Before that gets into the product, are there ways to improve the execution time of the SQL query as it is generated today?

Thanks for your help!
Markus