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 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 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.
Second, 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.
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.
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.