JPA Self-Join gegen große Tabelle bricht irgendwann ab

[Saheeda]

Hi,

ich habe eine Tabelle mit aktuell > 25.000 Datensätzen, gegen welche ich folgende Query ausführe:

  @Query("SELECT a FROM Customer a, Customer b "
            + "WHERE ("
            +          "lower(a.email) = lower(b.email) OR "
            +         "(lower(a.firstName) = lower(b.firstName) AND lower(a.lastName) = lower(b.lastName)) OR "
            +         "(lower(a.lastName) = lower(b.lastName) AND lower(a.street) = lower(b.street))"
            + ") "
            + "AND a.id != b.id")

Ziel ist, sämtliche Customer herauszufiltern, welche nach bestimmten Kriterien als ähnlich bzw. gleich gelten (Vor- und Nachname gleich oder Email gleich oder Nachname und Adresse gleich).

Problem:
Die Abfrage dauert ewig und bricht irgendwann ab mit diesem Stacktrace:

Last packet sent to the server was 0 ms ago.
2015-12-21 09:39:50,302 [http-bio-8080-exec-1] WARN  org.hibernate.engine.jdbc.spi.SqlExceptionHelper - SQL Error: 0, SQLState: null
2015-12-21 09:39:50,303 [http-bio-8080-exec-1] ERROR org.hibernate.engine.jdbc.spi.SqlExceptionHelper - Connection has already been closed.
2015-12-21 09:40:01,444 [http-bio-8080-exec-1] WARN  uncaughtException - Handler execution resulted in exception, request: GET:/customer/similiar_customers
org.springframework.orm.jpa.JpaSystemException: could not inspect JDBC autocommit mode; nested exception is org.hibernate.exception.GenericJDBCException: could not inspect JDBC autocommit mode
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:303) ~[spring-orm-4.1.3.RELEASE.jar:4.1.3.RELEASE]
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:214) ~[spring-orm-4.1.3.RELEASE.jar:4.1.3.RELEASE]
    at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:417) ~[spring-orm-4.1.3.RELEASE.jar:4.1.3.RELEASE]
Caused by: org.hibernate.exception.GenericJDBCException: could not inspect JDBC autocommit mode
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:54) ~[hibernate-core-4.3.1.Final.jar:4.3.1.Final]
Caused by: java.sql.SQLException: Connection has already been closed.

Laut StackOverflow ist das schlicht ein Timeout. Aber wie umgehe ich das? Wie bekomme ich trotzdem sämtliche Daten?

 

[stg]

Vorab mal:
Hast du mal getestet, wie lange die Ausführung des Queries dauert, wenn du ihn direkt auf der DB absetzt? Durch den Full Join kommst du da ja schon auf >625 Mio Rows, die geprüft werden müssen, das ist schon recht happig. Gegebenfalls zwingst du damit schon deine DB in die Knie und hier sollte der erste Punkt sein, an dem man mit der Optimierung ansetzt..?!

Ansonsten vielleicht auch einfach mal über alternative Ansätze nachdenken. Ist es eine einmalige Clean-Aufgabe? Wenn ja, dann muss das ja gar nicht unbedingt in den Code der Anwendung. Hier ist es vielleicht nur interessant bei Anlegen/Ändern einen User einen Vermerk (etwa über ein CRT) zu "ähnlichen" Usern anzulegen.

 

[Thallius]

Hast du keine Indexe auf den Spalten? Also so eine Abfrage sollte eigentlich nicht so lange dauern.

 

[Saheeda]

@stg
Ja, getestet und nach ~ 5-10 Minuten abgebrochen. Das ist leider keine einmalige Geschichte sondern eine permanente Funktionalität. Die steht zwar nur einem kleinen Kreis von Nutzern überhaupt zur Verfügung, aber eben ständig.


@Thallius
Es gibt eine Spalte id, wenn du das meinst.

Selbst stark vereinfacht braucht die Abfrage zu lange:

SELECT a.id, b.id FROM Customer a, Customer b JOIN ON (a.email = b.email AND a.id != b.id)

 

[Joose]

Hier etwas Infos zu Indexes:
http://www.w3schools.com/sql/sql_create_index.asp
http://www.tutorialspoint.com/sql/sql-indexes.htm

 

[Thallius]

Wenn du nach namen und email suchst, dann solltest du auf diese spalten unbedingt einen Index setzen. Sonst kann das nur lange dauern.

Deine neue Abfrage erscheint mir komisch, Probiere statt dessen doch bitte mal

SELECT a.id, b.id FROM Customer a LEFT JOIN Customer b ON (a.email = b.email) WHERE a.id!=b.id

Gruß

Claus

 

[Tobse]

Noch eine kleine Anmerkung meinerseits: man kann Ausschlusskriterien für die JOIN rows auch schon in die ON-Klausel schreiben (in aktuellen Fall a.id != b.id). Das führt dazu, dass die DB die gesamte Row garnicht erst erstellt um dann das WHERE darauf auszuführen, sondern bereits beim JOIN ausschließt.
Je nach Datenbank wird das so oder so optimiert; in der AWS-Cloud meiner Firma mit MySQL bringt das aber bei größeren Queries über > 10.000 Datensätze ein paar Prozent Performance-Schub.
In diesem Fall - wo du einen self join auf so viele Daten machst - kann das durchaus einen Unterschied machen, vermute ich mal.

 

[fhoffmann]

Ein Index auf den Spalten wird kaum helfen, wenn ein Vergleich mit lower(Spaltenname) erfolgt (weil der Index dann nicht greift).
Eine Lösung könnte darin bestehen, eigene "Suchspalten" in die Tabelle einzufügen, die durch einen Trigger gefüllt werden und in denen die Inhalte in Kleinbuchstaben stehen und möglicherweise auch Bindestriche und Leerzeichen gelöscht und Umlaute ersetz sind. Die Vornamen "Hans Jürgen", "Hans-Jürgen", "Hans Juergen" und "Hansjürgen" stehen in dieser Suchspalte dann alle als "hansjuergen". Natürlich muss auch diese Spalte einen Index haben.