Apache Ignite vs Oracle DBMS

Apache Ignite is a distributed database in memory, such databases are spreading and I want to compare it with what is already and has established itself, for example, the Oracle relational DBMS. Ignite has extensive distributed computing capabilities, there is also support for SQL at the ANSI-99 level, and in SQL performance, I want to make some comparison. Database tuning will be in both cases largely by default, in the case of Oracle it is XE, and in the case of Ignite it is two nodes on the same computer. Computer i5 7400 (4-core) 3.5GHz, 8GB of RAM, SSD disk.
As test data I will use KLADR data (~ 223 thousand records) as the execution environment for DBeaver queries in which two connections to Ignite and Oracle are configured. And the first thing I will do is import the data into the tables, I will transfer the KLADR data from the DBF to CSV, and then with the help of DBeaver I will perform the import into the tables.



Ignite is also configured to store data in persistent storage.
config \ default-config.xml

<!-- Enabling Apache Ignite Persistent Store. --> <property name="dataStorageConfiguration"> <bean class="org.apache.ignite.configuration.DataStorageConfiguration"> <property name="defaultDataRegionConfiguration"> <bean class="org.apache.ignite.configuration.DataRegionConfiguration"> <property name="persistenceEnabled" value="true"/> </bean> </property> </bean> </property> 

Kladr table structure
')

 CREATE TABLE Kladr ( NAME VARCHAR, CODE VARCHAR, SOCR varchar, INDEX VARCHAR, PRIMARY KEY (CODE)) WITH "affinityKey=CODE"; 

affinityKey = CODE - means that the data in Ignite will be distributed on the partition and also distributed on two nodes. Two nodes are provided by running two instances of Ignite.
Another not big SOCRBASE table is a reference book of abbreviations, it has another storage rule in a distributed network.

 CREATE TABLE Socrbase ( LEVEL LONG, SCNAME VARCHAR, SOCRNAME VARCHAR, KOD_T_ST LONG, PRIMARY KEY (KOD_T_ST)) WITH "template=replicated"; 

WITH "template = replicated" - the table will be in full on each node. Then each node when receiving a join request can connect its data with this directory, with a different storage model, it would be necessary to ensure the consistency of partition tables.

Here are two running nodes
first:



second:



For Orcale, the structure is the same, except for partition.

Persistent - the Ignite repository on disk looks like a distribution across partitions (files), for a total of 1024 for each node, i.e. all data for the node is distributed in 1024 blocks, some of which are on the first node, and others on the second.

Single node example



During the execution of queries, Ignite will make a distributed query on nodes, which in turn will collect the data that they have, then the data will be consolidated and sent to the final sample.

So the first is to import data using DBeaver from CSV, 223 thousand records. Here is the first result.

Data import 223 thousand records (KLADR)
Ignite 12 min	Oracle 5 sec.

Next, I will perform several simple requests for data search for comparison, I will take the second execution in a row for the result (it is always less for two databases).

Get 100 KLADR records for any 100 CODE, there is an index for CODE SELECT * FROM KLADR WHERE code IN ( SELECT code FROM KLADR k WHERE k."INDEX" IS NOT NULL limit 100 ) LIMIT 100;
Ignite 30 ms.	Oracle 6 ms.

Get 100 KLADR records for any 100 NAME, no index for NAME SELECT * FROM KLADR WHERE name IN ( SELECT name FROM KLADR k limit 100 ) LIMIT 100;
Ignite 80 msec.	Oracle 6 ms.

Count the number of subjects in the region SELECT count(*) FROM KLADR WHERE CODE like '02%'
Ignite 30 ms.	Oracle 6 ms.

The number of subjects in the region is over 1000 SELECT SUBSTR(k.code, 1, 2), count(k.code) FROM KLADR k GROUP BY SUBSTR(k.code, 1, 2) HAVING count(k.code) > 1000 ORDER BY SUBSTR(k.code, 1, 2)
Ignite 150 ms.	Oracle 60 ms.

Join request. Get 100 first level subjects SELECT k.* FROM KLADR k JOIN SOCRBASE s ON k.SOCR = s.SCNAME WHERE s."LEVEL" = 1 LIMIT 100;
Ignite 280 ms.	Oracle 2 msec.

Join request. Number of subjects at levels SELECT s."LEVEL", count(k.code) FROM KLADR k JOIN SOCRBASE s ON k.SOCR = s.SCNAME GROUP BY s."LEVEL";
Ignite 13 sec.	Oracle 140 ms.

Yes, in the latter case, it is 13 seconds. showed Ignite request that something is not all good with join, although the introduction of the data limiting condition reduces this time.

Probably these comparisons are enough, I will not do until the conclusion, I will continue to study Ignite ...

Materials:

Ignite
Getting Started
Getting Started SQL