How can we help you today?

Start a new topic
Discussions DbVisualizer Forums Support

Does DbVisualizer uses bulk array insert's against Informix and Oracle?

[This topic is migrated from our old forums. The original author name has been removed]

Hi, Currently when i import data into Oracle for example and i have a Commit Batch Size of 100 i see in the Oracle trace file: 1 parse for the INSERT statement. 100 times: Binding values and Execute per row. Commit 100 times: Binding values and Execute per row. Commit ... Is there a way to configure DbVisualizeer such that it does 100 Binds 1 Execute Commit; So doing a bulk array insert let's say? Thanks for help and any hints. Regards, Uwe
10 CommentsSorted by Oldest First
[This reply is migrated from our old forums.]

Re: Does DbVisualizer uses bulk array insert's against Informix and Oracle?
Uwe, There are experimental support for batch inserts in the Import feature, but it is not enabled by default. If you like to test this: 1) Make sure the Database Type is set to Auto Detect (recommended) or Oracle for your connection, 2) Open the dbvis_custom.prefs file located in the resources subfolder of the DbVisualizer installation folder, 3) Add this line at the end: dbvis.oracle.BatchImport=true 4) Restart DbVisualizer and run Import Regards Roger
[This reply is migrated from our old forums. The original author name has been removed]

Re: Does DbVisualizer uses bulk array insert's against Informix and Oracle?
Hi Roger, Thanks a lot for this tip I tested it with a Commit Batch Size of 1000. Works great. Further question: Do we have a similiar possibility for Informix? Reason behind: We have to transfer huge amount of data across both type of database systems. Why is this feature still experimental? Thanks for help and any hint. Regards, Uwe
[This reply is migrated from our old forums.]

Re: Does DbVisualizer uses bulk array insert's against Informix and Oracle?
Hi Uwe, Great to hear. To try this with Informix add this line: dbvis.informix.BatchImport=true It is experimental as it needs more broad testing. We're also looking into improving import in general which is planned for next-next feature Regards Roger
[This reply is migrated from our old forums. The original author name has been removed]

Re: Does DbVisualizer uses bulk array insert's against Informix and Oracle?
Hi Roger, After adding the additional line dbvis.informix.BatchImport=true i have the old behaviour with Oracle: Single Inserts. Meanwhile i have only dbvis.oracle.BatchImport=true in the dbvis-custom.prefs, still i have the behaviour with single INSERT's. AutoDetect is set to true for Oracle. What do i miss here? Regards, Uwe
[This reply is migrated from our old forums.]

Re: Does DbVisualizer uses bulk array insert's against Informix and Oracle?
Hi Uwe, There should not be any problem having both these properties set in dbvis-custom.prefs. May you have corrupted the linefeed characters in the file by mistake? If you have more than one DbVisualizer version installed, are you editing the file for the correct version? You can send the file to support@dbvis.com and we will have a look at it. Best Regards, Hans
[This reply is migrated from our old forums. The original author name has been removed]

Re: Does DbVisualizer uses bulk array insert's against Informix and Oracle?
Hi Hans, After several tests i detected the following rule when the batch feature for Oracle is enabled: CommitBatchSize
[This reply is migrated from our old forums.]

Re: Does DbVisualizer uses bulk array insert's against Informix and Oracle?
Uwe, defaultExecuteBatch is an Oracle specific setting which may run in before (if it have a lower number) the setting for Commit Batch Size in DbVisualizer. It is the lowest of these numbers that controls how often commit is performed. For Informix use the Commit Batch Size in DbVisualizer. Regards Roger
[This reply is migrated from our old forums. The original author name has been removed]

Re: Does DbVisualizer uses bulk array insert's against Informix and Oracle?
Hi, Meanwhile i use 9.2.19 64bit under Windows. Oracle and Bulk array insert's is fine. Still with all the recommended settings for Informix i do not see a difference in performance. For example: The same table and its structure, for 8000 rows under Oracle i need 1-2 seconds. Under Informix it takes between 15-20 seconds. CommitBatchSize is to 4000. Does it make sense to enable JDBC tracing? Regards, Uwe
[This reply is migrated from our old forums. The original author name has been removed]

Re: Does DbVisualizer uses bulk array insert's against Informix and Oracle?
Hi, So, it all works now for me. I'm using DbVisualizer 9.2.19 64bit under Windows. For Oracle and Informix i have the entries in the dbvis-custom.prefs file: dbvis.informix.BatchImport=true dbvis.oracle.BatchImport=true Regarding Oracle i do NOT use the defaultExecuteBatch setting. For both databases i have defined a CommitBatchSize=4000. In addition for fetching data i use for both databases the JDBC driver setting of: java.sql.statement.setFetchSize=10000 I did this via the Tool Properties, so for every new connection i have the setting. For the Informix Connection in question i used the following enviroment settings mentioned in the JDBC Guide: ENABLE_TYPE_CACHE=TRUE;FET_BUF_SIZE=1048576; Thanks again for your help. Regards, Uwe
[This reply is migrated from our old forums.]

Re: Does DbVisualizer uses bulk array insert's against Informix and Oracle?
Hi Uwe, Great to hear it's working now, and thanks for sharing your setup. Best Regards, Hans
Comments to this discussion are now closed!
More topics in Support
See all 1319 topics