Performance considerations

Speed is the most important feature of any data access component. Reporting, ETL or other data intensive processes require fast response from the system. With careful approach Vultar ODBC driver for TAFC/T24 is able to provide excellent results in terms of performance. Technically, each SQL request can be devided in two phases: jQL query and data transportation. Below we will describe in details which options are available in order to optimize both phases.

How can we speed up an SQL request?

To answer this question, it is important to understand which internal process is actually slowing down the request. There are several major factors that could affect the performance of the SELECT statement:

• jQL engine performance
• Data throughput rate
• EVAL function and I-Descriptors
• ODBC Application Server

To find out which process is the bottleneck, launch the Administrator tool and watch [Activity Monitor] page. The real time metrics may help identify the bottleneck of each SQL request:

[Status] column:

<Idle> - the user is connected, but does not perform any activity
<Querying database> - the driver is waiting a reply from jQL SELECT
<N records (X%) fetched> - the query is in data transportation phase

[TC/PS/RPS] column:

<TC> - number of threads collecting jBASE data packets from the pool.
<PS> - pool size. The number of jBASE data packets waiting in queue
<RPS> - data throughput rate (records per second)

In perfect conditions, <RPS> should be high, <PS> level varying but without constant growth, <Querying database> phase being short.

Please note, <TC> value represents the number of threads responsible for pool sweeping (reducing <PS> level). The number of threads may automatically increase (up to the number of CPU cores on the application server) in order to keep the <PS> value low.

jQL SELECT is the most important phase of any SQL request performed by the driver. The speed of a jQL SELECT depends upon file size, type, the presence of secondary indexes and/or concat files, and how busy is the jBASE server at a given moment.

While file size and type can not be changed in most of the cases, the use of jBASE indexes and concat files may dramatically improve the performance. However, jBASE indexes have to be applied with great care, due to the performance impact on file updates. According to Temenos standards, T24 files should not be indexed. In such cases, the use of concat files is the only way to significantly speed up requests on large files.

Vultar ODBC driver has a unique built-in feature that permits linking of concat files with actual data files. Please read more about the use of concat files in this chapter.

Next major impact on data delivery speed is driver’s data throughput rate. And this is the area where the driver shines most. It has the ability to extract, transform and send result sets from jBASE to application server in multiple parallel streams. On multi-core servers, the benchmarks grow on a linear way. The tables below demonstrate the efficiency of multi-threaded data selection. First column represents the number of SPU (SQL Processing Unit) used by an SQL request. Second column displays the average number of records per second retrieved by the application server. As a data source heavily distributed F.STMT.ENTRY file has been used.

With such speed, 250.000 records (30 fields, covering almost all usable data in F.STMT.ENTRY) will be delivered in approximately one minute with just 4 SPU (benchmarked on hardware available in year 2010). The above results show that multi-threading capability substantially scales up the processing capacity of the driver, being a crucial factor in the implementation of ETL systems. It should be noted, the number of selected fields considerably affects the throughput rate. Rather than executing SELECT * FROM TABLE, specify the exact list of field names that are necessary to extract (F.STMT.ENTRY file has 100+ fields, 70% with null values).

On the [Connection] page of Administrator tool specify the number of parallel sessions (SPU) that will be used by application server to transport jBASE data packets. Please note, the data transportation process is CPU intensive task.

I-Descriptors are inherently slow. The same applies to EVAL function, which also acts as an I-Descriptor. Therefore, the use of such elements in SQL requests must be done carefully. If a big result set is expected, the I-Descriptors should be avoided. Good alternatives are user defined functions (UDF). An UDF is written and compiled in jBC environment, hence running and returning the results much faster.

The application server’s hardware has to process huge amount of data delivered with multithreaded requests. It includes uploading jBASE data packets, merging thread results, packing the data (multithreaded LZMA compression) and sending it to client. All these activities require a decent hardware. “Activity Monitor” page in the Administrator tool provides enough information in order to estimate proper hardware configuration.

Please note, whether client DLL and Application Server will reside both on the same hardware, data compression/decompression processes will be skipped. As a result, query dataset will be delivered quicker to ODBC client application.