DF_TEXT Data Type Mapping in Flex2SQL - Performance Considerations

DF_TEXT fields are used in DataFlex to store text longer then 255 bytes. The number of DF_TEXT fields is limited by the maximum record length. This limit is higher in MS SQL Server then in a DAT file. There are though performance implications that should be considered before using SQL TEXT datatypes (VARCHAR(MAX), NVARCHAR(MAX) or TEXT) .

SQLFlex uses the SQL Server Native Client OLE DB (SQLNCLI-OLEDB) provider as the low-level COM API for data access, for maximum performance. SQLNCLI-OLEDB has some restrictions when using SQL TEXT columns (as well as to it’s binary cousins, VARBINARY(max) and IMAGE).

When you do a FIND, SAVE, or DELETE or call any other DataFlex command that requires data access, SQLFlex turns that into a SQL statement. SQLFlex executes the statements through cursors that are kept available in case the same command is re-executed, for added performance. But if the table accessed has at least one TEXT column, then any existing cursors cannot be reused, due to SQLNCLI-OLEDB limitations. This causes a slight slowdown, as the driver is forced to work around this restriction, by closing and reopening cursors.

There are no problems to have a table with many TEXT columns, but for maximum performance, the usage of TEXT columns should be avoided when possible. This can be done if your DF_TEXT field is smaller than 8,000 bytes, and then map DF_TEXT to SQL data types like CHAR, VARCHAR, or NVARCHAR instead of SQL TEXT or VARCHAR(MAX).

The DataFlex field data type can be defined by creating a FIELD_TYPE token in the INT file or based on the default mappings, shown in the following table:

 SQL Server Data Type                             DataFlex Data Type
 CHAR, VARCHAR or NVARCHAR <= 255 bytes           DF_ASCII
 CHAR, VARCHAR or NVARCHAR > 255 bytes            DF_TEXT
 VARCHAR(MAX), NVARCHAR(MAX), TEXT                DF_TEXT
 BINARY, VARBINARY, VARBINARY(MAX) or IMAGE       DF_BINARY

Flex2SQL has an option that you can use to specify the SQL native data type that should be used when converting a table. In the Flex2SQL menu, click on Tools -> Preferences and open the TAB Field Mappings. Assign new data types for the DF_TEXT and DF_BINARY fields and migrate you DAT file.

Let us say that you assigned a VARCHAR and a VARBINARY SQL data type to the DF_TEXT and DF_BINARY respectively as showed by the following image:

Default type

When you migrate a DAT file that has a DF_TEXT field, Flex2SQL will create it as a VARCHAR column when the field length is smaller than or equal to 8000 bytes. But if your DF_TEXT field is greater than 8000 bytes, Flex2SQL will create it as a VARCHAR(MAX) column instead.

Any way you choose to map DF_TEXT, the driver will handle it, but if speed is a primary concern, then we suggest trying to use the regular CHAR, VARCHAR, or NVARCHAR column types, whenever possible.