EII Platform Performance - Part II

In my last post, I described how rule based optimization applies in an EII scenario. Cost based optimization is an improvement on that, since it applies situational knowledge of the backend data sources to optimize the query plan. While Rule Based Optimization is hard-wired logic, Cost Based Optimization is dynamic, and uses its knowledge of the current state of the underlying database to arrive at the most optimal plan for executing a query.

CBO in a standalone Query Engine

In the case of a standalone database, the database engine maintains statistics about the data it stores. Examples of the type of statistics maintained includes things like the number of keys in an index, the average number of matches for a given key in an index, the number of rows in various tables etc. These statistics are used when optimizing a query to determine things like join ordering, the type of join algorithm to use etc. These determinations are made based on calculations of cost (hence Cost Based Optimization) of performing various operations involved in executing the query. The statistics maintained by the database aid in these calculations.

CBO in an EII Query Engine

In an EII scenario, Cost Based Optimization is a bit more complex. Various data sources have varying capabilities for maintaining statistics. For instance, while most relational databases maintain at least some basic statistics, other data sources like web services provide none. When a user query requires the EII server to query multiple data sources and then “join” the information across the data sources, the server has to take into account the statistics it has retrieved from the different data sources, the lack of statistics from some of them, and determine the proper orchestration of the query execution.

 

Here’s a simple example that shows how CBO can contribute to building an optimal query plan. Consider a scenario where detailed customer information is maintained in a CRM system built on an Oracle database, and an Order Entry System built on a DB2 database holders the Order information. While the DB2 database has customer information, we want to join the order information with the richer customer information available in the Oracle database. Say we want detailed customer information and order details for all customers located in “Australia”, who had ordered the book “Freakonomics”. This involves selections on tables in both databases and a join across data sources. Based on the statistics from both tables, you might be able to determine that there are likely to be about 20 customers in the database from Australia, where there are at least 10,000 orders for the book in question. In this case, based on the statistics available, the query engine might decide to make the Customers table in Oracle the outer table, and use a nested loop join algorithm which gets all the ids of all the customers in Australia from the Oracle table and builds an in-list and retrieves all corresponding order records from DB2 database. This choice of join order and join algorithm is based on the knowledge the EII server has of the characteristics of the data in the remote database. Whats more, as the data in the databases changes over time, the statistics will be refreshed accordingly and you might reach a point where a different choice of join order or algorithm might be appropriate. In this way Cost Based Optimization is more adaptive than Rule Based Optimization.

 

Next time, I will post on the various takes on caching in EII systems and the impact of caching policies on EII system and backend data source performance.

EII Platform Performance - Part I

Almost every customer and prospect I have talked to in the last few years has had performance as one of the top three criteria of evaluating an EII platform. Actually, this might be true for any data management offering. For EII, performance takes on an added dimension.

Examining the performance of an EII system

An EII platform’s performance has to be evaluated from two points of view:

1. The performance of the distributed query engine
2. The performance impact on the data sources

The first of these refers mainly to the query optimization capabilities of the EII query engine, which is relatively easier to evaluate. At a high level, you can look at two aspects of this:

1. What optimization features does the query engine provide
2. What tuning capabilities are available to a data architect/administrator

Rule based optimization

At a minimum, any query engine will provide some amount of optimization based on heuristics, referred to as rule based optimization. Usually, all query optimization takes a query expression tree, and tries to convert it to another equivalent tree (the equivalence being governed by the algebra for the query language). Presumably, the rules or heuristics followed in performing rule based will result in a tree that can be evaluated at a lower cost that the tree you started with.

For stand alone SQL engines, these rules are cut and dried. For distributed SQL engines, additional factors need to be taken into account such as the capabilities of the remote data sources, the amount of data being passed between EII server and the remote data source etc. For instance, a common form of rule-based optimization involves pushdown analysis. The goal of pushdown analysis is to determine whether a remote relational data source performs a SQL operation efficiently so as to reduce the size of the data set that the EII server must handle. This becomes especially important for table join operations involving complex query criteria, in which large amounts of filtered information may need to flow between different databases. The EII server uses a set of rules and heuristics to determine which pushdown operations will best shift the load to the data source and minimize overall system data handling.

The query engine inside the EII server takes a SQL request from a client, parses and optimizes the query to decide which SQL operations to perform locally and which operations to send to the remote data source for execution. The EII query engine will push down the search condition as much as possible to the data source for execution, because it avoids fetching a big result set back from the remote source. It also sends join operations on tables from the same data source to the remote resource for execution instead of fetching the tables back and doing the join locally. In addition, the EII query engine will push down any sorting criteria if possible so that result are returned from the remote sources sorted and minimize the need to re-sort the final result within the EII query engine.

While this discussion was mostly in the context of distributed SQL engines, the same concepts will apply to distributed XQuery engines as well. The way to evaluate how good a job a query engine does in terms of these kinds of optimizations, try out some distributed queries and see how the EII platform converts these queries to queries on the remote data sources.

In my next post, I will write about cost based optimization (more interesting and adaptive) and what it means in an EII context.

The EII SOA Convergence

The convergence of EII and SOA is an interesting topic that has garnered some attention in recent times with SOA platform vendors promoting the idea of a Data Services layer as part of a service oriented architecture. For the uninitiated, EII stands for Enterprise Information Integration, a new class of Data Integration solutions that complements ETL. EII is rooted in Virtual Database technology and its accompanying disciplines like Distributed Query Processing. In a nutshell, the main purpose of an EII platform is to make a bunch of data sources look like a single database.

There are interesting parallels between SOA and EII. SOA broadly describes an architecture where loosely coupled services collaborate to implement a solution. SOA virtualizes the process space, shielding a application from details like where the services reside, how the processes are implemented, what platforms they reside etc. Technology components like UDDI for discovery, BPEL for process definition, SOAP for payload, internet standards like HTTP for transport provide a standards based foundation for SOA.

EII virtualizes the data space, shielding applications from details like the location of the data source, the data model and query mechanisms supported by the data source, protocols used to access them etc. EII platform vendors provide tools for metadata discovery and modeling the virtual database. EII’s standards foundation is made up of standard query languages like SQL and XQuery, which are used to access the virtual database through standard interfaces like ODBC, JDBC and XQJ.

So there is some conceptual harmony at an architectural level, but is there more? For some EII vendors, integration into an SOA means their product can consume web services and/or it can publish data via web services. For EII products that present a relational model for their virtual database, this means some mapping capability from XML to relational (tabular) structures and vice versa.

In the context of a Data Services Layer for an SOA, you want the EII platform to provide an abstraction (and integration) of all the data sources of interest to your services. You want your services to see a cloud of data, which can be queried using some standard language, standard interface, over standard transports. For XML Web Services, the natural data model for that data cloud will be an XML data model and the natural query language for that cloud will be XQuery.

In some cases, the results of querying a data services layer are not just meant to be passed on to another service, but are meant to be interpreted. In such cases, a developer might want to code with a more familiar interface like JDBC/ODBC and process result sets (rather than parse and process XML results). In this case, you want to see this data cloud have a relational schema and you want to query it with SQL. This is one reason the Dual Core implementation from Ipedo makes sense in these use cases. You can see your data work through your favorite colored glasses as either a bunch of relational tables and views, or as a bunch of XML trees.

EII as a data integration technology also has a major role in BI solutions. Allowing BI tools like Business Objects' WEBI and Crystal Reports seamlessly access web services is important for Operational Business Intelligence. With these EII capabilities BI tools can now report off of an instance of an SOA. Phil Wainewright has written an article at Loosely Coupled that describes how EII is bringing BI into the SOA world.

If your SOA deals with a single data source, you might not need much of a Data Services layer. JDBC as an API provides you enough of an abstraction in that case. If you need to deal with multiple data sources that are all describing different aspects of similar entities, you definitely should look into implementing a Data Services layer implemented using EII technology.