Very slow database access - how to improve?

[Stuartp]

I have written an app that accesses an MS Access database on a server over the LAN. In our office over 100MB LAN all is fine. However in another office on what I think is a 10MB LAN the database is so slow to return data, and I can't honestly believe it's the network causing the problem.

Here is the code I have written to get to the data:

     searchConnection.Open ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\my-server\company\databases\mydata.mdb;Persist Security Info=False")

    cusRs.Open "SELECT CF_01_CUSTF.CF_CU_ACCOUNT, CF_01_CUSTF.CF_CU_NAME, CF_01_CUSTF.CF_CU_CONTACT001, CF_01_CUSTF.CF_CU_ADDRESS001, CF_01_CUSTF.CF_CU_ADDRESS002, CF_01_CUSTF.CF_CU_ADDRESS003, CF_01_CUSTF.CF_CU_ADDRESS004, CF_01_CUSTF.CF_CU_ADDRESS005, CF_01_CUSTF.CF_CU_POSTCODE, CF_01_CUSTF.CF_CU_PHONE001, CF_01_CUSTF.CF_CU_FAX001, CF_01_CUSTF.CF_CU_EMAIL " & _
                "FROM CF_01_CUSTF " & _
                "WHERE CF_01_CUSTF.CF_CU_NAME LIKE '%" & name & "%' OR CF_01_CUSTF.CF_CU_CONTACT001 LIKE '%" & name & "%' ", searchConnection, adOpenForwardOnly, adLockReadOnly


Do While Not cusRs.EOF
                    
Items added to a grid here
                    
cusRs.MoveNext
                    
Loop

I have stepped through the code to see where the slowness comes from. The query itself takes longer to run than I would expect, however in the do while...loop, when it gets to the end of the recordset, the final cusRs.movenext takes absolutely ages (around 10-15 seconds) before it realises it has reached the end of the record and goes to display the results.

I have used this code many times before and have never encountered a problem before now.

Is there something wrong with my code? I have tried lots of things including different cursor types, cursor location, connection strings etc. as well as adding indexes to key fields in the data, all to no avail.

Is there a change / improvement I can make to my code to get results from the database faster?

Any advice and suggestions much appreciated.

Many thanks

Stuart

 

[Droops]

Hello,

maybe (probably) a stupid question : have you tried compacting the database ?

Droops.

 

[Swi]

What type of grid are you adding the items to?

Swi

 

[MarkSweetland]

Wildcard characters at the beginning of a word while searching using the LIKE keyword results in an index scan(which defeats the purpose of an index) or a table scan.

Although it may not be feasible in your case, try removing the first % from the WHERE clause to speed up the query:

"WHERE CF_01_CUSTF.CF_CU_NAME LIKE '" & name & "%' OR "




Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach

 

[Stuartp]

Hi all, thanks for your comments.

Yes I did try to compact the database, but it didn't make any difference (it is a brand new database).

I am adding the items to a ListView control configured as a report.

I could try removing the first % from the query although I'm a little loathe to do it because our users don't necessarily search using the start of the string in the data.

As it is, today, I seem to have got round the problem. I began to experiment with MSDE 2000. I set it up on the same machine as the one with the Access database, imported the data into it and then configured my VB program to use this database. Query results are now returned instantly.

I'm very happy that this works, although I'm still curious as to why Access behaves in this way.

Stuart