Página 1 de 1

Desempenhos do MS Excel ao Realizar Pesquisas

Enviado: Sex Out 23, 2015 11:19 pm
por Mikel Silveira Fraga
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.

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
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:

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
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.

Re: Desempenhos do MS Excel ao Realizar Pesquisas

Enviado: Dom Out 25, 2015 8:27 pm
por Edcronos
alem do uso de if alinhado para se ganhar tempo de acesso se pode usar arrays
Range-->Array-->filtrage-->array-->planilha

a interação entre vba e celulas pode ser bem demorada se a planilha tiver muitas formulas e outras coisas, principalmente na gravação
isso pq o excel faz verificação de dependências e recalculo de tempo em tempo

eu criei varias ferramentas de filtragem que pelo menos na minha maquina traz todos os resultados de uma range de 600000 linhas * 10 colunas de modo instantâneo
para falar a verdade o vba leva mais tempo para passar a array para o excel do que para varrer a array e passar os resultados para outro array

eu passei a usar BD agora e ainda não tive a necessidade de me aprofundar no uso

mas como o Mikel falou, o ponto crucial do excel realmente é a segurança

Re: Desempenhos do MS Excel ao Realizar Pesquisas

Enviado: Seg Out 26, 2015 3:57 pm
por Edcronos
Para usar arrays de vba pegando e passando valores da planilha é muito facil

1º a array tem que ser tipo variante, creio que tbm aceite tipo string, mas tipo string fica limitado a comparações e não podem ser manipuladas matematicamente
2º a array tem que ter base 1 e não 0 Expl. (1 to 100,1 to 5), seria 100 linhas e 5 colunas, array(linhas,colunas)

para declarar a array

Dim Array_de_range() as variant

para passar os valores da range para a array é bem simples

Array_de_range = Range("A10:H100").value2--> prefira usar Value2 , isso pq o excel não vai verificar os valores e vai ser bem mais rápido


para passa da array para o exel é só fazer o contrario, mas no caso tem que respeitar o tamanho da array
exemplo : se a array de resultados ficou com 20 linhas
range("A1:H" & 20).value2 = array_resultado
ou de uma forma dinamica
range("A1:H" & ubound(array_resultado,1).value2 = array_resultado

bem, sou meio enrolado para explicar mas posso garantir que o ganho de performance é enorme