esProc Implements Dynamic Pluggable Computing Modules for Java Application

In JAVA development, programmers sometimes get stuck in such a situation where the other modules are relatively stable, but in the computing module some unpredictable changes often take place. You have to rewrite the computing module every time when you need, for example, the algorithm will change with business rules;the types of data sources and data structures must be adapted to the specific application environment; the query criterions must be adjusted accordingly based on user’s ever-changing demands. In such scenarios,there needs to be a dynamic pluggable computing module to replace the original computing module without affecting other functions. Here are some common examples:

1. With the same query functions, different users have different requirements on the query criterions.You need to customize the computing module as required by given users.

2. To read data from Excel and compute such data, the parameter input interface and result output interface all keep unchanged, but you needto use different algorithms dynamically depending on Excel format.

3. Each branch has its own database, you need to present the same query results from different databases with different types and structures.

4. Real wages of employees in an enterprise are computed based on performance scores with an algorithm that changed frequently, and you need to replace the old algorithm with a new one while other codes being unchanged.

There are many ways to implement such dynamic pluggable computing modules. Here we describe how the computing module in the above example 4 is implemented with esProc.

First, to implement the computing module in esProc, its script is shown as below:

         

This article focuses on input and output parts, but not on the specific algorithm. As can be seen from cell A1, this module has two input parameters: start and end, which come from the caller, namely, JAVA codes. As we can see from cell A12, the computed result will be output to JAVA through JDBC.

Then, to implement the calling code of dynamic module in JAVA, as shown below:

Class.forName("com.esproc.jdbc.InternalDriver").newInstance();

Connection conn = DriverManager.getConnection("jdbc:esProc:local://");

CallableStatement cstmt = conn.prepareCall("call dynamic(?,?)");

cstmt.setString(1, start);

cstmt.setString(2, end);

cstmt.execute();

ResultSet rs = cstmt.getResultSet();

As you see, the methods to call esProc script and stored procedure of common database are exactly same; where, Driver Name and URL must follow the naming rule of esProc. dynamic is the module name, i.e., prefix of esProc script file name dynamic.dfx. dynamichas two input parameters: start, end. The computed results are stored in variable rs, which can be directly output to a Web or a client application.

Finally, to plug the computing module dynamically.

When the algorithm changes, the old module requires to be replaced with a new one. As we can see from codes in the previous step, the computing module is less likely to couple with JAVA codes, so that it only needs to be replaced directly with the file of the same name. If you want to retain the old module, the module name can also be read from profile, or passed to prepareCallfunction via string parameter. The new module code is not listed here, because that’s not the point of this article.

Through the above steps, esProc will help you easily replace the old computing module with a new one.

Of course, the dynamic pluggable computing module can also be achieved in JAVA, but JAVA lacks the function library for basic algorithms, such as: grouping, aggregation, sorting, filtering, joining, distinct value, intersection, rank, etc., the programmer shave to write these basic algorithms manually to achieve computing modules. It is obviously unreasonable for you to implement these basic algorithms directly in the business logic, because thiswould lead to repeatedly writing similar codes for each computing module, further to make them too enormous and less readable. The ideal practice is to implement a set of basic algorithm library functions, then call these library functions in allcomputing modules.But it is harder for application programmers to design a complete set of well-built systematic basic-algorithm function library,this often leads to higher coupling codes, poor stability, eventually muddled maintenance on computing modules. In addition, JAVA codes need to be recompiled, and deployed with more trouble; when there are lots of pluggable computing modules, whether they would be used or not, these Java class/jar will occupy a certain memory space but cannot be released, producing a certain impacton performance.

esProc itself is a set complete of well-designed basic library functions, and only required one-tenth of JAVA codes, it can implement the computing module with equivalent functions, so as to return you a higher developing efficiency; esProc's main program and the script file are separated from each other, between which, a lower coupling makes maintenance far more convenient. In addition, esProc script can be used without compiling, as a real hot-deployment, esProc script will not be loaded into memory in advance, but loaded as needed, then released immediately after computation is finished, not taking up too much memory over a long time.   

esProc Enables Oracle JDBC to Speed Up Retrieve Process

Recently, a performance issue occurs in a big detail report, where the original data volume in the two data tables as it involves has exceeded 3 million rows; the data volume that the report can retrieve after filtering remains at round 2 million rows. As we expected before, by using paged retrieve method, this report would present a much higher performance. However, for the fact that the user also needs to export the data from the report, the only option is that the data retrieve can be achieved from Oracle at a time. As the JDBC of Oracle runs quite slowly, it takes least 6-7 minutes to retrieve the data of more than 2 million rows from the Oracle. In general, such performance cannot meet the expectation of the customer.

      

Through great efforts we have made for a long period, this issue was finally solved by a way that the retrieve time can be cut down to dozens of seconds, thus to achieve the performance as the user requires. The solution mainly involves the use of parallel mechanism of esProc, and by multiple JDBCs, to achieve the synchronization of data retrieves.

Basic Information

Hardware:

HP ProLiant DL380 G7, CPU Intel Xeon E5620*2, RAM 20G, HDD Raid5 1T

Software:

Oracle11g64-bit, esProc, CentOS 6.0, JDK 1.6u43

Data Volumes of 2 Data Tables:

VM: 100K rows, 168 cols; and 65k rows after filtering

CT: 3 million rows, 16 cols; and 1.95 million rows after filtering

Basic Idea

By utilizing the parallel mechanism of esProc, the task of retrieving data from the two Oracle tables can be divided into a number of sub-tasks, which can be executed synchronously. And then the retrieved results are merged into one for each table. Finally, the two tables with merged results are joined to return the set of results for the report.

Implementation

Main Program Script

First, the database connection is created, in order to fetch the records from the two tables. Second, the data is segmented as per the number of sub-tasks (the screen shot shows the case of 16 sub-tasks), and the query range is assigned for a subroutine. Third, the subroutine is called by using callx to complete the query process. In the end, the main program is available to merge the results and produce a set of the end results which will then be returned to the report.

Subroutine Script

It is easy for the subroutine to complete the query process within a specified query range.

Performance Comparison

Pre-parallel (No Parallel)	Post-parallel (Parallel)
	4 Parallels	8 Parallels	16 Parallels
436s	113s	81s	43s

As a result, here we see a leap from 400s to 40s in the data retrieve process.

Note:

1. What is described here covers only the part on how the data retrieve process can be expedited and how the esProc is used to generate the data source for the report in a fast way. However, the description on design and presentation of report is not mentioned here.

2. When the parallel retrieve process is used, it is required to create a connection with the database by each subtask, for example, connect(conn), which can be found in the subroutine script;  

3. Parallel retrieve process is suitable for the case where the Oracle resources are spare (e.g. the number of connections is still far below the upper limit). In other words, this feature can enable the report to make full use of the database resources.

Two Approaches to a Complex Report

The following is a relatively complex report.Two approaches are tried and here is a record on my thoughts.

The report is as following (to protect the customer’s information assets,we simplified the table structure in the testing environment):

After the simplification, the table structure are：

order_details table, contains the following fields:
Product id, productid
Order id, orderid
Unit price, price
Sales quantity, quantity

Sales order table, orders, with fields:
Order number, orderid
Sales date, orderdate
Oder status, ordertype (1 means valid order)
Customer id, customerid

Approach 1: with complex SQL statements
The report can be implemented with the following complex SQL statement:

select b.*,a.DAY1,a.QTY1,a.DAY2,a.QTY2
from
(select t2.productid,t2.quantity as QTY1,t1.orderdate as Day1,
row_number() over (partition by t2.productid order by t1.orderdate desc) rn,
lead (orderdate) over (PARTITION BY t2.productid order by t1.orderdate desc) as Day2,
lead (t2.quantity) over (PARTITION BY t2.productid order by t1.orderdate desc) as QTY2
from orders t1,order_details t2
where t1.ordertype='1'and t2.orderid=t1.orderid 
)a,
(select t2.productid, sum(t2.quantity)as ZS, count(t2.productid) asCS, 
sum(case when t2.quantity<=50 then 1 else 0 end) as CS1, 
sum(case when t2.quantity>50 and t2.quantity<=100 then 1 else 0 end)as CS2,
sum(case when t2.quantity>100 then 1 else 0 end) as CS3 
from orders t1,order_details t2 where t1.ordertype='1'and t2.orderid=t1.orderid 
group by t2.productid)b
where a.productid=b.productid and a.rn=1

This complicated SQL statement consists of two parts: sub query a leverages window function to compute the value for last order and second last order; sub query b leverage case when function to calculate the order count for sales quantities falling into different value groups. Generally speaking the statement is complicated. After 2 or 3 months, even the developer himself/herself will need more time to understand such SQL statement.

Approach 2: Solving the problem in a step-by-step way
The above SQL is hard to read, because it’s not in accordance with the human way of thinking. Normally, as in Excel, the problem could be resolved in such way:

1.Create an empty result table with the following fields: "Product id, total sales quantity, last order date, last sales quantity, second last sales date, second last sales quantity, order count for orders with sales quantity < 50, order count for orders with sales quantity between 50 and 100 , order count for orders with sales quantity >100. "

2.Retrieve the data from sales order table and sales order details table, and associate them with order number to form an associated table.

3.Separate the associated table into groups according to different products , with the members of each group corresponding to the order details of a product. These groups can be processed one by one.

4.Each group has the same product id, and sum of the group’s sales quantity is the " total sales quantity of the product ."

5.After each group is sorted by order date ( in descending order), the first record will be last order and the second will be second last order.

6.If each group is further separated into sub sets according to the order quantity of less than 50, between 50 to 100, and more than 100, the number of record in each group is then number of orders for each category.

7.In this way, the result for each product can be computed and added to the result table. Repeat loop to compute the data for next group of products.

However, SQL is not a procedural language. It’s hard for it to implement such computation. Here we tested the step-by-step computation of esProc. Personally, I think this is more in line with human way of thinking. The script is as following:

My Thoughts: comparison of two approaches

In fact, the aboveSQL statement is not the most complex onein the project. There are ones with tens or hundreds of linesofSQL statements in the project.Normally we start the writing from simple SQL statement, and then the complex ones. After they are done we feel a great deal of achievements. However, the ongoing maintenanceand modificationis becoming a headache, even for the developer themselves. If they are maintained by others, the difficulties are doubled. This is one of the reason for added project costs.

Possible reason for this is that, to meet are query request, SQL does not work in the same way as human mind. Human beings are used to solve the problems in a step-by-step way. If the codes are in line with this habit, it will be easy to understand, maintain and modify.

esProc’s approach is no better in terms of the length of codes. However, the process is easy for understanding. This makes it much easier for future maintenance. For complicatedcomputation process this is in great advantage.

esProc Implements Report with File as Data Resource

In the real business, quite a few report data is from file instead of database. Take the below case for example: To compute over the attendance data files for preparing the salary payable report, the log file for preparing the user behavior analysis report, and the stock trading record file for preparing the stock analysis report. Because the report usually comes with the parameter, people often need to group, filter, and perform other similar operations over the row data. To serve this purpose, the file should support the re-computation which is unfortunately just what the file lack. Nowadays, these two practices are generally adopted:

Import data to database

Since the file itself is not powerful enough in computing, it is natural for people to consider storing the data in the database, with an aim to leverage the computing capability of database to prepare data for report. As is commonly believed, regarding the solution to get the data source tables ready for most files, we cannot care more about the cost problem.

The expenditure usually comprises the costs in these 3 aspects: time, resources, and administrations.

Time cost. After all, programming does take time (even the load action requires scripting). Moreover, the speed of inserting data into the database is not always satisfactory - more time-consuming for more data volume.

Resource cost. Importing data to the database will definitely take up the expensive database space, and incur the extra database purchase cost.

Administration cost. The flat storage mechanism for database increases the difficult of administration when there are massive amount of data tables, along with the higher pressure and the administration and maintenance costs.

Process file using the custom data source of reporting tool

The typical reporting tools all provide the custom data source function. Programmers can thus code by themselves to implement the data source. With this advantage, the high level languages like JAVA and C# can be used to implement the data computation function for the specific files.

The major advantage of this practice is its flexibility. Theoretically, all computations can be completed by programming. But such programming is never an easy thing. Java and other alike senior languages lack the effective support for operations on set. Without the corresponding class library, a simple summary could require many (looping) codes, not to mention the grouping, summarizing, and other operations after associating.

Comparatively, the free edition product of esProc is a more convenient solution to provide the custom data source for the reporting tools.

How esProc implement report with file as data source

Let’s illustrate it with an example:

The data file is the stock price information for each month (a file for a month, and the file is name as “stock_record_yyyyMM”, for example: stock_record_200901. txt, stock_record_200902. txt…), including the stock code, trading date, and closing price for each stock. We need to query out the respective longest consecutive rising days for each stock in a certain period, along with the date and the closing price on which the longest consecutive days have reached.

The report format is shown below:

Stock Code (code of stock)

TradingDate (trading date)

Closing Price (closing price)

BallooningNum (maximum rise in number of days)

Setting parameters

The “begin” is the query starting month, and the “end” is the closing month. They are sent by report.

Scripts

• In A1, with the parameters for the inputted starting and ending months, compute the involved month sequence, that is, the used file
• In A2, with import, loop and retrieve all file data required for this period
• In A3, with derive, add field ballooningNum to the TSeq
• In A4 and A5, after sorting by the stock code and trading date, group by the stock code
• In A6, by looping, judge if the closing price is higher than that of the previous day, and compute the consecutive rising days for each stock on every trading day
• In A7, with pmax, select the records holding the longest consecutive rising days of each stock, including the date on which the longest consecutive rising days is reached and the closing price. Then, sort by ballooningNum in descending order.

• Lastly, in A8, export the result set for use in report with the function result 

Report calling esProc script to complete the report presentation

The esProc  is packaged into standard JDBC, allowing the reporting tool for invoking 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 call stockRecord(? ,?) (In which the “?” is the parameter, representing the starting/ending years or months) to complete the invoking.

Let’s take BIRT as an example to demonstrate the calling procedure:

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 simple steps. BIRT can use the esProc computing result to directly present the output.

Structural Data Computing in Java for Non-database

In Java, implementing via SQL is a well-developed practice for database computation. However, the structured data is not only stored in the database, but also in the text, Excel, and XML files. Considering this, how to compute appropriately regarding the structured data from non-database files? This article raises 3 solutions for your reference: implement via Java API, convert to database computation, and adopt the common data computation layers.

Implement via Java API. This is the most straightforward method. Programmers will benefit from Java API in controlling every computational step meticulously, monitoring the computed result in each step intuitively, and debugging conveniently. Needless to say, no learning cost is also an additional advantage of Java API.

Thanks to the well-developed API for retrieving and writing-back data to Txt, Excel, and XML files, Java has enough technical strength to offer the full support for such computation, in particular the simple computational goals.

However, this method requires great workload and quite inconvenient. For example, since the common data algorithms have not implemented in Java, programmers will have to spend great time and efforts to implement all the ins and outs manually by aggregating, filtering, grouping, and sorting and some other common actions. For another example of data storage and detail data retrieval through Java API, programmers will have to combine every data and 2D table with List/map and other objects, and then compute in nested loops at multi-levels. Moreover, such computation usually involves the set operations and relational computations on massive data, as well as the computations between objects and object properties. It takes great efforts to implement the underlying logics and even greater workload in handling the complex ordered computation.

In order to reduce the programming workload, programmers always prefer leveraging the existing algorithms to implementing all specifics by themselves. In view of this, the second choice below would be a better choice:

        

Convert to database computation.This is the most conservative method. Concretely speaking, it is to import the non-database data to the database via the common ETL tools like DataStage, DTS, Informatica, and Kettle. The advantages of this practice include the high computational efficiency, steadfast running, and less workload for Java programmers. It fits for the scenarios of great data volume, high performance demand, and medium-level computational complexity. These advantages are evident for the mixed computation on the database and the non-database files in particular.

The main drawback of this method is the great workload in the early stage of ETL and the great maintenance difficulty. First, since the non-database data cannot be used directly without field-splitting, merging, and judging, programmers have to write a great many of Perl/JS scripts to clean and re-organize the data. Second, the data is usually updatable, so the scripting must handle the changing incremental update issues. The data from various data sources can hardly be compatible with a normal form. So, the data is unusable before the level 2 or even the level 3 ETL process. Third, scheduling is also a problem when there are lots of tables – which table must be uploaded first? Which one is the second to upload? What's the interval? In facts, the huge workload of ETL is always beyond our expectation, and it is always quite tough to evade project risk. Plus, the real-time performance of ETL is poor owing to the regular transit of the database.

        

In some operating environments, there is probably no database service at all for the sake of security or performance. For another example, if most data is saved in the TXT/XML/Excel and no database involved,then the existence value of ETL gets void. What can we do? Let's try the 3^rd method:

        

The common data computational layer is typified by the esProc and R. The data computational layer is a layer in-between the data persistence layer and the application layer. This layer is responsible for computing the data from data persistence layer uniformly and returning the computed result to the application layer. The data computation layer of Java is mainly used to reduce the coupling between the application layer and the data persistence layer, and alleviate the computational pressure on them.

        

The common data computational layer offers the direct support for various data sources - not only the database, but also the non-database data sources. By taking the advantage, programmers can access to various data sources directly, free from such things as real-time problems. In addition, programmers are allowed to implement the interactive computation between various data sources conveniently, for example, the computations between DB2 and Oracle, and MYSQL and Excel. In the past, such access is by no means easy to implement.

        

The versatile data computational layers are usually more professional on structured data, for example, it supports the generic, explicit set, and ordered array. So, the complex computational goals, which are tough jobs for ETL/SQL and other conventional tools, can be solved with this layer easily.

        

The drawback of such method mainly lies in the performance. The common data computation layer is of the full memory computation, so the size of memory determines the upper limit of the data volumes to handle. But both esProc and R support the Hadoop directly so that their users can handle the big data in the distributed environment.

        

The main difference between esProc and R is that esProc supports the direct JDBC output and convenient integrating with Java codes. In addition, esProc IDE ismuch easier to use, with the support for the true debugging, and scripts in grid, and cell name for direct referencing the computed result. R does not provide such advantages, nor support for JDBC, and thus a bit complex for R users to integrate. However, R supports the correlation analyses and other model analyses. R programmers do not have to implement all specifics to generate the computed result directly. R also supports the Txt/ Excel / XML files and other lots of more non-database data sources.By comparison, esProc only supports 2 of them. The last but not the least advantage of R is that the low-end edition of R supports the open source to the full.

The above is the comparison between these three methods, and you can choose the right one based on your project characteristics.

esProc’s Contribution to Report Development

With the constant improvement and maturing of reporting tools, the presentation features of the reports are growing stronger, supporting more and more reporting styles. At the same time, some report development aspects has gradually become bottlenecks. Two obvious ones are: 1) data preparation and computation for reports with complex business logic; 2) report development with heterogeneous and diversified data sources. A new development tool, esProc, can remove both bottlenecks easily. This will reduce the difficulty of report development and improve efficiency. 

Regarding the first bottleneck, let’s see the issue with data preparation and computation for reports with complex business logic. 
In project practice, report developers need to write some very long and complex SQL statements (or stored procedure), to prepare and compute the data for reporting. 

The main reasons are: 

1.The original database structure is normally designed for transactional system, and is not suitable for direct presentation in statistical reports. This results in very complex business logic to get report data from original data. 

2. Even for data warehouse, in which all data has been sorted and prepared to some degree, the structure is not suitable for all reports, especially some new reports needed in later stage of the projects. Usually, more complex computation is needed on the basis of data warehouse before submitting data to reporting tools for presentation. 

Report developers normally find it’s very difficult to do complex statistical computation with SQL (or stored procedures), especially computation related to sequence and sets, eg., computing for products which are top 10 revenue contributors every month, or stocks rising for five consecutive days. SQL statements for preparing such data is extremely complicated and could be tens or hundreds lines long. 

Let's look at a real life example with SQL. The following statements are written for Oracle, leveraging the window function. Other DBMS does not support the window function to the same degree as Oracle, which means the SQL statements written for them could be longer. 

Example 1: Computing for customers who are 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(
 select to_char(SellDate,'mm') month,Client,sum(Quantity*Amount) SumValue
 from contract
 where SellDate>=to_date('2012-01-01','yyyy-mm-dd')
 and bSellDate<=to_date('2012-12-31','yyyy-mm-dd')
 group by to_char(SellDate,'mm'),Client order by month,client
 ) B
 ) C
 where rown<=10
 ) D
 group by Client
 having count(Client)=(
 select count(distinct(to_char(SellDate,'mm')))
 from contract
 where SellDate>=to_date('2012-01-01','yyyy-mm-dd')
 and SellDate<=to_date('2012-12-31','yyyy-mm-dd') )

Example 2: computing for stocks rising for 5 consecutive days.

 select max(number_of_consecutive_days)
 from (select count(*) number_of_consecutive_days
      from (select sum(flag_for_rising_or_falling) over(order by trading_days)                days_not_rising
        from (select trading_days,
              case when closing_price >lag(closing_price) over(order by                           trading_days)
              then 0 else 1 end flag_for_rising_or_falling
              from price_index) )
      group by days_not_rising) 

These two examples are relatively simple. For more complex business logic, there will be multiple levels of nesting, and SQL statements with many window functions, running to tens or even hundreds of lines. These complex SQL statements does not follow our natural way of thinking. They are hard to write or debug, and difficult to modify. After a period of time, even the original developer might need extra time to understand.

Moreover, complex SQL statements is normally slow to execute, leaving the end users waiting long for the report. Since it’s difficult to specify the full execution path for the database, it’s usually hard for the report developers to find a way to manually optimize these complex SQL.

As the new generation of development tools, esProc can easily resolve such bottlenecks.

First, esProc is more in line with our natural way of thinking. When used to solve the same problem, its codes are several times shorter than SQL. It’s easier to debug and maintain. The execution process of esProc can be decided by the developer, which means it’s easy to be optimized.
Also with the two examples mentioned above, let’s see what’s esProc's way of implementation:

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

Example 2: computing for stocks rising for 5 consecutive days.



Meanwhile, esProc's provides standard JDBC interface, which means reporting tools can access the result-set from esProc in the same easy and seamless way as they do with databases.

The second bottleneck is the trouble of developing reports with heterogeneous and diversified data sources.

Many complex reports contains data from multiple heterogeneous databases, non-relational databases or even file system. A typical example is the payroll report: basic information about employees are from HR database; salary information is from accounting system; and attendance record is in text file.
All mainstream databases support integration of heterogeneous data sources, but in reality, these functionality does not prove to be handy. Even if they support integration of heterogeneous data sources, the performance is very bad.
Reporting tools can also be used to integrate heterogeneous data sources, but a considerable number of such tools do not support multiple data sources association. For the few of them which does support, the association feature is very limited, and might suffer from poor performance with any increase of data amount. Developers need to take care of the codes for data integration.
However, ordinary high-level languages (Java, C#) used for developing customer-defined data sources for reports lack class libraries for batch data computing. It’s very tedious to develop codes for such computation. A simple filtering summary will require tens of lines of codes, which is time-consuming, and code readability is poor. 

This bottleneck can also be easily resolved by esProc.

esProc can access all kinds of heterogeneous databases, non-relational databases and files. Its complete computing capability can easily associate multiple data sources for computing. The code length is several times, or even ten times shorter than Java.

For example, there is a "Project Payment Schedule Report", in which the data are from project information table project underlying the Project Management System (Oracle based), payment information table payProcess underlying the Accounting System (SQL Server based), and contract information table contract underlying the Contract Management System (SQL Server based) .

With traditional methods, it’s very difficult to join these three tables from three databases through project number projectNo. However, with esProc, it could be done easily. The scripts in following illustration shows that with 3 JDBC connections, oracledb, mssqlFin and msssqlCon, data from 3 databases could be retrieved for easy association with esProc's join and new functions.


Note that esProc's computing power does not depend on a database or other third-party engine. Thus regardless of the data sources, once they are retrieved into esProc, you can rely on esProc's wealth of computing functions and class libraries to do unified computing.

In summary, esProc can easily resolve the two major bottlenecks encountered in report development, and significantly improve development efficiency.

Replace Stored Procedure with esProc for Report datasource

Stored procedure is a collection of SQL queries and flow control statements. The stored procedure is usually used to break a complex computation task down to several simple computation steps. Although the stored procedure can improve the flexibility at the cost of complexity, it is always hindered by the inherent drawbacks of SQL, such as incomplete stepwise mechanism, unordered data, and lack of sets and references.
esProc not only overcomes the above drawbacks of SQL, but also greatly lowers the requirements on the technical background of developers. It is an ideal tool to substitute the stored procedure.

Case and Comparison 
A telecommunications manufacturer develops a report for analyzing the sales value, sales amount, and link relative ratio of the Superior Products. The Superior Products refers to “products whose sales amounts are among the top 10 in every state”, and the data is mainly stored in the stateSales table. The data structure of this table is as given follows:


The data in stateSales table is mainly about the sales amount information of each product in each state. However, it is not easy for the reporting tool to directly use these data. This is because:

1. The data is still unarranged with lots of duplicate data of the same state and product.
2. The algorithm is complicated. It is hard to compute the Superior Product. 

In order to make it easy for external use, we will need to process the data in the stateSales table. This procedure is called Data Preparation. Let’s compute the Superior Product in this case, and compare the respective performance of data preparation between the stored procedure and the esProc.

Stored Procedure Solution 
01 create or replace package salesPkg
02 as
03   type salesCur is ref cursor;
04 end;
05 CREATE OR REPLACE PROCEDURE topPro(io_cursor OUT salesPkg.salesCur)
06 is
07   varSql varchar2(2000);
08   tb_count integer;
09 BEGIN
10   select count(*) into tb_count from dba_tables where
table_name='TOPPROTMP';
11 if tb_count=0 then
12 strCreate:='CREATE GLOBAL TEMPORARY TABLE TOPPROTMP (
               stateTmp NUMBER not null,
               productTmp varchar2(10) not null,
               amountTmp NUMBER not null
       )
       ON COMMIT PRESERVE ROWS';
13 execute immediate strCreate;
14 end if;
15 execute immediate'truncate table TOPPROTMP';
16 insert into TOPPROTMP(stateTmp,productTmp,amountTmp)
   select state,product,amount from stateSales a
   where not(
     (a.state,a.product) in (
      select state,product from stateSales group by state,product
 having count(*) >1
      )
     and rowid not in (
       select min(rowid) from stateSales group by state,product having
 count(*)>1
        )
       )
      order by state,product;
17 OPEN io_cursor for
18 SELECT productTmp FROM (
    SELECT stateTmp,productTmp,amountTmp,rankorder
    FROM (SELECT stateTmp,productTmp,amountTmp,RANK() OVER(PARTITION
BY stateTmp ORDER BY amountTmp DESC) rankorder
     FROM TOPPROTMP
     )
    WHERE rankorder<=10 order by stateTmp
    )
    GROUP BY productTmp
    HAVING COUNT(*)=(SELECT COUNT(DISTINCT stateTmp ) FROM TOPPROTMP);
 END;

01-04: Define the ”cursor”
05-09: Define the stored procedure and variables
10-15: Define the ”temporary table”, check whether to redefine, and empty the temporary data.
16: Filter out the duplicate data and write the filtered data to the temporary table. Since it is not easy to retrieve the non-duplicate data, this tip may be helpful: find the duplicate data, and then use the Not operator to get the required data in a reverse way, then the remaining data is the non-duplicate data. These operations could be implemented by embedding two subqueries.
17: Open the cusor, and prepare for the data export.
18: Get the products that is among top 10 in every states: Firstly, rank the product in each state (i.e. rank order) with the window function; Secondly, filter out the top 10 products in each state; Finally, get the top 10 products in every state. Because SQL lacks the function to compute the intersection set, a tip could be used in this case: Group by product. Then, check whether the number of the same products equals to the number of states. If so, then it indicates that this product is among top 10 in every state. These operations can be implemented with a three-nested query plus a subquery. The SQL-2003 window functions can greatly reduce the difficulty.

Regarding the stored procedure, there are several ways to achieve this goal. In the above procedure, the computation is broken down to 2 clear steps. Of course, in the practice, more temporary tables and more SQL statements could be used to split the procedure into more steps at the cost of a much more lines of codes.

esProc Solution


A1 Cell: Retrieve data
A2 Cell: Filter the duplicate data in the A1
A3 Cell: Group the data in A2 by state
B3 Cell: Of each group (state) in the A3, compute the serial number of records whose sales amounts are among the top 10.
A4 Cell: Get the product from A3 with serial number .
A5 Cell: Compute the intersection set of each group of product in the A4.

In this case, the function rank() is used to compute the rankings, taking amount as the criteria for comparison; the function pselect() is used to compute the serial number of records ranking below 10; the function isect() is used to compute the intersection set of data from multiple groups.

Comparison To solve 
such problems with the stored procedure, the developer must have a relatively much higher technical competence. Even so, the highly capable developers with strong technical background may still often find themselves in a dilemma of either composing brief statements of bad readability or a great many lines of codes. On one hand, composing brief statements requires well-grasped SQL techniques, and composing a great many lines of codes requires great programming skills. On the other hand, the stored procedure is based on the SQL statement and thus hard to root out these inherent drawbacks. Considering this, it defeats the purpose for introducing the stored procedure to overcome the drawbacks of SQL statements.

As we can see, esProc users can solves this problem from a rather intuitive and descriptive business perceptive, not requiring any so-called “tips” to compose any statement hard to read. Every step is concise and easy-to-read. The whole procedure goes step by step to the analysis target easily.

Compared with the stored procedure, esProc by nature is definitely more suitable to solve the complex computation.

Feature: a JDBC Interface 
JDBC is a widely-applied standard interface that is often used by the application of Java architecture to call various databases. esProc is built with Java totally and provide JDBC interface for external use. When being called, esProc will be recognized as a database, allowing the external application, such as reporting tools, to access it with SQL statements.

Feature: a Professional Analysis Tool for Mass Data
esProc is a tool ideal for mass data computation, with same analysis ability capable for SQL statement and stored procedure: On the one hand, esProc can be used to query, filter, group, and collect statistics on data like SQL statement; On the other hand, esProc can implement the loop and branch judgment for the analysis procedure like the stored procedure.

Feature: a Better Performance than that of Stored Procedure
Compared with esProc, both SQL statements and stored procedures are also the analysis tool for mass data but have some obvious defects: the incomplete step wise mechanism, incomplete set-lizing, lack of serial number, and lack of reference. Therefore, when confront with complex computation, it is usually quite hard to design the SQL statement or stored procedures, and requires extremely strong technical background to be a competent developer.
esProc has overcome all drawbacks of stored procedure inherited from SQL, and thus is much more powerful than the stored procedure on the respect of analysis. Furthermore, esProc enable those developers of weak technical background to implement the complex computation easily

esProc Values 
esProc boasts a powerful analysis ability and low requirements on developer’s technical background, which saves them from the complex and hard-to-understand stored procedure.
esProc is not bond to any database and supports most common datasources, allowing developers to migrate conveniently. It is an analysis tool that is not only highly efficient but also easy to maintain.
esProc is specially built for the mass data computation as a smarter choice for data analysis.