0

I'm currently trying to resolve an issue related to a program written by someone else that uses Jackcess version 1.1.8 to write information out to an Access database. In a run which should add 4 million rows to a given Access table, the following exception occurs:

08/29/2016 06:01:47 | ERROR | java.lang.IndexOutOfBoundsException
at java.nio.Buffer.checkIndex(Unknown Source)
at java.nio.HeapByteBuffer.getInt(Unknown Source)
at com.healthmarketscience.jackcess.ReferenceUsageMap.addOrRemovePageNumber(ReferenceUsageMap.java:82)
at com.healthmarketscience.jackcess.UsageMap.addPageNumber(UsageMap.java:201)
at com.healthmarketscience.jackcess.Table.newDataPage(Table.java:761)
at com.healthmarketscience.jackcess.Table.addRows(Table.java:714)
at com.healthmarketscience.jackcess.Table.addRow(Table.java:660)

From what I've seen online, it appears that the current version of Jackcess is 2.*, so is this something that can be solved by using the latest version, or is there a bigger problem, i.e., is 4 million too many rows for an Access table? Alternatively, is there a way to add rows via a Cursor object that would be less memory-intensive?

Sheldon R.
  • 452
  • 2
  • 7
  • 26
  • No, 4 million is not too many rows for an Access table *per se*. However, if your database file is hitting the 2 GB limit then that would cause problems. As for something "less memory-intensive", the exception does not indicate a problem with JVM memory (e.g., "out of heap space"). Does this application create a new database file, or does it write to an existing one? – Gord Thompson Aug 30 '16 at 19:52
  • @GordThompson, the code in question creates a Table object then calls its addRow() method, so my assumption is that the Table object is too big at some point to fit in memory, hence the exception... – Sheldon R. Aug 30 '16 at 20:09

1 Answers1

1

If the application in question is still important to your business then you need to update it to use a current version of Jackcess. You've gotten over 9 years out of it so far (Jackcess 1.1.8 was released in February 2007) and things have progressed a little bit since then.

Notably, the current version of Jackcess supports DatabaseBuilder#setAutoSync(false) which disables the row-by-row flushing of updates to the database file. (I searched the Jackcess 1.1.8 source code for 'autosync' and found no matches, so I assume that it wasn't supported back then.)

For a test insert of 100,000 rows using Jackcess 2.1.3, setAutoSync(true) (the default) took around 200 seconds, while the same code with setAutoSync(false) took 8 seconds.

A test insert of 4,000,000 rows with setAutoSync(false) took around 220 seconds, or just over 3.5 minutes. Based on the numbers above, the same operation without setAutoSync(false) would be expected to take somewhere on the order of 5,500 seconds, or 1.5 HOURS, to accomplish the same thing.

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
  • 1
    In light of the earlier conversation with the other commenter, I appreciate the gentle nature of your response, @GordThompson :) Turning off autosync could help with another issue I only talked about indirectly: the slowness of this application. For the creation of the table of the 4-million-row table, the program ran for four days before crashing. Based on your test results yielding a run time that's 25 times better than those for the default mode, that should have this program finishing in hours, not days. I'll keep you posted... – Sheldon R. Aug 31 '16 at 03:34
  • I updated the program to use the latest version of Jackcess, and the good news is that it ran in 14 minutes instead of four days. But the bad news is, it still crapped out. But at least it was more informative about what the problem is: the 2G size limit for Access databases has been hit. I saw on the MS-Access website that tables in can be linked from one database to another. Do you happen to know if Jackcess enables you to do so? – Sheldon R. Aug 31 '16 at 19:12
  • Yes, Jackcess can *work with* a database containing tables that are linked to tables in another Access database, and on Windows it is quite transparent. (On non-Windows machines we need to create a class that implements LinkResolver to reconcile the Windows path in the table link with a non-Windows path.) I don't recall seeing a mechanism in Jackcess to *create* a table link in a database, but a workaround for that would be to create a "template" database in Access with the required table links and start with that instead of a completely new empty database file. – Gord Thompson Sep 01 '16 at 12:47