Here are some recommendations on how to export data faster and more reliable.
There is a separate topic about exporting very large data sets. If that's not your case, please read below.
Preparing the Databases
When registering your databases, use FD engine if it supports your database type (available in Exportizer Enterprise). FD has some useful data options which allows flexible export configuration and sometimes speed up the exporting.
Preparing the Source Datasets
First, try to use SQL queries whenever possible (available in Exportizer Pro and Exportizer Enterprise). Yes, if you need to export a full table, it is more convenient to click it in the table list and then export. But if you need to add some data filters, then SQL code does it much more effectively. If you are new to SQL, try to start from this very simple query:
SELECT * FROM <table_name> WHERE <column_name1> = ... AND <column_name2> = ...
Here, <table_name> is the name of the table to export data from, and <column_name1> and <column_name2> are the names of columns in the table.
Second, if the source database is a remote database and the dataset is not very large, fetch all records before exporting. The fetched records are allocated in your local operating memory, so the exporting process will not interact with the server and will perform much faster.
Third, if the source database uses FD interface, try different values of Fetch size environment option to find the optimal performance.
Configuring the Data Exporting
In Export dialog, there are lot of options. Most of them concern the output format, but some of them can affect the export speed.
If the output / target format is not Database then you probably do not have much to optimize. Just ensure you do exactly what you need, without forcing the application to do the extra work.
In field mappings section, remove columns you don't need to have in the target. Note that BLOB and long text columns require more time to export.
Use calculated fields only when it is really necessary.
- The bigger the value of Commit interval, the faster is the exporting process. But rare commit requires more RAM on the target side, and can cause loss of a large part of exported data in case of export failure. So, try to find the balance between the export speed and the reliability.
- Use Memory saving mode. It exports data without opening the target dataset. Turn off this option only if your exporting process fails with the option turned on.
- Turn on the Use SQL parameters option (if available). Using SQL parameters improves the SQL code performance.
- Turn on the Batch mode option. This option is available if the parent options are turned on and when the target database was registered using FD engine (see the beginning of the topic). In this mode, SQL commands are combined in batches, which are passed and executed together. The size of the batches is directly proportional to Commit interval.