A Code Example of External Memory Grouping in esProc

In data analysis, we often need to group data and then compute the aggregate value for each group, or perform other computations on each group. esProc allows using groups function to compute aggregate values for groups of data, as well as grouping records of a table with group function for use in subsequent computations. However, external memory grouping is required when data being grouped is big and cannot be loaded to the memory in its entirety and thus the above-mentioned methods for data summarizing and grouping become useless.

A table with big data containing1,000,000 records is used to simulate call duration records of mobile phone users. It is stored in the binary file PhoneBill:

Mobile phone numbers used for the simulation are 8-digit integers whose first four digits are the fixed 1234 and the rest is randomly generated. The records start on a randomly generated day of August in 2014. Values of call duration are integers randomly generated with a 90 percent chance of being one minute. The maximum limit is 20 minutes. After the data file is prepared, B11 fetches the first 1,000 rows:

Perform the following computations based on the data in PhoneBill:

①     Compute total call duration of all users and their average call duration in each day of August.

②     Compute every user’s total call duration in August.

③     Store each day’s call records in a file.

④     Find the numbers of five users who make the longest call in total for each day of August.

To solve the first problem, data needs to be grouped and summarized by DateTime. As there are only 31 days in the month of August, the result set won’t contain big data and the aggregate operation can be performed in the memory:

A2 generates a cursor for the binary text data in A1. A3 groups and summarizes data from the cursor. The result is as follows:

Since there is not so much data in the result, the cursor data only needs to be traversed once. Here groups function is adequate to perform the group and aggregate operation and external memory grouping is not necessary. In fact the result set can be directly acquired without the cursor, thanks to the reasonable amount of data. For more related information, please see esProc External Memory Computing: Principle of Grouping. To compute the average call duration in a day, we need to first get the total call duration and the total number of calls in this day. The result of A4 is as follows:

It is a different case for the second task. As the number of users is far more than the number of days in August, we should consider if the result of data grouping and summarizing can be returned to the memory all at once. There are 10,000 users at most in this example, which, actually, is not a large number. It is merely used to illustrate the computation of big result set. Here we suppose the memory can only hold 1,000 records:

groupn function is needed to handle the grouping and summarizing of big data. The function fetches data from cursor and performs group operation in batches according to the pre-specified number of buffer rows, using the external memory. By Phone and according to the specified 1,000 buffer rows, A3 groups and aggregates cursor data, computing the total call duration in the month. The result of grouping and summarizing big data is still a cursor. Fetching data from it is no different to fetching data from any other cursor. The result of A3 is as follows:

A4 fetches the records of the first 1,000 users. Their total call duration is as follows:

By the way, if data in the cursor hasn’t been entirely fetched, cs.close() function needs to be called to clean the temporary files from the external memory in time.

The third task requires grouping data by the DateTime first before storing data of each day in a file:

Different from other grouping operations on big data, groupn function requires specifying group numbers directly in the grouping expression. In A3, the dates in DateTime are used as the group numbers. Different from the previous result of data grouping and summarizing, A3’s function returns a sequence of cursors. Each cursor corresponds to a group:

The fourth and fifth lines of code store cursor data in each day in a file. A6 selects four days’ data, and A7 fetches the first 1,000 records from it:

For the fourth problem, group data by DateTime, and then group and summarize data of each group. Finally, select the desired mobile phone numbers from the aggregate results:

A3 directly specifies dates of DateTime as group numbers in the grouping expression, and returns a sequence of cursors:

The code from the fourth to sixth line loops through cursor data of each group to compute the total call duration per user per day, and according to the aggregate result, finds records of users whose call duration is in the top five. Please note that when aggregate function topx is used to sort data in descending order, just add a negative sign before the sorting expression, like –TotalDuration in B5. Select data of five numbers that have made the longest call and store it in the table sequence in B3. After loops are finished, the final result can be viewed in B3 as follows:

An alternative choice is to use the files generated from handling the third problem, instead of grouping all the original data. 

Performance Test of esProc In-Memory Computing

In this article, we’ll test the performance of esProc in handling in-memory small data computing, and compare with that of Oracle when performing the same computation.

The test involves two cases: normal simple computing and complicated related computing:

The test data used in normal computing is order information, as shown below:

ORDERID CLIENT     SELLERID AMOUNT          ORDERDATE

1       287  47     5825         2013-05-31      

2       89     22     8681         2013-05-04      

3       47     67     7702         2009-11-22      

4       76     85     8717         2011-12-13      

5       307  81     8003         2008-06-01      

6       366  39     6948         2009-09-25      

7       295  8       1419         2013-11-11      

8       496  35     6018         2011-02-18      

9       273  37     9255         2011-05-04      

10     212  0       2155         2009-03-22      

esProc script for normal computing:

	A	B
1	=date("2013-09-01")	=date("2013-11-01")
2	=file("/ssd/data/orders.b").import@b()
3	=A2.select(ORDERDATE>A1 && ORDERDATE<B1)
4	=A3.groups@u(CLIENT,SELLERID;sum(AMOUNT):S,count(ORDERID):C)
5	=A4.select(C==13)
6	result A5

Note about the esProc script: A2 imports data in one go and its result can be used repeatedly. So it will not be counted in the execution time of in-memory computing, which only includes the time spent in executing steps from A3 to A6.

  

SQL script that Oracle uses:

select client,sellerid,sum(amount) s,count(orderid) c

from orders

where orderdate>to_date('2013-09-01','yyyy-MM-dd')

and orderdate<to_date('2013-11-01','yyyy-MM-dd')

group by client,sellerid

having count(orderid)=13

There are 8 million rows of data in orders.

Test result:

	esProc	Oracle
Execution time	0.570 seconds	0.623 seconds

Note: Execute Oracle’s SQL script four times and get the average of the last three times. In this way Oracle can make full use of its in-memory cached data. As for esProc script, we’ll execute it three times and get the average time.

During related computing testing, esProc imports the following files - orders.b, orders_detail.b, employee.b, department.b and performance.b. The relationships among them are as follows:

esProc script for data importing:

	A	B
1	="/ssd/data/"
2	=file(A1+"orders_detail.b").import@b()
3	=file(A1+"orders.b").import@b().primary(ORDERID).index()
4	=file(A1+"employee.b").import@b().primary(EID).index()
5	=file(A1+"department.b").import@b().primary(DEPT).index()
6	=file(A1+"performance.b").import@b().primary(EID).index()
7	=A2.switch(ORDERID,A3:ORDERID)	=A3.switch(SELLERID,A4:EID)
8	=A4.switch(DEPT,A5:DEPT)	=A5.switch(MANAGER,A4:EID)
9	=A4.switch(EID,A6:EID)	=A2.select(ORDERID.SELLERID.DEPT.MANAGER.EID.BONUS>6000)
10	=B9.groups@u(ORDERID.CLIENT:CLIENT,ORDERID.SELLERID.EID.EID:EID,PRODUCT;sum(PRICE*QUANTITY):S,count(ORDERID):C)
11	=A10.select(S>24500 && S<25000)	result A11

Note about esProc script: The code from A1 to A9 imports files and establishes the relationships (import and switch). This is done at one time and the result can be used repeatedly. This part of execution will not be included in the execution time of in-memory computing. The execution time is the time spent in executing steps from B9 to B11.

Oracle’s SQL：

select o.client,o.sellerid,od.product,sum(od.price*od.quantity) s,count(o.orderid) c

from orders_detail od left join orders  o on o.orderid=od.orderid

left join employee e on o.sellerid=e.eid

left join department d on d.dept=e.dept

left join employee e1 on d.manager=e1.eid

left join performance p on p.employeeid=e1.eid

where p.bonus>=6000

group by o.client,o.sellerid,od.product

having sum(od.price*od.quantity) between 24500 and 25000

Test one: Both the orders table and the orders_detail table have 8 million rows. There are less than 1,000 rows in other tables.

Test result:

	esProc	Oracle
Execution time	4.9 seconds	9.7 seconds

Test two: Both the orders table and the orders_detail table have 4 million rows. There are less than 1,000 rows in other tables.

Test result:

	esProc	Oracle
Execution time	2.3 seconds	5.1 seconds

Similarly, we execute Oracle’s SQL script four times and get the average of the last three times, in order to let Oracle make full use of its in-memory cached data. We execute esProc script three times and get the average.

Conclusion:

For small data in-memory computing, when the computation is simple without file relating, esProc is a little faster than Oracle. This is probably because, though Oracle can cache data, it still needs to convert the cached data (without using the physical disk) before processing it. When handling the complicated related computing, esProc works much faster than Oracle. The reason is that Oracle uses the hash algorithm to join two tables, while esProc uses a pointer to reference foreign key values, and thus does not need to compute hash values and query matching values.

Therefore, when there is enough available memory capacity, esProc can achieve much higher performance than conventional relational database if we import data into memory and arrange it beforehand. esProc is great in achieving high performance for in-memory computing.

Test environment:

CPU: Core(TM) i5-3450   four cores, four threads

Memory capacity: 16GB

OS：CENT OS

Oracle11g：Maximum memory capacity is 14G

esProc 3.1 version: Maximum memory capacity is 14G

Performance Test of File Retrieval in esProc

This article aims to test esProc performance of retrieving files and compares it with the result got by using Oracle JDBC.

The test data is some order records saved in the orders.b file. Import the file with esProc and its content would be like this:

ORDERID CLIENT     SELLERID AMOUNT ORDERDATE NOTE

1       287  47     5825         2013-05-31       gafcaghafdgie f ci…

2       89     22     8681         2013-05-04       gafcaghafdgie f ci…

3       47     67     7702         2009-11-22       gafcaghafdgie f ci…

4       76     85     8717         2011-12-13       gafcaghafdgie f ci…

5       307  81     8003         2008-06-01       gafcaghafdgie f ci…

6       366  39     6948         2009-09-25       gafcaghafdgie f ci…

7       295  8       1419         2013-11-11       gafcaghafdgie f ci…

8       496  35     6018         2011-02-18       gafcaghafdgie f ci…

9       273  37     9255         2011-05-04       gafcaghafdgie f ci…

10     212  0       2155         2009-03-22       gafcaghafdgie f ci…

…

note field is the string field for increasing each record’s length and doesn’t have practical meaning. Test process: Write a Java program to call esProc JDBC and Oracle JDBC respectively for retrieving and traversing the same data, and record the time taken. The Java program, esProc script, the data file and Oracle are all held in one machine, so no network is needed to transmit them.

Data volume: 890M

Hardware and software configuration for the test machine: Normal PC

CPU: Core(TM) i5-3450   4 cores, 4 threads

Memory: 16GB

SSD

OS：CENT OS

Oracle：11g

esProc version: 3.1

esProc script esProcjdbc.dfx for data retrieval (cellset parameter fileName is the file name):

	A
1	=file(fileName).import@b()
2	result A1

The test program of calling esProc JDBC by Java:

import java.sql.*;

import java.text.SimpleDateFormat;

import java.util.Date;

public class esproc {

         public static void testDataServer(String input){

                   Connection con = null;

                   try{

                            SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");                                    Date begin=new Date();

                            System.out.println("begin"+df.format(begin));

                            long diff=0;

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

                            con= DriverManager.getConnection("jdbc:esproc:local://");

                            com.esproc.jdbc.InternalCStatement st=

                                     (com.esproc.jdbc.InternalCStatement)con.prepareCall("call esProcjdbc(?)");

                            st.setObject(1,input);

                            ResultSet set =st.executeQuery();

                            int len=0;

                            while(set.next())

                            {

                                     String orderid=set.getString("ORDERID");

                                     String client=set.getString("CLIENT");

                                     String sellerid=set.getString("SELLERID");

                                     float amount=set.getFloat("AMOUNT");

                                     Timestamp ORDERDATE=set.getTimestamp("ORDERDATE");

                                     String note=set.getString("NOTE");

                                     len++;

                            }

                            System.out.println("len="+len);

                            diff=((new Date()).getTime()-begin.getTime())/1000;

                            System.out.println("time of all="+diff);

                   }

                   catch(Exception e){

                            System.out.println(e);

                   }

                   finally{

                            if (con!=null) {

                                     try {

                                               con.close();

                                     }

                                     catch(Exception e) {

                                               System.out.println(e);

                                     }

                            }

                   }

         }

         public static void main(String[] args) {

                   testDataServer(“/ssd/data/orders.b”);

         }

}

The SQL statement that Oracle implements:

Select * from orders;

The test program of calling Oracle JDBC by Java:

package jdbc;

import java.sql.*;

import java.util.Date;

import java.text.SimpleDateFormat;

public class oracle {

         public static void testDataServer(String input){

                   Connection con = null;

                   try{

                            SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");                                    Date begin=new Date();

                            System.out.println("begin"+df.format(begin));

                            long diff=0;

                            Class.forName("oracle.jdbc.driver.OracleDriver");

                            con= DriverManager.getConnection("jdbc:oracle:thin:@192.168.1.21:1521:orcl","test","test");

                            Statement st=con.createStatement();

                            ResultSet set =st.executeQuery(input);

                            int len=0;

                            while(set.next())

                            {

                                     String orderid=set.getString("ORDERID");

                                     String client=set.getString("CLIENT");

                                     String sellerid=set.getString("SELLERID");

                                     float amount=set.getFloat("AMOUNT");

                                     Timestamp ORDERDATE=set.getTimestamp("ORDERDATE");

                                     String note=set.getString("NOTE");

                                     len++;

                            }

                            System.out.println("len="+len);

                            diff=((new Date()).getTime()-begin.getTime())/1000;

                            System.out.println("time of all="+diff);

}

                   catch(Exception e){

                            System.out.println(e);

                   }

                   finally{

                            if (con!=null) {

                                     try {

                                               con.close();

                                     }

                                     catch(Exception e) {

                                               System.out.println(e);

                                     }

                            }

                   }

         }

         public static void main(String[] args) {

                   testDataServer(“select * from orders”);

         }

}

Test result:

	esProc	Oracle
Execution time	13 seconds	43 seconds

Conclusion:

Data retrieval using Oracle JDBC is more than one time slower than that via esProc JDBC. The reason is that data flow needs to be converted to object through Oracle JDBC, which takes time. There is no need for Java-based esProc to do the same thing, so it works much faster.

One point to note is that the time taken by the first execution of calling Oracle JDBC to retrieve data is not used by the test. On the other hand, because the data volume is well within 6G, Oracle’s maximum memory capacity, it can make full use of the cache memory to save data. Therefore most of the execution time is devoted to conversion to object via JDBC.

This shows that esProc approach of retrieving data into the external memory is more efficient than Oracle’s throughput capacity.

Generally data computing within database will achieve higher performance. But in some cases you need to retrieve a great amount of data from the database. For example, data outside the database is involved, or the algorithm is too complex and specialized to be expressed in SQL. In these cases, JDBC for database connectivity will be the key factor to determine the performance. 

Yet better performance can be achieved by using esProc to retrieve data from the database and save it in files for use in later computation. To handle or display a large amount of historical data by report applications, it is more efficient to retrieve data into files before processing than to process it by retrieving data directly from the database. Within the log analysis system, you can save data as files and use esProc to handle the computation if you must retrieve data from the database using a programming language to perform certain operations involving log files. 

Performance Test of Concurrent File Processing with esProc

This article aims to test esProc performance in processing files concurrently, based on filter, group and aggregate operations and comparison with Oracle.

Test data includes tables of ordering information of different years. With esProc, files (Orders2009.b, Orders2010.b, Orders2011.b and Orders2012.b) of binary format are imported. Below is a selection from the source data:

ORDERID CLIENT     SELLERID AMOUNT ORDERDATE NOTE

1       287  47     5825         2013-05-31       gafcaghafdgie f ci…

2       89     22     8681         2013-05-04       gafcaghafdgie f ci…

3       47     67     7702         2009-11-22       gafcaghafdgie f ci…

4       76     85     8717         2011-12-13       gafcaghafdgie f ci…

5       307  81     8003         2008-06-01       gafcaghafdgie f ci…

6       366  39     6948         2009-09-25       gafcaghafdgie f ci…

7       295  8       1419         2013-11-11       gafcaghafdgie f ci…

8       496  35     6018         2011-02-18       gafcaghafdgie f ci…

9       273  37     9255         2011-05-04       gafcaghafdgie f ci…

10     212  0       2155         2009-03-22       gafcaghafdgie f ci…

…

In which note is a string field for increasing each record’s length and hasn’t practical meaning.

Oracle imports the four tables Orders2009, Orders2010, Orders2011 and Orders2012. The data is the same as that of those files. The number of parallel threads is 4. esProc and Oracle use single thread, 2 threads and 4 threads respectively to process the computational task. Each subtask has an input parameter year.

esProc script group.dfx for data grouping and aggregation:

Oracle SQL1 for data grouping and aggregation:

"select /*+ Parallel(4) */ client,sum(amount),count(orderid) from orders"+string(year)+" group by client"

esProc script select.dfx for data filtering:

Oracle SQL2 for data filtering:

"select * from orders1g"+string(year)+" where SELLERID=1 and CLIENT=50"

Hardware description:

CPU: Core(TM) i5-3450  4 cores and 4 threads

Memory capacity: 16GB

SSD

Note:

1.       This performance test focuses on cases in which the result set is small and can be entirely loaded into memory.

2.       The input parameters for the four concurrent tasks are four different years. 

3.       The test result adopts the average value, the maximum value and the minimum value of results of four consecutive computations. Both esProc and Oracle might use the disk cache.

4.       Oracle’s available memory space is 6G.

Test result one: Each subtask handles 1G data. The total data is 4G.

Time unit: Second

1.Group and aggregate

2. Query and filter

Test result two: Each subtask handles 4G data. The total data is 16G.

Time unit: Second

1.Group and aggregate

2.Query and filter

Conclusion

1.       Oracle cache can be brought into full play in handling the 4G total data that is within the Oracle memory capacity. In this case Oracle gets a higher performance.

2.       When the total data is 16G, which exceeds Oracle’s available memory, esProc is generally better than Oracle if using the parallel processing. 

3.       On the same conditions, esProc has a steady computational time, while Oracle has a big gap between the maximum time and the minimum time.