JPA - Query.setFirstResult, Query.setMaxResults - intern?

[mad-din]

Hi Leute!

Weiß jemand von euch, wie JPA mit Hibernate als Implementierung intern mit Query.setFirstResult() und Query.setMaxResults() umgeht? Es ist so, ich hab eine Query, die ungefähr 20.000 Datensätze liefert. Die Abfrage dauert ca. 30 Sekunden. Begrenze ich die Abfrage auf ca. 1.000 Datensätze mit Query.setFirstResult(0) und Query.setMaxResults(1000) dauert die Abfrage ebenfalls 30 Sekunden. Das ist ja noch nicht das Problem, das liegt denke ich mal auch an der Datenbank und nicht an JPA/Hibernate.
Was mich nur wundert ist, dass selbst die Abfrage auf nur 1.000 Datensätze genausoviel Speicher braucht? Nur dann frage ich mich, wieso es wieder 30 Sekunden dauert, wenn ich setFirstResult(1000) ausführe und mir nochmals das Ergebnis geben lasse.
Kann mir jemand erklären wie JPA/Hibernate das intern übersetzt und damit umgeht? Holt sich der immer die komplette Liste und teilt es dann auf? Das würde erklären, warum immer der gleiche Speicher verwendet wird. Erklärt aber nicht, warum die nächste Result-List wieder 30 Sekunden auf sich warten lässt (intern wäre ja die ganze Liste vorhanden).
Oder wird jedesmal die Query erneut ausgeführt? Das wiederum erklärt, warum es immer 30 Sekunden dauert, erklärt aber nicht, warum immer der gleiche Speicher verwendet wird?

Danke schonmal & viele Grüße,
Martin

 

[robertpic71]

Weiß jemand von euch, wie JPA mit Hibernate als Implementierung intern mit Query.setFirstResult() und Query.setMaxResults() umgeht?

Das kommt jetzt auf den Datenbankdialekt an. Normalerweise versucht Hibernate das in den jeweiligen Datenbanksyntax umzusetzen.

Bei MySQL/Postgres/HSQL/H2
mit z.B.

select * from datei limit MaxRows Offset 0

die anderen Datenbank sind etwas komplizierter. Möglicherweise bietet deine Datenbank dieses Feature nicht?

Im Normalfall beschleunigt das Paging die Erstausgabe deutlich. Ich bin mir nicht sicher, ob Hibernate noch vorher ein count(*) macht - das kostet auch Zeit.

Was das Paging einbremsen kann: order by's oder where über Felder ohne index

Aber: Poste einmal Hibernateversion und den Datenbankdialekt die Datenbankversion, dann kann ich dir die nativen SQL-Commands dazu sagen.

Zusatzfrage: Sind die Zeit nur für das Lesen der Daten oder auch das Erstellen der GUI-Modelle?

/Robert

Nachtrag: Du kannst natürlich auch den Logger von Hibernate aktivieren, damit du die SQL-Befehle siehtst.

 

[mad-din]

HI!

Es handelt sich um Hibernate 3.3.0.SP1 mit Hibernate Annotations 3.4.0.GA und Hibernate EntityManager 3.4.0GA, Datenbankdialekt ist MySQL5InnoDB und ja, es geht nur um das Auslesen der Daten (extra in einer eigenen Klasse getestet, die die Datensätze nur auf der Konsole ausgibt).
Die Tabelle hat ca. 500.000 Einträge, es gibt insgesamt 3 Where-Bedingungen (zwei davon haben einen Index) und ein Order-By.
Ich hab mal den Debug-Modus angeschmissen und die Statements direkt im MySQL Query Browser reingeschmissen, der erste Aufruf dauerte genausolang, die anderen mit verschiedenen Limits und gleichem Offset hingegen waren wesentlich schneller.
Könnte es auch den Einstellungen für JPA liegen? Meine persistence.xml ist momentan noch sehr rudimentär aufgebaut, nur das nötigste, damit es läuft. Optimierungen mach ich immer erst später. Da ich aber mit JPA noch nicht so vertraut bin, fehlt mir dazu noch das nötige Wissen, um herausfinden zu können, an welchen Schrauben ich drehen kann, damit es besser wird.

Hast du da einen Tipp für mich?

Danke & viele Grüße aus der Schweiz,
Martin

 

[semi]

Setze das hier in die persistence.xml, dann wirst du sehen, was für SQL-Statements generiert werden.

<property name="hibernate.show_sql" value="true" />

Eventuell noch das hier dazu, wenn du die Ausgabe formatiert haben möchtest (sieht sch.. aus :wink: ).

<property name="hibernate.format_sql" value="true" />

 

[robertpic71]

Die SQL-Befehle müssten eigentlich mit Limit x Offset x arbeiten. Möglicherweise arbeitet er aber uch mit einem ScrollableCursor.

Am besten die SQL-Befehle wie von semi beschrieben loggen und mit einem DB-Tool testen (und hier posten).

Möglicherweise schließt dein Connectionpool auch preparedStatements und MySQL meint, er kann den (temporären) Index von der ersten Abfrage jetzt wegschmeißen.

Daher: welchen Connectionpool verwendest du und wie ist er konfiguriert? (im Zweifelsfall posten)

/Robert

 

[semi]

Hibernate generiert LIMIT und OFFSET, wenn man es verwendet.
So sieht der Code in MySQLDialect.java aus

	public String getLimitString(String sql, boolean hasOffset) {
		return new StringBuffer( sql.length()+20 )
			.append(sql)
			.append( hasOffset ? " limit ?, ?" : " limit ?")
			.toString();
	}

Kann es sein, dass du Native-Queries oder irgendwelche merkwürdigen Subqueries verwendest. Zeig am besten paar Zeilen von dem Code,
insbesondere, wie du die Queries erzeugst. Stimmt bei dir Dialect in der persistence.xml? Sollte eins von den beiden sein

<property name="hibernate.dialect" value="org.hibernate.dialect.MySQLInnoDBDialect" />
oder
<property name="hibernate.dialect" value="org.hibernate.dialect.MySQL5InnoDBDialect" />

 

[mad-din]

Hi!

Also die Limits werden soweit auch erzeugt, das scheint nicht das Problem zu sein. Hier mal bisschen Code:

persistence.xml

<?xml version="1.0" encoding="UTF-8"?>
<persistence xmlns="http://java.sun.com/xml/ns/persistence"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_1_0.xsd"
	version="1.0">
   <persistence-unit name="test" transaction-type="RESOURCE_LOCAL">
   	<properties>
   		<property name="hibernate.dialect" value="org.hibernate.dialect.MySQLInnoDBDialect" />
   		<property name="hibernate.connection.driver_class" value="com.mysql.jdbc.Driver" />
   		<property name="hibernate.connection.username" value="root" />
   		<property name="hibernate.connection.password" value="123" />
   		<property name="hibernate.connection.url" value="jdbc:mysql://localhost:3306/test" />
   	</properties>
   </persistence-unit>
</persistence>

LimitOffsetTest.java

import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.Persistence;
import javax.persistence.Query;

public class LimitOffsetTest {

	/**
	 * @param args
	 */
	public static void main(String[] args) {

		int firstResult = 0;
		int maxResult = 100;
		
		EntityManagerFactory emf = Persistence.createEntityManagerFactory("test");
		EntityManager em = emf.createEntityManager();
		
		Query q = em.createNamedQuery("testQuery");
		q.setFirstResult(firstResult);
		q.setMaxResults(maxResult);
		
		List result;
		
		while ((result = q.getResultList()).size() > 0) {
			for (int i = 0; i < result.size(); i++) {
				System.out.println("new source " + ((Source) result.get(i)).getId());
			}
			firstResult = (firstResult + maxResult) + 1;
			System.out.println("fetching next " + maxResult + " elements");
		}
		

	}

}

Und die NamedQuery dazu:

@NamedQuery(name="testQuery",
			query="SELECT source " +
				  "FROM Source AS source " +
				  "WHERE source.countryCode = 'DE'")

So, also Limit und Offset werden erzeugt. Die Query sieht auch so ganz ok aus, das Problem ist nur, dass er bei jedem Durchlauf der Schleife erstmal 30 Sekunden wartet bis er die Ergebnisse ausgibt. Kann das sein?

Viele Grüße,
Martin

 

[robertpic71]

Also das

Query q = em.createNamedQuery("testQuery");
q.setFirstResult(firstResult);
q.setMaxResults(maxResult);

gehört noch in die While-Schleife - du müsstest eigentlich immer die gleichen Sätze geliefert bekommen.
Ob man den Query vielleicht nur 1x (vor der Schleife) braucht, habe ich noch nicht probiert, bei einer
Webapplikation stellt sich diese Variante gar nicht.

/Robert

 

[mad-din]

Hi!

Falsch abgetippt Bei mir ist q.setFirstResult() und q.setMaxResult() noch in der while-Schleife, der Aufruf für die NamedQuery aber nicht. Es handelt sich hier auch nicht um eine Webapplikation.

Momentan leb ich jetzt einfach mal damit, dass es so ist, wie es ist.

Viele Grüße,
Martin

 

[byte]

Was steht denn im Log? Ich hatte kürzlich auch Probleme mit Pagination und MySQL. Ohne Joins hat Hibernate wie gewünscht die LIMITs im SQL erzeugt. Wenn ich aber ein komplexeres Statement mit LEFT OUTER JOINs gebaut habe, dann hat Hibernate plötzlich nicht mehr LIMIT genommen sondern die Pagination In-Memory auf Collection-Ebene gemacht. Das hat natürlich elendig lange gedauert, weil erstmal alle Datensätze geholt wurden.
Hibernate spuckt in dem Fall aber eine Warnung als Logmeldung aus, die darauf hinweist!

 

[mad-din]

Hi!

Nein, das Problem lag weder an Hibernate noch an JPA. Das Problem ist scheinbar der MySQL Query Browser, auf der Konsole benötigt die gleiche Abfrage genauso lang wie über Hibernate/JPA. Beim MySQL Query Browser hingegen gehts wesentlich schneller, der brauch aber auch wesentlich mehr Speicher. Ich denke, dass der MySQL Query Browser die Abfragen irgendwie cacht und dadurch die schnellen Ergebnisse kommen. Also ist es ganz normal, dass die Abfrage so lang dauert (habs auch grad auf der Produktiv-Datenbank getestet, da dauert keine Sekunde ).

Danke euch allen und viele Grüße,
Martin