SQL Server: Daten schon vorhanden?

Computer: Software & Programmierung PHP
#1

Hallo,
in einer Tabelle steht u.a. ‚Baum_nr‘. Diese ist immer eindeutig und nur einmal vorhanden.
In einem PHP-array stehen ebenfalls Werte, die per ‚Baum_nr‘ nummeriert sind.

Nun möchte ich die array-Einträge, die noch nicht in der DB stehen, dort eintragen. Also das array mit der DB abgleichen. Dies soll aber möglichst performant geschehen, da die Abfrage jetzt schon ca. 10 Sek dauert und in Zukunft noch 1000 mal mehr Daten in die DB kommen.

Bisher so (funktioniert auch):

  foreach ($BaumArray as $vv) {
     ...
    $baum_nr = $vv[1];
     ...
  // prueft, ob die jeweilige Zeile schon in der DB vorhanden ist und ueberspringt diese wenn ja
  $stmtCheck = sqlsrv_query($con, "SELECT baum_nr FROM $table_raw WHERE baum_nr = $baum_nr";
  if ($stmtCheck) {
    $rows = sqlsrv_has_rows($stmtCheck);
    if ($rows === true) {
      continue; // ueberspringt den aktuellen Durchlauf der Schleife, weil die Daten schon vorhanden sind
    }
  }  

    ... schreib neue Eintraege in die DB ....
}

Gibt’s ne schnellere Möglichkeit?

Gruß,
Paul

#2

Hallo!

Dein Problem ist, dass du sehr viele einzelne SQL-Abfragen durchführst, und das kostet sehr, sehr viel Zeit.

Mit

SELECT baum_nr FROM MyTrees where baum_nr in (123, 546, 725, 245, 7841)

bekommst du eine Liste von baum_nr zurück, die bereits in der DB existieren.

Anschließend bastelst du dir ein

INSERT INTO MyTrees (spalte1, spalte2, spalte3) VALUES
(wert1, wert2, wert3),
(wert1, wert2, wert3),
(wert1, wert2, wert3),
(wert1, wert2, wert3)

zusammen, um mit einem einzigen Aufruf alle fehlenden Zeilen einzutragen.

Es gibt auch die Möglichkeit, das alles in einem einzigen SQL-Aufruf zu machen, also derart, dass die Datenbank selbst nur nicht existierende Zeilen einfügt. Das kann aber tatsächlich langsamer werden, weil die DB dann intern für jede Zeile prüft, ob sie vorhanden ist, oder nicht. Ich würde es wie beschrieben machen, weil zwei Aufrufe sind OK, und die sind nun auch recht übersichtlich.

Nochwas: Hast du einen Index auf baum_nr? Wenn nicht, muss die DB immer alle Zeilen durchsuchen, um diejenigen mit einer bestimmten Nummer zu finden. Ein Index ist quasi ne interne, sortierte Liste der Einträge in der Spalte, so dass die Suche nach nem bestimmten Wert sehr sehr schnell geht. Man muss nur einmal einstellen, dann man nen Index auf ne bestimmte Spalte haben möchte, danach kümmert sich die Datenbank selbst darum. Für deine Abfragen ändert sich (bis auf den Geschwindigkeitszuwachs) nichts.

Das ist jetzt recht einfach gehalten, denn man kann da noch sehr viel mehr machen. Das Problem mit den zwei Aufrufen ist z.B. auch, dass zwei Personen gleichzeitig versuchen könnten, den gleichen Baum in die DB einzufügen.

3 Like
#3

Hallo,

@sweber hat es schon richtig erklärt: Erst alle Baum-IDs auf einen Schlag holen, dann in PHP abgleichen und so die notwendigen INSERTs ermitteln.

Diese solltest du dann auch nicht einzeln machen, sondern in eine Transaktion packen. Die hat zwei Vorteile: Es werden entweder alle oder kein Insert durchgeführt. Gerade bei parallelen Zugriffen ist das wichtig. Außerdem kann die DB dann den Index in einem Schritt aktualisieren, statt nach jedem Insert.

Gruß,
Steve

1 Like
#4

Hi!

Jo, mit ner Transaktion könnte man das doppelte Einfügen von Einträgen durch unterschiedliche Personen elegant vermeiden. Wenn das nen entsprechenden Fehler wirft, muss man die ganze Aktion (Abfrage & einfügen) wiederholen.

Allerdings muss man dann baum_nr auf unique setzen. Denn damit erzwingt man innerhalb der Tabelle, dass keine doppelten Nummern in der Spalte vorkommen können, und erst das wird dann nen Fehler generieren.

@steve_m: Mit meinem Vorschlag werden ja auch viele Zeilen gleichzeitig eingetragen. Wird das mit ner Transaktion effizienter? Ich denke, der Server sieht in beiden Fällen, dass mehrere Einträge geschrieben werden, und könnte den Index in beiden Fällen in einem Rutsch updaten. (jetzt ohne Berücksichtigung, dass wir doppelte Einträge vermeiden wollen)

#5

Da wäre es guter Brauch, dieses Feld baum_id zu nennen. Eine (Haus-)Nummer kann mehrfach auftreten (in verschiedenen Straßen). Dass ein index Sinn macht, wurde ja schon geschrieben.

Falls du an einem Baumkataster arbeitest, würde mich eine kleine Beschreibung interessieren. Welche Daten werden pro Baum erfasst? Geo-Koordinaten?

#6

Hallo,
die Baum_nr war nur ein Beispiel. Es gibt auch eine ID.
Nein nichts mit Bäumen. War nur als Beispiel gedacht.

Gruß

#7

Hä? Mach doch einfach ein Insert mit dem kompletten Array aller Bäume. Wenn diese Baum_nr in der DB als unique definiert ist, kümmert sich die Datenbank darum, dass nur die neuen eingetragen werde. Hier noch zusätzlich was in PHP zu prüfen ist absolut sinnlos, genau dafür ist doch die Datenbank da.

Wieviele Bäume sind denn drin in der DB? Eine Abfrage über 10s ist auf jeden Fall zu lang und zeugt - zusammen mit der Eingangsfrage - von etwas Unkenntnis über Datenbanken. Ich empfehle, die Indexe zu prüfen und die Abfragen an die Indexe anzupassen.

#8

Besten Dank. Das umgeht das Problem natürlich komplett.

#9

Nö.

Du kannst jeden einzelnen Baum per INSERT einfügen. Bei den doppelten knallt es, das muss man abfangen.

Schiebt man alle Bäume mit einem einzigen INSERT rein, dann wird keiner eingetragen, wenn es bei einem knallt.

Das kann man mit fortgeschrittenem SQL zwar auch lösen, ich fürchte aber, das wird @Paul zu viel. Und 100 einzelne Aufrufe können auch mit Index etwas Zeit kosten.

#10

Scheint aber zu funktionieren mit allem in einem statement. Ich habe in der SQL Server Datenbank den Wert „Doppelte Schlüssel ignorieren“ auf ja gesetzt. Es werden alle noch nicht vorhandenen Einträge korrekt geschrieben.

#11

Da muss man gar nichts abfangen. Die Datenbank meldet das zurück, das kann man aber in dem konkreten Fall ignorieren weil wir ja damit rechnen.

100 einzelne Aufrufe kosten Zeit? Ich gehe mal von mindestens 5000 INSERTS pro Sekunde aus, da fallen 100 einzelne Aufrufe nicht mehr auf als 10. Ich würde sowas immer als einzelne INSERTS machen, weil es beim Erzeugen der SQL-Anweisung viel weniger Komplexität erzeugt aber vor allem weil damit auch prepared statements gehen. Damit erreicht man - wenn man sich nicht komplett daneben benimmt - Sicherheit vor SQL Injection.

#12

Dieses Thema wurde automatisch 30 Tage nach der letzten Antwort geschlossen. Es sind keine neuen Nachrichten mehr erlaubt.