3

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:

enter image description here enter image description here

if I add loop in the inner join, we have low CPU usage on HASH MATCH, but a high percentage with the index seek:

ex 1 enter image description here

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:

enter image description here

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

4
  • Can you post the query plan using plan explorer ? Also what is the table structure and indexes on the tables ?
    – Kin Shah
    Commented Sep 3, 2015 at 19:44
  • 1
    Since you only want top 100 rows, why are you pulling 3 million records from the underlying tables and then just getting top 100 without any order by clause ? So, in the inner query you should do a TOP 100 with column names and ORDER BY - dont use *, explicitly mention column names. You can just create a view on the inner query and create index on the view. that would improve the performance. Try this query
    – Kin Shah
    Commented Sep 3, 2015 at 21:48
  • @kin this top 100 is just a test, the real pivot where are going to create will be bigger, with a lot more rows. thats what we are trying to think...we need this one fast , to know how to make the real one fast too. I will try to do what you said, create a indexed view on the inner query,
    – Racer SQL
    Commented Sep 4, 2015 at 11:11
  • 2
    Did you resolve this? Commented Apr 30, 2016 at 14:01

1 Answer 1

-1

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.

1
  • 1
    How does a common table expression perform any better than a derived table?
    – ErikE
    Commented Mar 17, 2017 at 4:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.