While generating a script, I tried to modify a where for inner join . However, the time when running with inner join was more than 10 minutes and with the where, it was in a matter of seconds, as the example below. I would like to know why this difference in performance.
Note: I entered only the beginning of the script.
SELECT
cs.sym as 'Status',
crt.sym as 'Tipo',
ccc.last_name as 'Contato',
co.abbreviation as 'Lotação do contato',
ISNULL((SELECT TOP 1
cc.last_name
FROM
act_log al WITH (NOLOCK), ca_contact cc WITH (NOLOCK)
WHERE
cr.persid = al.call_req_id
AND al.analyst = cc.contact_uuid
AND al.type = 'cl'
ORDER BY al.id DESC),'') as 'Fechada por',
--OU--
ISNULL((SELECT TOP 1
cc.last_name
FROM call_req as cr
inner join act_log as al on (cr.persid = al.call_req_id)
inner join ca_contact as cc on (al.analyst = cc.contact_uuid)
WHERE al.type = 'soln'
ORDER BY al.id DESC),'') as 'Fechada por',