I have the following situation, I need to bring data from a vehicle table of the system in which I work and another with integrated data of a partner system, because integration is not very consistent data and the way to connect the tables can be by chassis , badge, or id:
select COUNT(*) from syo_veiculo = 290518
select COUNT(*) from syo_oficina = 945336
select vei.id_veiculo, vei.id_cliente, vei.id_empresa, ofi.no_os, ofi.dt_emissao, ofi.id_empresa as empresaofi
from syo_veiculo vei
join syo_oficina ofi ON vei.id_veiculo = ofi.id_produto OR vei.ds_placa = ofi.ds_placa OR vei.no_chassi = ofi.no_chassi
where vei.dt_venda BETWEEN 1435100000000 AND 1435900000000
group by vei.id_veiculo, vei.id_cliente, vei.id_empresa, ofi.no_os, ofi.dt_emissao, empresaofi
= 879 linhas em 30622 ms, 30337 ms, 30275 ms
Note that the result of the query was obtained in about 30 seconds, without grouping, would bring 1078 rows at the same time.
Alternative Query:
select *
from (
select vei.id_veiculo, vei.id_cliente, vei.id_empresa, ofi.no_os, ofi.dt_emissao, ofi.id_empresa as empresaofi
from syo_veiculo vei
join syo_oficina ofi ON vei.id_veiculo = ofi.id_produto
where vei.dt_venda BETWEEN 1435100000000 AND 1435900000000
union all
select vei.id_veiculo, vei.id_cliente, vei.id_empresa, ofi.no_os, ofi.dt_emissao, ofi.id_empresa as empresaofi
from syo_veiculo vei
join syo_oficina ofi ON vei.ds_placa = ofi.ds_placa
where vei.dt_venda BETWEEN 1435100000000 AND 1435900000000
union all
select vei.id_veiculo, vei.id_cliente, vei.id_empresa, ofi.no_os, ofi.dt_emissao, ofi.id_empresa as empresaofi
from syo_veiculo vei
join syo_oficina ofi ON vei.no_chassi = ofi.no_chassi
where vei.dt_venda BETWEEN 1435100000000 AND 1435900000000
) as tbaux
group by id_veiculo, no_os, id_cliente, id_empresa, dt_emissao, empresaofi
= 879 linhas em 1969 ms, 791 ms, 724 ms
Note that the return came much faster, the same 879 rows without the collation would bring 2172 rows at the same time.
- I want to understand why the query with
union all
where it has many more validations takes much less time, does not make sense to me, even more that the result was the same -
Is there any exception in these cases
union all X join com OR
, for example an FK in the workshop table could make any difference in favor ofjoin
?
I want to understand why I want to better plan my next queries and get the best performance.