FoxPro 2.10 DOS idx updates |
Andrew |
2015-04-19 20:20:25 |
In our system all inserts and update/modifications are only done with the Foxpro app. We want to use the Hxtt driver for additional functionality but will only be reads (SELECT).
In order to speed up these reads we want to create additional indices (idx) for columns that are joined on and/or filtered on in the WHERE clause.
Is there a way to have the new indices update when data is inserted via the Foxpro app without modifying any of the legacy Foxpro code? Currently only the original indices created from the DOS app get updated and the new ones don't get updated without manually running a reindex after every update (obviously unworkable)
|
Re:FoxPro 2.10 DOS idx updates |
HXTT Support |
2015-04-19 21:04:58 |
〉Is there a way to have the new indices update when data is inserted via the Foxpro app without modifying any of the legacy Foxpro code?
Only tableName.cdx can be updated automatically by your Foxpro app.
|
Re:Re:FoxPro 2.10 DOS idx updates |
Andrew |
2015-04-20 17:49:03 |
That is unfortunate, yet completely understandable.
Here is our dilemma though: running a simple SELECT query with an INNER JOIN on 2 columns - tableA ~13k rows and tableB ~165k rows. Using an older ODBC driver I can retrieve the last few months orders (~250 records) in under 150 ms, but with Hxtt driver it times out after 50 seconds:
SELECT r.REF,r.PONO,d.QTY,d.COST
FROM "po-rec" AS r
INNER JOIN "po-dtl" AS d
ON r.REF = d.REF
WHERE r.RECEIVED <> 1
Both REF columns are similar numeric column types.
If I remove the IDX files (PO-REC.IDX and PO-DTL.IDX) completely from the directory, the ODBC driver performs at same speed, the Hxtt is still painfully slow or times out. So I know it has nothing to do with indexing.
If I create indices on both REF columns I get adequate performance from the Hxtt driver, but seeing as these cannot be updated in the Foxpro app, this will not do.
Is there any reason an old ODBC driver without any indices at all would outperform the Hxtt driver so significantly in this type of scenario?
|
Re:Re:Re:FoxPro 2.10 DOS idx updates |
HXTT Support |
2015-04-20 18:02:27 |
>If I remove the IDX files (PO-REC.IDX and PO-DTL.IDX) completely from the directory, the ODBC driver performs at same speed, the Hxtt is still painfully slow or times out. So I know it has nothing to do with indexing.
Then it's simple issue. You should use a CGP file to contains PO-REC.IDX and PO-DTL.IDX so that HXTT DBF can find those index files. Please read 6. The ntx can't be updated when I update the DBF file.
|
Re:Re:Re:FoxPro 2.10 DOS idx updates |
HXTT Support |
2015-04-20 18:07:03 |
>Is there any reason an old ODBC driver without any indices at all would outperform the Hxtt driver so significantly in this type of scenario?
Because VFP driver will use large memory and tempory index to match it. If you use delayedClose=120 connection propery, HXTT DBF will utilize automatic temporary index for your join sql.
|
Re:Re:Re:Re:Re:FoxPro 2.10 DOS idx updates |
Andrew |
2015-04-20 22:19:06 |
>Then it's simple issue. You should use a CGP file...
I think you misunderstand. Please read the first part of the post. All of the data in these files are ONLY able to be inserted and updated via the FoxPro DOS app. We can use Hxtt driver only for SELECT queries. We do this for added reporting features, etc.
So any additional indices we can create, and include via CGP files, that actually DO speed up these queries are useless because they do not get updated when the Foxpro app inserts/modified data. That was the basis for our initial question. We removed the IDX just to compare the 2 different drivers when no indices were present so that we could rule IDX file out as reason why your JDBC driver was so much slower than the ODBC driver.
|
Re:Re:Re:Re:Re:FoxPro 2.10 DOS idx updates |
Andrew |
2015-04-20 23:01:44 |
>Because VFP driver will use large memory and tempory index to match it.
So I added delayedClose=120 and the basic SELECT still times out. So I am not sure what is the issue? The difference is ~100ms to a timeout, so it's very big.
Interestingly when I create brand new indices, e.g.:
USE PO-REC
INDEX ON REF TO PO-REC
USE PO-DTL
INDEX ON REF TO PO-DTL
sing the query in the original post the ODBC driver is about ~100ms and the Hxtt is ~2300ms.
|
Re:Re:Re:Re:Re:Re:Re:FoxPro 2.10 DOS idx updates |
Andrew |
2015-04-20 23:22:48 |
OK - if I remove the "lockType=FOXPRO" then the query goes from timeout (with no IDX) or several thousand ms (with IDX) to just a couple hundred ms with/without IDX. Still not as fast as the ODBC, but certainly close enough.
However, we need to run simultaneously with the Foxpro app so as I understand it, we *MUST* use lockType=FOXPRO, correct? However the performance hit makes it unusable?
There must be something we are doing incorrectly?
|
Re:Re:Re:Re:Re:Re:Re:Re:FoxPro 2.10 DOS idx updates |
HXTT Support |
2015-04-21 05:37:45 |
>we *MUST* use lockType=FOXPRO, correct?
For select/readony purpose, lockType=FOXPRO is not necessary. With it, it has to wait Foxpro application to release lock on index file for write.
|
Re:Re:Re:Re:Re:Re:Re:Re:FoxPro 2.10 DOS idx updates |
Andrew |
2015-04-21 22:44:36 |
Interesting. However, the huge differences in performance I mention above though are on dev machines that do not have the Foxpro app on the... they only have copies of the DBF and IDX files. So there is no Foxpro app that Hxtt is waiting on to release an IDX file?
Of course, in production, the Foxpro app would be there but in these tests that is not the case. So does the lockType=FOXPRO setting slow down SELECTS significantly even when there is no other Foxpro app using the data?
|
Re:Re:Re:Re:Re:Re:Re:Re:FoxPro 2.10 DOS idx updates |
HXTT Support |
2015-04-22 00:05:36 |
|
With lockType=FOXPRO setting, it hasn't data cache and will lock/unlock many times.
|
Re:Re:Re:Re:Re:Re:Re:Re:FoxPro 2.10 DOS idx updates |
Andrew |
2015-04-22 09:14:26 |
OK - that makes sense!
Last question on this then: with the lockType removed from the connection string, will the Hxtt driver (used for SELECT only) ever lock up the db so that the Foxpro app has issues? My understanding was that the lockType setting worked both ways: it protected the Foxpro app from Hxtt and protected Hxtt from Foxpro.
In the past with the ODBC driver (using JDBC-ODBC bridge) sometimes the driver would lock up the db and prevent the Foxpro app from working. The only solution would be to restart Tomcat.
This is actually one of the main reasons that we are have been investigating Hxtt!
|
Re:Re:Re:Re:Re:Re:Re:Re:FoxPro 2.10 DOS idx updates |
HXTT Support |
2015-04-22 16:44:49 |
>with the lockType removed from the connection string, will the Hxtt driver (used for SELECT only) ever lock up the db so that the Foxpro app has issues?
Then HXTT DBF doens't lock those data files, and Foxpro app won't met any issue if HXTT DBF doesn't modify those data rows.
>with the ODBC driver (using JDBC-ODBC bridge) sometimes the driver would lock up the db and prevent the Foxpro app from working.
Yeah. It's deadlock. With lockType=FOXPRO, HXTT DBF can also lock db, but you can use lockTimeout connection property to avoid deadlock.
|
Re:Re:Re:Re:Re:Re:Re:Re:FoxPro 2.10 DOS idx updates |
Andrew |
2015-04-22 17:48:14 |
OK - so to sum it up (for our use case):
1 - remove lockType for all SELECT queries and we should have no issues with performance and no deadlocks as a rsult either.
2 - if we ever do wish to use Hxtt to insert/modify data, then use the lockType=FOXPRO, but set up a lockTimeout.
Right?
|
Re:Re:Re:Re:Re:Re:Re:Re:FoxPro 2.10 DOS idx updates |
HXTT Support |
2015-04-22 17:49:43 |
|
Yeah.
|
Main
webmaster@hxtt.com