We have this query, that runs in 1:10
. We need to improve this. This is just a test query, but we will implement this upgrade on our real query.
select top 100 *
from (
select codCliente
,Resposta
,NrQuestao --,COUNT(1) as Qtde
,(
select COUNT(1) as qtde
from tblRespostaINT T2
where T2.codCliente = T0.codCliente
and T2.codNrQuestao between 9
and 29
) as QResp
from tblRespostaINT T0
inner join tblQuestao T1 on T0.codNrQuestao = T1.codNrQuestao
where T0.CodQuestionario = 1
and T1.codNrQuestao between 9
and 29
--and codCliente = 5156
--Group by codCliente, Resposta,NrQuestao
) as tblx
Pivot(Sum(resposta) for NrQuestao in (
[I_1_P14_1]
,[I_2_P14_1]
,[I_3_P14_1]
,[I_4_P14_1]
,[I_5_P14_1]
,[I_6_P14_1]
,[I_7_P14_1]
,[I_9_P14_1]
,[I_10_P14_1]
,[I_11_P14_1]
,[I_12_P14_1]
,[I_13_P14_1]
,[I_14_P14_1]
,[I_15_P14_1]
,[I_16_P14_1]
,[I_17_P14_1]
,[I_18_P14_1]
,[I_19_P14_1]
,[I_20_P14_1]
,[I_21_P14_1]
,[I_22_P14_1]
)) tbl
I've added some indexes but none of them helped. This is the execution plan:
if I add loop
in the inner join
, we have low CPU usage on HASH MATCH, but a high percentage with the index seek
:
It takes 30sec to run the insider query:
select codCliente
,Resposta
,NrQuestao --,COUNT(1) as Qtde
,(
select COUNT(1) as qtde
from tblRespostaINT T2
where T2.codCliente = T0.codCliente
and T2.codNrQuestao between 9
and 29
) as QResp
from tblRespostaINT T0
inner join tblQuestao T1 on T0.codNrQuestao = T1.codNrQuestao
where T0.CodQuestionario = 1
and T1.codNrQuestao between 9
and 29
--and codCliente = 5156
--Group by codCliente, Resposta,NrQuestao
What can we do? is there some index tip I should know for problems like these?Should we use only joins?
XML plan: http://pastebin.com/raw/anLifzBC