Das deutsche Python-Forum

MOIN HELFENDE

ich lese eine textdatei_1 (ca. 3GB) aus und speichere alles in einer datenbank.
nun lese ich eine zweite textdatei_2 aus, welche update-informationen für die erste textdatei_1 (ca. 2,5 GB) enthält.
ich möchte also die erste textdatei_1 mit den informationen der zweiten textdatei_2 updaten.
wie kann ich das am schnellsten machen?
zur zeit verwende ich:

Code: Alles auswählen

self.cursor.execute("UPDATE " + self.table_name_a + " SET Name = :name WHERE "
                                                        "id= :id",
                                                        {'name': name_neu, 'id': id})

Das ist mir aber zu langsam.
im vergleich ist das einfache INSERTEN im ersten durchlauf ( dauer ca. 20 min) wesentlich
schneller als das UPDATEN (durchsuchen und ändern) der datenbank.
gibt es da eine schnellere methode? vielleicht erst SELECT und dann REPLACE?

Es ist schlecht, variable Tabellennamen zu benutzen. ›table_name_a‹ ist ja auch kein guter Attributnamen.

Wie sieht denn Dein Datenbankdesign aus?

So allgemein würde ich `executemany()` ins Auge fassen. Falls es wirklich viele Daten sind, nicht alle auf einmal sondern in grösseren Bündeln, damit nicht am Ende alles auf einmal `commit()`ed werden muss. Also sofern das von der Semantik her in Ordnung ist.

Erstell eine zweite temporäre Tabelle und nutze COPY (oder äquivalent falls du nicht postgres nutzt) um die komplette zweite Datei (hoffentlich in CSV Format) in diese temporäre Tabelle zu kopieren.

Dann nimmst du einen exklusiven Lock auf die erste Tabelle und erstellst eine dritte Tabelle als Kopie der ersten mit "create table ... as select ..." mit einem Query der die ersten beiden Tabellen joint und so Reihen aus der ersten mit den entsprechenden aus der zweiten überschreibt.

Schließlich tauscht du durch umbenennen erste und dritte Tabelle aus und dropst die erste Tabelle.

Dieser Ansatz ist nicht nur der schnellste er wird dich auch mit einer Tabelle und Indexen belassen, die brauchbar sind und nicht nur aus bloat bestehen.

@__blackjack___ executemany hilft nicht zwangsläufig, hat bei psycopg2 z.b. keinen Effekt auf Performance.

@DasIch: Ich weiss, aber ich gehe davon aus das es nicht schaden wird und es macht die Absicht deutlich(er).

danke erstma für die rege teilnahme

hier mein code design:

Code: Alles auswählen

class db_Artikelstammdaten(object):

    table_name_a = 'A_Hauptsatz_1'

    def __init__(self, path_db_file):
        self.path_db_file = path_db_file
        self.conn = sqlite3.connect(self.path_db_file)
        self.conn.text_factory = str
        self.cursor = self.conn.cursor()

    def __enter__(self):
        return self.conn

    def __exit__(self, exc_type, exc_val, exc_tb):
        if self.conn:
            self.conn.close()

    def create_product(self):
        self.cursor.execute("CREATE TABLE IF NOT EXISTS " + self.table_name_a + "("
                            "[...]")

    # Hier kommt dann der ganze data-Bums an:
    def update_product_a(self, data):
        with self.conn:
            for update in data:
                artikelnummer = update[0]
                preiskennung = update[1]
                preis = update[2]
                rabattkennung = update[3]
                rabatt = update[4]
                # [...]
                einkaufspreis = ...
                self.cursor.execute("UPDATE " + self.table_name_a + " SET Preis = :preis WHERE "
                                    "Artikelnummer = :artikelnummer",
                                    {'preis': einkaufspreis, 'artikelnummer': artikelnummer})

            self.conn.commit()

data wird durch das chunk-weise auslesen der update-datei geholt, zurechtgebastelt und dann immer zeilenweise übergeben.
eine zeile, die dann in
def update_product_a(self, data):
ankommt, sieht so aus:

Code: Alles auswählen

    data = [[u'TN10139508840', u'1', u'24600', u'1', u'4800', u'', u'', u'', u''], 
	[u'TN10139507636', u'1', u'29400', u'1', u'4800', u'', u'', u'', u''],
	[u'TN10139637656', u'1', u'29400', u'1', u'4800', u'', u'', u'', u'']]

dann die nächste, die nächste, die nächste,....
die update-textdatei_2 ist 34311801 Bytes.
die andere für das bloße INSERTen ist in 20min durch und liegt sogar bei 325315663 Bytes (zehn mal größer und
gefühlt 1000 mal schneller verarbeitet !)
chunkgröße hab ich schon variiert, bringt natürlich nichts.
insgesamt ist das sehr langsam...

@DMD-OL: das wichtigste fehlt. Die Tabellendefinition. Gibt es einen Index auf Artikelnummer?

Es macht immer noch keinen Sinn, den Tabellennamen in die SQL-Statements hineinzuformatieren. Cursor sind etwas kurzlebiges. Das sollte man nicht als Attribut auf einer Klasse haben. Durch das with self.conn wird schon automatisch ein Commit ausgeführt. Was soll der Context-Manager der Klasse db_Artikelstammdaten bewirken?

ach so, sorry:

Code: Alles auswählen

        self.cursor.execute("CREATE TABLE IF NOT EXISTS A_Hauptsatz_1("
                            "ID INTEGER PRIMARY KEY,"
                            " Satzartkennung TEXT,"
                            " Verarbeitungskennung TEXT,"
                            " Artikelnummer TEXT,"
                            " Textkennung TEXT,"
                            " Artikelbezeichung_1 TEXT,"
                            " Artikelbezeichung_2 TEXT,"
                            " Preiskennung INTEGER,"
                            " Preiseinheit INTEGER,"
                            " Mengeneinheit TEXT,"
                            " Preis INTEGER,"
                            " Rabattgruppe TEXT,"
                            " Hauptwarengruppe TEXT,"
                            " Langtextschluessel TEXT,"
                            " Leer_1 TEXT"
                            ")")

Also kein Index auf Artikelnummer, was auch die langsamen Updates erklären würde.

Er hat eine ~3GB große Tabelle auf die er ~2.5GB an Updates ausführt.

Mit UPDATEs ist das langsam ob mit oder ohne Index. Mit Index wäre es nur weniger langsam. Außerdem ist die Tabelle und der Index danach absurd aufgebläht sodass man die Tabelle sowieso neubauen muss.

@DasIch: warum sollte sich bei einem Update auf ein anderes Feld, sich der Index für Artikelnummer ändern? Das Neubauen muß man nicht explizit machen, VACUUM oder auto vacuum ist da geeigneter.

MVCC dürfte in diesem Fall dazu führen dass für jede Zeile die vom UPDATE betroffen wird, diese als gelöscht markiert wird und eine neue Zeile hinzugefügt wird. Im Index muss man dies auch machen damit der entsprechende Eintrag im Index auf die neue Zeile zeigt.

(AUTO)VACUUM unter SQLite baut anscheinend neu unter postgres wäre in so einer Situation ein pg_repack durchaus sinnvoll, VACUUM selbst würde ewig brauchen.

ok, jungs. ich hab eigentlich eher an INSERT OR REPLACE gedacht, weil UPDATE ja INSERT sowieso benutzt.
is aber auch eher ein tropfen auf heißen stein?
MVCC und VACUUM kenn ich nicht, kann ich mir aber gern anschauen und umsetzten, wenns hilft.

hab diese seite gefunden:
http://www.sqlitetutorial.net/sqlite-replace-statement/
da wird ein UNIQUE INDEX gesetzt und mit REPLACE INTO eingefügt oder überschreiben.
ich denke, ich brauche irgendwie eine methode, mit der die datenbank möglichst schnell durchsucht werden kann.
ob das damit schneller geht?

@DMD-OL: Du mußt einfach nur der Spalte Artikelnummer einen Index geben. Das dürfte den Update um den Faktor 100 beschleunigen. Vergiß das ganze andere Zeugs, das sind nur Vermutungen, falls es immer noch zu langsam sein sollte. Erster Schritt ist immer eine Tabelle mit den richtigen Indizes zu erzeugen.

und welchen index muß ich artikelnummer geben PRIMARY KEY oder UNIQUE?
hab auch das hier gefunden:
CREATE UNIQUE INDEX idx_positions_title ON positions (title); (http://www.sqlitetutorial.net/sqlite-replace-statement/)

@DMD-OL: als Primärschlüssel hast Du ja ID. Und ob die Artikelnummer unique ist oder nicht, weißt nur Du. Ein »CREATE INDEX« reicht auch schon, um Abfragen zu beschleunigen.

@sirius: wäre es denn besser die artikelnummer den primärschlüssel zu geben und die id ganz weg, die brauch ich nämlich eigentlich gar nicht?
das wichtigste ist einfach die artikelnummer.

Code: Alles auswählen

    def create_product(self):
        self.cursor.execute("CREATE TABLE IF NOT EXISTS A_Hauptsatz_1("
                            "Satzartkennung TEXT,"
                            " Verarbeitungskennung TEXT,"
                            " Artikelnummer TEXT PRIMARY KEY,"
                            " Textkennung TEXT,"
                            " Artikelbezeichung_1 TEXT,"
                            " Artikelbezeichung_2 TEXT,"
                            " Preiskennung INTEGER,"
                            " Preiseinheit INTEGER,"
                            " Mengeneinheit TEXT,"
                            " Preis INTEGER,"
                            " Rabattgruppe TEXT,"
                            " Hauptwarengruppe TEXT,"
                            " Langtextschluessel TEXT,"
                            " Leer_1 TEXT"
                            ")")

ist das so möglich und auch "100 x" schneller?

Wenn die Artikelnummer eindeutig ist, dann braucht es keine zusätzliche ID. Es kann aber auch Sinn machen, falls die Artikelnummer ein unhandlicher String ist.

hi
vielen dank für deine hilfe.
ich habe es jetzt so und es läuft sehr gut UND WIRKLICH SCHNELL

Code: Alles auswählen

    def create_product(self):
        self.cursor.execute("CREATE TABLE IF NOT EXISTS " + self.table_name_a +
                            "(ID INTEGER PRIMARY KEY, "
                            "Satzartkennung TEXT, "
                            "Verarbeitungskennung TEXT, "
                            "Artikelnummer TEXT UNIQUE, "
                            "Textkennung TEXT, "
                            "Artikelbezeichung_1 TEXT, "
                            "Artikelbezeichung_2 TEXT, "
                            "Preiskennung INTEGER, "
                            "Preiseinheit INTEGER, "
                            "Mengeneinheit TEXT, "
                            "Preis INTEGER, "
                            "Rabattgruppe TEXT, "
                            "Hauptwarengruppe TEXT, "
                            "Langtextschluessel TEXT, "
                            "Leer_1 TEXT"
                            ")"
                            )

DANKE AN ALLE

@DMD-OL: hat Dir eigentlich schonmal jemand gesagt, dass Tabellennamen fix sein sollten und nicht durch irgendwelche Variablen festgelegt? Warum ist Preis ein INTEGER? Rechnest Du in Pfennig?

Das deutsche Python-Forum

Schnellster Weg eine Datenbank upzudaten?

Schnellster Weg eine Datenbank upzudaten?

Re: Schnellster Weg eine Datenbank upzudaten?

Re: Schnellster Weg eine Datenbank upzudaten?

Re: Schnellster Weg eine Datenbank upzudaten?

Re: Schnellster Weg eine Datenbank upzudaten?

Re: Schnellster Weg eine Datenbank upzudaten?

Re: Schnellster Weg eine Datenbank upzudaten?

Re: Schnellster Weg eine Datenbank upzudaten?

Re: Schnellster Weg eine Datenbank upzudaten?

Re: Schnellster Weg eine Datenbank upzudaten?

Re: Schnellster Weg eine Datenbank upzudaten?

Re: Schnellster Weg eine Datenbank upzudaten?

Re: Schnellster Weg eine Datenbank upzudaten?

Re: Schnellster Weg eine Datenbank upzudaten?

Re: Schnellster Weg eine Datenbank upzudaten?

Re: Schnellster Weg eine Datenbank upzudaten?

Re: Schnellster Weg eine Datenbank upzudaten?

Re: Schnellster Weg eine Datenbank upzudaten?

Re: Schnellster Weg eine Datenbank upzudaten?

Re: Schnellster Weg eine Datenbank upzudaten?