June 30, 2014

esProc/Oracle: Single Machine Performance Comparison Test(III)

6 Test Use Case

6.1 Small Data Volume Concurrent Scan

This use case tests Oracle and esProc for scanning performance against small data volume tables (files).It's done with a multi-task concurrent access mode.Each task is accessing different table (file). Among them, Oracle is running 16 parallel processes, while esProcis running 4 in parallel. Tests proved thatthis is the parallel level for highest performance.

Data Record,please click here to view the full data:
Note:Unit for time is seconds. Concurrent 2 means 2 SQL process are executed at the same time.The same is for concurrent 4.









Data characteristics:
1. Data with blue background is the peak performance value for this use case.We can see that both Oracle and esPro is running at peak performance with 1 concurrency. Meanwhile in such situation,Oracle's performance is several times higher than esProc.

2. Performance degrades significantly both for Oracle and esProc when concurrency number changes from 1 to 2. Oracle still has advantage but the difference with esProc is not too much.

3. esProc handles each task with equal performance, while Oracle is extremely unstable.

6.2 Large Data Volume Scan
This use case tests Oracle and esProc for their performance during large data volume tables (files) scanning. Test is done in a single task, none concurrent way. 3 Parallel levels are tested, which are 1, 2 and 4 parallel tasks respectively.

Data Record,please click here to view the full table:
















Data Property:
1. Oracle is at peak performance with 1 parallel process, and peformance starts to degrade with 2. However, esProc's 4 parallel computation performance is normally higher than 1, sometimes even several times higher, excepting for a few occurance.

2. esProc demonstrates obvious advantage over Oracle in this use case test. This could be better observed with narrow tables and more computation requirements, such as 106, 110 and 114. Use case 129 and 119 are exceptions where Oracle performs slightly better than esProc.

3. esProc is observed to be capable of increasing the computation performance several times with the rise of parallel numer, such as in the case of use case 106, 110, 114, 118, 122, 126 and 130. These are all for narrow table access, and with more computations.

4. In some cases esProc's peformance could also degrade, for example with use case 103, 107, 111, 115, 119, 119, 123 and 127. These are all for wide table access with less computation.

6.3 Small Data Volume Concurrent Grouping

This use case tests Oracle and esProc for grouping computation performance against small data volume tables (files). It's done with multi-task concurrent access mode. Each task is accessing different tables/files.

Data Record,please click here to view the full table:

Note: according to the data volume, esProc can choose to do pure in-memory computation or mix-mode-in-memory-and-out-memory computation. This use case leverages in-memory computation, while the large data volume grouping use case in later part of this report is done with mix-mode-in-memory-and-out-memory computation. Pure in-memory computation has the risk of memory overflow. 

In this case we use the parallel level of 1, 2 and 4 to avoid memory overflow. Green character reflects parallel 1red character is for parallel 2, and black for parallel 4. Oracle forces a mixed mode computation, which avoids memory overflow. Each task is done with a fixed number of parallel 16 to achieve best performance.












Data Characteristics:
1. Oracle performs better than esPro in general, especially with concurrency 1. With this configuration the performance of Oracle can be several times better.

2. In concurrent task of Oracle is extremely unstable. Performance varies from task to task, usually with several times difference. esProc's performance is stable.

3. Date grouping are all done with two-tier grouping, which, as we could see, Oracle performs better.

6.4 Large Data Volume Grouping

This use case tests Oracle and esProc for grouping computation performance against large data volume tables (files).It's done with none concurrent single task mode for parallel level of 1, 2 and 4.

Data Record,please click here to view the full table:















Data Characteristics:

1. Oracle is at peak performance with 1 parallel process, and performance starts to degrade with 2. However, esProc's 4 parallel computation performance is normally higher than 1, sometimes even several times higher.

2. esProc's performance is several times higher than Oracle in this use case test.

6.5 Small Data Volume Concurrent Joining

This use case tests Oracle and esProc for joining performance against small data volume tables (files).It's done with multi-task concurrent access mode. Each task is accessing different tables/files. Oracle has no performance improvement with parallel computation, and thus no such configuration is used for it. esProc has no performance improvement when running with more than 4 tasks in parallel. Tests are done for 4 parallel tasks.

Data Record,please click here to view the full table:











Data Characteristics:
1. esProc demonstrates a performance advantages of 1 or more times over Oracle.

2. esProc's performance variation among each concurrent task is very small, as compared with Oracle. Oracle's performance variation remains, but is less than in other use case.

3. Parallel mode yields no performance gain for Oracle, while esProc reaches peak performance with 4 parallel tasks.

6.6 Large Data Volume Joining

This use case tests Oracle and esProc for their performance during large data volume tables (files) join. Test is done in a single task, none concurrent way. Among them, Oracle reaches peak performance with 1 parallel task, while esProc is with 4.

Data Record,please click here to view the full table:













Data Characteristics:
1. esProc performs better than Oracle.

2. With multi-tier join, performance degradation for both of them are little.

6.7 Large Data Volume Large Grouping

This use case tests Oracle and esProc for grouping computation performance against large data volume tables (files), when the grouping results are too big to be stored in memory. It's done with none concurrent single task mode for parallel level of 1, 2 and 4 for their respective performances.

Data Record:










Data Characteristics:

1. esProc performs better than Oracle.

2. Oracle is at peak performance with 2 parallel process. esProc is at peak performance with 4.


Related:
esProc/Oracle: Single Machine Performance Comparison Test(I)

esProc/Oracle: Single Machine Performance Comparison Test(II)

esProc/Oracle: Single Machine Performance Comparison Test(IV)

Please Click here to download the full version。