Performance query slow

Question

Performance query slow

Navigation

#1 by (0 votes)

-2

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

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

And I have this query that takes about 30m, all the inner 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 query so I can not change them

Any hint of something that may 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

asked by anonymous 04.12.2018 / 23:11

1 answer

How to print text in the same line in Python How to solve the module error not found? (Installing opencv-python)

score 0 · Answer 1

I can think of a few improvements, basically:

remove and from join ; checking these conditions at the junction increases the amount of validations in the time to join a table to other.

stop using between ; it is preferable to use < > .

remove unnecessary validations ;

use integer codes instead of strings ; the automaker code is not an 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)

Improvement 4 is very difficult to implement since the bank is in production; but it does seem to have a lot of flaws in the foreign key (OBS: I say this without seeing the structure, just by the query you brought).

So, the ideal would be a 5 option that would reshape the structure; just to give an example, color should be carro , not modelo .