Sort vs OrderBy - performance impact

Question

In addition to jpa's @OrderBy which is ordering elements on SQL level, hibernate provides @SortComparator and @SortNatural, which are sorting elements after loading from db.

1. Which one is better when it comes to performance?
2. If @OrderBy is better, why would anyone use the other option, as it is not even a jpa standard?

Answer 1

First, @SortComparator and @SortNatural are not sorting elements after loading them from the DB, but sorting them during their insertion. So the Elements are sorted in memory.

When using @OrderBy(COLUMN_NAME ASC) Hibernate sorts the Elements of the Collection, when the Elements are loaded from the DB by executing the Select-Statement with ORDER BY.

I measured the performance for @SortNatural and @OrderBy(clause = "random_string ASC". Storing and Loading of 200k random strings with a length of eigth characters. (The used code is down below.)

The results are:

Storing data with @SortNatural:

1. 472 seconds
2. 424 seconds

Loading data with @SortNatural:

1. 2,6 seconds
2. 1,7 seconds
3. 1,7 seconds
4. 3,0 seconds
5. 1,2 seconds

Storing data with @OrderBy

1. 431 seconds
2. 413 seconds

Loading data with @OrderBy

1. 3,3 seconds
2. 3,3 seconds
3. 3,0 seconds
4. 3,5 seconds
5. 4,8 seconds

hibernate.cfg.xml

<!DOCTYPE hibernate-configuration PUBLIC
        "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
        "http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
    <session-factory>
        <property name="connection.driver_class">com.mysql.jdbc.Driver</property>
        <property name="connection.url">jdbc:mysql://localhost:3306/hibernateTest</property>
        <property name="connection.username">root</property>
        <property name="connection.password"></property>
        
        <property name="conection.pool_size">1</property>
        
        <property name="dialect">org.hibernate.dialect.MySQL5Dialect</property>
        
        <property name="show_sql">true</property>
        
        <property name="current_session_context_class">thread</property>
        <property name="hibernate.hbm2ddl.auto">create</property>
    </session-factory>
</hibernate-configuration>

ClassWithStringCollection

@Entity
@Table(name="class_with_string_collection")
public class ClassWithStringColloction{
@Id
@Column(name="id")
private int id;

@ElementCollection
@CollectionTable(name="random_strings")
@Column(name="random_string")
//@SortNatural
@OrderBy(clause = "random_string ASC")
protected SortedSet<String> randomStrings = new TreeSet<String>();

public ClassWithStringColloction() {
    
}

public ClassWithStringColloction(SortedSet<String> modules) {
    this.randomStrings = modules;
}

public int getId() {
    return id;
}

public void setId(int id) {
    this.id = id;
}

public SortedSet<String> getRandomStrings() {
    return randomStrings;
}

public void setRandomStrings(SortedSet<String> randomStrings) {
    this.randomStrings = randomStrings;
}
}

main

public class main {

    public static void main(String[] args) {

        SessionFactory factory = new Configuration()
                            .configure("hibernate.cfg.xml")
                            .addAnnotatedClass(ClassWithStringColloction.class)
                            .addAnnotatedClass(RandomString.class)
                            .buildSessionFactory();
        Session session = factory.getCurrentSession();
        
        try {

            long timeBefore;
            long timeAfter;
            long elapsed;
    
            SortedSet<String> randomStrings = new TreeSet();
            ClassWithStringColloction classWithSC = new ClassWithStringColloction(new TreeSet());
            
            //performance measurement propagating data to DB
            
            RandomStringGenerator randStringGen = new RandomStringGenerator(10);
            String randomString = "";
            
            session.beginTransaction();
            session.persist(classWithSC);
            classWithSC = session.find(ClassWithStringColloction.class, 0);
            randomStrings = classWithSC.getRandomStrings();

            timeBefore = System.currentTimeMillis();
            for (int i = 0; i < 200000; i++) {
                
                randomString = randStringGen.nextString();
                randomStrings.add(randomString);
                session.update(classWithSC);
                if (i % 100 == 0) {
                session.flush();
                session.clear();
                }
            }
            
            session.getTransaction().commit();

            timeAfter = System.currentTimeMillis();
            elapsed = timeAfter - timeBefore;
            System.out.println("Time for storing 200000 String:" + elapsed + " ms");
            
            
            //Performance measurement for loading stored data.
            session = factory.getCurrentSession();
            
            session.beginTransaction();
            timeBefore = System.currentTimeMillis();
            classWithSC = session.get(ClassWithStringColloction.class, 0);
            randomStrings = classWithSC.getRandomStrings();
            System.out.println(randomStrings.first());
            session.getTransaction().commit();
            
            timeAfter = System.currentTimeMillis();
            elapsed = timeAfter - timeBefore;
            System.out.println("Time for loading 200000 Strings:" + elapsed + " ms");
            
            System.out.println("Done");
        
        } catch (Exception e) {
            e.printStackTrace();
        }
        session.close();
        
    }

}

RandomStringGenerator 1.Answer of How to generate a random alpha-numeric string

Answer 2

reference: https://thoughts-on-java.org/ordering-vs-sorting-hibernate-use/

Use OrderBy, if the function is frequently called.

Use Sort, if you want to load all of data to memory and you manually manage it.