Export Data to another Database - Improving

[bdemarchi]

Hi,

I am using DBeaver to migrate data between databases.

The process works correctly and I love it that I can do it through Eclipse interface and without confusion.

I would like to suggest 2 features to improve even further that amazing feature:

1. Add multiple rows with 1 single INSERT statement.
Most databases allow users to add multiple rows using INSERT command.
I am aware that there are differences between the syntax between databases but that feature allows a MUCH faster migration process.
For instance, I did a test on my MySql database, one single INSERT statement with 500K lines took only a couple seconds to be processed.
I know that would not be advisable through Eclipse, but it would work perfectly within certain constraints and also observing the target database limitations.
This should speed up migration process by a great deal.


2. Allow users to configure the DATA TYPE MAPS for each database.
I am not aware of how the Export Data > Database works to suggest the types on the target database, but I am getting errors on some of the suggestions.
For instance, when migrating from Ingres to MySql, Ingres MONEY type is suggested to be MySql NUMERIC type. I get the following error "SQL Error [1264] [22001]: Data truncation: Out of range value for column...".
If you have only 1 table to migration, then the current interface where the user can select the type of each column is great and we do not need anything else.
But, when you select hundreds of tables then you get to a error prone situation if the user needs to be checking each table column to ensure the correct type is selected on the target database.
This feature would allow users to map the types between 2 databases. Doing this once would eliminate or minimize the tedious error prone process of checking each table columns type.
Another way this could be done, allow the user to specify the types he wants to control, in the example above, I would only add the map for MONEY > DECIMAL(19,4).

I am a good Java developer, working with it since 2000, let me know if you would like some assistance with this, I have the time and the energy to help if you so wish.

Thanks,
Fabricio

[Serge]

Hi,

1. There is a reason why DBeaver uses separate INSERT statements for different lines. When there are NULLs in some row columns DBeaver never insert them explicitly but just skips them in INSERT/VALUES. Just because target database may have some DEFAULT for this column(s) or some BEFORE INSERT trigger which will set correct value according to predefined database logic. Some earlier version of DBeaver inserted explicit NULLs and it led to a problems.
Also, DBeaver reuses INSERT statement if combination of NULLs is the same for different rows. Also DBeaver uses JDBC insert batches. It depends on driver - how to implement statement reuse. Good drivers rebinds values and uses batches to insert multiple rows at once, simple drivers (e.g. MySQL) just generates separate INSERT statement for each row. I can't affect drivers behavior though.

2. You can configure target column(s) data types. Click on a table then on "Columns mapping" button. Here you can manually change target data type of any column.
Although I agree that having a better automatic data-type mapping algorithm is a big deal. If you have suggestions on this - please say.

[bdemarchi]

Hi,

2. That is ok, it was just a suggestion, since doing a mapping for each table does take a long time, it would be alot easier if users could specify the mapping for all the types of the selected tables other then setting it to each table. Regarding the algorithm, I don't see that as a major issue, with the feature I am talking about users would probably correct any type mapping only once for the whole export.

1. I understand, I saw that during migration dbeaver is using binds to insert data, and I agree, the JDBC driver should handle the performance of each command you send.
My suggestion is actually completely different from what you do. It would take a more specialized approach since different database do have different sintax for inserting batch data.
Lets look to examples and see if that give you some ideas.

In SQL Server or MySQl you can insert multiple rows like this:
INSERT INTO XXX (col1, col2, col3) VALUES (A,B,C) , (D,E,F), (G,H,I), (J,K,L), ... ;

With that sintax one single insert is run at the database side to insert multiple rows, a lot faster then sending 1 INSERT statement at a time to the database server.

My experience, as I said before, shows that this is MUCH faster then what dbeaver is currently using. There is just no way you can compare performance of both approaches, they are dramatically different.