Performance Comparison
Many-to-Many (M:N) Relationships
with Indexing

Benchmarking RQL and SQL Query Languages with MariaDB and Xodus Databases

The best query performance is achieved when indexing is comprehensive, covering all value fields of object classes used in searches, along with all object relationships.

An object model consisting of 12 Java classes: N1-N4, M1-M4 and S1-S4.

Object Model

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.

Object Model
Object Model

Many-to-many (M:N) relationships are indexed using a completely new and unique method, delivering nearly the same efficiency as many-to-one (M:1) relationships, with only about a 20% difference in performance.

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 0,216 0,069 0,147
4 x 1M 981 1,614 0,523 1,091
4 x 2M 2026 3,065 0,993 2,072
Xodus / JetBrains Execution Times / Seconds
Number of Objects Number of Found Objects Total Time Create Objects Search Time
4 x 100K 109 0,230 0,108 0,122
4 x 1M 981 2,053 1,571 0,482
4 x 2M 2026 8,007 6,141 1,866

N/A= not available, Processor = AMD Ryzen 7 3700X

RootDB has really fast query times (Search Time) on both tested databases. With MariaDB, search times increase perfectly linearly as the number of objects increases. While execution times with Xodus/JetBrains increase more irregularly, they still outperform MariaDB at all object counts.

RootDB with MariaDB performs significantly better than Xodus/JetBrains in generating result objects. However, Xodus/JetBrains performs surprisingly well compared to MariaDB, which is a fully-fledged relational database. Of course, neither of them was originally designed as an object database.

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))); "

ID fields and junction tables for many-to-many (M:N) relationships are indexed, enabling efficient database searches.

MariaDB Execution Times / Seconds
Number of Objects Number of Found Objects Total Time Create Objects Search Time
4 x 100K 109 N/A N/A 0,281
4 x 1M 981 N/A N/A 3,344
4 x 2M 2026 N/A N/A 6,593

N/A= not available, Processor = AMD Ryzen 7 3700X

Thanks to many-to-many (M:N) indexing, execution times (Search Time) remain in the tolerable range, although still significantly worse than above with RootDB/RQL.

Object creation times and total execution times are not available because SQL lacks a mechanism to automatically generate M1 result objects.

Xodus is a key-value storage and does not have SQL, so it could not be tested with SQL.

RootDB/RQL queries are typically 3x faster than SQL queries in MariaDB.

Additionally, the results are complete Java objects, not just ID values ​​like in SQL.

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.

Page content © 2024 

company name

Contact us:

mail address