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
Slow updates on large foxpro table
Paul Mooney
2008-08-14 10:58:07.0
My simple application to maintain an 8.2 million record foxpro database is taking too long to update record. Seek times are fast but updates each take at 10 secords.
After 20 record updates the time take can grow to 50 seconds.

In an update only one record is updated. The record is first check to determine if it exists in the DBF. If it does it is updated otherwise the record is inserted. When records are updated the indexes ( CDX) are also updated.

Do you expect the updated to take so long with this size of table.
Re:Slow updates on large foxpro table
HXTT Support
2008-08-14 17:40:32.0
What's your query/update sql? Whether you haven't disabled transaction? Whether you haven't a suitable index expression for your where clause? Whether you are using lockType connection property for Xbase compatible mode?
Re:Re:Slow updates on large foxpro table
Paul Mooney
2008-08-15 02:11:12.0

Our Query is as follows :

UPDATE urglocal SET free=false WHERE unique=?;

We have not disabled autoCommit (presuming autocommit is true by default),

we have the following indexes in a .cdx file :

Structural CDX file: C:\VOL6\PINNACLE\URGNET\DB\URGLOCAL.CDX
Index tag: PARTIC Collate: Machine Key: PART+IC+YARDORDER F
or: .NOT.FREE
Index tag: URGID Collate: Machine Key: URGID For: .NOT.FRE
E
Index tag: UNIQUE Collate: Machine Key: UNIQUE For: .NOT.FREE
Index tag: IDUNIQUE Collate: Machine Key: URGID+STR(UNIQUE,12)
For: .NOT.FREE
Index tag: YDVSTOCKNO Collate: Machine Key: URGID+VSTOCKNO For:
.NOT.FREE
Index tag: FREE Collate: Machine Key: FREE
Index tag: DELETED Collate: Machine Key: DELETED()
Index tag: URGID2 Collate: Machine Key: URGID
Index tag: MODPART Collate: Machine Key: MODEL+PART+YEAR+YARDORDER For: .NOT.FREE


we are not currently setting any specific lock type


should we disable autoCommit ?
and/or should we set any specific properties on our Connection ?

Thanks in advance.
Re:Re:Re:Slow updates on large foxpro table
HXTT Support
2008-08-15 02:56:32.0
>Seek times are fast but updates each take at 10 secords.
>After 20 record updates the time take can grow to 50 seconds.
>UPDATE urglocal SET free=false WHERE unique=?;
Please backup your database, then zip and email us a sample with those inex files. I guess that it should be an index update speed issue.
Re:Slow updates on large foxpro table
Paul
2008-08-19 02:12:11.0
How can I take advantage of an index like
"IDUNIQUE Collate: Machine Key: URGID+STR(UNIQUE,12) For: .NOT.FREE " ?

What sort of SQL should I write to make use of this index?
Re:Re:Re:Re:Re:Slow updates on large foxpro table
HXTT Support
2008-08-25 07:16:28.0
>"IDUNIQUE Collate: Machine Key: URGID+STR(UNIQUE,12) For: .NOT.FREE " ?
where URGID+STR(UNIQUE,12)=? and not free
Re:Re:Re:Re:Re:Slow updates on large foxpro table
HXTT Support
2008-08-25 07:22:50.0
>UPDATE urglocal SET free=false WHERE unique=?;
You should use UPDATE urglocal SET free=false WHERE unique=? and not free;
BTW, you're using a very old package. You can find some new feature at here.
Re:Slow updates on large foxpro table
Paul Mooney
2008-08-26 06:27:29.0
With the CDX driver and an 8.2 million record database
running the command
update urglocal set free=true where URGID+STR(UNIQUE,12)="az33 1234567890' and NOT FREE;

Took around 200 seconds

update urglocal set free=true where URGID = "az23" and UNIQUE= 1234567890 and NOT FREE;

Takes between 1 second and 2 seconds

Using IDX driver instead gave much better results sometimes 10 ms sometimes ~2 s

We are having trouble getting consistent results but I will update this issue further when I have more results + test code.

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