Performance Tips¶

Use REREAD¶

Use REREAD in your code to explicitly list the file(s) you’re interested in. If no files are named as arguments to the REREAD command, all opened tables are locked and their record buffers are refreshed.

Reread FILE
    Move some_value to NEWFILE.Field
    Saverecord NEWFILE
Unlock

Use Data Dictionaries with Smart File Locking¶

DataFlex includes a data dictionary layer to store business rules and validations. Besides providing database validation, data dictionaries employ “smart file mode” technology.

Before a save or a delete, the data dictionary determines which files need to be locked. Using smart locking will help determine which files need to be locked and can dramatically improve database processing speed.

Move Filtering to the SQL Server¶

If you have code that loops through records in a table and you’re only interested in a subset of the data, an SQL filter will allow you to move the filtering mechanism from the client to the server. This cuts down on the amount of data that needs to be transferred to the client.

For example, say that you’re looping through your CUSTOMER table in alphabetical order to prepare a report, but you’re only interested in customers with a balance of between $1000 and $2000. A standard way to accomplish this would be through something similar to:

Repeat
    Find Gt Customer by Index.2 // By Customer.Name
    If (Found) Begin
        If ((Customer.Balance > 1000) and (Customer.Balance < 2000))
        DO SOMETHING
    End
Until (not(Found))

In this case all customer records are transferred to the client, when in reality we are only interested in records with a balance value in a certain range. We can move the filtering to the SQL Server:

Use mertech.inc

SET_SQL_CONSTRAINT of Customer.File_Number to “and BALANCE > 1000 and BALANCE < 2000”
SQL_CONSTRAINT OF Customer.File_Number to ACTIVATE

Repeat
    Find Gt Customer by Index.2 // By Customer.Name
    If (Found) Begin
    DO SOMETHING
    End
Until (not(Found))
SQL_CONSTRAINT_OF Customer.File_Number to DEACTIVATE

You may also choose to create indexes by hand on your SQL Server to support your filters or other embedded SQL commands. The Mertech drivers will co-exist with and use indexes that are not explicitly defined in DataFlex.

Support for SQL filters at the DDO level¶

DataFlex 18.0 introduced support for SQL filters at the DDO level. Now, in addition to using custom commands in the driver, SQL filters can be used as a part of normal DataFlex programming. Flex2SQL v14.0 and higher fully supports this new DataFlex feature.

Identify Missing Indexes¶

A common cause of application performance problems is missing indexes. One reason for a missing index is that duplicate row errors occurred during the migration process. The Flex2SQL Classic Migration Utility Index Maintenance option can check whether the indexes for each file were created properly.

Defragment the Database¶

If a query is taking a lot longer to compete than before, even though nothing in it has changed, database fragmentation may be an issue. This is especially true if the SQL database is set to grow by a fixed number of megabytes instead of a percentage.

If finds are slow, but the indexes are reported to be fine by Flex2SQL, please consult the documentation for your SQL Server on how to rebuild your table and index spaces.

Recompute Index Statistics¶

Index selectivity is a number that determines the effectiveness of an index. Index selectivity can be calculated:

The best possible index selectivity is when all records in a table have a different value for the columns in the index (this is typical for primary keys and unique constraints).

It is inefficient to maintain an index that has a low selectivity value. Most SQL databases include an optimizer that determines when to use an index and when it is faster to ignore the index and do a scan instead. Indexes with low selectivity values may be ignored even though they are included in a WHERE clause.

If large chunks of data have been inserted into your table, ask your SQL Server to recompute the index statistics and then modify or remove superfluous indexes.

Move Business Logic to the SQL Server¶

Significant performance improvement can be seen by rewriting some batch processes in SQL. This can be either as embedded SQL statements, or as a stored procedure or function for execution directly on the server.

A simple example is a business process that computes the outstanding balance for all customers. In a record oriented scenario, all the records have to be brought over to the client in a loop that computes a running total.

This can instead be turned into a short snipped of SQL code that executes fully on the server, with minimal network traffic and interaction:

SQL_SET_STMT to “SELECT SUM(BALANCE) from CUSTOMER”
SQL_PREPARE_STMT
SQL_EXECUTE_STMT
SQL_FETCH_NEXT ROW INTO iBalance

Please note that not all business logic will see a performance improvement by moving to embedded SQL. Embedded SQL that simply returns a large record set will not show significant gains over fetching the same records through a regular FIND GT loop.

Identifying those business processes where a large portion of the logic can be handled by the SQL Server to keep network traffic down.

Consider Cache Performance¶

The Mertech driver monitors the database operations you perform in real time, and adjusts the number of records and columns it fetches and caches. One scenario that can be detrimental to the cache is looping through records, while doing individual lookups on the same table. This causes caches to be flushed.

Repeat
    Find gt customer by Index.1

    // DO SOMETHING WITH THE CUSTOMER, LIKE LOOPING THROUGH CHILD RECORDS
    // BASED ON SOMETHING HERE, YOU REALIZE YOU WANT TO LOOK UP ANOTHER
    // CUSTOMER, AND THEN RETURN BACK TO THE LOOP
    Move Customer.Recnum to iSavedRecnum
    // Find the record of interest
    // the previous cache from the FIND GT is discarded
    Move something to Customer.Recnum
    Find eq customer by Recnum

    // DO SOMETHING WITH THE CUSTOMER
    // Return to the previous position in the loop, no rows are cached
    Move iSavedRecnum to Customer.Recnum
    Find Eq Customer by Recnum
until (not(found))

Reseeding of the key buffers during a FIND GT leads to cache clearing.

The workaround in this case may be to perform the separate customer lookup through embedded SQL. Or, to move the whole process to embedded SQL, if it cannot be performed separately from the loop.

Use Lazy Open Mode¶

The lazy open mode feature is designed to improve an application’s initialization performance by deferring table setup. Lazy open mode delays the setup of table metadata (table, columns, indexes description) in memory until a field is accessed, even if an OPEN command is issued.

Lazy open mode is particularly useful for applications that open a large number of files when the program is launched. For example, if a program opens 30 files but only 10 are accessed immediately, then the metadata for only those 10 tables is fetched from the server at application initialization. The remaining tables are not opened until an action outside of open is performed on them.

Lazy open mode is disabled by default. Lazy Open mode can be enabled by either calling a command or through the MERTECH.INI file.

[MSSQL]
LazyOpenMode yes
[PGSQL]
LazyOpenMode yes