Heavy MS-Access Database - Need Help urgently

[Mahasing]

I am using VB as front end and MS Access as backend. When i am retrieving records from the database, its taking too long (30 - 40 seconds when there are some 8000 records). I came to know that the order by clause is causing it so much time. But order by is required ( a must ). So how can i go about with it. This is an urgent requirment. So, please someone help me. I would apreciate any kind of help from anyone.
Thanking you all in advance.
Mahesh

[Cakkie]

What you can try to do is to create an index on the field that you are sorting on. This will cause the provider to search the field faster, thus sorting it faster.

Tom Cannaerts
slisse@planetinternet.be

Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the universe trying to produce bigger and better idiots. So far, the universe is winning -- Rich Cook

[Mahasing]

Thanks Tom for your reply. But i have already given index on the particular field and still the records are accessed very slowly. Can you or someone else please suggest some other options?

Thanks,
Mahesh

[ben_632]

HI,

If you have a Where clause, make sure you have the right indexes on both tables. Also, try to change the order of the joined tables in the select clause or the order of the joins to a way in which the small tables will be joined first (sometimes, the provider doesn't do the best join process). Anyway, 8000 records are a lot of records and it consumes time even on faster databases like SQL-Server. If you're filling a grid or a combo, the drawing process also consumes a lot of time.

Ben

[Green_Beret]

Can you post the SQL Query?

Regards,
The Beret.