How can we improve this PIVOT operation? ( SQL Server 2008 R2 )

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?

Query Plan:

XML plan: http://pastebin.com/raw/anLifzBC

I would stay away from LOOP joins, or specifying any specific joins. The SQL Server query optimizer is very good at picking the best join method to use, and it will pick LOOP on its own if that is the right one. Also, if there is a missing non-clustered index that could improve the performance of the query, SQL Profiler will find it and suggest it to you.

One thing that I noticed missing from your query is the use of common table expressions. I haven’t had a pivot yet where I haven’t needed to use common table expressions to get the data ready to pivot. I would rewrite your query using common table expressions (particularly the correlated subquery) and see if that improves performance. If it doesn’t another thing you could consider doing is changing the order of columns in the clustered index on the tblRespostaINT to better support that query.

Finally I believe that what you are doing in the inner select can be accomplished with a windowed function, and if it can that should be faster.