Data Export Recommendations - Exportizer Documentation

Here are some recommendations on how to improve your data exporting performance, i.e. 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 the source database, ADO is quite good interface to choose. If you have problems with it, Exportizer Enterprise offers FD interface.

When registering the target database (when exporting to Database), consider using 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 can significantly speed up the exporting.

Preparing the Source Datasets

Try to use the SQL query tool 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.
If the source database is a remote database and the dataset is not very large, fetch all records before exporting (e.g., by clicking the Last Record button). 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.
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.

So please consider the following:

If Use raw data option is available, and if you sure the data conform to the target format, apply it. For example, when exporting to HTML, it is important that all double quotes (") and angle brackets (< >) in the source data are converted to so called HTML entities; but if you sure there are no such characters in your data, turn on this option to avoid unnecessary conversions.
If the target format supports colors and fonts (e.g. HTML or Excel (XLSX)), and you don't care about colors and fonts, turn off the corresponding options.
If the output / target format is Database (available in Exportizer Pro and Exportizer Enterprise), then you have more options to consider:
- 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.
In the 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 (available in Exportizer Pro and Exportizer Enterprise) only when it is really necessary. Calculated field is a field, where formula is used to get the field value, for example: dataset_field_val(1, 'price') * dataset_field_val(1, 'count'). Please do not confuse Exportizer calculated fields (application level) with calculations you do in SQL (database level); always prefer database level calculations if possible.
- If you export data to a database, and your export mode involves creating/recreating the target table(s), check the target columns' attributes carefully.
  If you are automating the exporting process, save the field mappings to file and then use the reference to that file in the command line or action file. If you use the short command line form of the field mappings (/FieldMappings parameter), the applications spends additional time to choose the correct target attributes and there is some risk that those attributes will be chosen incorrectly. In contrast, the mappings file contains all target attributes, so it is recommended to use it (/FieldMappingsFile parameter) whenever possible.