How to expedite VB.net database searches / Access97

Question

Use a query searching in 3 different tables, returning this query attribute in a Datatable that will fill a Datagridview.

The problem is that the search itself, this a little time, I used the TOP as a limiter but still takes some time. I use a Access database 97, all indexed tables.

Well, I wonder if there is any way I can use a cache to expedite this query, or what I can improve on my query, this delay is sent to me.

Follows 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

Filling in the Datagrid:

DataGridView1.DataSource = Nothing
da = New OleDbDataAdapter(MySQL, Conexao)
dt = New DataTable
da.Fill(dt)
Me.DataGridView1.DataSource = dt

Answer 1

MS Access is not a real database management system and has no way to achieve the same performance as a SGBD. In addition to other limitations such as the maximum file size of 1Gb .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:

http://www.fmsinc.com/microsoftaccess/performance.html

Answer 2

Have you tried changing your query to one 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

Withdraw the ORDER BY can improve the performance of query also.

Other points that may affect performance:

• Your tables have a primary key?
• Are the indexes set? (Not your case)
• There are no duplicate lines?