Main   Products   Offshore Outsourcing   Customers   Partners   ContactUs  
JDBC Databases
  HXTT Access v5.2
 
  Buy Now
  Support
  Download
  Document
  FAQ
  HXTT Cobol v2.1
  HXTT DBF v5.2
  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 ACCESS
Speed degrades at higher record numbers.
William Wedeman
2006-04-27 13:30:01.0
The following is a partial log from an insert into an access db created by the HXTT jdbc driver. Each entry represents 100 records inserted. Notice the drop in speed from 44400 records on. I have tried all versions of the jdbc driver with the same result. Cpu usage also jumps very high at this point as well. I have tried many different settings in both java and with HXTT driver to no avail. bling|42900|Thu Apr 27 14:58:14 CDT 2006 bling|43000|Thu Apr 27 14:58:15 CDT 2006 bling|43100|Thu Apr 27 14:58:15 CDT 2006 bling|43200|Thu Apr 27 14:58:15 CDT 2006 bling|43300|Thu Apr 27 14:58:16 CDT 2006 bling|43400|Thu Apr 27 14:58:16 CDT 2006 bling|43500|Thu Apr 27 14:58:16 CDT 2006 bling|43600|Thu Apr 27 14:58:16 CDT 2006 bling|43700|Thu Apr 27 14:58:17 CDT 2006 bling|43800|Thu Apr 27 14:58:17 CDT 2006 bling|43900|Thu Apr 27 14:58:17 CDT 2006 bling|44000|Thu Apr 27 14:58:18 CDT 2006 bling|44100|Thu Apr 27 14:58:18 CDT 2006 bling|44200|Thu Apr 27 14:58:18 CDT 2006 bling|44300|Thu Apr 27 14:58:18 CDT 2006 bling|44400|Thu Apr 27 14:58:19 CDT 2006 It gets progressively slower and slower from this point forward. bling|44500|Thu Apr 27 14:58:31 CDT 2006 bling|44600|Thu Apr 27 14:58:45 CDT 2006 bling|44700|Thu Apr 27 14:58:58 CDT 2006 bling|44800|Thu Apr 27 14:59:12 CDT 2006 bling|44900|Thu Apr 27 14:59:25 CDT 2006 bling|45000|Thu Apr 27 14:59:39 CDT 2006 The structure looks as follows: Trans_date Long Int Trans_code Int Trans_amt Dec Trans_time Int checknum text 9 user_name text 12 serv_prov text 4 carr text 4 servind text 2 tranid Long Int custactnr text 16 Thank you for any help Bill Wedeman Systems Analyst 210 576 2163
Re:Speed degrades at higher record numbers.
William Wedeman
2006-04-27 13:55:37.0
Additonal Info.

Downloaded most recent driver. Now performance does not degrade. It abends. Here is the error.

bling|44400|Thu Apr 27 15:50:17 CDT 2006

*** SQLException caught ***

SQLState: 60000
Message: Failed to insert a new record into table ledger! For more information
, please use SQLException.getNextException().
Vendor: 393216

SQLState: 22000
Message: A corrupted data page 543
Vendor: 139264

Exception in thread "main" java.lang.NullPointerException
at udp.buildmeta.main(buildmeta.java:199)

Re:Re:Speed degrades at higher record numbers.
HXTT Support
2006-04-28 02:39:20.0
>Downloaded most recent driver. Now performance does not degrade. It abends.
Recurred. The bug only in the latest package has been fixed.

>It gets progressively slower and slower from this point forward.
Recurred and adjusted code for frequent insert.

Please download the latest package. Thanks for your valuable report.

Speed degrades at higher record numbers.
William Wedeman
2006-05-12 12:12:33.0
Driver is much faster Thank you.

I had another error however.

The job had loaded approx 700 thousand records and db was approximately 144 MB when the following error occurred. (FYI export files can be up to 200 MB and over 2 million records, well withing access limits). The lines below repeated themselves hundreds of times. Even using various methods I was unable to capture the beginning lines of the error. Program abends 952 records into loading an 8000 record table. Numerous tables have been loaded before this one is loaded. If the table is loaded by itself, it loads with no error, so I think the data is ok.

at com.hxtt.sql.access.s.if(Unknown Source)
at com.hxtt.sql.access.i.a(Unknown Source)
at com.hxtt.sql.access.i.if(Unknown Source)
at com.hxtt.sql.access.i.a(Unknown Source)
at com.hxtt.sql.access.s.if(Unknown Source)
at com.hxtt.sql.access.i.a(Unknown Source)
at com.hxtt.sql.access.i.if(Unknown Source)
at com.hxtt.sql.access.i.a(Unknown Source)
at com.hxtt.sql.access.s.if(Unknown Source)
at com.hxtt.sql.access.i.a(Unknown Source)
at com.hxtt.sql.access.i.if(Unknown Source)

My purpose for this tool was to provide a means for someone to download selected data to a single file, into a transportable Access database format (MDB) so that the data could be easily sent via ftp or cd. Data is written once but read many times.

You had mentioned on your web site that you were open for suggestons. A load tool I think would be most beneficial. Load would be fast because writer would have exclusive right to file until completed. For example,

A tool simulating Microsoft Access Import could pull data from a source and write to an access file. Parameter could be a list or '%' for all, a source connection and a parameter to control append or overwrite.

Another way would be like the mysql Load sql statement:

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'
[REPLACE | IGNORE]
INTO TABLE tbl_name
[FIELDS
[TERMINATED BY 'string']
[[OPTIONALLY] ENCLOSED BY 'char']
[ESCAPED BY 'char']
]
[LINES
[STARTING BY 'string']
[TERMINATED BY 'string']
]
[IGNORE number LINES]
[(col_name_or_user_var,...)]
[SET col_name = expr,...)]

Again, required resources are minimized since the data is not available until load routine is finished with it. The information is parsed and written directly to the file with minimal overhead -- very fast a hundred or so thousand records per minute.

Just some thoughts.

Thank you for your time.
Re:Re:Re:Re:Speed degrades at higher record numbers.
HXTT Support
2006-05-13 00:29:18.0
Thanks. Recurred by inserting more than one million rows, and fixed a bug in inserting row into mdb file with 127MB size. Please download the latest v1.1.036 package. One team of HXTT is developing a migration tool between MS Access and Oracle/MS SQLServer, and I have forwarded them your valuable suggestion.

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