Performance Comparison
Many-to-Many (M:N) Relationships
without Indexing
Benchmarking RQL and SQL Query Languages with MariaDB and Xodus Databases
Indexes are crucial for performance, but in large databases, avoiding excessive indexing can be even more important.
Indexes consume a substantial amount of memory, and their maintenance negatively impacts database performance.
An object model consisting of 12 Java classes: N1-N4, M1-M4 and S1-S4.
All relationships between classes are M:N (many-to-many), marked with arrows.
RQL Queries with RootDB in MariaDB and Xodus/JetBrains
RQL query: "m2.m3.m4.value = 50"
//Variable rdb is the opened RootDB object database.
//RootStore object is created for the M1 class.
RootStore r2 = rdb.getRootStore("fi.rootrql.example8.M1");
Query query = Query.createQuery()
.addQ1Query(r2, "(m2.m3.m4.value = 50)")
.setParts(true);
List<M1> list = query.queryQ1Objects();
rdb.close();
Query Result Example: RootDB with MariaDB when the number of objects is 4 x 100K, see the table below.
RootDB and RQL can be used with all types of databases, not limited to relational databases.
-
MariaDB is a relational database that uses the SQL command language.
-
Xodus is a key-value storage that operates without a predefined database schema.
| MariaDB | Execution Times / Seconds | ||||
|---|---|---|---|---|---|
| Number of Objects | Number of Found Objects | Total Time | Create Objects | Search Time | |
| 4 x 100K | 109 | 1,30 | 0,08 | 1,22 | |
| 4 x 1M | 981 | 7,21 | 0,48 | 6,73 | |
| 4 x 2M | 2026 | 17,39 | 0,94 | 16,45 | |
| Xodus / JetBrains | Execution Times / Seconds | ||||
|---|---|---|---|---|---|
| Number of Objects | Number of Found Objects | Total Time | Create Objects | Search Time | |
| 4 x 100K | 109 | 1,52 | 0,02 | 1,50 | |
| 4 x 1M | 981 | 10,89 | 0,41 | 10,48 | |
| 4 x 2M | 2026 | 29,60 | 3,53 | 26,07 | |
N/A= not available, Processor = AMD Ryzen 7 3700X
Considering that no indexing is used, RootDB's search times (Search Time) are excellent in both databases.
Execution times for both databases increase somewhat irregularly with the number of objects, but the growth remains relatively close to linear.
The execution times show that RootDB provides strong performance even with large databases.
Consistent, efficient performance is always available without requiring optimization of database searches, unlike SQL statements, which often need to be optimized individually for each database product.
SQL Query Using a Traditional Relational Schema in MariaDB
SQL query:"SELECT m1_id FROM `fi.rootrql.example8b.m1_m2` WHERE m2_id IN ( SELECT m2_id FROM `fi.rootrql.example8b.m2_m3` WHERE m3_id IN (SELECT m3_id FROM `fi.rootrql.example8b.m3_m4` WHERE m4_id IN (SELECT id FROM `fi.rootrql.example8b.m4` WHERE value = 50))); "
| MariaDB | Execution Times / Seconds | ||||
|---|---|---|---|---|---|
| Number of Objects | Found Objects / Count | Total Time | Create Objects | Search Time | |
| 4 x 100K | 109 | N/A | N/A | 0,718 | |
| 4 x 1M | N/A | N/A | N/A | Infinite | |
| 4 x 2M | N/A | N/A | N/A | Infinite | |
N/A= not available, Processor = AMD Ryzen 7 3700X
The performance of MariaDB as a standard SQL relational database literally collapses when indexes are omitted.
Even with a relatively small number of 4 x 1M objects, search results become unavailable, with search times exceeding 10 minutes.
This demonstrates the necessity of indexing, which, in large databases, imposes significant burdens on memory requirements and data maintenance performance.
Xodus is a key-value storage system that does not support SQL, so it could not be tested using SQL.
RootDB/RQL queries deliver predictable and superior performance in database searches, as execution times increase nearly linearly with the number of objects.
Traditional relational databases often encounter serious problems with database searches when SQL queries involve multiple many-to-many (M:N) relationships.
Search times can be exceedingly long, even with a relatively small number of records, and searches can become completely stalled, as demonstrated by the examples of infinite search times above.
The tests were conducted on five-year-old hardware (processor and SSD).
Modern multi-core processors and SSDs could deliver execution times for RootDB/RQL that are over 10 times faster.
Links to other pages on this site.
- Performance Comparison Many-to-Many (M:N) Relationships with Indexing
- Performance Benchmarking on Many-to-Many (M:N) Relationships with Variations in Indexing and Sizes of Object Databases: Performance Comparison with Multiple Query Conditions in an Object Hierarchy
- RootDB, Technical Features
- RootDB and RQL, Home
Page content © 2024
Contact us:
