Slow query performance

Asked

Viewed 43 times

-2

I have a database that has about 3 teras and I’m having problems optimizing some querys.

In this example I have 3 tables: Automaker, Car and model.

And I have this query that takes about 30m, all joins are indexed and the query was made going from the smallest table to the largest, the options that are white to 'ZZZ' are filters that the user can do in the query so I can not change them

Any hint of something that might be wrong or another way to make a query.

Select * from Montadora m

INNER JOIN Carro c 
on c.codMontadora = m.codigo
and c.pais between '   ' AND 'ZZZZZ'
and c.estado between '   ' AND 'ZZZZZ'

INNER JOIN Modelo o
on o.codMontadora = c.codMontadora
and o.pais = c.pais
and o.estado = c.estado
and o.versao between '   ' AND 'ZZZZZ'
and o.cor between '   ' AND 'ZZZZZ'
and o.motor between '   ' AND 'ZZZZZ'

where 
m.codigo = 'GM'
sql query
  • 1

    Why put selection criteria in the join condition? It would not be better to merge and then apply the selection criteria or even, if the conditions imply a substantial reduction in the number of records, make a subselect applying the selection criteria and use the result in the join?

  • Could page this by placing a limit on select.

  • if you put a EXPLAIN before select you can analyze where the bottleneck is.

1 answer

0

I can think of a few improvements, basically:

  1. take out the and of join; check these conditions at the junction increases much the amount of validations at the time of joining a table to another.
  2. stop using between; it is preferable to use < >.
  3. remove the unnecessary validations; you say there are "filters the user can make in the query", then these filters may or may not come (and if they do not, they are unnecessary validations).
  4. use whole codes instead of strings; the automaker code is not integer; in addition, several fields could be tables referenced by code, which would decrease the loss in validation.
Select * 
FROM Montadora m
INNER JOIN Carro c ON c.codMontadora = m.codigo
INNER JOIN Modelo o ON o.codMontadora = c.codMontadora
WHERE m.codigo = 'GM'
  AND o.pais = c.pais
  AND o.estado = c.estado
  AND (c.pais >= @pais_ini AND c.pais <= @pais_fim)
  AND (c.estado >= @estado_ini AND c.estado <= @estado_fim)
  AND (c.versao >= @versao_ini AND c.versao <= @versao_fim)
  AND (c.cor >= @cor_ini AND c.cor <= @cor_fim)
  AND (c.motor >= @motor_ini AND c.motor <= @motor_fim)

The improvement 4 is very difficult to implement, since the bank is in production; but it seems to have quite a flaw in the question foreign key (NOTE: I say this without seeing the structure, just for the consultation you brought).

So the ideal would be an option 5 which would be to reformulate the structure; just to illustrate, the color should be from carro, not of modelo.

Browser other questions tagged sql query

You are not signed in. Login or sign up in order to post.