ADO - Melhorando o desempenho de sua aplicação

Muito bem . Você já esta dominando a ADO e migrando suas aplicações ( quando vale a pena ) para a nova tecnologia. Criar Conexões , Recordsets  e Procedures armazenadas já deixou de ser um bicho de sete cabeças ( passou a ficar com 5 cabeças... ) . Que tal agora focar um ajuste fino em suas aplicações para melhorar o desempenho ?

Vamos a seguir sugerir e dar orientações para melhorar o desempenho de sua aplicação ADO. Você deve ter em mente que algumas destas sugestões irão funcionar sob certas circunstâncias e que de  uma maneira geral estamos supondo que você é uma pessoa sensata e que possui bons hábitos de programação. (Você usa  Option Explicit ? )

Melhorando o Desempenho

1- ) Defina sempre suas variáveis

Parece óbvio mas se você não definir as suas variáveis isto torna a sua aplicação mais lenta. Assim temos:

a-) Código definindo a variável Connection -

b-) Código sem definir a variável Connection : :-(

Neste código , o tipo da variável Connection não é conhecido em tempo de compilação ; isto  faz com que o código gerado obtenha esta informação usando IDispatch o que é mais lento.

2-) Vincule os registros ( colunas ) para um objeto Field antes de percorrer os registros

a-) O código abaixo obtém a referência para os objetos Fields antes de percorrer o Recordset . A referência é usada durante a navegação pelo recordset .

a-) O código a seguir faz com que para cada registro seja necessário uma consulta ao Recordset.

3-) Procure utilizar instruções SQL para atualizar (incluir, alterar,excluir) os dados do seu banco de dados evitando fazer isto via objeto Recordset. Embora as vezes isto seja um tanto trabalhoso é o meio mais rápido pois você não vai precisar de um objeto Recordset ou objeto Command. Isto diminui o tempo de processamento e os requisitos da memória do sistema e evita tráfego de rede.

As instruções usadas são:

Você deve tomar muito cuidado com o seguinte:

• Nas instruções UPDATE E DELETE quando você não especifica uma condição (WHERE) todos os registros são atualizados / excluidos do banco de dados.
• Se você estiver inserindo datas no seu banco de dados verifique o formato da data suportado pelo mesmo.
• Ao inserir dados em um campo você tem que estar ciente das limitações quanto ao tamanho máximo de dados que você pode inserir e quanto a permissão de incluir valores NULL.
• Muito cuidado com dados do tipo AutoNumber (Autonumeração, ou Identity no SQL Server) . Neste caso você não pode inserir um valor para o campo pois o banco de dados gera automaticamente os valores para a chave primária.
• Ao construir uma string SQL com variáveis fornecidas pelo Visual Basic você deve substituir cada apóstrofo por dois apóstrofos. Ex: teste= Replace(variavel, " ' ", " ' ' ")
•  

4-) Selecione apenas os registros que você realmente vai precisar. 

Ao  montar sua string SQL para selecionar registros é muito mais fácil usar ( SELECT * ) do que relacionar os campos desejados (Oh! preguiça...) , mas isto tem um custo , principalmente se a quantidade de colunas retornadas for grande: O desempenho do sistema irá piorar.

Assim se você precisa somente do nome e do código de um cliente não use:

Select * from Clientes    prefira    Select codigo,nome from clientes

5-) Seja muito criterioso ao selecionar o Tipo de Cursor , a localização do Cursor  e o tipo de bloqueio.

Se você não precisa fazer atualizações nem se mover para frente e para trás em seu Recordset , não tenha dúvidas utilize o padrão ADO : adUseServer , adOPenForwardOnly e adLockReadOnly. Isto lhe dará um melhor desempenho. 

Se você precisar se mover por todo o seu Recordset , o cenário muda , prefira usar o Cursor do lado do cliente -  adUseClient , e neste caso utilize adLockReadOnly como tipo de bloqueio (a não ser que você realmente precisa de outro tipo de bloqueio). 

Já se você precisar de um Recordset atualizável o Cursor do lado do cliente - adUseClient - irá onerar mais o seu sistema. Utilize AdUseServer.

Nota: Lembra-se dos tipos de Cursores ?

6-) Ajuste a propriedade CacheSize do seu Recordset adequadamente.

A ADO usa a propriedade CacheSize do objeto Recordset para determinar o número de colunas que irão compor o cache. Enquanto você estiver trabalhando dentro do intervalo das colunas no Cache , a ADO retorna os dados do Cache. Ao movimentar ser para fora da faixa das colunas presentes no Cache , a ADO atualiza o Cache com um  novo intervalo de colunas.  Como não existe um valor padrão para o tamanho do Cache onde o desempenho é melhor , você deverá fazer os ajustes , testando vários valores , e usar o valor para CacheSize que lhe oferece melhor desempenho.

Nota - Voce sabe O que é CacheSize  ?

Quer um exemplo ?  Então veja :  O exemplo abaixo usa a propriedade CacheSize para mostrar a diferença no desempenho para um operação feita com um valor de 30 para CacheSize e para um CacheSize não definido.

Eu estou abrindo o banco de dados Pubs do SQL Server 2000 e usando a tabela roysched , a minha string de conexão é  : 

Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=pubs;Data Source=MACORATI .

Se você for testar na sua máquina o string vai ser diferente.

Public Sub CacheSizeX()

    Dim rstRoySched As ADODB.Recordset
    Dim strCnn As String
    Dim sngStart As Single
    Dim sngEnd As Single
    Dim sngNoCache As Single
    Dim sngCache As Single
    Dim intLoop As Integer
    Dim strTemp As String

    ' abre a tabela roysched
    strCnn = "Provider=SQLOLEDB.1;Persist Security Info=False;"_

    User ID=sa;Initial Catalog=pubs;Data Source=MACORATI"

    Set rstRoySched = New ADODB.Recordset
    rstRoySched.Open "roysched", strCnn, , , adCmdTable

    ' inicia contagem do tempo
    sngStart = Timer

    For intLoop = 1 To 2
        rstRoySched.MoveFirst

        Do While Not rstRoySched.EOF
            ' realiza uma operação para teste
            strTemp = rstRoySched!title_id
            rstRoySched.MoveNext
        Loop
    Next intLoop

    sngEnd = Timer
    sngNoCache = sngEnd - sngStart

    ' Trabalha com um Cache de 30 registros
    rstRoySched.MoveFirst
    rstRoySched.CacheSize = 30
    sngStart = Timer

    ' inicia contagem do tempo
    For intLoop = 1 To 2

        rstRoySched.MoveFirst
        Do While Not rstRoySched.EOF
            ' realiza uma operação para teste
            strTemp = rstRoySched!title_id
            rstRoySched.MoveNext
        Loop
    Next intLoop

    sngEnd = Timer
    sngCache = sngEnd - sngStart

    ' Mostra a diferença no desempenho
    MsgBox "Resultado do Desempenho:" & vbCrLf & vbCrLf & _
        "   Sem cache    : " & Format(sngNoCache, _
        "##0.000") & " segundos" & vbCr & vbCr & _
        "  CacheSize=30  : " & Format(sngCache, _
        "##0.000") & " segundos" , vbinformation, "Usando CacheSize"
    rstRoySched.Close

End Sub

Veja o resultado:

7-) Utilize os provedores OLE DB nativos

Nas versões anteriores a versão 2.0 da MDAC você tinha que usar provedores OLE DB para ODBC , usando assim um driver ODBC apropriado para ter acesso a base de dados. A partir da versão 2.0 a MDAC fornece provedores nativos para SQL Server , Oracle e para o Jet (.mdb) , e você pode usá-los para acessar as bases de dados com mais rapidez e gastando menos recursos de memória. Veja abaixo :

8-) Utilize a opção adExecuteNoRecords para comandos que não retornam registros ( Colunas )

A partir da versão 2.0 , a ADO incluiu a opção adExecuteNoRecords, a qual deve ser usada para comandos que não retornam registros. Ao usar esta opção a ADO não cria o objeto Recordset , não define nenhuma propriedade para Cursor , e otimiza o desempenho do provedor , que para este caso não precisa verificar as propriedades das colunas. Veja exemplo abaixo ( não precisamos de objeto recordset )

9-) Use Connection.Execute para processamento simples.

Se você precisa somente abrir uma conexão , executar um processamento que retorne poucas colunas ou não retorna nada, processar o resultado e fechar a conexão, use Connection.Execute ao invés de Recordset.Open ou Command.Execute. 

Ao utilizar Connection.Execute a ADO não preserva a informação do estado do comando o que aumenta o desempenho de sua aplicação. 

Obs: Naturalmente se você precisar de um recordset ou vai utilizar parâmetros no seu comando não vai poder usar Connection.Execute

10-) Desconecte o seu Cursor para operações de leitura e/ou de processamento muito demorado.

O Cursor do lado do Cliente , permite que o Recordset seja desconectado. Utilize esta característica quando você vai realizar uma operação  muito demorada que não requer muito recursos do banco de dados. Você sempre vai poder reconectar o seu Recordset para atualizações posteriores.

E , até breve...