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.

Scenario

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.

Results

Smaller times mean better result.

Conclusions

In the test, for bigger number of queries, Java JDBC has better performance (since 5000 queries).

Environment

Used OS: Windows 11 x64

Used Software:

• Java

  openjdk version "11.0.10" 2021-01-19
  OpenJDK Runtime Environment AdoptOpenJDK (build 11.0.10+9)
  OpenJDK 64-Bit Server VM AdoptOpenJDK (build 11.0.10+9, mixed mode)

• Node.js

  v16.13.0

• MariaDB

  v10.3.13

Used PC:

• Ryzen 9 5900x
• DRR 4 (2x 32GB)
• Samsung SSD M.2 970 EVO (1TB)
• GeForce GTX 970 (4GB RAM)

Java program

Example src/com/example/Program.java file:

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:

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<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):

mvn clean compile

Hint: or use mvn clean install.

Running (in command line):

mvn exec:java -Dexec.mainClass=com.example.Program

JDBC jar file source:

1. MySQL JDBC - Maven Repository
2. Direct JDBC jar file link

Node.js program

Example index.js file:

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:

{
  "name": "my-project",
  "version": "1.0.0",
  "dependencies": {
    "mysql": "^2.18.1"
  }
}

Installation (in command line):

npm install

Running (in command line):

node index.js

Database preparation

Example create_tables.sql file:

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):

INSERT INTO `users`
	(`name`, `role`)
VALUES
	('John', 'admin'),
	('Chris', 'moderator'),
	('Kate', 'user'),
	('Denis', 'moderator');

See also

1. Java - download Adopt Open JDK for free - Java 8, 11, 16 or 17

2. Java - how to make MySQL select query with JDBC?

3. Node.js - install under Windows

4. Node.js - MySQL Select query

References

1. Download Java 11
2. Download Node.js 16.13.0
3. Download MariaDB Server