 |
ActiveDelphi
.: O site do programador Delphi! :.
|
| Exibir mensagem anterior :: Exibir próxima mensagem |
| Autor |
Mensagem |
adriano_servitec
Colaborador
Registrado: Sexta-Feira, 30 de Janeiro de 2004
Mensagens: 17618
|
 Enviada: Ter Jul 06, 2021 8:51 am Assunto: Melhorar performance select |
 |
|
Fiz este select aqui
| Código: | SELECT
endereco.endereco_logradouro,
endereco.endereco_complemento,
bairro.bairro_descricao bairrodescricao,
cidade.cidade_descricao as cidadedescricao,
uf.uf_sigla as ufsigla,
endereco.endereco_cep as cep
FROM endereco
INNER JOIN cidade on cidade.cidade_codigo = endereco.cidade_codigo
INNER JOIN bairro on bairro.bairro_codigo = endereco.bairro_codigo
INNER JOIN uf on uf.uf_codigo = endereco.uf_codigo
WHERE (
UPPER(endereco.endereco_logradouro || cidade.cidade_descricao ||
bairro.bairro_descricao || uf.uf_sigla || uf.uf_descricao) LIKE :PBUSCA_CEP
) |
Mais analizando a performance vi que nas tabelas
ENDERECO, BAIRRO, CIDADE passaram 1.094.578 vezes em cada um deles. E 27 vezes na tabela UF (Sem index nessa ultima)
Sendo que essa quantidade está apenas em ENDERECO
BAIRRO tem 55.449
CIDADE tem 10.894
Na minha tabela.
Como seria para melhorar o tempo de busca visto que acho que não é necessários passar todos os 1.094.578 dados do endereço em cada um deles.
Uso firebird 2.5
Obrigado.
_________________
Jogo seu smartphone? Acesse o link e confira.
https://play.google.com/store/apps/details?id=br.com.couldsys.rockdrum
https://play.google.com/store/apps/details?id=br.com.couldsys.drumsetfree |
|
| Voltar ao Topo |
|
 |
imex
Moderador
Registrado: Sexta-Feira, 7 de Janeiro de 2011
Mensagens: 11666
|
| Enviada: Ter Jul 06, 2021 10:26 am Assunto: |
|
|
Bom dia,
Não sei se vai fazer diferença, mas experimente separar os filtros para ver se melhora:
| Código: | WHERE
UPPER(uf.uf_sigla) LIKE :PBUSCA_CEP OR
UPPER(uf.uf_descricao) LIKE :PBUSCA_CEP OR
UPPER(cidade.cidade_descricao) LIKE :PBUSCA_CEP OR
UPPER(bairro.bairro_descricao) LIKE :PBUSCA_CEP OR
UPPER(endereco.endereco_logradouro) LIKE :PBUSCA_CEP |
E experimente também inverter a ordem das tabelas (se possível) no From e Joins, começando a partir da tabela Uf:
| Código: | FROM uf
INNER JOIN cidade on cidade.uf_codigo = uf.uf_codigo
INNER JOIN bairro on bairro.cidade_codigo = cidade.cidade_codigo
INNER JOIN endereco on endereco.bairro_codigo = bairro.bairro_codigo |
Espero que ajude
Editado pela última vez por imex em Dom Out 01, 2023 5:45 pm, num total de 2 vezes |
|
| Voltar ao Topo |
|
|
adriano_servitec
Colaborador
Registrado: Sexta-Feira, 30 de Janeiro de 2004
Mensagens: 17618
|
| Enviada: Ter Jul 06, 2021 10:48 am Assunto: |
|
|
| imex escreveu: | Bom dia,
Não sei se vai fazer diferença, mas experimente separar os filtros para ver se melhora:
| Código: | WHERE
UPPER(uf.uf_sigla) LIKE :PBUSCA_CEP OR
UPPER(uf.uf_descricao) LIKE :PBUSCA_CEP OR
UPPER(cidade.cidade_descricao) LIKE :PBUSCA_CEP OR
UPPER(bairro.bairro_descricao) LIKE :PBUSCA_CEP OR
UPPER(endereco.endereco_logradouro) LIKE :PBUSCA_CEP |
E experimente também inverter a ordem das tabelas (se possível) no From e Joins, começando a partir da tabela Uf:
| Código: | FROM uf
INNER JOIN cidade on cidade.uf_codigo = uf.uf_codigo
INNER JOIN bairro on bairro.cidade_codigo = cidade.cidade_codigo
INNER JOIN endereco on endereco.bairro_codigo = bairro.bairro_codigo |
Espero que ajude
|
Bom dia
Assim da erro
| Código: | | Arithmetic exception, numeric overflow, or string truncation. string rigth truncation |
| Código: | SELECT
endereco.endereco_logradouro,
endereco.endereco_complemento,
bairro.bairro_descricao bairrodescricao,
cidade.cidade_descricao as cidadedescricao,
uf.uf_sigla as ufsigla,
endereco.endereco_cep as cep
FROM uf
INNER JOIN cidade on cidade.uf_codigo = uf.uf_codigo
INNER JOIN bairro on bairro.cidade_codigo = cidade.cidade_codigo
INNER JOIN endereco on endereco.bairro_codigo = bairro.bairro_codigo
WHERE
UPPER(uf.uf_sigla) LIKE :PBUSCA_CEP OR
UPPER(uf.uf_descricao) LIKE :PBUSCA_CEP OR
UPPER(cidade.cidade_descricao) LIKE :PBUSCA_CEP OR
UPPER(bairro.bairro_descricao) LIKE :PBUSCA_CEP OR
UPPER(endereco.endereco_logradouro) LIKE :PBUSCA_CEP |
_________________
Jogo seu smartphone? Acesse o link e confira.
https://play.google.com/store/apps/details?id=br.com.couldsys.rockdrum
https://play.google.com/store/apps/details?id=br.com.couldsys.drumsetfree |
|
| Voltar ao Topo |
|
|
adriano_servitec
Colaborador
Registrado: Sexta-Feira, 30 de Janeiro de 2004
Mensagens: 17618
|
| Enviada: Ter Jul 06, 2021 10:54 am Assunto: |
|
|
desta forma aqui
| Código: | SELECT
endereco.endereco_logradouro,
endereco.endereco_complemento,
bairro.bairro_descricao bairrodescricao,
cidade.cidade_descricao as cidadedescricao,
uf.uf_sigla as ufsigla,
endereco.endereco_cep as cep
FROM uf
INNER JOIN cidade on cidade.uf_codigo = uf.uf_codigo
INNER JOIN bairro on bairro.cidade_codigo = cidade.cidade_codigo
INNER JOIN endereco on endereco.bairro_codigo = bairro.bairro_codigo
--WHERE
-- UPPER(uf.uf_sigla) LIKE :PBUSCA_CEP OR
-- UPPER(uf.uf_descricao) LIKE :PBUSCA_CEP OR
-- UPPER(cidade.cidade_descricao) LIKE :PBUSCA_CEP OR
-- UPPER(bairro.bairro_descricao) LIKE :PBUSCA_CEP OR
-- UPPER(endereco.endereco_logradouro) LIKE :PBUSCA_CEP
WHERE (
UPPER(endereco.endereco_logradouro
|| cidade.cidade_descricao
|| bairro.bairro_descricao
|| uf.uf_sigla
|| uf.uf_descricao) LIKE :PBUSCA_CEP
) |
Até melhorou um pouco por trazer da forma correta cada passada de campos nas tabelas, mais ainda esta um pouco demorado
_________________
Jogo seu smartphone? Acesse o link e confira.
https://play.google.com/store/apps/details?id=br.com.couldsys.rockdrum
https://play.google.com/store/apps/details?id=br.com.couldsys.drumsetfree |
|
| Voltar ao Topo |
|
|
adriano_servitec
Colaborador
Registrado: Sexta-Feira, 30 de Janeiro de 2004
Mensagens: 17618
|
| Enviada: Ter Jul 06, 2021 11:06 am Assunto: |
|
|
Outra coisa que percebi mImex é usando um cast
| Código: | WHERE
UPPER(cast(uf.uf_sigla as varchar (255) character set WIN1252)) LIKE :PBUSCA_CEP OR
UPPER(cast(uf.uf_descricao as varchar (255) character set WIN1252)) LIKE :PBUSCA_CEP OR
UPPER(cast(cidade.cidade_descricao as varchar (255) character set WIN1252)) LIKE :PBUSCA_CEP OR
UPPER(cast(bairro.bairro_descricao as varchar (255) character set WIN1252)) LIKE :PBUSCA_CEP OR
UPPER(cast(endereco.endereco_logradouro as varchar (255) character set WIN1252)) LIKE :PBUSCA_CEP |
Não causa erro, mais também o mesmo filtro de busca não trouxe o resultado
No caso fiz o testes
| Código: | --WHERE
-- UPPER(cast(endereco.endereco_logradouro as varchar (255) character set WIN1252)) LIKE :PBUSCA_CEP OR
-- UPPER(cast(cidade.cidade_descricao as varchar (255) character set WIN1252)) LIKE :PBUSCA_CEP OR
-- UPPER(cast(bairro.bairro_descricao as varchar (255) character set WIN1252)) LIKE :PBUSCA_CEP OR
-- UPPER(cast(uf.uf_sigla as varchar (255) character set WIN1252)) LIKE :PBUSCA_CEP OR
-- UPPER(cast(uf.uf_descricao as varchar (255) character set WIN1252)) LIKE :PBUSCA_CEP
WHERE (
UPPER(endereco.endereco_logradouro
|| cidade.cidade_descricao
|| bairro.bairro_descricao
|| uf.uf_sigla
|| uf.uf_descricao) LIKE :PBUSCA_CEP
) |
Where com OR sem concatenar retornou nenhum valor e ficou o execute time em 4s 203ms, já o de baixo concatenado retornou em 2s 672ms e com alguns valores
Acho que esse deve ser o maximo que consegue executar então.
_________________
Jogo seu smartphone? Acesse o link e confira.
https://play.google.com/store/apps/details?id=br.com.couldsys.rockdrum
https://play.google.com/store/apps/details?id=br.com.couldsys.drumsetfree |
|
| Voltar ao Topo |
|
|
adriano_servitec
Colaborador
Registrado: Sexta-Feira, 30 de Janeiro de 2004
Mensagens: 17618
|
|
| Voltar ao Topo |
|
|
pestana
Colaborador
Registrado: Sábado, 25 de Junho de 2005
Mensagens: 3147
Localização: Araras-SP
|
| Enviada: Ter Jul 06, 2021 2:07 pm Assunto: |
|
|
Adriano todas estas junções da cláusula From estão com chaves?
_________________
Ao invés de ficar desanimado no que deu de errado, olhe para frente, aprenda com os erros e veja o que ainda pode ser feito. A determinação e a persistência é uma das etapas para o sucesso. |
|
| Voltar ao Topo |
|
|
adriano_servitec
Colaborador
Registrado: Sexta-Feira, 30 de Janeiro de 2004
Mensagens: 17618
|
|
| Voltar ao Topo |
|
|
adriano_servitec
Colaborador
Registrado: Sexta-Feira, 30 de Janeiro de 2004
Mensagens: 17618
|
| Enviada: Ter Jul 06, 2021 4:36 pm Assunto: |
|
|
Estava vendo aqui algo que me deixou curioso
Se eu digitar no select %RUA%MORRETES%
RETORNOU 19 REGISTROS DA TABELA E ABAIXO
| Citação: | O performance analysis do ibexpert retornou
1.104.349 - TABELA ENDERECOS
55.449 - TABELA BAIRROS
10.894 - TABELA CIDADES |
| Citação: |
------ Performance info ------
Prepare time = 16ms
Execute time = 2s 703ms
Avg fetch time = 193,07 ms
Current memory = 9.730.792
Max memory = 9.877.448
Memory buffers = 2.048
Reads from disk to cache = 35.472
Writes from cache to disk = 1
Fetches from cache = 2.610.913
|
****************************************************
Se eu digitar no select %RUA%CURITIBA%
RETORNOU 1000 REGISTROS DA TABELA (QUE LIMITEI) E ABAIXO
| Citação: | O performance analysis do ibexpert retornou
468.821 - TABELA ENDERECOS
21.341 - TABELA BAIRROS
5.939 - TABELA CIDADES |
| Citação: | ------ Performance info ------
Prepare time = 0ms
Execute time = 532ms
Avg fetch time = 38,00 ms
Current memory = 9.754.880
Max memory = 9.877.448
Memory buffers = 2.048
Reads from disk to cache = 15.711
Writes from cache to disk = 0
Fetches from cache = 1.109.033 |
Porque a que tem mais registros se comportou mais rápido seu retorno e o performance analysis ficou com menos passada?
_________________
Jogo seu smartphone? Acesse o link e confira.
https://play.google.com/store/apps/details?id=br.com.couldsys.rockdrum
https://play.google.com/store/apps/details?id=br.com.couldsys.drumsetfree |
|
| Voltar ao Topo |
|
|
imex
Moderador
Registrado: Sexta-Feira, 7 de Janeiro de 2011
Mensagens: 11666
|
| Enviada: Ter Jul 06, 2021 4:48 pm Assunto: |
|
|
| Acho que você tem que tentar fazer uns testes sem concatenar os campos para filtrar, e procurando eliminar as funções que não forem necessárias. No caso da sigla da UF por exemplo, acho que o Upper, Cast e o Like não são necessários, podendo ser utilizado um simples "igual", inclusive desconfio que o erro que ocorreu no início foi por causa desse campo da sigla. |
|
| Voltar ao Topo |
|
|
imex
Moderador
Registrado: Sexta-Feira, 7 de Janeiro de 2011
Mensagens: 11666
|
| Enviada: Ter Jul 06, 2021 5:01 pm Assunto: |
|
|
Segue uma outra sugestão para testes onde seria utilizado um Select separado para deixar cada tabela no From e somente com os filtros dos campos dessa tabela. Ex:
| Código: | SELECT
endereco.endereco_logradouro,
endereco.endereco_complemento,
bairro.bairro_descricao bairrodescricao,
cidade.cidade_descricao as cidadedescricao,
uf.uf_sigla as ufsigla,
endereco.endereco_cep as cep
FROM endereco
INNER JOIN cidade on cidade.cidade_codigo = endereco.cidade_codigo
INNER JOIN bairro on bairro.bairro_codigo = endereco.bairro_codigo
INNER JOIN uf on uf.uf_codigo = endereco.uf_codigo
WHERE
UPPER(endereco.endereco_logradouro) LIKE :PBUSCA_CEP
union
SELECT
endereco.endereco_logradouro,
endereco.endereco_complemento,
bairro.bairro_descricao bairrodescricao,
cidade.cidade_descricao as cidadedescricao,
uf.uf_sigla as ufsigla,
endereco.endereco_cep as cep
FROM uf
INNER JOIN cidade on cidade.uf_codigo = uf.uf_codigo
INNER JOIN bairro on bairro.cidade_codigo = cidade.cidade_codigo
INNER JOIN endereco on endereco.bairro_codigo = bairro.bairro_codigo
WHERE
uf.uf_sigla = :PBUSCA_CEP OR
UPPER(uf.uf_descricao) LIKE :PBUSCA_CEP |
No exemplo acima teria que ser adicionado um Select para a tabela Bairro e outro para a tabela Cidade. Acho que dessa forma não será feita a passagem pelas tabelas dos Joins quando o registro da tabela do From não passar pelo filtro, talvez melhore o desempenho.
Espero que ajude |
|
| Voltar ao Topo |
|
|
adriano_servitec
Colaborador
Registrado: Sexta-Feira, 30 de Janeiro de 2004
Mensagens: 17618
|
| Enviada: Ter Jul 06, 2021 6:05 pm Assunto: |
|
|
Imex precisei colocar um cast ainda
| Código: | SELECT
endereco.endereco_logradouro,
endereco.endereco_complemento,
bairro.bairro_descricao bairrodescricao,
cidade.cidade_descricao as cidadedescricao,
uf.uf_sigla as ufsigla,
endereco.endereco_cep as cep
FROM endereco
INNER JOIN cidade on cidade.cidade_codigo = endereco.cidade_codigo
INNER JOIN bairro on bairro.bairro_codigo = endereco.bairro_codigo
INNER JOIN uf on uf.uf_codigo = endereco.uf_codigo
WHERE
UPPER(endereco.endereco_logradouro) LIKE :PBUSCA_CEP
union
SELECT
endereco.endereco_logradouro,
endereco.endereco_complemento,
bairro.bairro_descricao bairrodescricao,
cidade.cidade_descricao as cidadedescricao,
uf.uf_sigla as ufsigla,
endereco.endereco_cep as cep
FROM uf
INNER JOIN cidade on cidade.uf_codigo = uf.uf_codigo
INNER JOIN bairro on bairro.cidade_codigo = cidade.cidade_codigo
INNER JOIN endereco on endereco.bairro_codigo = bairro.bairro_codigo
WHERE
uf.uf_sigla = cast(:PBUSCA_CEP as varchar(72)) OR
UPPER(uf.uf_descricao) LIKE :PBUSCA_CEP |
Mais não retornou nada quando passei assim no parametro
%RUA%MORRETES%PARANÁ%
Se passar no parametro %RUA%MORRETES% retorna os registros, mais de todos os estados.
Fiz mais um union com a tabela cidades, mais também não da retorno se eu colocar assim no parametro
%RUA%MORRETES%CURITIBA%
| Código: | SELECT
endereco.endereco_logradouro,
endereco.endereco_complemento,
bairro.bairro_descricao bairrodescricao,
cidade.cidade_descricao as cidadedescricao,
uf.uf_sigla as ufsigla,
endereco.endereco_cep as cep
FROM endereco
INNER JOIN cidade on cidade.cidade_codigo = endereco.cidade_codigo
INNER JOIN bairro on bairro.bairro_codigo = endereco.bairro_codigo
INNER JOIN uf on uf.uf_codigo = endereco.uf_codigo
WHERE
UPPER(endereco.endereco_logradouro) LIKE :PBUSCA_CEP
union
SELECT
endereco.endereco_logradouro,
endereco.endereco_complemento,
bairro.bairro_descricao bairrodescricao,
cidade.cidade_descricao as cidadedescricao,
uf.uf_sigla as ufsigla,
endereco.endereco_cep as cep
FROM uf
INNER JOIN cidade on cidade.uf_codigo = uf.uf_codigo
INNER JOIN bairro on bairro.cidade_codigo = cidade.cidade_codigo
INNER JOIN endereco on endereco.bairro_codigo = bairro.bairro_codigo
WHERE
uf.uf_sigla = cast(:PBUSCA_CEP as varchar(72)) OR
UPPER(uf.uf_descricao) LIKE :PBUSCA_CEP
union
SELECT
endereco.endereco_logradouro,
endereco.endereco_complemento,
bairro.bairro_descricao bairrodescricao,
cidade.cidade_descricao as cidadedescricao,
uf.uf_sigla as ufsigla,
endereco.endereco_cep as cep
FROM cidade
INNER JOIN endereco on endereco.uf_codigo = cidade.uf_codigo
INNER JOIN bairro on bairro.cidade_codigo = cidade.cidade_codigo
INNER JOIN uf on uf.uf_codigo = cidade.uf_codigo
WHERE
UPPER(cidade.cidade_descricao) LIKE :PBUSCA_CEP |
_________________
Jogo seu smartphone? Acesse o link e confira.
https://play.google.com/store/apps/details?id=br.com.couldsys.rockdrum
https://play.google.com/store/apps/details?id=br.com.couldsys.drumsetfree |
|
| Voltar ao Topo |
|
|
imex
Moderador
Registrado: Sexta-Feira, 7 de Janeiro de 2011
Mensagens: 11666
|
| Enviada: Qua Jul 07, 2021 9:28 am Assunto: |
|
|
| Olhando melhor os seus exemplos, acho que tem que concatenar mesmo para o filtro funcionar. Nesse caso talvez uma alternativa seja adicionar um campo na tabela Endereco para gravar os valores concatenados e de preferência com uma collation case insensitive ou com o conteúdo todo em maiúsculas ou minúsculas para evitar a função Upper também. |
|
| Voltar ao Topo |
|
|
adriano_servitec
Colaborador
Registrado: Sexta-Feira, 30 de Janeiro de 2004
Mensagens: 17618
|
|
| Voltar ao Topo |
|
|
imex
Moderador
Registrado: Sexta-Feira, 7 de Janeiro de 2011
Mensagens: 11666
|
| Enviada: Qua Jul 07, 2021 2:14 pm Assunto: |
|
|
Boa tarde,
A sugestão seria criar um campo na tabela Endereco para gravar o endereço completo, como por exemplo o valor retornado pelo trecho abaixo:
| Código: | UPPER(endereco.endereco_logradouro
|| cidade.cidade_descricao
|| bairro.bairro_descricao
|| uf.uf_sigla
|| uf.uf_descricao) |
Aí você poderia utilizar esse campo no filtro deixando a tabela Endereco no From, o que provavelmente vai evitar a passagem pelas tabelas do Join quando não for necessário, além de deixar de executar a função Upper e a concatenação:
| Código: | | WHERE endereco.endereco_completo like :BUSCA_CEP |
Espero que ajude |
|
| Voltar ao Topo |
|
|
|
|
Enviar Mensagens Novas: Proibido.
Responder Tópicos Proibido
Editar Mensagens: Proibido.
Excluir Mensagens: Proibido.
Votar em Enquetes: Proibido.
|
|
FAQ
Pesquisar
Membros
Grupos
Registrar
Perfil
Entrar e ver Mensagens Particulares
Entrar
