Das deutsche Python-Forum

Seit 2002 Diskussionen rund um die Programmiersprache Python
https://www.python-forum.de/

Anfängerfrage für Datenbank Abfragen mit Subselect oder so

https://www.python-forum.de/viewtopic.php?t=14409

Seite 1 von 1

Anfängerfrage für Datenbank Abfragen mit Subselect oder so

Verfasst: Freitag 25. April 2008, 12:28
von würmchen
Hallo Leute,
ich fange gerade an mit Datenbank Programmierung und habe gleich am Anfang eine Frage.

Ich muss eine Datenbank mit rund 4000000 Einträgen abfragen. Ich bekomme eine Liste von Primärschlüsseln von sagen wir 3000 Stück und jetzt muss ich dann eben aus der Datenbank die Daten zu den Primärschlüsseln raussuchen.

Wie löse ich sowas am komfortabelsten? Ich wollte jetzt mit einer Schleife einfach für jeden Wert in meiner Liste eine Datenbankabfrage machen, was in 3000 Abfragen resultieren würde, gibt es hier einen einfacheren Weg?

Jemand hat mir was von Subselects oder sowas in MySQL erzählt, aber ich konnte damit nicht viel anfangen.

Verfasst: Freitag 25. April 2008, 12:35
von Jan-Peer
Hallo,

für deinen Fall würde es wohl auf ein 'IN' hinauslaufen:

Code: Alles auswählen

SELECT a, b, c FROM Tabelle1 WHERE a IN (1,2,3 ...);
Statt einer expliziten Liste wie in dem Beispiel oben kannst du auch eine weitere Select-Abfrage in die Klammern Schreiben, vorausgesetzt diese liefert nur eine Spalte zurück.

Verfasst: Freitag 25. April 2008, 12:40
von würmchen
Danke für die schnelle Antwort, funktioniert sowas auch, wenn ich mehrere Keys habe?
Also sowas wie

Code: Alles auswählen

SELECT a, b, c FROM Tabelle1 WHERE a IN (1,2,3 ...) AND b IN (2,3,4 ...);
Weil das wäre für mich interessant, wenn er dann jeweils die ersten, dann die zweiten usw Einträge aus der Liste nehmen würde..., also in dem Fall

SELECT ... a=1 AND b=2
SELECT ... a=2 AND b=3
SELECT ... a=3 AND b=4
usw....

Re: Anfängerfrage für Datenbank Abfragen mit Subselect oder

Verfasst: Freitag 25. April 2008, 13:28
von Leonidas
würmchen hat geschrieben:Wie löse ich sowas am komfortabelsten?
Mit einem ORM. Wenn du eine Datenbank als Speicher für Objekte nutzen willst, ist das bei weitem am komfortabelsten. Besser als die Queries selbst zusammensetzen und dann daraus manuell die Objekte wieder rekonstruieren.

Re: Anfängerfrage für Datenbank Abfragen mit Subselect oder

Verfasst: Freitag 25. April 2008, 15:46
von gerold
Hallo würmchen!
würmchen hat geschrieben:Datenbank
Welches Datenbanksystem? Die unterscheiden sich nämlich.
würmchen hat geschrieben:Ich muss eine Datenbank mit rund 4000000 Einträgen abfragen.
Du meinst, dass du eine Tabelle mit 4 Mio. Datensätzen abfragen möchtest.
würmchen hat geschrieben:Wie löse ich sowas am komfortabelsten? Ich wollte jetzt mit einer Schleife einfach für jeden Wert in meiner Liste eine Datenbankabfrage machen, was in 3000 Abfragen resultieren würde, gibt es hier einen einfacheren Weg?
Die Frage ist nicht, wie es komfortabler wird. Denn komfortabler als die 3000 einzelnen Abfragen in einer Schleife wird es nicht. Du kannst die Sache aber **extrem beschleunigen**, wenn du mit einer temporären Tabelle arbeitest und zuerst die Daten vorgefiltert in die temporäre Tabelle schreibst, dann noch einen Index hinzufügst, der die Laufzeit der nächsten Abfragen verbessert und dann die Daten aus dieser temporären Tabelle raus holst. Aber wenn du es komfortabel haben möchtest, dann bist du mit der Schleife und den 3000 einzelnen Abfragen besser dran.
Noch etwas: Eine SQL-Anweisung kann nicht unendlich groß sein. Es kann leicht sein, dass mit 3000 Werten als Bedingung, die SQL-Anweisung zu groß wird.

mfg
Gerold
:-)

Verfasst: Freitag 25. April 2008, 16:01
von Jan-Peer

Code: Alles auswählen

SELECT a, b, c FROM Tabelle1 WHERE a IN (1,2,3 ...) AND b IN (2,3,4 ...);
Würde funktionieren, sofern die Datenmenge nicht die Möglichkeiten der Datenbank / des Servers sprengt.
Weil das wäre für mich interessant, wenn er dann jeweils die ersten, dann die zweiten usw Einträge aus der Liste nehmen würde..., also in dem Fall

SELECT ... a=1 AND b=2
SELECT ... a=2 AND b=3
SELECT ... a=3 AND b=4
usw....
Er würde ALLE möglichen Kombinationen von Elementen aus beiden Listen nehmen.

Verfasst: Montag 28. April 2008, 14:10
von würmchen
Ich muss sagen, das ich mich da im Moment ein wenig im Kreis bewege.
Ich muss auch zugeben, das ich mich mit Komfortabel falsch ausgedrückt habe, denn es kommt mir vor allem auf die Geschwindigkeit an.

Mein Problem ist, das es eine, für meine Begriffe unglaubliche große, MySQL Datenbank ist. Sie besteht aus 90 Tabellen und in der einen besonders großen Tabelle sind zum beispiel insgesamt 292 000 000 Einträge.
Die kompletten Daten sind insgesamt 120GB...

Ich habe jetzt angst, dass wenn ich ein ORM System nutze, mir das dann zuviel Infos aus der Datenbank holt, die ich eventuell nicht brauche und so vielleicht den Arbeitsspeicher von dem Server sprengt.

Muss dazu sagen das ich das System so nicht entworfen habe, sondern es so vorgefunden habe und da nur schwer noch was ändern kann.

Sind meine Bedenken unbegründet oder was würdet Ihr mir empfehlen. Prinzipiell würde ich glaube ich am liebsten den Vorschlag von Gerold verfolgen, aber ich habe im Moment keine Vorstellung wie ich das angehen soll, gibt es dazu vielleicht Beispiele?

Grüße Wurm

Verfasst: Montag 28. April 2008, 14:48
von Y0Gi
Die Dinger werden übrigens als "Subqueries" bezeichnet - für den Fall, dass du nach einschlägiger Literatur suchst.

Verfasst: Montag 28. April 2008, 19:03
von gerold
würmchen hat geschrieben:Prinzipiell würde ich glaube ich am liebsten den Vorschlag von Gerold verfolgen, aber ich habe im Moment keine Vorstellung wie ich das angehen soll, gibt es dazu vielleicht Beispiele?
Hallo würmchen!

Du kannst mit dem SQL-Befehl ``CREATE TEMPORARY TABLE`` http://dev.mysql.com/doc/refman/5.0/en/ ... table.html eine temporäre Tabelle erstellen. Diese wird automatisch gelöscht, wenn die Verbindung zur Datenbank geschlossen wird.

Mit ``INSERT INTO`` kannst du Datensätze "vorgefiltert" in diese temporäre Tabelle schreiben. Dadurch reduziert sich für die folgenden Abfragen die Anzahl an zu durchsuchenden Datensätzen. Wenn du z.B. Rechnungen eines bestimmten Zeitraums brauchst, dann könntest du die Datensätze nach dem Erstelldatum der Rechnungen gefiltert in diese Tabelle schreiben.

Noch mehr Geschwindigkeit kannst du rausholen, wenn du die Felder nach denen du in den folgenden Abfragen suchst, indizierst. Bei deiner Beispielabfrage ``SELECT ... a=1 AND b=2``, würde es sich also anbieten, die Felder ``a`` und ``b`` zu indizieren. http://dev.mysql.com/doc/refman/5.0/en/ ... index.html
Die Indizes erstellt man aber erst, nachdem die temporäre Tabelle befüllt wurde. Das ist schneller.

mfg
Gerold
:-)

Verfasst: Montag 28. April 2008, 19:51
von gerold
Hallo!

Ich weiß nicht, ob dieses Beispiel besser verdeutlicht, wie ich das gemeint habe. Vielleicht.

Code: Alles auswählen

#!/usr/bin/env python
# -*- coding: iso-8859-15 -*-

try:
    from pysqlite2 import dbapi2 as sqlite3
except ImportError:
    import sqlite3
import datetime


# Demodatenbank im Speicher
conn = sqlite3.connect(
    ":memory:", detect_types = sqlite3.PARSE_DECLTYPES | sqlite3.PARSE_COLNAMES
)

# Basistabelle erstellen
sql = """
CREATE TABLE basisdaten (
  id INTEGER PRIMARY KEY,
  creation_timestamp TIMESTAMP,
  message TEXT
)
"""
conn.execute(sql)
conn.commit()

# Basistabelle befüllen
sql = """
INSERT INTO basisdaten (
  creation_timestamp, message
) VALUES (
  ?, ?
)
"""
values = []
for i in xrange(100):
    timestamp = datetime.datetime.now() + datetime.timedelta(minutes = i)
    values.append([timestamp, "Message " + str(i + 1)])

conn.executemany(sql, values)
conn.commit()

# Temporäre Tabelle erstellen
sql = """
CREATE TEMPORARY TABLE tmp_gefiltert (
  id INTEGER, 
  message TEXT
)
"""
conn.execute(sql)
conn.commit()

# Temporäre Tabelle mit Daten befüllen
sql = """
INSERT INTO tmp_gefiltert (
  id, message
) SELECT 
    id, message
  FROM
    basisdaten
  WHERE
    creation_timestamp > ?
"""
min_timestamp = datetime.datetime.now() + datetime.timedelta(minutes = 80)
conn.execute(sql, (min_timestamp, ))
conn.commit()

# Index erstellen
sql = """
CREATE UNIQUE INDEX ix_tmp_gefiltert_id ON tmp_gefiltert (id)
"""
conn.execute(sql)
conn.commit()

# Temporäre Tabelle in einer Schleife abfragen
sql = """
SELECT
  id, message
FROM
  tmp_gefiltert
WHERE
  (id = ?) AND
  (message LIKE ?)
"""
bedingungen = (
    (85, u"%85"),
    (88, u"%88"),
    (90, u"%90"),
    (95, u"%95"),
)
for bedingung in bedingungen:
    cur = conn.cursor()
    cur.execute(sql, bedingung)
    for row in cur:
        print row
mfg
Gerold
:-)

Verfasst: Montag 28. April 2008, 20:35
von Leonidas
würmchen hat geschrieben:Ich habe jetzt angst, dass wenn ich ein ORM System nutze, mir das dann zuviel Infos aus der Datenbank holt, die ich eventuell nicht brauche und so vielleicht den Arbeitsspeicher von dem Server sprengt.
Brauchbare ORMs arbeiten sowieso lazy. Ich würde an deiner Stelle das erst einmal mit SQLAlchemy ausprobieren.

Verfasst: Dienstag 6. Mai 2008, 10:59
von würmchen
gerold hat geschrieben:Hallo!

Ich weiß nicht, ob dieses Beispiel besser verdeutlicht, wie ich das gemeint habe. Vielleicht.

Code: Alles auswählen

#!/usr/bin/env python
# -*- coding: iso-8859-15 -*-
...
# Index erstellen
sql = """
CREATE UNIQUE INDEX ix_tmp_gefiltert_id ON tmp_gefiltert (id)
"""
conn.execute(sql)
conn.commit()

# Temporäre Tabelle in einer Schleife abfragen
sql = """
SELECT
  id, message
FROM
  tmp_gefiltert
WHERE
  (id = ?) AND
  (message LIKE ?)
"""
bedingungen = (
    (85, u"%85"),
    (88, u"%88"),
    (90, u"%90"),
    (95, u"%95"),
)
for bedingung in bedingungen:
    cur = conn.cursor()
    cur.execute(sql, bedingung)
    for row in cur:
        print row
Hi Gerold,
vielen Dank für dieses Ausführliche Beispiel.... Ich hab nur eine Frage...

Code: Alles auswählen

CREATE UNIQUE INDEX ix_tmp_gefiltert_id ON tmp_gefiltert (id)
Diese Zeile versteh ich nicht :-), ok, ist die wichtigste bei der ganzen Sache, aber wo kommt dasn "ix_tmp_gefiltert_id" her und warum wird das später nicht genutzt. hätte jetzt erwartet das es eine art neue Spalte ist, die ich dann zum Suchen benutze oder so...

Ich Frag mich halt was das für eine Syntax ist, oder ob es nur ein Name ist, den ich später nicht mehr brauch...

Verfasst: Dienstag 6. Mai 2008, 11:34
von gerold
würmchen hat geschrieben:wo kommt dasn "ix_tmp_gefiltert_id" her und warum wird das später nicht genutzt
Hallo würmchen!

Einen Index in einer Datenbank kannst du dir wie den Index in einem Buch vorstellen. Du suchst im alphabetisch sortierten Buch-Index nach einem Stichwort. Und neben dem Stichwort steht, wo im Buch dieses Stichwort verwendet wird.

Einen Datenbank-Index kannst du dir wie eine *unsichtbare* Tabelle vorstellen. Diese Tabelle ist nach dem ersten Feld sortiert, damit man ein Stichwort schnell findet. Und im zweiten Feld steht, in welcher Zeile der zugehörigen Tabelle die Daten zu finden sind.

Nehmen wir mal an, ich suche in 100 Zahlen nach der Zahl ``40``. Sind diese Zahlen nicht sortiert, dann muss ich jede Zahl ansehen um feststellen zu können, ob es die Zahl ``40`` ist oder nicht. Sortiere ich die Zahlen vorher, dann finde ich die Zahl ``40`` sehr schnell.

Eine Tabelle wird nach dessen Primärschlüssel sortiert. Es ist nicht möglich, diese Tabelle nach mehreren Feldern zu sortieren. Da man aber in unsortierten Feldern nicht schnell suchen kann, hat man Indexe eingeführt. Diese Indexe werden **im Hintergrund** automatisch bei SELECT-Abfragen verwendet. Das Datenbanksystem entscheidet selbst, welche Indexe es verwendet um so schnell wie möglich Daten zu finden.

Generell gilt, dass jedes Feld das als Fremdschlüssel verwendet wird auch indiziert sein soll. Der Primärschlüssel ist automatisch indiziert, da normalerweise die Tabelle mit diesem Primärschlüssel sortiert wird. Eindeutige (UNIQUE) Indizes können schneller durchsucht werden als nicht eindeutige Indizes.

Indizes von kleinen Tabellen mit wenig Daten, werden üblicherweise NICHT vom Datenbanksystem verwendet, da ein Durchlaufen der wenigen Daten meist schneller ist als das Durchsuchen eines Index. Aber je größer eine Tabelle wird, desto mehr bringt ein Index. Wird bei einer Abfrage ein Großteil einer Tabelle zurück gegeben, dann wird auch kein Index verwendet. Aber diese Optimierungen nimmt dir dein Datenbanksystem ab. Hauptsache du erstellst Indizes für die Felder, nach denen du oft suchst. Das kann dir dein Datenbanksystem nicht abnehmen.

Indizes haben auch Nachteile. Indizes beschleunigen zwar den Suchvorgang, bremsen aber das Ändern und Hinzufügen von Daten, da nach jeder dieser Transaktionen die Indizes neu sortiert werden müssen. Das ist auch der Grund, weshalb ich im Beispiel die Tabelle zuerst befülle und dann erst den Index erstelle. Und Indizes brauchen Platz auf der Festplatte. Die Datenbank wird mit jedem Index größer.

mfg
Gerold
:-)

Verfasst: Samstag 10. Mai 2008, 09:49
von sma
Die erste Frage, die ich hätte wäre: Wie häufig brauchst du deine Abfrage und wie schnell muss das Ergebnis da sein. Wenn's angenommen nur 1x ist und es auch nicht schnell sein muss, laufe einfach alle Datensätze ab und prüfe im Hauptspeicher, ob a und b einer deiner Paare entsprechen.

Wenn's schnell und/oder häufig ausgeführt werden soll, würde ich die 3000 (zusammengesetzten) Primärschlüssel erst in eine (temporäre) Datenbanktabelle schreiben, indizieren und dann die DB einen Join machen lassen.

Stefan
Alle Zeiten sind UTC+01:00
Seite 1 von 1

Powered by phpBB® Forum Software © phpBB Limited

Deutsche Übersetzung durch phpBB.de