What better way to solve slowness to generate report?

1

At one time, I developed a page of reports, the users have a filters option, this is working, but as the data in the database are growing it is getting slower and I do not know which option is best to solve this problem, some of the things I thought were:

  • If it is always better to bring the data already calculated by the query, or if in some cases, it is better to return all the data, and do these calculations using php, for example?

  • Am I creating the querys correctly? What I mean by this, I'm doing the accounts in several sub-querys, here is an example:

    SELECT DISTINCT t.nome_tag,
        (
            SELECT count(vw.tag_id) FROM view_relatorio vw
            WHERE vw.tag_id = t.id AND vw.cliente_id IN (1,2,3,10,20)
              AND vw.conforme = 1 AND vw.dt_validate >= '2016-09-09'
        ) as conforme,
    
        (
            SELECT count(vw.tag_id) FROM view_relatorio vw
            WHERE vw.tag_id = t.id AND vw.cliente_id IN (1,2,3,10,20)
              AND vw.conforme = 0
        ) as naoConforme,
    
        (
            SELECT count(vw.tag_id) FROM view_relatorio vw
            WHERE vw.tag_id = t.id AND vw.cliente_id IN (1,2,3,10,20)
        ) as totalAtribuido
    
    FROM view_relatorio view
    JOIN tag t ON t.id = view.tag_id
    WHERE view.cliente_id IN (1,2,3,10,20)
    

Remembering that this is a small example and has several other querys.

I have already built views to improve, as I have read in some places, I have already indexed columns in the necessary tables, so in order to improve the part of the database I have made several adjustments.

    
asked by anonymous 09.09.2016 / 20:32

1 answer

2

The 3 queries are practically repeated (one to pick up the conformers, one non-conformist handle and one handle the total). Count () is an operation that defaults to the bank because it has to scan the records. It also has JOINs in 4 queries. The bank can not take advantage of all these reps. In this case it is better to do a query that takes a tab and adds it to an array. Another suggestion is to review the data model to see what can be improved (separate into more tables: for example [tag_id, client_id, conform], or even just [tag_id, conform]). To measure time, take a look at the "EXPLAIN ..." command. It shows how the query will be executed.

    
09.09.2016 / 20:57