Query Performance in H2 Database with Spring Boot

October 25, 2025

This guide shows how to improve query performance in Spring Boot with H2 database using indexes, logging, and query tuning.

1. Add Dependencies

In pom.xml:

<dependency>
  <groupId>org.springframework.boot</groupId>
  <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
  <groupId>com.h2database</groupId>
  <artifactId>h2</artifactId>
</dependency>

2. Enable SQL Logging

In application.properties:

spring.jpa.show-sql=true
spring.jpa.properties.hibernate.format_sql=true
logging.level.org.hibernate.SQL=DEBUG

This lets you monitor queries being executed.

3. Add Indexes

In schema.sql:

CREATE TABLE employee (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    department VARCHAR(50)
);

CREATE INDEX idx_employee_department ON employee(department);

Indexes make queries faster for filtered fields.

4. Write Efficient Queries

In EmployeeRepository.java:

@Repository
public interface EmployeeRepository extends JpaRepository<Employee, Long> {
    List<Employee> findByDepartment(String department);
}

Spring Data generates optimized queries automatically.

5. Use Pagination

Avoid fetching large datasets at once.

Page<Employee> findByDepartment(String department, Pageable pageable);

This reduces memory load and improves speed.

6. Test Performance

Use the H2 console at /h2-console to run EXPLAIN on queries:

EXPLAIN SELECT * FROM employee WHERE department='IT';

This shows how indexes are applied.