Access Connector: Java heap space

KNIME Analytics Platform
1

Hi,

is it just me, or does the current Microsoft Access Connector result in an

ERROR Microsoft Access Connector 3:1 Execute failed: Java heap space

with bigger .mdb files? Bigger means > 1,2 GB.

I am not able to get any connection. I’ve tried Access and DB Connector with the latest driver.

image
image658×241 7.54 KB

Thanks for any hint.

Tried on Win10 and 11 with 5.8.3.

1 Like
2

Hi @Awiener ,

here are a few things you can try. First thing is to increase the memory allocated to KNIME. Also you could try and make sure the MS Access DB does not contain any unwanted garbage and is compressed (https://support.microsoft.com/en-us/office/compact-and-repair-a-database-6ee60f16-aed0-40ac-bf22-85fa9f4005b2)

  • Locate knime.ini: This is in your KNIME installation folder.

  • Edit -Xmx: Find the line starting with -Xmx (e.g., -Xmx2048m). Increase it to at least -Xmx8g (8GB) or more if your machine allows.

  • Restart KNIME for this to take effect.

Check out the Official Guide KNIME Installation - Changing Memory Settings for further information.

Then you could try and deactivate the “retrieve in configuration” setting in the Connector node. If this is active it will try and make sure that every setting and column is present before you are able to access it, which is useful but might slow down or hinder things with really large databases. The downside is you would have to know what you are doing without additional checks.

image
image996×994 33.8 KB

You might see if there are further tweaks to the connector settings. Especially concerning timeouts (increase the time KNIME has to finish the operation).

And then you might want to make sure there is a maximum of Java Heap space available:

  • So do not run any other workflows

  • Make sure the workflow is as small as it gets

Hope that helps,

Sena

4 Likes
3

@Awiener the hint of @NurSenaAlici to COMPACT the database is the right approach. Also make sure you do not hit any restrictions that MS Access might have.

further down in this thread there are options to handle larger local databases if you do not have to use MS Access.

Another option would be Duck DB

3 Likes
4

Thank you @NurSenaAlici and @mlauber71 .

Creating the connection takes ages, so I’ve enlarged -Xmx. That helped. However performance is still bad, so I‘ve “converted” the .mdb to .sqlite with the help of this workflow:

Now we work with the .sqlite which is tremendously faster in reaction. As is it just a minor data source, that works for the workflow.

3 Likes
5

@Awiener glad you found a solution and SQLite is a good choice. If you need an alternative for also large datasets or different type handling H2 is also a nice local option where you can split very large datasets across several local files if necessary.

2 Likes
6

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.