Write better distributed queries for Informix data servers

A guide to improving query performance

Learn how to optimize your queries for the best possible performance when accessing multiple Informix servers. In this article, you'll learn to read the optimizer's Explain output and understand how Informix processes distributed queries. You'll learn to recognize inefficiencies in a query execution plan and write queries that avoid these pitfalls.

Share:

Evan C. Lee (evanlee@us.ibm.com), Principle Software Engineer, DB2, IDS, and XPS server development, IBM

Evan Lee has been involved in the development of the Informix data server since the first version. In addition, he contributed to the recent development of XPS and the DB2 for Linux, UNIX, and Windows Version 9 release.



10 May 2007

Introduction

The Informix data servers, both Informix Dynamic Server (IDS) and Extended Parallel Server (XPS), allow the user to query multiple remote data sources (data from other Informix database servers) in the form of a single SQL statement. An Informix distributed query (DQ) is a powerful and increasingly popular way of accessing data that is distributed across many Informix servers. It is an extremely quick way of performing enterprise-wide data analysis and data-management, such as data warehousing and data mining, with very little or no additional setup required. Distributed queries enable on-demand access to data distributed across any Informix data server without the need to learn a new query language or some form of data extract, transform, and load (ETL) process. An Informix distributed query is just like any other regular SQL query, the only difference being that some of the database objects in the query exist on other data servers. These database objects are called remote objects. Examples of database objects are tables, views, and stored procedures.

The performance of a query is paramount in any database system, whether distributed or not. This article explains why in distributed queries, the choice of a query execution plan by the optimizer can significantly affect the query's performance more so than if it had been a non-distributed one. You will learn through examples and the outputs from sqexplain, to determine how a DQ is decomposed and distributed, and how the final result is constituted. This article examines, in detail, some of the key areas in which a query plan can adversely impact performance, and looks at some of the solutions a user can take to help the optimizer produce a better plan.

Finally, the article examines other ways to facilitate the optimizer in its cost analysis and, consequently, in its choice of better query plans. These scenarios are generalizations of work that was done to improve the performance of various commercial applications. Some of this work became the basis of the IDS 11 (codenamed "Cheetah") release — work such as the remote execution of user-defined routines (UDRs) and the estimation of varying length data columns.

This article begins with a background section that briefly explains what an Informix distributed query is, the various components that makes up the distributed environment, and how a DQ is decomposed, distributed, and the results reconstituted. This is followed by an explanation of some of the terms used and a description of a distributed environment that is used throughout this article to illustrate the problems and solutions.

IDS 11 notes

This article is written with IDS 11 in mind. Therefore, a number of solutions presented are based on the features of the IDS 11 release. Some workarounds for prior versions are mentioned. If a workaround is not mentioned, that does not mean there is no solution for earlier versions.

The heart of the article is divided into two parts:

  • Part 1 looks at various inefficiencies that can arise from a query plan, namely: unnecessary trips, round and double-round trips, and unnecessary column-data. These represent situations such as unnecessary query results being sent by the various remote servers to the coordinating server, intermediate query results bouncing around between the database servers, portions of data are unnecessary shipped between servers, and in other cases, UDRs as well as built-in functions are invoked unnecessarily through the expensive SQL EXECUTE PROCEDURE call. Each call to EXECUTE PROCEDURE is expensive not only because each is a separate SQL query, but in a production environment, it may take a long time before one of these calls is processed by the remote server. This article describes each of these areas in detail. It starts with one or more examples to illustrate the idea of what the issue is, then explains why each issue can be a problem, and how to correct it.
  • Part 2 of the article examines some ways the user can help the optimizer in its cost calculations. This, in turn, leads to a better choice of query plans. Specifically, the choice of an optimal query plan is a complex task undertaken by the optimizer. The introduction of certain information (conditions in the WHERE clause, also known as predicates) can help the optimizer to produce a better plan, while the presence of some predicates can actually cause the optimizer to make a sub-optimal execution plan choice. Some techniques to improve on the optimizer's ability to compute selectivity and to re-write certain DQ in order to improve performance, are presented here.

Background

This section briefly explains what an Informix distributed query is, how the query is distributed to the various Informix database servers by a coordinating Informix database server, and how the coordinator reassembles, transforms, and aggregates the intermediate results from the various participating database servers. For a detailed description of the Informix distributed query and on setting up various database servers, refer to the IBM Informix Database Design and Implementation Guide found in the Resources section. Commonly used terms, and a description of a distributed environment that is used throughout this article to illustrate the problems and solutions, are presented here.

An Informix distributed query is in many ways just like any other SQL query that you send to a database server. The difference is that some of the database objects referenced in the query reside in other Informix database servers (called remote objects). For a broader Informix definition of remote objects, which is not used in this article, see the Terminology section. An example of a remote object is a table, a view, or a UDR. The syntax to identify a remote object is given below. Any Informix database server from which the user connects to and issues a DQ to, is considered the coordinator for that query. Figure 1 shows the Informix server, Earth, acting as a coordinator for a user-specified query. The coordinator compiles and optimizes the query sent by the user. If the user's query is a DQ, that is it references remote objects, and depending on the query plan picked by the coordinator's optimizer, the coordinator constructs one or more remote queries, connects to the appropriate remote servers, and sends the remote queries in the form of SQL requests to these remote servers (also known as participants). In Figure 1, the coordinator, Earth, issues remote queries to Mars and Pluto.

Figure 1. Shows the flow of the query from the user to the remote queries issued by the coordinator to the participants
Query from the user to the remote queries issued by the coordinator to the participants

The results for the remote queries is returned by the participants, and the coordinator is responsible for consolidating and joining the results. The coordinator may also have to transform, sort, and aggregate these results before returning the final answers to the user. This is depicted in Figure 2, where Mars and Pluto return the results to Earth.

Figure 2. Shows the flow of the results returned by the participants to the user
Shows the flow of the results returned by the participants to the user

Note, Earth, Mars, and Pluto are any typical Informix servers so long as Earth is made aware of the existence of Mars and Pluto through the SQLHOSTS file (and their connection information). For more information on configuring a database server to be aware of other servers, refer to the IBM Informix Database Design and Implementation Guide in the Resources section.

Terminology

  • Remote Objects: In Informix, a remote object such as a table, is broadly defined to be an object in another database. This other database can reside within the same database server. A query referencing objects in other databases within the same database server are considered a cross-database query, whereas a query referencing remote objects in another server is considered cross-server. This article does not consider remote object that resides in another database within the server as remote objects. These objects are merely organizational in nature and are no different from the objects in the local database when it comes to their access. In another words, a distributed query in this article means a cross- server query exclusively.

    Syntax to denote remote objects:

    <database name>@<server name>:[owner.]<object name>

    example: remDB@Pluto:stock

    In this example, "stock" is a table defined in the database remDB, that is managed by the Informix server Pluto.

  • Informix coordinator and participant: The term coordinator is used for the Informix IDS or XPS server that accepts a distributed query from a client. If the query contains references to remote database objects, the coordinator transforms the query into one or more remote queries. Each of these remote queries are sent to the remote database server to retrieve the data for the remote objects. The remote database server is called a participant. The participant treats and processes a remote query from the coordinator just like any query from any database client. When updates are involved, the coordinator and one or more participants operate within the context of a distributed transaction. Note that a remote query is just like any other standard SQL query. It can be called a remote query here to indicate that it is generated and sent from a coordinator to the participant unbeknownst to the user.
  • Tuple: The term tuple (as well as row) is used instead of record to denote the information that is sent between the servers. This is because in most cases, only certain fields in a record is being sent, and in other cases, a tuple may be the result of a join of partial records from two or more tables.
  • Predicate: A predicate in SQL is an algebraic expression in which the evaluation of the expression results in either a true or false answer. For example, ColA > 5 , or, ColA > ColB. A predicate can be conjuncted (ANDed) with another predicate or disjuncted (ORed) with another predicate to form a more complex predicate.
  • Filter: A filter is a collective term for a WHERE clause, on clause, or HAVING clause. A filter is made up of one or more predicates conjuncted or disjuncted together. It serves to determine if a tuple from a table or a join is to be selected.
  • Tuple Qualification: When it is said that a tuple is being qualified, it is meant that a predicate associated with a query is being evaluated to determine if that tuple is to be selected as part of the result set for the query. The term qualification is also used to signify such a process.
  • User-defined routine (UDR): The term includes all functions and procedures that are not part of the server's built-in functions. This includes aggregate functions, strategy functions, and operators, to support virtual indexes and blades. A UDR is provided by the user or a blade package, and it can be written in any of the supported languages including Informix Stored Procedure Language (SPL).

Scenario used

Two Informix database server are used, Earth and Pluto. Create a database, myDB, on server Earth using the storesdemo9 script (see the Appendix section). myDB contains all the loaded tables defined in storesdemo9. The table you will be using is Stock. Do the same for the server Mars; create a database called remDB and load it with the tables from the storesdemo9 script. You also need to introduce a UDR called similar_item(). The SQL UDR prototype for this fictitious routine is defined in the appendix and the function of this routine is to return a ranking of how similar an item is to another, based on their descriptions. This is purely fictitious and its purpose is merely to demonstrate the effect of UDR and stored procedures on the processing of remote queries. You can simply create a function that returns a random number for this purpose.

Assumption

For the sake of illustration and simplicity in the analyses, it is assumed that the servers are to be equally configured and working under similar work loads.


Part 1 -- Inefficiencies in query plans

A distributed query, as compared to a standard non-distributed one, is more susceptible to certain inefficiencies in a query plan. This is not only because of the overhead in compiling and processing the extra queries by the participants, and the cost incurred for the transportation of intermediate records between servers over longer distances and slower network protocols, the repetitive nature of join processing tends to have a multiplicative effect on these additional costs. Furthermore, such degradation is exacerbated when a multi-phase commit protocol is used to handle DQ involving updates. In other words, in a non-distributed environment, the impact of certain inefficiencies may not be noticeable as compared to a DQ. This part of the article examines some of the critical ones and provides solutions to them.


Unnecessary trips

This section discusses what constitutes as an unnecessary trip. Why they are expensive, and how to avoid them.

Unnecessary tuple trips occurs when the participant is unable to "pre-select" and ship only the qualifying tuples to the coordinator, or the participant is unable to perform any aggregation function and thereby avoiding the need to ship tuples across the network. Pushing the predicates to the participant is one way to accomplish such as a task. Another technique to rewrite nested functions will be presented in the Variable length data types section.

The example below shows the plan for a DQ containing an aggregate function, MAX. It demonstrates two forms of unnecessary trips.

Example 1a. Aggregation and qualification by the coordinator
QUERY:
------
SELECT max(stock_num)
   FROM remDB@pluto:stock
      WHERE similar_item(description,'ball') > 5

Estimated Cost: 8
Estimated # of Rows Returned: 1

  1) remdb@pluto:elee.stock: REMOTE PATH

    Remote SQL Request:
    select x0.stock_num ,x0.description from remdb:"elee".stock x0


        Filters: elee.similar_item(remdb@pluto:elee.stock.description ,'ball' ) > 5

UDRs in query:
--------------
        UDR name:   similar_item

First, in this particular plan, the coordinator fetches the tuples from the subordinator servers and then executes the aggregate function. This is unnecessary if the participant performs the aggregation and returns only one numeric to the coordinator instead.

Second, the qualification of each Stock tuple is performed by the coordinator. That is, the decision if the tuple is to be used in the aggregation is decided at the coordinator's site. This means that if the tuple does not qualify, an unnecessary trip is wasted in sending the disqualified tuple across the network. In other words, the coordinator applies the predicate similar_item(remdb@pluto:elee.stock.description ,'ball' )> 5 on the tuple. If you get the subordinator to do this, transporting all the rows is unnecessary.

How do you tell from the sqexplain out that the aggregation is performed by the coordinator, and that the qualification is also done by the coordinator? Look at the remote query that is listed under Remote SQL Request: select x0.stock_num ,x0.description from remdb:"elee".stock x0

This is the exact SQL text that is issued to the participant. It does not contain the MAX aggregate function, nor does it contain the predicate similar_item(description,'ball') > 5. You can tell that the predicate is performed by the coordinator from the line : Filters: elee.similar_item(remdb@pluto:elee.stock.description ,'ball' )>5.

Note that the current version of sqexplain does not show where the aggregate function and aggregation is performed. Also, the output: Estimated # of Rows Returned: 1 means the expected result from the coordinator to the client is 1. It does not show the number of tuples that had to be sent between the participant and the coordinator, which is the entire Stock table.

Why is the MAX aggregation not performed by the participant? The reason is that the UDR function similar_item() is defined in the coordinator. Therefore, qualification has to be performed by the coordinator. After deciding if a tuple is qualified (to be selected), the coordinator may have to send the qualified tuples back to the participant for the final aggregation (an example of a round trip), this will typically present an even greater performance problem. Hence, the optimizer usually picks a plan that avoids an unnecessary round trip for each qualified tuple and computes the aggregation itself as part of the final query processing phase. The lines in the output that show that the UDR is executed as part of the coordinator's plan by the coordinator are:

UDRs in query:
--------------
 UDR name:   similar_item

Solution to Example 1.a

To push the predicate to the participant, and therefore avoid the unnecessary trips, two things must be done:

  1. Create an identical UDR in the participant. Note, this can only be done for SPL routines for pre-Cheetah releases. In Cheetah, UDRs in general are allowed to be remotely referenced.
  2. Explicitly reference the remote UDR, similar_item().

Use the following DQ instead of the original (note the explicit remote reference to similar_item):

SELECT max(stock_num)
   FROM remDB@pluto:stock
      WHERE remDB@pluto:similar_item(description,'ball') > 5

The solution below shows that the MAX aggregation and the predicate containing the call to "similar_item()" is now part of the remote query. There are two important things missing from the new output:

  1. There are no lines showing the filters: entry, meaning that there are no filters to be applied by the coordinator.
  2. There are no lines showing the UDRs in query:

The predicate containing the UDR, similar_item() is now pushed to the participant.

Solution 1a. Aggregation and qualification by the coordinator
QUERY:
------
SELECT max(stock_num)
   FROM remDB@pluto:stock
      WHERE remDB@pluto:similar_item(description,'ball') > 5

Estimated Cost: 8
Estimated # of Rows Returned: 1

  1) remdb@pluto:elee.stock: REMOTE PATH

    Remote SQL Request:
    select max(x0.stock_num ) from remdb:"elee".stock x0
    where (remdb:"elee".similar_item(x0.description ,'ball' )> 5 )

Example 1.b

Example 1b is another example where tuples from a remote table are sent to the coordinator to be qualified and only those selected are used in the subsequent join operation. The remote query in this case is:

Remote SQL Request:

select x0.description ,x0.stock_num from remdb:"elee".stock x0

You can see from the request that the filter predicate is not pushed (incorporated into) the remote query. Rather, it is a filter that belongs in the coordinator's query plan as shown by the line:

Filters: elee.similar_item(elee.s.description ,'ball' )> 5

Again, note the line showing Estimated # of Rows Returned: 1. It refers to the final result of the entire DQ. It does not show the fact that the entire remote table has to be fetched from the participant. If the selectivity of the filter predicate is high, many of the tuples sent by the participant are unnecessary and you can achieve significant gains by pushing the filter to the participant.

Example 1b. DQ involving a join
QUERY:
------
SELECT count(*)
   FROM remDB@pluto:stock s, stock my_stock
      WHERE similar_item(s.description,'ball') > 5
         AND s.stock_num = my_stock.stock_num

Estimated Cost: 29
Estimated # of Rows Returned: 1

  1) elee.s: REMOTE PATH

    Remote SQL Request:
    select x0.description ,x0.stock_num from remdb:"elee".stock x0


        Filters: elee.similar_item(elee.s.description ,'ball' )> 5

  2) elee.my_stock: INDEX PATH

    (1) Index Keys: stock_num manu_code unit   (Key-Only)  (Serial, fragments: ALL)
        Lower Index Filter: elee.s.stock_num = elee.my_stock.stock_num
NESTED LOOP JOIN

UDRs in query:
--------------
    UDR name:   similar_item

Solution 1b is the same as for Example 1a, namely to create an identical UDR, similar_item(), on the subordinate server and explicitly referencing it in the DQ. The output from pushing the predicate is shown below. Again, note that the predicate is incorporated into the remote query.

Solution 1b. DQ involving a join
QUERY:
------
SELECT count(*)
   FROM remDB@pluto:stock s, stock my_stock
      WHERE remDB@pluto:similar_item(s.description,'ball') > 5
         AND s.stock_num = my_stock.stock_num

Estimated Cost: 29
Estimated # of Rows Returned: 1

  1) elee.s: REMOTE PATH

    Remote SQL Request:
    select x0.description ,x0.stock_num from remdb:"elee".stock x0 where 
    (remdb:"elee".similar_item(x0.description ,'ball' )> 5 )


  2) elee.my_stock: INDEX PATH

    (1) Index Keys: stock_num manu_code unit   (Key-Only)  (Serial, fragments: ALL)
        Lower Index Filter: elee.s.stock_num = elee.my_stock.stock_num
NESTED LOOP JOIN

Round trips

This section discusses what constitutes round trips. Why they are expensive, and how to avoid them.

Round trips occurs when the coordinator has to send back a part of each tuple it receives from the participant for further transformation or qualification. In the extreme case, the same tuple, or part of it, is sent back several times by the coordinator for more processing. This situation can occur independently of the unnecessary trip scenario and it is just as expensive because of the overhead involved in executing a remote routine on each tuple through the SQL Execute function call, in addition to the shipping of portions of each tuple back and forth between the servers.

Example 2a. Round-trip in a simple join with a remote table
QUERY:
------
SELECT r.stock_num
 FROM remDB@pluto:stock r, stock my_stock
      WHERE r.stock_num > 320 AND
            remDB@pluto:similar_item(r.description,'ball') =
            remDB@pluto:similar_item(my_stock.description,'ball')

Estimated Cost: 9
Estimated # of Rows Returned: 1

  1) elee.r: REMOTE PATH

    Remote SQL Request:
    select x0.stock_num ,x0.description from remdb:"elee".stock x0 where 
    (x0.stock_num > 320 )


  2) elee.my_stock: SEQUENTIAL SCAN

        Filters: remdb@pluto:elee.similar_item(elee.r.description ,'ball' ) =
        remdb@pluto:elee.similar_item(elee.my_stock.description ,'ball' )
NESTED LOOP JOIN

In Example 2a, without looking at the sqexplain output, it appears that the predicates are pushed to the participant through the explicit reference to the remote UDR, similar_item(), and you have avoided the unnecessary tuple scenarios mentioned earlier. However, there is another problem. If you examine the output below, you see that the join predicate, "remDB@pluto:similar_item(r.description,'ball') = remDB@pluto:similar_item(my_stock.description,'ball')" is not pushed to the remote participant. This is because the remote query is made up of the following:

select x0.stock_num ,x0.description from remdb:"elee".stock x0 where (x0.stock_num > 320 )

You also see that the above join predicate is processed as a filter for a nested loop join that will be performed by the coordinator. This filter contains two separate remote function calls to the UDR similar_item()— that is, two separate EXECUTE FUNCTION remDB@pluto:similar_item() SQL statements will be sent to the participant for each pair of unqualified joined tuples. This in itself is very expensive, and if the column for the tuple, namely description, is a large character column, the shipping cost can also be substantial.

The solution to the above problem is the opposite of the solution undertaken for avoiding unnecessary tuples. Instead of pushing the UDR call to the remote participant, you want to "pull" the call and have the coordinator execute them locally. That is, you need to create a local copy of the function on the coordinator and rephrase the DQ as follows:

SELECT r.stock_num
 FROM remDB@pluto:stock r, stock my_stock
      WHERE r.stock_num > 320 AND
            similar_item(r.description,'ball') =
            similar_item(my_stock.description,'ball')

The output below shows the join filter to contain two local UDR calls to similar_item().

Solution 2a
QUERY:
------
SELECT r.stock_num
 FROM remDB@pluto:stock r, stock my_stock
      WHERE r.stock_num > 320 AND
            similar_item(r.description,'ball') =
            similar_item(my_stock.description,'ball')

Estimated Cost: 30
Estimated # of Rows Returned: 1

  1) elee.my_stock: SEQUENTIAL SCAN

  2) elee.r: REMOTE PATH

    Remote SQL Request:
    select x0.stock_num ,x0.description from remdb:"elee".stock x0 where 
    (x0.stock_num > 320 )


DYNAMIC HASH JOIN
    Dynamic Hash Filters:
elee.similar_item(elee.similar_item(elee.r.description ,'ball' ) =
elee.similar_item(elee.my_stock.description ,'ball' )

UDRs in query:
--------------
    UDR name:   similar_item
    UDR name:   similar_item

Example 2b

The next example is very similar to the previous one, with the exception that part of each joined tuple is again sent back to the participant for further processing by the remote UDR, similar_item(). This occurs during the evaluation of the DQ select's projection list.

Note that the call to similar_item() in the DQ's projection list is not included in the remote SQL request. Therefore, it is processed separately by the coordinator during the handling of the final output to the client.

Example 2b. Multiple round-trips in a join with a remote table
QUERY:
------
SELECT remDB@pluto:similar_item(r.description,'ball')
 FROM remDB@pluto:stock r, stock my_stock
      WHERE r.stock_num > 320 AND
            remDB@pluto:similar_item(r.description,'ball') =
            remDB@pluto:similar_item(my_stock.description,'ball')

Estimated Cost: 9
Estimated # of Rows Returned: 1

  1) elee.r: REMOTE PATH

    Remote SQL Request:
    select x0.description ,x0.stock_num from remdb:"elee".stock x0 where
    (x0.stock_num > 320 )


  2) elee.my_stock: SEQUENTIAL SCAN

        Filters: remdb@pluto:elee.similar_item(elee.r.description ,'ball' ) =
        remdb@pluto:elee.similar_item(elee.my_stock.description ,'ball' )
NESTED LOOP JOIN

The solution below, as in Example 2.a, is to "pull up" the call to similar_item() The resulting output is shown below. Note, there are three calls to the local copy of similar_item()

Solution 2b. Multiple round-trips in a join with a remote table
QUERY:
------
SELECT similar_item(r.description,'ball')
 FROM remDB@pluto:stock r, stock my_stock
      WHERE r.stock_num > 320 AND
            similar_item(r.description,'ball') =
            similar_item(my_stock.description,'ball')

Estimated Cost: 30
Estimated # of Rows Returned: 1

  1) elee.my_stock: SEQUENTIAL SCAN

  2) elee.r: REMOTE PATH

    Remote SQL Request:
    select x0.description ,x0.stock_num from remdb:"elee".stock x0 where
    (x0.stock_num > 320 )



DYNAMIC HASH JOIN
    Dynamic Hash Filters:
    remdb@pluto:elee.similar_item(elee.r.description ,'ball' ) =
    remdb@pluto:elee.similar_item(elee.my_stock.description ,'ball' )

UDRs in query:
--------------
    UDR name:   similar_item
    UDR name:   similar_item
    UDR name:   similar_item

The last example is meant to show that you need to look at the output before deciding which UDR to push and which one to pull. This is because, in this example, one should be pushed while the other is pulled.

Example 2c. Multiple round-trips in a join that requires a mixed solution
QUERY:
------
SELECT remDB@pluto:similar_item(r.description,'ball')
 FROM remDB@pluto:stock r, stock my_stock
      WHERE r.stock_num > 320 AND
            remDB@pluto:similar_item(r.description,'ball') =
            remDB@pluto:similar_item(my_stock.description,'ball')
      GROUP BY  r.stock_num,r.description
      HAVING
            remDB@pluto:similar_item(r.description,'ball') > 0

Estimated Cost: 12
Estimated # of Rows Returned: 1
Temporary Files Required For: Group By

  1) elee.r: REMOTE PATH

    Remote SQL Request:
    select x0.description ,x0.stock_num from remdb:"elee".stock x0 where 
    ((remdb:"elee".similar_item(x0.description ,'ball' )> 0 )
    AND (x0.stock_num > 320 ) )


  2) elee.my_stock: SEQUENTIAL SCAN

        Filters: remdb@pluto:elee.similar_item(elee.r.description ,'ball' ) =
        remdb@pluto:elee.similar_item(elee.my_stock.description ,'ball' )
NESTED LOOP JOIN

In the above example, the predicate in the HAVING clause is already pushed to the remote query in the form of a WHERE clause (see the query in the output's remote SQL request). This means that the remote query in the predicate is processed by the participant as part of the processing of the remote query. If you pull this UDR, meaning make it a local coordinator UDR, you end up with the unnecessary tuple scenario and that is not good. The solution here is to pull-up all the UDRs except the one in the HAVING clause.


Unnecessary tuple columns

This section looks at some of the ways in which you can reduce the size of each tuple flowing between the servers.

Unnecessary tuple columns occurs when the server (participant or coordinator) sends column-data that are either not needed, can be avoided, or can be represented in a compressed format. Although these are less costly than the earlier scenarios, they can be a significant performance factor if many large-sized columns are involved. The following are some of the considerations when writing distributed queries:

  • Avoid using the star-notation, as in select *, in DQ. Although it is convenient to use the star-notation in the projection list of a query, in the case of DQ, this expansion is propagated to all the remote tables in the query. This means all of the columns in each tuple of the remote tables have to be shipped. In DQ, this wasted effort is amplified by the number of intermediate tuples fetched remotely.
  • Use VARCHAR and LVARCHAR instead of fixed length data (another reason for using varying length data is explained in the Variable length data types section). This saves time, sending lengthy string columns of intermediate tuples across the network. Again, in DQ, the number of intermediate tuples shipped across the network is generally much larger than the final result set shipped to the client.
  • Push any UDR in the DQ's projection list that transforms large-sized data into a smaller-sized item to the remote queries and make sure, through the sqexplain output, that the remote queries contain the pushed down UDR. Otherwise, you will end up with the round- trip scenario describe earlier. This suggestion, pushing the UDR, must be done with the understanding of the trade-offs involved. On one hand, executing the UDR on the coordinator is applied on each of the tuple in the final result set. On the other hand, the UDR is applied by the remote participants on each of the intermediate tuples shipped to the coordinator. The first case is typically faster than the latter case. However, the trade-off is that you save on the shipping of smaller-sized intermediate tuples in the latter case, as compared to shipping larger sized tuples in the first case. One quick and simple way to resolve this is through a stop-watch. The other is through analysis, by estimating the cost of shipping various sized objects, the time to execute a UDR that may be a function of size as well, the size of the final result set and the number of intermediate tuples, and finally, weighing the cost of the two options.

Part 2 -- Assist the optimizer

The remaining sections discuss ways in which the user can facilitate the optimizer in producing better query plans within the context of distributed queries. It focuses on two critical areas: selectivity calculation and cost estimation. These sections explain why it is important to get the selectivity and cost estimation as accurate as possible for distributed queries, and how the user can help in the process. Key topics are:

  • The identification and elimination of redundant predicates
  • Introduction of redundant predicates
  • The use of variable length datatypes

Eliminate redundant predicate

This section briefly explains the role of selectivity calculations in determining a query plan, shows what a redundant predicate is, and why a redundant predicate adversely affects the computation of filter selectivity, and consequently, to the choice of a less optimal query plan by the optimizer.

The selectivity of a filter or join predicate is used by the optimizer to estimate the number of qualified tuples that are fetched from a table or the number of tuples that are produced by a join. These estimated numbers are then used to arrive at the cost associated with a particular query plan. They fundamentally determine how each table is accessed, how each join is processed, and the order in which the joins and table accesses is carried out. When these numbers are wrong, and there is one or more very large tables involved, the plan selected can be very bad.

This is an example of a redundant predicate given the WHERE clause:

(s.stock_num > 2 OR s.description in ( 'chair','table')) AND s.stock_num > 2

The following predicate is redundant:

(s.stock_num > 2 OR s.description in ( 'chair','table'))

This is because, the given that predicates are logically equivalent:

(s.stock_num > 2 OR s.description in ( 'chair','table')) AND s.stock_num > 2 is equivalent to (stock_num > 2)

In other words, the logical outcome of the given predicate is determine solely by the logical outcome of (stock_num > 2). Conversely, the predicate (s.stock_num > 2 OR s.description in ( 'chair','table')) has no logical effect on the given predicate.

This is how a redundant predicate adversely affect selectivity estimations:

Use the notation S[x] to denote the selectivity of a predicate x. S[x] is similar to the probability function P[x]. S[x] returns a decimal value between 0 and 1.

For this illustration, let p1 be the predicate representing (stock_num > 2) and p2 represent (s.description in ( 'chair','table')). Also suppose S[p1] = 0.2 and S[p2] = 0.6

The selectivity of conjunctive predicates is typically based on the formula: S[ p1 AND p2 ] = S[p1] * S[p2]

The selectivity of disjunctive predicates is: S[ p1 OR p2 ] = S[p1 ] + S[p2] - S[ p1 AND p1 ] (You can use a Venn diagram to understand this).

Note: The formulae assume p1 and p2 are defined over two different distribution — that is, over two different columns. It is also assumed that these columns are uncorrelated for simplicity.

You can restate the given WHERE clause as: (p1 OR p2) AND p1 The selectivity, S[ p1 AND p2 ] = 0.2 * 0.6 = 0.12 and S[ p1 OR p2 ] = 0.2 + 0.6 - 0.12 = 0.68

The selectivity for the entire predicate is calculated as: S[ (p1 OR p2) AND p1 ] = S[p1 OR p2] * S[p1] = 0.68 * 0.2 = 0.136

However, because the entire WHERE clause is logically equivalent to p1, the correct selectivity for the WHERE clause should be simply S[p1], which is 0.2. Therefore, the selectivity computation is off by 0.064. The optimizer has under estimated the selectivity of this WHERE clause by 32 percent.

The output below shows two different plan results from the same query. The first is the plan for the query containing a redundant predicate. The second shows the same query with the redundant predicate manually removed. Because of the under-estimation of the selectivity for the remote table, s, the optimizer chose a plan that constructs the hash table from the remote table. On the other hand, the second plan calls for the local table to be the hash table.

Example 4a.i. Redundant predicate in the WHERE clause of a simple join query
QUERY:
------
SELECT count(*)
   FROM remDB@pluto:stock s, stock my_stock
      WHERE
             (s.stock_num > 2 OR s.description in ( 'chair','table'))
         AND s.stock_num > 2 AND my_stock.stock_num = s.stock_num

Estimated Cost: 60
Estimated # of Rows Returned: 1

  1) elee.my_stock: INDEX PATH

    (1) Index Keys: stock_num manu_code unit   (Key-Only)  (Serial, fragments: ALL)
        Lower Index Filter: elee.my_stock.stock_num > 2

  2) elee.s: REMOTE PATH

    Remote SQL Request:
    select x0.stock_num ,x0.description from remdb:"elee".stock x0 where 
    ((x0.stock_num > 2 ) AND ((x0.stock_num > 2 ) OR
    (x0.description IN ('chair' ,'table' )) ) )



DYNAMIC HASH JOIN
    Dynamic Hash Filters:
    elee.my_stock.stock_num = elee.s.stock_num
Example 4a.ii. Redundant predicate removed from WHERE clause of a simple join query
QUERY:
------
SELECT count(*)
   FROM remDB@pluto:stock s, stock my_stock
      WHERE
         s.stock_num > 2 AND my_stock.stock_num = s.stock_num

Estimated Cost: 56
Estimated # of Rows Returned: 1

  1) elee.s: REMOTE PATH

    Remote SQL Request:
    select x0.stock_num from remdb:"elee".stock x0
    where (x0.stock_num > 2 )


  2) elee.my_stock: INDEX PATH

    (1) Index Keys: stock_num manu_code unit   (Key-Only)  (Serial, fragments: ALL)
        Lower Index Filter: elee.my_stock.stock_num > 2


DYNAMIC HASH JOIN
    Dynamic Hash Filters:
    elee.my_stock.stock_num = elee.s.stock_num

Note that redundant predicates can sometimes be difficult to detect as the following example will show. This predicate: ((f = 1 and a = 1 and ((a = 1 or b = 1 ) and (c = 1 or (d = 1 and e = 1 ))) ) or h = 1) and h = 1 is equivalent to the predicate (h=1). All the other terms are redundant.


Introduce redundant predicate

This section is presented to demonstrate that despite the fact that redundant predicate can cause incorrect selectivity computations, the derivation and use of certain redundant predicates can improve DQ significantly. For simple WHERE clauses, the IDS optimizer is able to generate some of these redundant predicates. You can tell by looking at the explain output for additional predicates in the WHERE clause.

Example 5a. Shows the WHERE clause of a simple join query where the redundant predicate can improve performance
QUERY:
------
SELECT count(*)
   FROM remDB@pluto:stock s, stock my_stock
      WHERE
             my_stock.stock_num = 2 AND s.description in ( 'chair','table')
         OR (my_stock.stock_num > 30 AND s.description in ( 'ball','bat'))

Estimated Cost: 209
Estimated # of Rows Returned: 1

  1) elee.s: REMOTE PATH

 Remote SQL Request:
 select x0.description from remdb:"elee".stock x0
 where ((x0.description IN ('chair' ,'table' )) OR (x0.description IN ('ball' ,'bat' )) )


  2) elee.my_stock: INDEX PATH

    Filters: ((elee.my_stock.stock_num = 2 AND elee.s.description IN ('chair' , 'table' ))
    OR (elee.my_stock.stock_num > 30 AND elee.s.description IN ('ball' , 'bat' )) )

    (1) Index Keys: stock_num manu_code unit   (Key-Only)  (Serial, fragments: ALL)
        Lower Index Filter: elee.my_stock.stock_num = 2

    (2) Index Keys: stock_num manu_code unit   (Key-Only)  (Serial, fragments: ALL)
        Lower Index Filter: elee.my_stock.stock_num > 30
NESTED LOOP JOIN

Variable length data types

In the Cheetah release, the optimizer is smarter when estimating the size of a tuple that contains variable length columns, such as LVARCHAR. Instead of relying on the maximum declared size for those columns, the optimizer looks at the actual space occupied by the data in these columns to derived at the average tuple size. This better estimate in turn allows the optimizer to compute a more accurate shipping cost. Therefore, it make a better choice of a query plan.

For example, if the remote table contains a column that is defined to be a varying character column of 32 thousand bytes, that is, LVARCHAR( 32,000 ). The optimizer uses the actual disk space occupied by all the data in the column to determine the average length for the column, instead of using either the maximum length or half of the maximum value. So if all the data in the column are 10 bytes long, then the average length on that column is about 10 bytes at that point in time.

In a distributed database environment, the use of varying length data types instead of fixed length types for the columns in a table not only improves on the optimizer's choice of a plan, it cuts down on the amount data flowing between the servers, as discussed earlier.


Acknowledgements

I like to thank Tom Houston, Srinivasan R. Mottupalli, Nancy Miller, Keshava Murthy, and Bingjie Miao for taking time out of their hectic schedules to provide superb criticism and suggestions to this article.


Conclusion

It is not the intent of this article to explain how the optimizer derives and computes the cost of various viable query execution plans, nor how the optimizer makes its selection. The intent of this article is to show you a way to recognize and understand the ramifications of certain plan. How it can adversely impact the performance of a DQ, and how to re-phrase such a DQ so as to avoid those scenarios.

Unnecessary trips occur when the participant sends tuples that are not fully qualified to the coordinator for further filtration or aggregation. In the extreme case, all the records from the entire remote table is sent to the coordinator for qualification. It is considered unnecessary if you can "push" the qualification or aggregation to the participant instead. This typically eliminates an enormous amount of tuple traffic between the coordinator and participant as well as the amount of overhead needed to prepare, convert, or transform each tuple for sending and receiving. This is an area in which performance can be improved dramatically in production systems. You need to recognize such a situation and know how to improve upon it.

The existence of round trips is another area of concern in the processing of a distributed query. Recognizing them and avoiding them is equally important. This article also presents other scenarios in which performance can be an issue and shows a few ways to correct them.

Finally, you learned some manual ways in which you can rephrase a DQ so that the optimizer can pick a more effective query plan. These involves the elimination as well as introduction of redundant predicates, and the use of the varying length datatypes.


Appendix -- Demo database and miscellaneous setup information

To enable sqexplain, issue the following command to the coordinator after connecting to it.

In esqlc use: EXEC SQL EXPLAIN ON;

In DBaccess use: SET EXPLAIN ON;

This article uses two IDS servers, Pluto for the remote database server and Earth for the server from which the client issues a distributed query. A UDR, similar_item() is also used as an example. The superstores_demo database is used for most of the illustrations. For more information on the superstores_demo database, see the Resources section. A copy of this database, myDB, is created for the Earth server and another copy, remDB, is created for the Mars server. To accomplish this: On the local server, Earth, run $INFORMIXDIR/bin/dbaccessdemo9 myDB to create and populate the database, myDB. On the remote server, run$INFORMIXDIR/bin/dbaccessdemo9 remDB to create and populate remDB. To enable sqexplain dynamically for sessionid, issue onmode -Y sessionid 1.

The following is the UDR used in this article:

CREATE FUNCTION similar_item ( description CHAR(1028), matching CHAR(90) )
   RETURNS integer
external name "$USERFUNCDIR/similar_item.udr(similar_item)"
language c;

Resources

Learn

Get products and technologies

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=218940
ArticleTitle=Write better distributed queries for Informix data servers
publish-date=05102007