0
When 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 Where, it was in a matter of seconds, as the example below. I wonder why this difference in performance.
Note: I only inserted 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',
the difference doesn’t seem to be only the Inner Join or Where... take the top 1 and run the select isolated to see the difference of the results
– Rovann Linhalis
The queries are not "well" equal. At first you are reading data not commited using
WITH (NOLOCK), you should put this in the second query (inner join act_log as al WITH (NOLOCK)). According to theWHEREis also different in the fieldal.type. Try to make both queries equal and run again. The Execution plan can help identify differences as well– Ricardo Pontual
The code is incomplete and does not allow proper analysis. However, the two sections are different constructions and cannot be directly compared. In the second section there are 3 tables in the FROM clause; in the first section there are 2 tables in the FROM clause and it is probably a correlated subconsultation, because of
cr.persid = al.call_req_id. In addition, the filter for the columnal.typeis different in both parts.– José Diz