Ytukyg

coud you help me optimizing the following statement. It has a bad prerformance when dealing with huge amount of data (in my case 3Mio Messages and 25Mio MessageWorkItems).

Does anybody have any suggestions? Thank you in advance.

select distinct msg.id,  msgWorkItem_1.description

from message msg 

left outer join message_work_item msgWorkItem_1 on msg.id=msgWorkItem_1.message_id 

and (   msgWorkItem_1.id in (

        select max(msgWorkItem_2.id) 

        from message_work_item msgWorkItem_2 

        inner join message_work_item_type msgWorkItem_Type on msgWorkItem_2.message_work_item_type_id=msgWorkItem_Type.id 

        where 

            msgWorkItem_2.creation_type= 'mobile'

            and msgWorkItem_2.description is not null 

            and msgWorkItem_Type.code <> 'sent-to-app-manually' 

        --  Is it possible to avoid this correlation to the outer query ? )

            and msgWorkItem_2.message_id = msg.id)

        ) 

where msg.deactivation_time > ? 

order by msgWorkItem_1.description asc

STEP 1 : Lay out the query so I have any hope of reading it

SELECT

  DISTINCT

  msg.id,

  msgWorkItem_1.description

FROM

  message msg 

LEFT OUTER JOIN

  message_work_item   AS msgWorkItem_1

    ON  msgWorkItem_1.message_id = msg.id 

    AND msgWorkItem_1.id =

        (

          SELECT

            MAX(msgWorkItem_2.id) 

          FROM

            message_work_item      AS msgWorkItem_2 

          INNER JOIN

            message_work_item_type AS msgWorkItem_Type

              ON msgWorkItem_2.message_work_item_type_id=msgWorkItem_Type.id 

          WHERE

                msgWorkItem_2.creation_type= 'mobile'

            AND msgWorkItem_2.description IS NOT NULL 

            AND msgWorkItem_Type.code <> 'sent-to-app-manually' 

            --  Is it possible to avoid this correlation to the outer query ?

            AND msgWorkItem_2.message_id = msg.id

        )

WHERE

  msg.deactivation_time > ? 

ORDER BY

  msgWorkItem_1.description ASC

STEP 2 : rewrite using analytic functions instead of MAX()

SELECT

  DISTINCT

  message.id,

  message_work_item_sorted.description

FROM

  message

LEFT OUTER JOIN

(

  SELECT

    message_work_item.message_id,

    message_work_item.description,

    ROW_NUMBER() OVER (PARTITION BY message_work_item.message_id

                           ORDER BY message_work_item.id DESC

                      )

                         AS row_ordinal

  FROM

    message_work_item

  INNER JOIN

    message_work_item_type

      ON message_work_item.message_work_item_type_id = message_work_item_type.id 

  WHERE

        message_work_item.creation_type= 'mobile'

    AND message_work_item.description IS NOT NULL 

    AND message_work_item_type.code <> 'sent-to-app-manually' 

)

  message_work_item_sorted

    ON  message_work_item_sorted.message_id  = message.id

    AND message_work_item_sorted.row_ordinal = 1

WHERE

  message.deactivation_time > ? 

ORDER BY

  message_work_item_sorted.description ASC

With more information we could probably help further, but as you gave no definition of the tables, constraints, or business logic, this is just a re-write of what you're already implemented.

For example, I strongly doubt you need the DISTINCT (provided that the id columns in your tables are unique).