Query Timeout when Getting List of Pending Transfers for Manual Processing at leandrosardi/flywheel#472

[leandrosardi]

This query has query timeout problems:

use euler;
go

select 
    /*
    distinct 
    */
    datediff(hh, h.create_time, getdate()) as created_hours_ago,
    /*
    u.id,
    */ 
    h.id
    /*, c.name, e.email, p.name, h.create_time, h.inmessage_tries,
    h.gct, h.gct_id_user,
    u.prospecting_reservation_id, u.prospecting_reservation_time, h.inmessage_start_time,
    --u.username, u.prospecting_reservation_id, u.prospecting_reservation_time, h.inmessage_tries, c.name, 
    'prospecting do_messages=yes username='+u.username+' chat_id='+CAST(h.id as varchar(500)), h.body
    */
--select COUNT(distinct h.id)
from lnchat h with (nolock)
/*
join lnuser u with (nolock) on u.id=h.id_lnuser
join lnchat i with (nolock) on (i.id_result=h.id_result and i.id_lnuser=h.id_lnuser and i.id<>h.id and i.direction<>3)
join [message] m with (nolock) on (m.id=i.id_message and isnull(m.trigger_type,0)=0)
join pipeline p with (nolock) on p.id=m.id_pipeline
join [user] e with (nolock) on e.id=p.id_user
join client c with (nolock) on c.id=e.id_client
*/
where 
    isnull(h.wlt_enabled, 0) = 1

--and isnull(h.inmessage_tries, 0) < 3
--and p.name = 'HACK-00 - Olive'

--and h.direction=0
and h.create_time > DATEADD(WW,-1,GETDATE())
and h.create_time >= dateadd(hh, -36, getdate())

--and h.create_time > DATEADD(HH,-6,GETDATE())
--and isnull(h.inmessage_tries,0) > 0
/*
--and h.create_time < '2020-09-17 04:51:00' -- select DATEADD(HH,-12,GETDATE())
and h.inmessage_end_time is null
and h.id not in ( -- canceled messages
    SELECT h.id
    FROM lnchat h WITH (NOLOCK)
    JOIN lnchat i WITH (NOLOCK) ON (
        i.id_lnuser=h.id_lnuser AND 
        i.id_result=h.id_result AND
        ISNULL(i.direction, 0) = 1 AND
        i.inmessage_end_time IS NOT NULL AND
        i.inmessage_end_time > h.create_time
    )
    WHERE h.create_time > DATEADD(WW,-1,GETDATE())
    AND ISNULL(h.direction, 0) = 0
    AND h.inmessage_end_time IS NULL
)
order by h.create_time asc;
*/
go

[leandrosardi]

Fixed.