Optimizing eXtremeSQL Query Performance

Query performance with eXtremeSQL can be improved by the proper use of indexes and understanding how the Query Optimizer works. Please use the following link for a detailed description of the eXtremeSQL Query Optimizer.

Covering and Inclusive indexes

In many cases, query performance on in-memory (transient) database classes can also be optimized by use of a covering index. If all the columns requested in the select list of query are available in the index, then the query engine doesn't have to look up the table again which can significantly increase the performance of the query. Since all the requested columns are available within the index, the index is "covering" the query. Hence, the query is called a covering query and the index is a covering index.

The eXtremeDB DDL compiler supports syntax to define the covering index for the class using the <*> syntax. For example:

 
    class Tab
    {
        string name;
        int4   code;
        string body;
         
        tree<*> pk;
    };
     

Here the covering index includes all fields in the class Tab. If, for example, only the name and code fields were relevant for queries, then the covering index could be defined as follows:

 
    class Tab
    {
        string name;
        int4   code;
        string body;
         
        tree<name, code> pk;
    };
     

To define a covering index in SQL, the “(*)” syntax can be used:

 
    create index pk on Tab(*);
     

Or, again to cover only fields name and code:

 
    create index pk on Tab(name,code);
     

Key-Value-Inclusive

Further performance optimization may be possible by using a “key-value-inclusive” index to leverage CPU cache memory which is much faster than normal RAM. (See the Key-Value-Inclusive and Covering Indexes page for a detailed explanation.) A “key-value-inclusive” index is defined in eXtremeDB DDL using the keyword inclusive as follows:

 
    class Tab
    {
        string name;
        int4   code;
        string body;
         
        inclusive tree<name,code> pk;
    };
     

or in SQL:

     
    create index pk on Tab(name,code) using INCLUSIVE;
     

(Note that these “Covering” and “key-value-inclusive” index optimizations only makes sense for, and should be used in conjunction with, the MURSIW transaction manager because, even if the field values are present in the index, the MVCC transaction manager still requires access to the object header in order to determine the right version of the value.)

 

External Sort

Sometimes the SQL engine consumes excessive amount of memory or simply runs out of available memory while sorting large datasets. To address this, the SQL engine implements an external sort capability through temporary files. To adjust this external sort operation, the memory limits are set either via the workMemLimit parameter of the SqlOptimizerParameters structure, or by passing the limit value to the SQL function setworkmem(). For example:

     
    select setworkmem(4*1024*1024*1024);
 

Once the set limit is reached, the pre-sorted temporary array is written (serialized) into a file. Hence there can be a number of files. When all the sort files have been created, the "reader" opens all of them at the same time and reads the content back record-by-record.

After the query has been completed all temporary files are removed. The files are created in the current server's directory, the file names are formed as follows:

     
    sprint( "sort-%p-%p.%d", mco_get_pid(), runtime->engine, (int)++nFiles);
     

If the files for any reason are left behind when the server is no longer running , they can be deleted manually.