How to streamline searches in VB.net / Access97 database

Navigation
0

I use a query that does the search in 3 different tables, return this query attribute in a Datatable that will fill a Datagridview .

The problem is that the search itself is a little time consuming, I used TOP as a limiting but it still has a certain delay. I use an Access 97 database, all indexed tables.

Well, I would like to know if there is any way I can use a cache to speed up this query, or what I can improve on my query , this delay is sendable to me.

Follow the code: 

SELECT TOP 10 Pedidos.Número, Clientes.RazaoSocial As [Cliente], vendedores.nome As [Vendedor], Pedidos.Data As [Data], 
Format(Pedidos.Valor, '###,##0.00') As [VrPedido], Pedidos.Flag As [Flag], Pedidos.Status As [Status],Cliente As [codcli], Vendedor As [codVend] 

FROM Pedidos, vendedores, Clientes 

WHERE Lançamento = 0 And Pedidos.vendedor = Vendedores.código And Clientes.CodigoCliente = Pedidos.Cliente ORDER BY data desc, número desc

Populating the DataGrid: 

DataGridView1.DataSource = Nothing
da = New OleDbDataAdapter(MySQL, Conexao)
dt = New DataTable
da.Fill(dt)
Me.DataGridView1.DataSource = dt
    
asked by anonymous 14.07.2015 / 17:22

2 answers

1

MS Access is not a real database manager system and can not achieve the same performance as a SGBD . In addition to other limitations such as the maximum 1Gb size of the file .mdb (version 97).

My answer is: use MySQL , PostGres , MS SQL , NoSQL , etc.

If this is not an option, the link below has several techniques to improve the performance of a Base Access:

link

    
14.07.2015 / 17:49
0

Have you tried changing your query to INNER JOIN ? 

SELECT TOP 10 Pedidos.Número, Clientes.RazaoSocial As [Cliente], vendedores.nome As [Vendedor], Pedidos.Data As [Data], 
Format(Pedidos.Valor, '###,##0.00') As [VrPedido], Pedidos.Flag As [Flag], Pedidos.Status As [Status],Cliente As [codcli], Vendedor As [codVend] 
FROM Pedidos
INNER JOIN vendedores ON Pedidos.vendedor = Vendedores.código
INNER JOIN Clientes ON Clientes.CodigoCliente = Pedidos.Cliente
ORDER BY data desc, número desc

Removing ORDER BY can improve the performance of query as well.

Other points that can affect performance:

  • Do your tables have a primary key?
  • Are the indexes defined? (It's not your case)
  • Are there no duplicate lines?
14.07.2015 / 17:59
How to generate PDF with Crystal Reports and send it by email? Table does not update correctly after editing