Main   Products   Offshore Outsourcing   Customers   Partners   ContactUs  
JDBC Databases
  HXTT Access v5.2
  HXTT Cobol v2.1
  HXTT DBF v5.2
 
  Buy Now
  Support
  Download
  Document
  FAQ
  HXTT Excel v4.2
  HXTT Paradox v5.2
  HXTT Text(CSV) v5.2
  HXTT XML v1.2
Offshore Outsourcing
Oracle Data Import/Export
DB2 Data Import/Export
Sybase Data Import/Export
Free Resources
  Firewall Tunneling
  Search Indexing Robot
  Conditional Compilation
  Password Recovery for MS Access
  Password Recovery for Corel Paradox
  Checksum Tool for MD5
  Character Set Converter
  Pyramid - Poker of ZYH
   
   
   
Hongxin Technology & Trade Ltd. of Xiangtan City (abbr, HXTT)

HXTT DBF
FoxPro 2.10 DOS idx updates
Andrew
2015-04-19 20:20:25.0
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.0
���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.0
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.0
>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.0
>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.0
>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.0
>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.0
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.0
>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.0
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.0
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.0
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.0
>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.0
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.0
Yeah.

Search Key   Search by Last 50 Questions




Google
 

Address: 9 Station Rd., Xiangtan City, Hunan Province, P.R. China
Postcode: 411100
Phone: (86)731-58225727
Fax: (86)731-58225727
Email: webmaster@hxtt.com
Copyright © 1999-2011 Hongxin Technology & Trade Ltd. | All Rights Reserved. | Privacy | Legal | Sitemap