The test is performed on data table T3 with 165 million records. The binary data file being saved in esProc format takes up 14.6G physical storage,in which fields are shown below:
CREATE TABLE "T3"
( "L11" NUMBER(11,0),
"L4"
NUMBER(9,0),
"D4"
VARCHAR2(9),
"C4"
VARCHAR2(10),
"R2"
DATE,
"R4"
DATE,
"FL6"
NUMBER(9,0),
"FD6"
VARCHAR2(6),
"FC6"
VARCHAR2(9),
"TL1"
NUMBER(2,0),
"TL11"
NUMBER(7,0),
"TN1"
NUMBER(5,2),
"TN11"
NUMBER(23,2),
"TN21"
NUMBER(9,2),
"TN31"
NUMBER(9,2)
)
Provide the same hardware for both Oracle and esProc with the below environment configuration:
Model for test: Dell Power Edge T610
CPU: Intel Xeon E5620*2
RAM: 20G
HDD: Raid5 1T
Operating system: CentOS 6.4
JDK: 1.6
Oracle version: 11g
esProc version: 3.1
1.
Indexing Performance Comparison
4 indexes have been created for both Oracle and esProc. The first 3 are the single-field indexes, and the forth index is the composite indexes.
Note: The test results in this article are all represented in seconds unless otherwise remarked.
As can be seen
from the above figure, indexing in Oracle is faster than that in esProc. The
main application scenario of esProc is the data computing in BI. In this
sector, the data seldom changes. Owing to this, the comparatively slow speed is
acceptable since indexing can be regarded as a one-off job.
2.Single-field Indexing Performance Comparison
In below discussion, let’s compare the query speed between Oracle and esProc. To start with, let’s compare three single-field indexes. In which, the ind1 is the single-field index for the integer field L4; the ind2 is the single-field index for real number field TN21; and the ind3 is the single-field index for the character field C4. Compare the respective time consumed to query based on filtering criteria.
2.1. Less than 10 records satisfying the query conditions
As can be seen
from the above figure, compared with Oracle, esProc is faster in handling the
integer field, comparable in handling the real number field, and slower in
handling the character field.
2.2. Around 100 records satisfying the query conditions
As can be seen
from the figure, esProc is relatively faster for the numeric field, and the
speeds of esProc and Oracle in handling the character field are close.
2.3. Around 10000 records satisfying the query conditions.
As can be seen
from the above figure, esProc runs faster, but the difference is not great.
2.4. Around 100000 records satisfying the query conditions
As can be seen
from the above figure, esProc runs faster, demonstrating its obvious
advantages.
3. Multi-field Composite Indexing Performance Comparison
3.1. Less than 10 records satisfying the query conditions
As can be seen from the above figure,
Oracle performance is better.
3.2. Around 100 records satisfying the query conditions
As can be seen from the above figure,
Oracle performance is relatively better if there are around 100 records
satisfying the conditions.
3.3. Around 10000 records satisfying the query conditions.
As can be seen from the above figure, esProc performance is
obviously superior when the composite indexes of the 3 fields are all the
filter criteria and there are 10000 records satisfying the condition.
3.4. Around 100000 records satisfying the query conditions
As can be seen from the above
figure, esProc performance is better if there are 100000 records satisfying the
condition.
4.
Findings on Performance
Comparison
1. Indexing in Oracle is several
times faster than that in esProc. Comparatively, esProc more fit for the BI data
computing. In most BI scenarios, relatively few data changes, and indexing can
be regarded as a one-off job. A bit slowdown in speed is also acceptable.
2.After indexing, in case a small number (<10000) of records satisfy the query conditions, Oracle is often superior to esProc; In case the number of records is at a medium level (>10000,<100000), their performance is close; In case a great number of records (>100000) return, esProc demonstrates an obvious performance advantage.
No comments:
Post a Comment