|
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
|
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)
|
>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.
|
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.
|
|
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.
|
Main
webmaster@hxtt.com