Fredriko


I'd best start this thread by admitting I know less than nothing about FoxPro so dummy answers would be appreciated.
I have a customer who is connecting software I have written to a foxpro database via ODBC.
The customer has created a System DSN using the Microsoft Visual Foxpro Driver v6.00.8167 that points to a "free table directory". My software simply runs a query on a table (Details.dbf).
The software works fine until the number of records hits 10K+. At this stage each single query (which only returns one record) takes at least 5 seconds to execute.
At first thought I figured the peformance was down to a lack of indexes on the table. They have since added several indexes with no improvement in performance. My suspicion is that the ODBC driver is not using the indexes. Can anybody shine any light on this and suggest possible solutions to this extremely poor performance




Re: ODBC Driver

CetinBasoz


It's really a case where dummy replies could be givenSmile Few things come to mind:

-Is it really a problem on VFP side What about network. I have seen many cases where IT personnel claimed that their network was perfect and ended it really be faulty network card/switch driver etc. One of them was very interesting, all but one computer was getting the data awfully slow. IT claimed network was right to death but one day they called me and said "Cetin, solved! It suddenly started to fly after replacing a switch on another building" (they had few connected buildings).

-Similar to the above, Novell comes on top of my suspicion list. If it's a novell netware then check that out.

-UNC paths and mapped drives. Even if you use a UNC path having a mapped drive to target changes things dramatically (no idea, why but I think pure OS issue).

-Faulty database and/or table. Hard to track this one. You might try getting the data to a table created from "scratch". Especially check table header. I have seen cases which make it really slow when reccount in header is off by 1 record (yes, it still looks like a normal table and works).

10K-20K is really a very low number for foxpro. Thus I don't think using indexes or not could be the explanation. Even w/o index 5 secs in such a table is slow. Sometimes indexes even wok against the speed of foxpro (not to mean do not use indexes, just saying sometimes excessive unnecessary indexes are not your friend).

ODBC driver is not updated since then. You may try OLEDB driver instead. OLEDB is not meant to be faster than ODBC, but at least it's updated and is aware of VFP9/8/7 data engines have much wider language support including stored procedures etc.

If none helps (maybe before checking those) the design of tables might be part of the game (ie: a single table with many fields+memos vs a table divided to multiple tables with fewer fields or SQL retrieving fewer fields). ODBC driver was not aware of delayed memo fetch AFAIK (but not sure since I don't use excessive memo fields). This might be just a dummy idea tooSmile I would test the queries from within VFP itself to check against ODBC/OLEDB driver issue.

Finally another thought is locking mechanism. Might it be that there is a deadlock situation or alike


Good luck. It's hard to nail down such issues.






Re: ODBC Driver

Cindy Winegarden

CetinBasoz wrote:
....ODBC driver is not updated since then. ....

The latest ODBC driver is downloadable from http://msdn2.microsoft.com/en-us/vfoxpro/bb190233.aspx . It's version 6.01.8629.01. It appears that Fredriko doesn't have the latest driver.







Re: ODBC Driver

CetinBasoz

Yes might be. I don't remember latest version really and didn't pay attention to built number.