Code Examples of Foreign Key Function in esProc

If field (or combination of fields) k is table M’s primary key, and k, at the same time, exists in table B, then k is regarded as the foreign key of B. The foreign key maintains a relationship between two tables and is one of the most important concepts in structured data computing. Through object references, esProc makes foreign key easy to function. The following examples aim to illustrate the operation.

Example 1 Create relationship between a referenced table and a referencing table 
order is the referencing table, emp is the referenced table, connect emp to order. Display emp'sName field, Gender field and Salary field and order's OrderID field and Amount field.
Note: Only tables emp and order are used here. Later, table dep is to be used in other examples. The relationship between the three tables over foreign key is shown as follows:

Data may originate from databases or texts. For example:
order=esProc.query("select OrderID,SellerId,Amount,OrderDate from sales")
emp=esProc.query("select EId,Name,Gender,Birthday,Dept,Salary from emp")
dep=esProc.query("select * from department")

esProc code:
A3=order.switch(SellerId, emp:EId)
A4=order.new(OrderID,Amount,SellerId.Name,SellerId.Gender,SellerId.Salary)
Computed results:
 

Code interpretation:
 A3: Replace records of SellerID in order with their counterparts in emp, create foreign key relationship between the two tables.
 A4: Get OrderID field and Amount field in order, and get Name, Gender and Salary field in emp through related references. We can see that, with object references, fields in emp can be accessed to directly from order. Thus complex and difficult join statements can be skipped.

Example 2: Query referencing table according to condition of referenced table 

Find orders signed by female sellers whose salary is greater than 10,000.
esProc code:
A3=order.switch(SellerId, emp:EId) / the same as above example
A5=order.select(SellerId.Salary>10000 && SellerId.Gender=="F")

Computed results:
 

Click the above blue hyperlinks and corresponding employee information will be shown:
 


Example 3: Group according to referenced table 
Compute sales amount of each department.
esProc code:
A3=order.switch(SellerId, emp:EId) / the same as above example
A5=order.groups(SellerId.Dept;sum(Amount))
Computed results:
 

Field names can be changed, like order.groups(SellerId.Dept:dt;sum(Amount):amt). The effect of name change is shown below:
 

Example 4: Complex association between multiple tables

For departments whose sales amount is greater than 50,000, find their managers’ names.
esProc code:
A3=order.switch(SellerId, emp:EId)
A4=dep.switch(Manager,emp:EId)
A5=emp.switch(Dept,dep:DeptNo)
A6=order.groups(SellerId.Dept:dt;sum(Amount):amt)
A7=A6.select(amt<=50000).(dt).(Manager).(Name)
Computed results:
 
Code interpretation:
A3, A4, A5：Create complete foreign key relationship.
A6: Compute sales amount of each department (See the above example). See below:
 
A7：Use object references to solve the question intuitionally. Expression A6.select(amt<=50000).(dt).(Manager).(Name) can be divided into four steps according to full stops. See below:
1. Find records whose sales amount is greater than 50,000 in A6.
2. Get records corresponding to dt field (in table dep)
3. Get records corresponding to Manager field (in table emp)
4. Get Name field.

Details are shown below:
A6.select(amt<=50000)  

.(dt)  
.(Manager)  

.(Name)  

Thinking of Set in esProc

1。Sequence and Set in esProc
Unlike traditional programming languages, esProc employs set commonly. In fact, the sequence in esProc belongs to the field of set. So it’s quite important to deeply understand the concept of sets when using esProc. Like an integer and a string, the set is a basic data type; it could be a variable value, a computation of an expression, and a return value of a function.

As a data type, esProc provides  operators of two sets A and B, like intersection, union, union of set, subtract and so on: A^B，A|B，A&B，A\B.
It is recommended for users to deeply understand and master these set operations. The thinking of sets and good use of data may bring about an easier solution.

The following is an illustration of using set operations to simplify code:

Unlike a set in the sense of mathematics,that in esProc is arranged orderly; there may be duplicates of a member in a set, like that in a sequence or sequence table.

In the scope of mathematics, the set Intersection and Union operations are both commutative; in other words, A∩B=B∩A and A∪B=B∪A. However, this commutative property is not valid for esProc because the member order in a sequence for esProc cannot be changed at will. For esProc, the result of the intersection / union operation is required to be arranged according to the order of the left operator.

Because of this ordering feature of sequence members, we should adopt function A.eq(B)to judge whether two sequences have the same members instead of simply using the comparison operator ==:

2. Parameters in a Loop Function

With the data type of set, you can handle several operations on the members of set in a single clause, without the need of loop code.

Sometimes, the loop functions will not process the set members but the values computed based on the members. In this case, you are allowed to use parameters to represent the formula in a function, in which the "~" represents the current member.

For the nested loop functions, "~" is interpreted as a member in an inner sequence; in such case, if you want to reference a member in an outer sequence, name of the outer sequence must headed the "~".

The above rule is also applicable to the field reference when ~ is omitted, of which the fields will be

interpreted as fields in an inner RSeq first, if such fields cannot be found in the inner RSeq, esProc will search for them in the outer RSeq.

3.Order of Loop

The arguments in a loop function will be computed according to their order in the original sequence. This is very important.

In many cases, it is available for a single expression to implement the function which can be achieved by simple loop code.

4.Computation Sequence

Different from such loop functions as sum and avg returning a single aggregate value, a computation sequence function A.(x) returns a set. Often, a new set can also be created by employing computation sequence, in addition to using the basic set operations such as union, intersection and subtract.

The execution of an aggregate function with arguments will be divided into two steps:

1) Use arguments to produce computed column; 

2) Aggregate the result.

In other words, A.f(x) = A.(x).f()

The new function is used to return a sequence table by computation sequence.

In addition, there is a run function for relevant sequence computation. This function returns the original sequences themselves instead of the result of the relevant sequence computation. Generally, this function is used to assign values to fields in a record sequence (sequence table).

5.Impure Sets
esProc has no restriction on the consistency of sequence member types, that is, a sequence may consist of numbers, character strings, and records.

However, in many cases, it would be of little practical significance to arrange data in variable types in one sequence. Therefore, users should not be too concerned about it.

On the other hand, a record sequence – A sequence consisting of records – can consist of records from different sequence tables; this is very convenient.

Under the environment of esProc, it is not necessary that records in a record sequence originate from the same sequence table. As long as they have the same field names, the records can be processed uniformly. Here, merits of esProc include simpler program writing, higher efficiency and reduced occupation of memory. For SQL, however, two tables with different structures must be united into a new one by using the UNION clause before making any operations.

6. Sets consisting of Subsets

Since the member of a set in esProc is less restrictive by nature, a set may be a member of another set.If A is a set consisting of other sets, functions A.conj(), A.union(), A.diff(), A.isect()could be employed to compute concatenate, union, difference and intersection between subsets of A.

Also, a record sequence may be a member of a sequence.

7. Understanding Group

The group operation is commonly used in SQL. However, many people have no in-depth knowledge about it. The nature of a group operation is to split a set into several subsets according to a certain rule. In other words, the return value of a group operation shall be a set consisting of subsets. However, people often do not need to use such a set; instead, they may need to view a part of aggregate values of its subsets. Therefore, group operations are often followed by the summarization operations for subsets.

This is what the SQL does. The GROUP BY clause is always followed by a summarization operation. Because there are no explicit set data types in SQL, a set consisting of subsets cannot be returned directly. Therefore, summarization operations must follow group operations.

As time passes, one would think that the group operations are always accompanied with the follow-up summarization operations and forget that the group operations and the summarization operations are independently.

However, sometimes, we would still be interested in these grouped subsets but not the summarized values. Although a part of us might be interested in the summarized values, they would still need to hold these subsets for reuse, but not to discard them once a summarization is completed. 

This requests us to understand the original meaning of the group operations. With a full realization of the thinking of sets, esProc can achieve this goal very well. In fact, the basic group functions in esProc can only be used for grouping which is independent to the summarization operation.

The group result is a set consisting of several subsets, and the subset also can be grouped. Members in the group result are also sets; and they can be grouped.Both of the two operations will produce a multilayer set. 

Because these results are so deep in hierarchy, they are rarely used in practice. The above example is only to show you the thinking pattern of set and the nature of the operation.

8. Non-Equi Group

Besides the ordinary group function, esProc provides an align@a() function for processing alignment group and an enum() function for processing the enum group.

The group implemented by the group function is called an equi-group with the following features:

1) Any member in the original set must and can only be in one subset;

2) There is no empty subset.

The above two features are unavailable for the alignment group or the enumeration group.

Alignment group is an operation that calculates grouping expressions with members in a set and makes a match between resulting subsets and specified values. For the alignment group, the following steps are required:

1) Specify a group of values;

2) In the set to be grouped, move members whose property values are equal to the specified values to one subset;

3) Each resulting subset must be corresponding to a pre-defined condition.

It is possible that a member exists in none of subsets, an empty set exists, or any member exists in both subsets.

The following case will group employees by specified states sequence:

Enum group: First, specify a group of conditions, take the members in the set to be grouped as arguments and compute these conditions, members satisfying these conditions will be grouped into a subset; each subset is corresponding to a pre-defined condition. A member may be in none of these subsets, or in two subsets at the same time; besides, an empty set may appear.

The following case will group employees by specified age groups:

Although apparently it seams that these two functions differ greatly with the group, the three functions share the same nature regarding the group operation – to split a set into several sub-sets. The only difference is that these three functions split sets in different ways.

The Disadvantages of SQL Computation (II)

In the above computation, we have used the keyword top. In fact there is not such a thing (it can be combined out by other computation computations) in the theory of relational algebra, and this is not the standard writing style of SQL.

Let us see how difficult it is to look for the top 10 places when there is no top.

Rough working thought: Seek out the number of members whose sales amount are higher than itself to rank the sales person, and then get the members whose places do not exceed 10, and the SQL is written as follows:

select sales

from ( select A.sales sales, A.product product,

(select count(*)+1 from sales_amount

where A.product=product AND A.amount<=amount) ranking

        from sales_amount A )

where product='AC' AND ranking<=10

or

select sales

from ( select A.sales sales, A.product product, count(*)+1 ranking

from sales_amount A, sales_amount B

where A.sales=B.sales and A.product=B.product AND A.amount<=B.amount

group by A.sales,A.product )

              where product='AC' AND ranking<=10

Professional technical personnel may not necessarily write such SQL statement well! And only the first ten places are computed.

To say the least, even if there is top, it only makes it easy to get the preceding part lightly. If we change the problem into getting the 6^th place to the 10^th place, or seeking the salesman whose sales amount is 10% more than that of the next one, the difficulty is still there.

The reason causing this phenomenon lies in the third important disadvantage of SQL: Lack the support of ordered set. SQL inherits the unordered set in mathematics, which directly causes the fact that the computations relating to sequence are rather difficult. And it can be imagined how common the computations relating to sequence (such as over the preceding month, over the same period last year, the first 20%, and rankings) will be.

The newly added window functions in SQL2003 standard provides some computation capabilities relating to sequence, which makes it possible to solve some problems in a relatively simple method and alleviate the problem of SQL to a certain extent. But the use of window functions is often accompanied by sub-query, and it cannot enable user to directly use the sequence number to access set member, so there are still many ordered computations that are difficult to solve. 

Now we want to pay attention to the gender proportion of the “good” salespersons that are computed out, that is, how many males and females there are respectively. Generally, the gender information is recorded in the employee table but not in the performance table, and it is simplified as follows:

employee	Employees table
name	Names of employees, suppose there is no repeated name.
gender	Genders of employees.

We have already computed out the name list of “good” salespersons, and the relatively natural idea is to seek out their genders from the employee table using name list, and count the number. But in SQL, it is necessary to use join operation to get information across tables . In this way, following the initial result, SQL will be written as:

select employee.gender,count(*)

from employee,

( ( select top 10 sales from sales_amount where product='AC' order by amount desc )

intersect

( select top 10 sales from sales_amount where product='TV' order by amount desc ) ) A

where A.sales=employee.name

group by employee.gender

With only an associated table more, it is made so over-elaborate and in reality there are rather more cross-table storages and they are often multi-layered. For example, for salespersons, there are departments where there are managers, and now we want to know by which managers these “good” salespersons are managed. Then there are three table joins, and it is indeed no easy job to write clear where and group in this computation.

This is just the fourth important disadvantage of SQL as we want to say: Lack of object reference, in relational algebra, the relations between objects completely depends on foreign key. This not only makes the efficiency very low in looking for relation, but also makes it impossible to directly treat the record pointed by foreign key as the attribute of primary record . Try thinking, can the above statement be written as this:

select sales.gender,count(*)

from (…)          // …is the SQL computing the “good” salespersons above

group by sales.gender

Evidently, this statement is not only clearer, and at the same time, the computation will also be more efficient (without join computation).

We have analyzed, through a simple example, the four important difficulties of SQL. We believe this is just the main reason why SQL fails to reach the original intention of its invention. The process of solving business problem based on a kind of computation system is in fact the process of translating business problems into formalized computation syntax (similar to the case in which a pupil solves application problem, translates the problem into formalized four arithmetic operations). Before overcoming these difficulties, SQL model system rather does not comply with people’s natural thinking habit, causing great barriers in translating problems, making it very difficult for SQL to be applied, on a large scale, in data computation for business problems.

For still another example which is easily understood by programmer, use SQL as data computation, which is similar to the case in which assembly language is used to complete four arithmetic operations. We very easily write out the calculation expression such as 3+5*7, but to use assembly language (take X86 as the example), it needs to be written as

mov ax,3

mov bx,5

mul bx,7

add ax,bx

In either writing or reading, such code is far inferior to 3+5*7 (it will be more troublesome if we come across decimal). Though it cannot be regarded as a big problem to a skilled programmer, to most people, however, this kind of writing is too hard to understand. In this sense, FORTRAN is really a great invention.

The Disadvantages of SQL Computation (I)

SQL is invented primarily to provide a method to access structured data in order to transparentise the physical storage scheme, so a lot of various types of English vocabularies and syntaxes are used in SQL to reduce the difficulty in understanding and writing it. And the relational algebra as the basic theory of SQL is a complete computation system, which can compute everything in principle. In terms of this, we certainly should use SQL to satisfy various demands for data computation.

But, though relational database has achieved a huge success, evidently SQL fails to realize its original aim of invention. Except very few simple queries can be completed by end user using SQL, most of SQL users are still technical personnel, and even many complex queries are no easy job for technical personnel.

Why? We inspect the disadvantage of SQL in computation through a very simple example.

Suppose there is a sales performance table consisting of three fields (to simplify the problem, date information is omitted):

sales_amount	Sales performance table
sales	Name of salesman, suppose there is no duplicate name.
product	Products sold
amount	Sales amount of the salesman on the product

Now we want to know the name list of the salespersons whose sales amounts rank among the top 10 places both in air-conditioners and TV sets.

This question is rather simple and people will very naturally design out the computation process as follows:

1) Arrange the sequence according to the sales amount of air-conditioner and find out the top 10 places.

2) Arrange the sequence according to the sales amount of TV and find out the top 10 places.

3) Get the intersection of the results of 1 and 2 and obtain the answer.

   

Now we use SQL to do it.

1) Find out the top 10 places of the sales amount of air-conditioner. This is very simple:

select top 10 sales from sales_amount where product='AC' order by amount desc

2) Find out the top 10 places of the sales amount of TV. The action is the same:

select top 10 sales from sales_amount where product='TV' order by amount desc

3) Seek the intersection of 1 and 2. This is somewhat troublesome, as SQL does not support computation by steps. The computation result of the above two steps cannot be saved, and thus it is necessary to copy it once again:

select * from

( select top 10 sales from sales_amount where product='AC' order by amount desc )

intersect

( select top 10 sales from sales_amount where product='TV' order by amount desc )

A simple 3-step computation has to be written like this using SQL, and daily computations of more than 10 steps are in great numbers. So this evidently goes beyond the acceptability of many people.

In this way, we know the first important disadvantage of SQL: Do not support computation by steps. Dividing complex computation into several steps can reduce the difficulty of a problem to a great extent. On the contrary, completing many steps of computation into one step can increase the difficulty of a problem to a great extent.

It can be imagined that, if a teacher requires pupils to create only one calculation formula to complete the calculation in solving application problems, how distressed the pupils will feel (of course, there are certain clever children who can solve the problem)!

SQL query cannot by conducted by steps, but the stored procedure written out with SQL can operate by steps. Then, is it possible to use the stored procedure to conveniently solve this problem?

For the time being, we just ignore how complex is the technical environment in which the stored procedure is used (this is enough to make most people give it up) and the incompatibility caused by differences of databases. We only try to know theoretically whether it is possible to use SQL to make this computation simpler and faster.

1) Compute the top 10 places sales amount of air-conditioners. The statement is still the same, but we need to save the result for use by Step 3, while in SQL, it is only possible to use table to store set data. So we need to create a temporary table: 

create temporary table x1 as

select top 10 sales from sales_amount where product='AC' order by amount desc

2) Compute the top 10 places of the sales amount of TV. Similarly

create temporary table x2 as

select top 10 sales from sales_amount where product='TV' order by amount desc

3) Seek the intersection, the preceding steps are troublesome but this step is simpler.

select * from x1 intersect x2

After the computation is done in steps, the working thought becomes clear, but it still appears over-elaborate to use a temporary table. In the computation of mass structured data, temporary set, as intermediate result, is rather common. If the temporary table is created for storage in all cases, the computation efficiency is low and it is not intuitive.

  

Moreover, SQL does not allow the value of a certain field to be a set (namely temporary table), so in this way, it is impossible to implement some computations even if we tolerate the over elaborate.

If we change the problem into computing the salespersons whose sales amounts of all products rank among the top 10 places, try thinking how to compute it. By continuing to use the above-mentioned working thought, it is very easy to get the below points:

1) Group the data according to products, arrange the sequence of each group, and get the top 10 places;

2) Get the intersection of the top 10 places of all products; 

As we do not know beforehand how many products there are, so it is necessary to also store the grouping result in a temporary table. There is a field in this table that needs to store the corresponding group members, which is not supported by SQL, so the method is unfeasible.

If supported by window function (SQL2003 standard), it is possible to change the working thought. After grouping by product, compute the number of times each salesman appears in the top 10 places of the sales amounts of all product category group. If the number of times is the same as the total number of the product categories, it indicates this salesman is within the top 10 places regarding the sales amounts of all product categories.

select sales

from ( select sales,

       from ( select sales,

              rank() over (partition by product order by amount desc ) ranking

              from sales_amount)

      where ranking <=10 )

group by sales

having count(*)=(select count(distinct product) from sales_amount)

How many people can write such complex SQL?

Moreover, in many databases, the window functions are not supported. Then, it is only possible to use the stored procedure to develop a loop, according to the sequence, the top 10 places of each product, and seek the intersection of the result of the preceding time. This process is not very much simpler than using high level language to develop, and it is also necessary to cope with the triviality of the temporary table.

Now, we know the second important disadvantage of SQL: Set-lization is not complete. Though SQL has the concept of set, it fails to provide set as a kind of basic data type, which makes it necessary to transform a lot of natural set computations in thinking and writing. 

esProc Implements Cross-database Report Associating

In practical applications, many reports are populated with data from different types of databases. So, the cross-database data sources are quite common in report developing. Currently, there are many ways to implement such cross-database report associating, and none of them are perfect.

Rely on the native multi-source associating function of the reporting tools 

Nowadays, most major reporting tools are real convenient in specific aspects for enabling its report users to implement the associating across multiple data sources. However, these native functions for implementing the associating across multiple data sources are not always so effective and efficient in handling many database report associating problems. It all depends on the data structures and specific businesses. Considering reporting tools are mainly designed for the data presentation but not the data computing, we cannot really complain.

Even for those reporting tools that can barely do this job, they are relatively inefficient in handling the cross-database associating - far worse than database. Moreover, since a great number of hidden rows and columns are often involved in the implementation procedure, the performance can be further hindered with more memory spaces are consumed.

So, the below two methods get popular:

Consolidating data into one database 

Consolidating data from various databases to one database is very common in various applications with an aim of leveraging powerful computing capability of database (SQL). However, this practice costs more. It unavoidably takes additional expansive database spaces, accumulate excessive data in database, incur great difficulty in administration and management, and cause more pressure. In addition, the additional database purchases and management costs may also another burden to bear. ETL data migration means lots of incredible workload. Not to mention that the trigger is necessary to prepare those reports demanding high on the realtimeness. The original database performance will be affected greatly.

Implement the cross-database associating to prepare data for report with the senior languages 

To remedy the drawbacks of the above two solutions, some users use the senior programming languages like Java to implement the cross-database computation and customize the data source for reports. The major advantage of this practice is its flexibility. Theoretically, all computations can be implemented by programming. But such programming is never an easy thing. Java and other alike senior languages still have not effectively supported the function and syntax for the operations on sets. Without the corresponding class library, a simple group could require many (looping) codes, not to mention the grouping, summarizing, and other operations after associating.

In such challenging conditions, esProc is an ideal choice to implement the cross-database report associating.

How esProc implements cross-database report associating? 

Let’s check out how esProc implements the report associating across databases with an example.

Business Description 
The monthly payable salary of a typical enterprise employee is related to the basic salary, attendance, and performance of that month. The attendance information comes from the attendance system (HSQL database) of Human Resources. The basic salary and performance data is stored in the finance system (MySQL database). To compute the employee salary, we need to consolidate these two types of data:

Implementation steps 

Compose script (crossDB.dfx) to complete the cross-database associating, and prepare data for the report 

• In A1 and A2, connect HSQL and MySQL data sources respectively with function connect 
• In A3, A4, and A5, retrieve the required data from two databases respectively with the query clause. To this point, all data have retrieved and database exchange is not necessary. In A6 and A7, the connection between the two databases is closed. 
• In A8, use Join clause to complete the joining of these three tables 
• In A9, compute the payable salary with the algorithm: Basic salary*(1-Attenance coefficient+Performance coefficient) 
• Lastly, generate the result set for use in report with the function result in A10

Calling esProc Script in Report for Report Presentation 
The esProc class is packaged into standard JDBC, allowing reporting tools for calling the esProc script in the same way as calling stored procedures. For example, in this example reporting tool, firstly, configure the esProc JDBC just in an alike way to configure the database connection; secondly, establish the dataset for stored procedure, and then use the call crossDB() to complete the calling.

Let’s take BIRT as an example to demonstrate the procedure of calling:
1.Copy the esProc JDBC driver package to the corresponding directory
2.Configure the report data source



3.Set DataSet and call the esProc script


Then, the whole calling procedure can be completed in a few very simple steps for BIRT to use the result from esProc and present the output directly.

Conclusions 

As can be learned in this example, esProc is quite good at cross-database computing and returning the result in the form of standard ResultSet, acting as the data source for the reports. In the report, it is quite simple to call the esProc script in a way similar to calling the stored procedure.

One more thing worthy of attention is the price. Although a priced commercial software, esProc is free for its report data source feature, and only a reasonable fee is charged for its big data cluster feature. In a word, no additional cost will be incurred if using esProc to meet the challenges on report associating across multiple databases.

Several Factors Affect Report Performance

In software projects users often complain that the report and query is too slow to be accepted. This brings great pressure to the developers.So, what are the main steps in report production process? What stages of this process may suffer from performance issues? How to optimize these stages?

Let's look the steps for report processing, which is shown below:

Figure 1：Report handling process illustration

As shown in the above figure,the report process is:

1. The user selectsparameters for report, and the report engine will interpret the report according to the report template and input parameters. Requests for data computation and retrieval are then sent to data layers.

2. The data layer is responsible for data retrieval, computation and the return of the data. Data layers are normally traditional databases: Oracle, DB2, etc.

3.  The data layer pass the data result, through interfaces like JDBC, to reporting tools.

4.  Reporting toolspresents to users the data they received with visual reports, according to the report template & styles required.

During the whole process of report preparation, each stage in the diagram, report layer and data layer can become a performance bottleneck.

One thing worth to discuss is that in Figure 1, the stage"R2 generate visual report" normally includes some data computation process: data returned from the data layer has to be further processed to meet the report requirement.It’s especially true for those reporting tools with programming capabilities that this stage will consume some computation time.For example: the the custom scripts for BIRT, custom dataset for Runqian Report, and inter-cell computation.

Therefore, we can separate this stage, which is good for us to analyze the factors affecting the performance of the report. After being separated,the process is as below:

 

Figure 2 Report handling process illustration after being further separated

In Figure 2, the highlighted stagesare the main reasons for performance degradation. Obviously, the interpretation of report by the report engine, and the presentation of the report seldom become the key factor for performance issues. Instead, data computation, as well as JDBC data transmission is normally the root of such issues. Therefore, optimization on data computation or JDBC transmission is a more rewarding approach.
As we are clear with the report generation, let’s now deep dig for reasons in the highlighted stages of Figure 2, which are causing the performance issues. 

I.The data layer"D1 executing data computation"stage.

In development practices, report developers often need to write very long and complex SQL statements (or stored procedure) to prepare data for the report.

Developers usually find that it’s difficult to implement complex computation with SQL (or stored procedures), especially when they are related to ordered sets. For example, when computing for products, which ranked in the monthly top 10 products lists, or stocks, whose price rise for last five consecutive days. The SQL statement to prepare data for these report are too complicated and may run for tens or even hundreds of lines.

Since it’s difficult to manually specify the full execution path for the database, the execution of complex SQL statements are often slow. It’s hard for developers to manually optimize these complex SQL statements. This is the main factor that affects the performance of "D1 executing data computation" stage.

Example: Computing for customers who rank in Top 10 revenue contributor in every month.

select Client from(

select * from(

select B.*,row_number() over(partition by month order by SumValuedesc)

rown from(

selectto_char(SellDate,'mm') month,Client,sum(Quantity*Amount) SumValue

from contract

whereSellDate>=to_date('2012-01-01','yyyy-mm-dd')

andSellDate<=to_date('2012-12-31','yyyy-mm-dd')

group by to_char(SellDate,'mm'),Client order by month,client ) B

)C

whererown<=10

)D

group by Client

having count(Client)=(

select  count(distinct(to_char(SellDate,'mm')))

from contract

whereSellDate>=to_date('2012-01-01','yyyy-mm-dd')

andSellDate<=to_date('2012-12-31','yyyy-mm-dd')

)

II.The data layer "D2 JDBC data transmission" stage.

The JDBC drivers from traditional mainstream database are usually less efficient when transfer data stream into Java objects. However, they cannot be bypassed. When handling large amounts of data the performance is not acceptable. Even with new versions of databases, the problem remains. Therefore, for reports against large amount of data, JDBC is often the reason that the report is becoming too slow.

III.The report layer "R2 data re-computation"stage.

1. Many reporting tools handle " R2 data re-computation" and "R3 generating visual presentation"together. The data computation stage includes lot of display attributes(font, color, cell size, etc), which will slow down the computationspeed.

2. The Computational programming in reporting tool often includes custom functions, JavaScript or java language and does not provide complete relational data computation library. Most of the functionality requires programmers to write their own implementation. The result is high cost and lengthy development, as well as unpredictable code quality or report performance.

3. Reporting tools do not have the ability for parallel computing. This means complex task with large amount of data cannot be split into small and separated tasks to be processed by stand-alone or multiple machines.Thusit is difficult to effectively improve computing speed.

Know the causes for low performance issues existed in these stages, we can now consider the following optimization alternatives:

I.The data layer"D1 executing data computation"stage.

To cope with the difficulties associated with complex SQL statement optimization, we can use procedural programming language. In this regard, esProc is a good choice. Its code is more in line with the natural way of thinking, and isseveral times shorter than SQL when solving same issues. On the one hand, it’s easy to develop, debug and maintain, reducing development costs; on the other hand, it’s up for the programmer to select the execution path and process, which is easier to improve computing performance.

With use esProc, it’s very easy to computefor customers who rank in Top 10 revenue contributor in every month:

II.  The data layer "D2 JDBC data transmission" stage

To resolve the issue of slow data transmission with JDBC in traditional database, we can consider storing the relatively large-scale historical data in file system. It’s much faster to access data in file system through esProc, than JDBC transmission from traditional database. Although the data transmission from esProc to reporting tools is also done through JDBC, it’s much faster as esProc’s JDBC doesn’t need to convert the data stream object in any way.

Another possible solution is the use of parallel computing with several esProc subroutines to retrieve data from database through multiple JDBC connections. During actual test against Oracle we found that with 16 parallel tasks the data transmission speed can be increased by ten times. Considering the increased overhead to the database, this solution is suitable for systems where the database is less stressed.

III.  The report layer "R2 data re-computation" stage

1．esProc does not include display properties in its computation. Thus the computing speed is improved .

2. esProc comes with a rich library for relational data computation, which offers great varietyof performance optimization options. Especially when used with ordered sets, programmers can effectively leverage the regularity ofbusiness data to control the execution plan and improve computing speed.

3. esProc comes with parallel computing capabilities, with which we can split large data and more complex tasks into small parallel tasks running on multiple machines. This will effectively improve the computing speed.

Note: for specific performance test measurements, please refer to esProc’s performance test reports.