Performance - Java MySQL JDBC library vs Node.js mysql package in sequential query test
In this short article, we would like to show performance comparison between Java and Node.js that connect to MySQL database. The main goal is to show connection mechanism overhead between different technologies: Java JDBC library and Node.js mysql
package.
In the below test we execute SELECT * FROM `users` WHERE `role` = 'moderator'
query on 120 rows stored in database repeating the same query sequentaly 1, 5, 10, 50, 100, 500, 1000, 5000, 10000 and 50000 times.
Smaller times mean better result.
Number of queries |
Java JDBC library execution time | Node.js mysql package execution time [in ms] |
1 | 1 | 1 |
5 | 5 | 3 |
10 | 10 | 7 |
50 | 30 | 20 |
100 | 49 | 33 |
500 | 145 | 113 |
1000 | 248 | 198 |
5000 | 728 | 884 |
10000 | 1312 | 1685 |
50000 | 5282 | 7749 |
In the test, for bigger number of queries, Java JDBC has better performance (since 5000 queries).
Used OS: Windows 11 x64
Used Software:
- Java
xxxxxxxxxx
1openjdk version "11.0.10" 2021-01-19
2OpenJDK Runtime Environment AdoptOpenJDK (build 11.0.10+9)
3OpenJDK 64-Bit Server VM AdoptOpenJDK (build 11.0.10+9, mixed mode)
- Node.js
xxxxxxxxxx
1v16.13.0
- MariaDB
xxxxxxxxxx
1v10.3.13
Used PC:
- Ryzen 9 5900x
- DRR 4 (2x 32GB)
- Samsung SSD M.2 970 EVO (1TB)
- GeForce GTX 970 (4GB RAM)
Example src/com/example/Program.java
file:
xxxxxxxxxx
package com.example;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class Program {
private static final String DB_NAME = "test";
private static final String DB_HOST = "127.0.0.1"; // 'localhost'
private static final String DB_USER = "root";
private static final String DB_PASSWORD = "root";
private static final String DB_URL = "jdbc:mysql://" + DB_HOST + "/" + DB_NAME + "?serverTimezone=UTC";
public static void main(String[] args) throws SQLException {
int repeats = 2; // 1, 5, 10, 50, 100, 500, 1000, 5000, 10000, 50000
try (Connection connection = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD)) {
System.out.print("initialization: ");
measure(connection, 1); // initialization
System.out.print("testing: ");
measure(connection, repeats); // testing
}
}
private static void execute(Connection connection, int repeats) throws SQLException {
String sql = "SELECT * FROM `users` WHERE `role` = ?";
for (int i = 0; i < repeats; ++i) {
try (PreparedStatement statement = connection.prepareStatement(sql)) {
statement.setString(1, "moderator");
try (ResultSet result = statement.executeQuery()) {
while (result.next()) {
int id = result.getInt("id");
String name = result.getString("name");
String role = result.getString("role");
// `id`, `name` and `role` are extracted to get better measurements
}
}
}
}
}
private static void measure(Connection connection, int repeats) throws SQLException {
long t1 = System.nanoTime();
execute(connection, repeats);
long t2 = System.nanoTime();
long dt = (t2 - t1) / 1000000L;
System.out.println("dt=" + dt + " ms");
}
}
Example pom.xml
file:
xxxxxxxxxx
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.example</groupId>
<artifactId>my-project</artifactId>
<name>My project</name>
<description>My project description.</description>
<version>0.0.1-SNAPSHOT</version>
<packaging>jar</packaging>
<properties>
<java.version>11</java.version>
<maven.compiler.source>${java.version}</maven.compiler.source>
<maven.compiler.target>${java.version}</maven.compiler.target>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
</properties>
<dependencies>
<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.30</version>
</dependency>
</dependencies>
<build>
<finalName>${project.artifactId}</finalName>
<sourceDirectory>src</sourceDirectory>
<plugins>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-compiler-plugin</artifactId>
<version>3.7.0</version>
<configuration>
<source>${maven.compiler.source}</source>
<target>${maven.compiler.target}</target>
<verbose>true</verbose>
</configuration>
</plugin>
</plugins>
</build>
</project>
Compilation (in command line):
xxxxxxxxxx
mvn clean compile
Hint: or use
mvn clean install
.
Running (in command line):
xxxxxxxxxx
mvn exec:java -Dexec.mainClass=com.example.Program
JDBC jar file source:
Example index.js
file:
xxxxxxxxxx
const mysql = require('mysql');
const connection = mysql.createConnection({
host: '127.0.0.1', // 'localhost'
user: 'root',
password: 'root',
database: 'test',
});
const execute = (i, repeats, callback) => {
const query = 'SELECT * FROM `users` WHERE `role` = ?';
const values = ['moderator'];
connection.query(query, values, (error, result) => {
if (error) {
connection.end();
throw error;
}
for (let j = 0; j < result.length; ++j) {
const entry = result[j];
const id = entry.id;
const name = entry.name;
const role = entry.role;
// `id`, `name` and `role` are extracted to get better measurements
}
if (i < repeats) {
execute(i + 1, repeats, callback);
} else {
if (callback) {
callback();
}
}
});
};
const measure = (repeats, callback) => {
const t1 = Date.now();
execute(0, repeats, () => {
const t2 = Date.now();
const dt = t2 - t1;
console.log("dt=" + dt + " ms");
if (callback) {
callback();
}
});
};
const repeats = 1; // 1, 5, 10, 50, 100, 500, 1000, 5000, 10000, 50000
connection.connect((error) => {
if (error) {
throw error;
}
measure(1, () => { // initialization
measure(repeats, () => { // testing
connection.end();
});
});
});
Example package.json
file:
xxxxxxxxxx
{
"name": "my-project",
"version": "1.0.0",
"dependencies": {
"mysql": "^2.18.1"
}
}
Installation (in command line):
xxxxxxxxxx
npm install
Running (in command line):
xxxxxxxxxx
node index.js
Example create_tables.sql
file:
xxxxxxxxxx
CREATE TABLE `users` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(100) NOT NULL,
`role` VARCHAR(15) NOT NULL,
PRIMARY KEY (`id`)
)
ENGINE=InnoDB;
Example insert_data.sql
file (execute it 30 times to fill database):
xxxxxxxxxx
INSERT INTO `users`
(`name`, `role`)
VALUES
('John', 'admin'),
('Chris', 'moderator'),
('Kate', 'user'),
('Denis', 'moderator');