Desempenhos do MS Excel ao Realizar Pesquisas
Enviado: Sex Out 23, 2015 11:19 pm
Boa noite a todos.
Estou abrindo esse tópico, por orientação do próprio Tomás, pois se trata de um assunto bastante relevante. O tópico abaixo, foi o que gerou a necessidade de discutirmos esse assunto: Nesse tópico, foi postado uma planilha, adaptada com base no Modelo de Cadastro v1, mas onde não é utilizado Instruções SQL para realização das buscas no formulário de pesquisa.
Conforme nosso colega Gustavo questionou, a retirada das Instruções SQL torna a busca pelo resultado muito lenta, quando inserimos um considerável número de registros, como no exemplo do colega que possui 2.000 linhas de registros.
O principal problema em relação a se trabalhar com buscas e pesquisas sem o uso de Instruções SQL, é termos que realizar uma análise por todas as linhas existentes na planilha, neste exemplo, uma varredura nas 2.000 linhas. Agora, quando temos a possibilidade de realizar o filtro por mais de um campo/coluna do registro como parâmetro, o número de testes é multiplicado pela quantidade de parâmetros existente. Vamos analisar o código de busca do próprio modelo que foi postado, apenas a parte relacionada a validação para realização do filtro.
Como podemos observar, para a realização do filtro, é realizada uma validação através de uma função chamada ValidaCampoFiltro, para os seguintes campos: NomeEmpresa, NomeContato, Endereco, Telefone, Cidade e Regiao.
Voltando ao exemplo do nosso colega, quando vamos realizar um filtro referente a 2.000 registros, e tendo 6 campos para serem validados por linha, são realizados 12.000 testes lógicos para realização do filtro.
Mas existe uma forma de reduzir o tempo de resposta desta busca. Neste caso, e me corrijam se eu estiver enganado, a única sugestão que me vem a mente, é a utilização de um conjunto de Instruções If Aninhadas. O que ocorre com o uso de If´s Aninhadas, é o fato de reduzirmos a quantidade de testes por eliminação. Quando o primeiro teste falhar (a primeira condição não atende os requisitos), os demais testes não serão realizados, e assim por diante em relação aos demais testes. Dessa forma, não teremos mais os 12.000 testes lógicos sendo realizados, pois nem todos os registros/linhas irão atender ao parâmetros especificados. Vejamos como ficou o código após a alteração:
Mas diante da situação, pensa nosso colega Gustavo e os demais: "Não vale a pena trabalhar com o MS Excel, quando trabalhamos com grande números de registros?", ou "Se o tempo de resposta é tão demorado, por que perder tempo montando um modelo como este?", ou ainda "Devo parar de trabalhar com o MS Excel?".
Bem meus amigos, como havia colocado no tópico em que postei os modelos, os mesmos foram adaptados para um curso de VBA Básico que ministrei e que quis muito compartilhar.
Como usuário do MS Excel, não gosto de armazenar informações em nenhum tipo de planilha, independente de ser a própria planilha ou uma planilha como base de dados. Utilizo o MS Excel apenas com dois objetivos principais: Análise e Testes de Dados, pois o MS Excel é uma das melhore ferramentas do mercado do tipo; e como Front-End de SI (Sistemas Integrados).
Na minha opinião, para se ganhar um desempenho adequado em relação a manipulação de dados, o ideal é utilizar um Banco de Dados Relacional em conjunto com o MS Excel. O MS Excel não é seguro e nem confiável para armazenagem de dados.
Confesso que não utilizei muitos bancos de dados em meu tempo como Desenvolvedor VBA, mas gosto muito de trabalhar com o MySQL, MS Access (nativo do MS Office) e com o Firebird. Já vi planilhas rodando com o NoSQL e SQLite. Mas tenho uma imensa curiosidade de trabalhar com o MS SQL Server e um MySQL for Linux, mas tem me faltando tempo para realizar esses testes.
Bem amigos, vou ficando por aqui. Espero ter sanado a dúvida do colega Gustavo e de outros amigos do fórum que possuírem as mesmas dúvidas sobre o assunto.
Um forte abraço a todos e um excelente final de semana.
Estou abrindo esse tópico, por orientação do próprio Tomás, pois se trata de um assunto bastante relevante. O tópico abaixo, foi o que gerou a necessidade de discutirmos esse assunto: Nesse tópico, foi postado uma planilha, adaptada com base no Modelo de Cadastro v1, mas onde não é utilizado Instruções SQL para realização das buscas no formulário de pesquisa.
Conforme nosso colega Gustavo questionou, a retirada das Instruções SQL torna a busca pelo resultado muito lenta, quando inserimos um considerável número de registros, como no exemplo do colega que possui 2.000 linhas de registros.
O principal problema em relação a se trabalhar com buscas e pesquisas sem o uso de Instruções SQL, é termos que realizar uma análise por todas as linhas existentes na planilha, neste exemplo, uma varredura nas 2.000 linhas. Agora, quando temos a possibilidade de realizar o filtro por mais de um campo/coluna do registro como parâmetro, o número de testes é multiplicado pela quantidade de parâmetros existente. Vamos analisar o código de busca do próprio modelo que foi postado, apenas a parte relacionada a validação para realização do filtro.
Código: Selecionar todos
If ValidaCampoFiltro(NomeEmpresa, i, 2) = True And _
ValidaCampoFiltro(NomeContato, i, 3) = True And _
ValidaCampoFiltro(Endereco, i, 5) = True And _
ValidaCampoFiltro(Telefone, i, 10) = True And _
ValidaCampoFiltro(Cidade, i, 6) = True And _
ValidaCampoFiltro(Regiao, i, 7) = True Then
'Código desejado.
End If
Voltando ao exemplo do nosso colega, quando vamos realizar um filtro referente a 2.000 registros, e tendo 6 campos para serem validados por linha, são realizados 12.000 testes lógicos para realização do filtro.
Mas existe uma forma de reduzir o tempo de resposta desta busca. Neste caso, e me corrijam se eu estiver enganado, a única sugestão que me vem a mente, é a utilização de um conjunto de Instruções If Aninhadas. O que ocorre com o uso de If´s Aninhadas, é o fato de reduzirmos a quantidade de testes por eliminação. Quando o primeiro teste falhar (a primeira condição não atende os requisitos), os demais testes não serão realizados, e assim por diante em relação aos demais testes. Dessa forma, não teremos mais os 12.000 testes lógicos sendo realizados, pois nem todos os registros/linhas irão atender ao parâmetros especificados. Vejamos como ficou o código após a alteração:
Código: Selecionar todos
If ValidaCampoFiltro(NomeEmpresa, i, 2) = True Then
If ValidaCampoFiltro(NomeContato, i, 3) = True Then
If ValidaCampoFiltro(Endereco, i, 5) = True Then
If ValidaCampoFiltro(Telefone, i, 10) = True Then
If ValidaCampoFiltro(Cidade, i, 6) = True Then
If ValidaCampoFiltro(Regiao, i, 7) = True Then
'Código desejado.
End If
End If
End If
End If
End If
End If
Bem meus amigos, como havia colocado no tópico em que postei os modelos, os mesmos foram adaptados para um curso de VBA Básico que ministrei e que quis muito compartilhar.
Como usuário do MS Excel, não gosto de armazenar informações em nenhum tipo de planilha, independente de ser a própria planilha ou uma planilha como base de dados. Utilizo o MS Excel apenas com dois objetivos principais: Análise e Testes de Dados, pois o MS Excel é uma das melhore ferramentas do mercado do tipo; e como Front-End de SI (Sistemas Integrados).
Na minha opinião, para se ganhar um desempenho adequado em relação a manipulação de dados, o ideal é utilizar um Banco de Dados Relacional em conjunto com o MS Excel. O MS Excel não é seguro e nem confiável para armazenagem de dados.
Confesso que não utilizei muitos bancos de dados em meu tempo como Desenvolvedor VBA, mas gosto muito de trabalhar com o MySQL, MS Access (nativo do MS Office) e com o Firebird. Já vi planilhas rodando com o NoSQL e SQLite. Mas tenho uma imensa curiosidade de trabalhar com o MS SQL Server e um MySQL for Linux, mas tem me faltando tempo para realizar esses testes.
Bem amigos, vou ficando por aqui. Espero ter sanado a dúvida do colega Gustavo e de outros amigos do fórum que possuírem as mesmas dúvidas sobre o assunto.
Um forte abraço a todos e um excelente final de semana.