VB GURU's!!!!Need urgent help here on SLOW database access!!!

[raj]

Hi Everyone;
Thank'yall in advance!!!!All ideas are greatly appreciated!!!!
The problem is slow database access(findfirst) - I'm using a live visual foxpro database which i've connected to visual basic through intrinsic data control through access(Linked). Now there's a HUGE table in that database, which contains around 1,80,000 records. What i'm doing is I'm
just using a 'Findfirst' method to find records and then fill certain fields in textboxes.
so now if the record is in top records, it get it very fast but if its at the bottom, it takes like 10-mins & other than that if that record is not in table then it get struck.
So Is there any way to make it faster???
Thanx in advance!!!!
Cheers

Raj

Hello,

Have you tried making one field an indexed field and doing
your search on that field?

[raj]

Yessssss i have an indexed field in that table and i'm doing search on that field but still its slow .......
Any ideas again to make it faster???????
thanx

[Clunietp]

Is there any way you can archive stale data? Maybe divide the data up into different tables? (by some sort of category)

Desktop databases (like access, foxpro) begin to slow down after 50-60 thousand records per table....

[raj]

even if i do SQL its slow that way . Its faster in C++ demo(sql statement) but i dunno how to link C with VB....may be something like - to make dll file......i dunno whether it'll work that way or not....

but still its a live database that changes every 15 mins
and i have to use it live in prog.

any suggestions????????
thanx
Raj

[SkippySolutions]

Hey RAJ,

If you are on an indexed field, try using Seek as s first try, if that is still too slow try this:

Create a query/stored procedure in Foxpro, and then use that instead. That way the FoxPro engine is the one doing the work, and you do not have to run back and forth to the database.

With any of the Findxxx methods, I think the records are passed one by one through to the app, the app's data engine tests it, and then determines if it is a match. Where as if you use a query on the FoxPro side, FoxPro does all of the work.

Hope this helps

[Michael]

Is it essential that the table is linked? It may be worth importing the table, indexing it and then trying it.

At worst it will prove that the linking may be the problem.