Sqlite sehr langsam

[Gabelmensch]

Tag,

ich habe eine Datenbank "bilderliste.s3db", mit einer Tabelle "bildverz", in der sich 16 Spalten befinden. Die ganze Datenbank hat ca. 100.000 Zeilen und ist 103MiB gross.

Mein Suchwort ist bsp. die vierte Spalte "namen", ich möchte alle einträge haben die auf den String in der Variable "var" passen. Mein Code sieht so aus:

Code: Alles auswählen

def holeausdatenbank_liste(var):
	print "var:", var
	sql = """
	SELECT
		*
	FROM
		bildverz
	WHERE
		name = ?
	"""
	cur = datenbank.cursor() 
	cur.execute(sql, [var])
	rows = cur.fetchall()
	print rows
	return rows


for name in liste:
	holeausdatenbank_liste(name)

Der Zugriff und Ausgabe je name dauert ca. 0,5 Sekunden. Mache ich etwas falsch, oder ist SQlite nunmal so langsam?

EDIT: Ich habe ausversehen auf Absenden gedrueckt. Ich brauche eine Zugriffszeit von min. 100 Ausgaben je sekunde, was wuerdet ihr empfehlen?

Moeglicherweise ein anderes Format wie dictionary oder Shelve? Ich habe mir dieses hier durchgelesen: http://www.python-forum.de/topic-6157.html

Die Plattform ist ein Fedora 11 64Bit, Python 2.6.

[ms4py]

16 Spalten hört sich nach einem sehr schlechten Datenbankdesign an.
Wie sieht denn deine Struktur aus?

Edit:
Wenn es nur um die Ausgabe geht:
statt cur.fetchall() - Ende der Funktion

Code: Alles auswählen

for row in cur:
  print row

dürfte die Sache erheblich beschleunigen.

Allerdings kannst du dann die Daten natürlich nicht zurückgeben.

[Hyperion]

16 Spalten hört sich nach einem sehr schlechten Datenbankdesign an.

Das kann man aus der Anzahl Spalten schließen?

@Topic: Naja, Ausgaben dauern eben durchaus relativ lange. Wie kommst Du denn auf den Wert 0.5 sec je gefundener Datensatz? Oder ist das die gesamte Laufzeit?

Und: Wozu brauchst Du 100 Zugriffe pro Sekunde? Evtl. kann man das ja auch umschiffen. Generell sind 100.000 Einträge eben schon recht viel. Wenn man alle anfassen muss, dauert es eben seine Zeit. Das könntest Du ja mal mit einem Dictionary simulieren

[Gabelmensch]

Naja, Struktur im Grunde keine, stell es dir wie eine Officetabelle mit 16 Spalten und 100k Zeilen vor. Alles TEXT Spalten.

[Gabelmensch]

@Topic: Naja, Ausgaben dauern eben durchaus relativ lange. Wie kommst Du denn auf den Wert 0.5 sec je gefundener Datensatz? Oder ist das die gesamte Laufzeit?

Gesamte Laufzeit / Anzahl der Abfragen.

Und: Wozu brauchst Du 100 Zugriffe pro Sekunde? Evtl. kann man das ja auch umschiffen. Generell sind 100.000 Einträge eben schon recht viel. Wenn man alle anfassen muss, dauert es eben seine Zeit. Das könntest Du ja mal mit einem Dictionary simulieren

Wozu brauche ich sie? Um die Laufzeit gering zu halten.

Im Ernst, ich glaube ich teile die Datenbank beim erstellen in einzelne Tabellen auf. Ein name eine Tabelle, mit den dazugehoerigen eintraegen.

16 Spalten hört sich nach einem sehr schlechten Datenbankdesign an.
Wie sieht denn deine Struktur aus?

Edit:
Wenn es nur um die Ausgabe geht:
statt cur.fetchall() - Ende der Funktion

Code: Alles auswählen

for row in cur:
  print row

dürfte die Sache erheblich beschleunigen.

Allerdings kannst du dann die Daten natürlich nicht zurückgeben.

Danke, das beschleunigt es schonmal. Allerdings nur auf 10 Datensaetze pro Sekunde.

[ms4py]

16 Spalten hört sich nach einem sehr schlechten Datenbankdesign an.

Das kann man aus der Anzahl Spalten schließen?

Die Anfrage, der Code und diese Tatsache haben mich zu dieser Schlussfolgerung gebracht.
Und wie es aussieht, ist diese auch richtig.

@Gabelmensch:
Hört sich so an, als hättest du ziemlich Redundanz in deiner Struktur. Beschäftige dich mal mit dem Thema Normalisierung eines Datenbankschemas.

[Hyperion]

Wie wäre es mit einer Abfrage? Evtl. bringt eine Kaskade von "or"s auch ein schnelleres Ergebnis. Ohne genauere Kenntnisse der Struktur kann man da allerdings wenig zu sagen. Ob Normalisierung hier greit, lässt sich auch schwer voraussagen.

[DasIch]

@Topic: Naja, Ausgaben dauern eben durchaus relativ lange. Wie kommst Du denn auf den Wert 0.5 sec je gefundener Datensatz? Oder ist das die gesamte Laufzeit?

Gesamte Laufzeit / Anzahl der Abfragen.

Dir ist schon klar dass der größte Teil der Laufzeit für die Ausgabe draufgeht?

[OldBoy]

Hi,

ist auf das Feld "name" ein Index gesetzt? Wenn nicht, muss sqlite bei jeder Anfrage die ganze (100MiB große Datei) durchsuchen.

Schau mal bei sqlite nach unter dem Stichwort: CREATE INDEX.

[gerold]

Hallo Gabelmensch!

Wie OldBoy bereits schrieb, brauchst du unbedingt einen Index. Im Falle deiner oben gezeigten Abfrage, muss es ein Index auf das "name"-Feld sein.

Wie es bei SQLite ist, weiß ich nicht, aber es gibt Datenbanksysteme, die *-Abfragen langsamer abarbeiten, als Abfragen, in denen die gewünschten Felder mit Namen aufgelistet werden.

Und versuche immer nur genau so viel an Informationen aus der DB herauszuholen, wie du gerade benötigst. Alles was zusätzlich über die Schnittstelle geht, braucht natürlich Zeit.

Lass' dir nicht einreden, dass 16 DB-Felder ein Indiz für schlechted DB-Design wären. Das kann man ohne Kenntnis der zu speichernden Daten keinesfalls sagen. Wenn du z.B. so gut wie immer alle oder fast alle der 16 Felder brauchst, dann ist es so gar sinnvoll, alle diese Felder in eine Tabelle zu legen. Erst recht dann, wenn sie fast immer oder immer alle mit Daten gefüllt sind.

mfg
Gerold

[Gabelmensch]

Tag,

belegt sind alle Zellen der Tabelle. Derzeit brauche ich nur 14 Spalten, aber moeglicherweise will ich es irgendwann noch erweitern. Ich habe jetzt die ganze Datenbank in eine Liste geladen und führe so die Abfragen durch. Jedenfalls teste ich noch weiter.

[sma]

Wenn die Anforderung ist, mit maximal 10ms pro Anfrage auszukommen und das Datenvolumen "nur" 100 MB umfasst und auch noch konstant ist, würde ich einfach alles in den Hauptspeicher laden. Ein weiteres GB kostet weniger als auch nur eine Entwicklerstunde.

Stefan

[ms4py]

Lass' dir nicht einreden, dass 16 DB-Felder ein Indiz für schlechted DB-Design wären. Das kann man ohne Kenntnis der zu speichernden Daten keinesfalls sagen. Wenn du z.B. so gut wie immer alle oder fast alle der 16 Felder brauchst, dann ist es so gar sinnvoll, alle diese Felder in eine Tabelle zu legen. Erst recht dann, wenn sie fast immer oder immer alle mit Daten gefüllt sind.

Aber nicht, wenn die Tabelle voll mit Redundanzen ist!!
Und die Beschreibung vom TS

Im Ernst, ich glaube ich teile die Datenbank beim erstellen in einzelne Tabellen auf. Ein name eine Tabelle, mit den dazugehoerigen eintraegen.

lässt darauf schließen...

@TS Erklärung zur Redundanz:
Es gibt Daten, die einmal mit jedem Namen verknüpft sind, z.B. Vorname, Adresse, Telefon, und es gibt Daten die mehrmals mit einem Namen verknüpft sind, z.B. eine Liste von Bildern.
Der Einsatz von nur einer Tabelle wäre in diesem Fall eine sehr schlechte Datenstruktur. Bsp:

Code: Alles auswählen

bildvz(name, vorname, adresse, telefon, bild)

In diesem Fall stehen dann Daten, die eigentlich nur einmal mit einem Namen verknüpft sind, mehrmals in der gefüllten Tabelle.
In diesem Fall spricht man von Redundanz.

Die Auflösung von Redundanz geschieht in diesem Beispiel durch eine zweite Tabelle mit Fremdschlüssel auf die erste:

Code: Alles auswählen

namen(id, name, vorname, adresse, telefon)
bildvz(id, name_id, bild)

Aber am besten schreibst du jetzt endlich mal deine Datenstruktur (also die Feldernamen), damit wir diese Diskussion beenden können...

[Gabelmensch]

Guten Morgen,

meine Datenstruktur sieht so aus:

Code: Alles auswählen

bildvz(pruefsumme, dateivolumen, pfad, dateineme, name, kamera, klassifikation,
erstelldatum, breite, hoehe, flaeche, breite_gerundet, hoehe_gerundet, flaeche_gerundet,
 sortierdatum, verhaeltnis)

Code: Alles auswählen

pruefsumme = Sha1
dateivolumen = Bytes
pfad = $bildervault/
dateiname = originaldateiname.endung
name = Fotograf
kamera = z.B. Hersteller Modell Nummer 
klassifikation = 0 - 99999
erstelldatum = 321654 (sekunden seit Epoch)
breite = Pixel
[...]
breite_gerundet = pixel auf MP gekuerzt
[...]
sortierdatum = 654654 (sekunden seit Epoch)
verhaeltnis = 1.456 (hoehe / breite)

Es ist Redundanz vorhanden, das stimmt. Die 42 Bilder von "name = John Doe" in "klassifikation = 23" (Weihnachtsfeier 1996 im Segelverein), haben vermutlich alle die gleichen Abmessungen. Dann gibt es noch die 66 Bilder von "name = Jane Doe", die 12 von "name = Karl Otto", diese sind auch in "klassifikation = 23" vorhanden.

Die Dateien liegen im "$bildervault/klassifikation/sortierdatum/" und heissen "pruefsumme". Das "sortierdatum" ist der Tag an dem ich die Bilder auf meiner Platte gespeichert habe.

Nun moechte ich die Bilder als Softlinks in eine brauchbare Verzeichnisstruktur schieben, soetwas wie "Weihnachtsfeier Segelverein 1996/20_12_1996_20_15_33.jpg". "20_12_1996_20_15_33" ist eine Datums/Zeitangabe aus der Erstellzeit. Oder "kamera/20_12_1996_20_15_33.jpg", "name/20_12_1996_20_15_33.jpg"...

Mit einer Liste im Ram erreiche ich die Abfragegeschwindigkeit locker. Was schlaegst du bei der SQlite Datenbank vor?

[ms4py]

Als erstes Mal diese Spalten löschen, da sich diese anhand der anderen Spalten berechnen lassen:

Code: Alles auswählen

breite_gerundet, hoehe_gerundet, flaeche_gerundet,
verhaeltnis

Und ansonsten brauchst du das dann auch nicht wirklich weiter optimieren, weil die IO-Zeit, die du für das Verschieben brauchst, deutlich höher sein dürfte, als deine Datenbankabfrage, dann spielt das vermutlich gar keine große Rolle mehr...