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

Diskutiere Self-Join gegen große Tabelle bricht irgendwann ab im Data Tier Forum; Hi, ich habe eine Tabelle mit aktuell > 25.000 Datensätzen, gegen welche ich folgende Query ausführe: @Query("SELECT a FROM Customer a,...

  1. Saheeda
    Saheeda Aktives Mitglied
    Hi,

    ich habe eine Tabelle mit aktuell > 25.000 Datensätzen, gegen welche ich folgende Query ausführe:
    Code (Text):
      @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:

    Code (Text):
    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?
     
  2. Vielleicht hilft dir dieser Java-Kurs hier weiter --> (hier klicken)
  3. stg
    stg Bekanntes Mitglied
    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.
     
  4. Thallius
    Thallius Bekanntes Mitglied
    Hast du keine Indexe auf den Spalten? Also so eine Abfrage sollte eigentlich nicht so lange dauern.
     
  5. Saheeda
    Saheeda Aktives Mitglied
    @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:
    Code (Text):
    SELECT a.id, b.id FROM Customer a, Customer b JOIN ON (a.email = b.email AND a.id != b.id)
     
  6. Joose
    Joose Super-Moderator Mitarbeiter
  7. Thallius
    Thallius Bekanntes Mitglied
    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

    Code (Text):

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

    Claus
     
  8. Tobse
    Tobse Bekanntes Mitglied
    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.
     
  9. fhoffmann
    fhoffmann Aktives Mitglied
    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.
     
Die Seite wird geladen...

Self-Join gegen große Tabelle bricht irgendwann ab - Ähnliche Themen

Spring Data: Lazy Fetch mit Self-Join.
Spring Data: Lazy Fetch mit Self-Join. im Forum Datenbankprogrammierung
[Modellierung] Verstoß gegen die NF?
[Modellierung] Verstoß gegen die NF? im Forum Datenbankprogrammierung
Enum gegen Enum tauschen
Enum gegen Enum tauschen im Forum Allgemeine Java-Themen
JAVAFX - Übergabe der Inhalte an eine Scene - Wo ist der Vorteil gegenüber Swing?
JAVAFX - Übergabe der Inhalte an eine Scene - Wo ist der Vorteil gegenüber Swing? im Forum AWT, Swing, JavaFX & SWT
Benötige gegen Bezahlung ein Paar Methoden in Java
Benötige gegen Bezahlung ein Paar Methoden in Java im Forum Private Stellangebote und Stellensuche von Usern
Thema: Self-Join gegen große Tabelle bricht irgendwann ab