When a list of records (example list of incidents) is Exported, the exported records are duplicated. If we increase the size of NX_EXPORT_FETCH_GROUP_SIZE in NX.env file, the problem would be resolved. How does the NX_EXPORT_FETCH_GROUP_SIZE work?

Document ID : KB000017689
Last Modified Date : 14/02/2018
Show Technical Document Details

Description:

This document explains how the variables 'NX_EXPORT_FETCH_GROUP_SIZE' and 'NX_EXPORT_MAX_FETCH_ROWS' work and how to determine their sizes.

Solution:

The variable NX_EXPORT_FETCH_GROUP_SIZE sets the number of the fetched record for each group. So theoretically, if we want to export 30000 records and both variables NX_EXPORT_MAX_FETCH_ROWS and NX_EXPORT_FETCH_GROUP_SIZE are set to 30000, the records will be exported without duplicated rows. However, if NX_EXPORT_FETCH_GROUP_SIZE is set to very large, the export would fail.

e.g. When there are 5000 records we want to export and each variable is set as shown below.

NX_EXPORT_FETCH_GROUP_SIZE=300
NX_EXPORT_MAX_FETCH_ROWS=5000

In this case, 17 groups will be created to fetch 5000 rows, i.e. when exported records, almost same query will be run 17 times and each query fetches 300 records (the 17th query will fetch 200 records.) Each executed query has 300 conditions. (The value "300" is from NX_EXPORT_FETCH_GROUP_SIZE.) If you set 30000 as NX_EXPORT_FETCH_GROUP_SIZE, 30000 conditions will be added to one query and the length of the query becomes very long.

As a result, if the number of group increases, the query length executed by the Service Desk Export function may become over the database server limitation/setting. If it happens, the critical error will occur and nothing will be exported. (The export will fail.) So we cannot guarantee that all data can be exported if NX_EXPORT_FETCH_GROUP_SIZE is set to 5000. Also, if it is set to very large size, performance problems may occur too.

The maximum size of @NX_EXPORT_FETCH_GROUP_SIZE depends on what queries will be posted by the export, the database setting and environments (for performance problem). So we cannot say what the best size is for them. You would have to test the export function and try to find the appropriate size for your server.