sehr viele Daten in einer MySQL Datenbank überprüfen/schreiben

Installation und Anwendung von Datenbankschnittstellen wie SQLite, PostgreSQL, MariaDB/MySQL, der DB-API 2.0 und sonstigen Datenbanksystemen.
boeserkorn
User
Beiträge: 16
Registriert: Freitag 22. September 2017, 19:46

Hallo,

ich hab mal wieder eine Frage worauf ich im Internet keine Antwort gefunden habe.

ich habe mehrere Tausend Daten die in einer Datenbank stehen, diese wollte ich nun überprüfen ob sie noch aktuell sind bzw. ob sie überhaupt existieren.
gibt es eine Möglichkeit diese schnell zu verarbeiten oder muss jeden einzelnen Wert überprüfen ob dieser existiert und entsprechend neu anlegen bzw. den Wert überschreiben?

die Tabelle ist folgender Maßen aufgebaut

Code: Alles auswählen

ID|Uhrzeit|Wert
1 |2017-02-02 12:30|523.23 
...
Danke für die Antworten, ich hoffe man versteht mein Vorhaben
Sirius3
User
Beiträge: 17703
Registriert: Sonntag 21. Oktober 2012, 17:20

@boeserkorn: natürlich muß man jeden einzelnen Datensatz auf Aktualität prüfen. Ob das schnell ist oder nicht, hängt davon ab, wie die Daten konkret aussehen, wie ein Vergleich aussieht. Wenn es nur darum geht, alle Daten auf den aktuellsten Stand zu bringen, dann reicht es, alles zu löschen und neu anzulegen.
boeserkorn
User
Beiträge: 16
Registriert: Freitag 22. September 2017, 19:46

Danke für die Antwort.

würde ein "UPDATE" Befehl ein Fehler zurück geben wenn die Uhrzeit noch nicht existiert und ich danach den Wert mit Uhrzeit mit dem Befehl "INSERT" anlege.....?



oder ist das keine gute Idee, wollte natürlich so wenig wie möglich Rechenleistung sparen bzw. es so schnell wie möglich machen.
__deets__
User
Beiträge: 14480
Registriert: Mittwoch 14. Oktober 2015, 14:29

Da optimiert doch schon wieder jemand rum ohne zu wissen, ob er Probleme hat... Zuerstmal kommt Korrektheit. Wenn dein Programm tut, was es tun soll, dann ist erstmal gut. Ob es das nun unter Verwendung von so wenig CPU-Zyklen wie moeglich tut ist voellig zweitrangig, solange dir die nicht an anderer Stelle wirklich fehlen.

Und rein konzeptionell kann ein UPDATE nur auf Daten arbeiten, die auch existieren. Es ist darum auch kein Fehler, wenn es auf gar keinen Daten oder nicht auf Daten zu einem speziellen Zeitpunkt arbeitet, die es ja per Definition nicht kennen kann, wenn der Zeitpunkt gar nicht existiert.

Statt so spezifischer Annahmen und Fragen waere es glaube ich mal angezeigt das du erklaerst, was du machst, welche Daten wie repraesentiert sind, und was du fuer Aufraeumarbeiten zu tun gedenkst. Denn so bleibt die Hilfe notwendigerweise Stueckwerk.
Benutzeravatar
noisefloor
User
Beiträge: 3829
Registriert: Mittwoch 17. Oktober 2007, 21:40
Wohnort: WW
Kontaktdaten:

Hallo,

@boeserkorn: "mehrere tausend Daten" ist für ein RDBMS quasi nichts. Viel ist für ein RDBMS vielleicht ab eine Millionen oder so. Und dann kommst noch auf des RDBMS an (also z.B. SQLite oder PostgreSQL).

Und auch "lahme" Hardware (wie z.B. ein Raspi) ist bei ein paar tausend Daten und einer simplen Datenstruktur wie der gezeigten kein Problem.

Wie __deets__ schon im vorherigen Post sagt: "Premature optimization is the root of all evil".

Gruß, noisefloor
DasIch
User
Beiträge: 2718
Registriert: Montag 19. Mai 2008, 04:21
Wohnort: Berlin

Mehrere Millionen Zeilen kann man problemlos jeden Tag in eine Datenbank schieben, zumindest sofern man Partitioning betreibt.

Wenn du viele Daten hast kopier die einfach mit COPY oder LOAD DATA INFILE in eine temporäre Tabelle und verteile bzw. update Tabellen auf Basis der temporären Tabelle. Auf die Weise kannst du innerhalb von Minuten auch wesentlich mehr Daten als du hast verarbeiten.

Vermeide es Zeile für Zeile zu arbeiten, dass ist sehr langsam sobald deine Datenmenge wächst.

Außerdem solltest du dir Upserts anschauen, es klingt als ob dass dein Problem genau lösen würde.
boeserkorn
User
Beiträge: 16
Registriert: Freitag 22. September 2017, 19:46

@DasIch

was genau ist Upserts, gibt es dort ein Tutorial bzw eine Seite wo es gut erklärt wird?
DasIch
User
Beiträge: 2718
Registriert: Montag 19. Mai 2008, 04:21
Wohnort: Berlin

Ein Upsert ist eine Kombination aus INSERT und UPDATE. Sinn des ganzen ist dass du mit einem Statement atomar eine Zeile in eine Tabelle einfügst oder updatest. Bei MySQL gibt es dies als INSERT ... ON DUPLICATE KEY UPDATE.
boeserkorn
User
Beiträge: 16
Registriert: Freitag 22. September 2017, 19:46

ich raffe es irgendwie nicht.

unten ist ein Ausschnitt der Tabelle die aktualisieren möchte bzw. neue Werte hinzufügen.
Ich habe mehrere Tabellen die aktualisieren werden müssen bzw. neue Werte hinzugefügt werden.
idLastgang = wird automatisch erhöht
Viertelstunde = darf nur einmal in der Tabelle vorkommen in Verbindung mit Anlagenpv_idAnlagenPV, neue Zeitstempel sollen natürlich aktualisiert werden

Lastgangwert = dieser Wert soll aktualisiert werden bzw. neu angelegt werden
Geschäftspartnernummer = soll auch aktualisiert werden, dient nur zur Info bzw. zur nachvollziehbarkeit
ZPB = soll auch aktualisiert werden, dient nur zur Info bzw. zur nachvollziehbarkeit
Anlagenpv_idAnlagenPV = sollte bei neuen erstellten Werten die ID von der Anlage bekommen (von einer anderen Tabelle)

momentan aktualiesere ich mit diesen Befehl:
[codebox=mysql file=Unbenannt.sql]UPDATE `lastgang` SET"
"`Viertelstunde` = '2017-08-31 23:45:00', "
"`lastgangwert` = '0.600', "
"`Geschaftspartnernummer` = '2250100***59', "
"`ZPB` = 'DE000****************00000000002129719' "
"WHERE `idLastgang` = 867347 AND `anlagenpv_idAnlagenPV` = 6;[/code]
und neue erstelle ich mit diesen:
[codebox=mysql file=Unbenannt.sql]INSERT INTO `lastgang` (`Viertelstunde`, `lastgangwert`, `Geschaftspartnernummer`, `ZPB`, `anlagenpv_idAnlagenPV`)"
"VALUES ('2017-09-01 00:00:00'', '0.900', '2250100***59', 'DE000****************00000000002129719', 6);[/code]

habe mir die "UPSERT" varianten von MySQL angeschaut, werde da aber nicht schlau draus und schaffe es nicht auf meine Tabellen es anzupassen.

weiss noch jemand eine Rat, momentan schaffe ich 4 Datensätze pro Sekunde zu erstellen bzw zu aktualisieren
wenn ich eine zweite Verbindung aufbaue halbiert sich die Geschwindigkeit.

Code: Alles auswählen

idLastgang, Viertelstunde, Lastgangwert, Geschäftspartnernummer, ZPB, Anlagenpv_idAnlagenPV
'867347', '2017-08-31 23:45:00', '0.000', '2250100***59', 'DE000****************00000000002129719', '6'
'867346', '2017-08-31 23:30:00', '0.000', '2250100***59', 'DE000****************00000000002129719', '6'
'867345', '2017-08-31 23:15:00', '0.000', '2250100***59', 'DE000****************00000000002129719', '6'
'867344', '2017-08-31 23:00:00', '0.000', '2250100***59', 'DE000****************00000000002129719', '6'
'867343', '2017-08-31 22:45:00', '0.000', '2250100***59', 'DE000****************00000000002129719', '6'
'867342', '2017-08-31 22:30:00', '0.000', '2250100***59', 'DE000****************00000000002129719', '6'
'867341', '2017-08-31 22:15:00', '0.000', '2250100***59', 'DE000****************00000000002129719', '6'
'867340', '2017-08-31 22:00:00', '0.000', '2250100***59', 'DE000****************00000000002129719', '6'
'867339', '2017-08-31 21:45:00', '0.000', '2250100***59', 'DE000****************00000000002129719', '6'
'867338', '2017-08-31 21:30:00', '0.000', '2250100***59', 'DE000****************00000000002129719', '6'
Sirius3
User
Beiträge: 17703
Registriert: Sonntag 21. Oktober 2012, 17:20

@boeserkorn: warum sehen alle Deine Werte wie Strings aus? idLastgang, Anlagenpv_idAnlagenPV und Lastgangwert sollten Zahlen sein, Viertelstunde sollte ein Datum sein, wobei Viertelstunde kein guter Name (grottenschlecht trifft's besser) ist, `Messzeitpunkt` wäre besser. Bei `Anlagenpv_idAnlagenPV` ist irgendwie der Anlagenpv doppelt, sollte also `idAnlagenPV` heißen.

Was ist denn nun Dein PRIMARY-KEY? idLastgang oder Viertelstunde+Anlagenpv_idAnlagenPV? Wenn Viertelstunde+Anlagenpv_idAnlagenPV eindeutig sind, warum gibt es noch idLastgang? Wenn idAnlagenPV eindeutig ist, warum stehen dann Geschäftspartnernummer und ZPB nicht in einer eigenen Tabelle?

4 Datensätze pro Sekunde hören sich so an, als ob Du keine Indizes benutzt. Welche Constraints und Indizes hast Du erzeugt?
boeserkorn
User
Beiträge: 16
Registriert: Freitag 22. September 2017, 19:46

es ist doch egal wie was heißt, ZPB und Geschäftspartnernummer habe ich später hinzugefügt nur als Info, könnte sie auch leer lassen.
der PrimaryKey ist idLastgang, was du mit "Indizes" & "Constraints" meinst weiss ich jetzt auch nicht, ich bin kein Profi in Datenbanken und lerne immer noch was dazu, sonst würde ich nicht fragen.

Anlagenpv_idAnlagenPV heißt deswegen so, weil die Tabelle Anlagenpv heißt und der Key idAnlagenPV
Sirius3
User
Beiträge: 17703
Registriert: Sonntag 21. Oktober 2012, 17:20

@boeserkorn: dann solltest Du schnell lernen, was Constraints und Indizes sind. Denn genau darum geht es ja bei Deinem Problem. Wenn Id und Zeitpunkt eindeutig sein sollen, muß das über ein Constraint in der Datenbank festgelegt werden. Wenn Du einigermaßen schnell in der Datenbank suchen willst, mußt Du entsprechende Inidizes anlegen. Namen sind WICHITG; es ist eben nicht egal, wie etwas heißt. Es zeigt, ob man das Problem verstanden hat, oder nicht. Wenn ich einen Namen idAnlagenPV lese, würde ich denken, dass es die ID der Tabelle AnlagenPV ist, da brauch ich die Tabelle nicht nochmal zu wiederholen. Dopplungen in den Daten zeigen, dass die Datenbank nicht gut designed (Fachbegriff "normalisiert") ist. ZPB und Geschäftspartnernummer sollten sich nicht ständig wiederholen, wenn idAnlagenPV schon eindeutig ist.
DasIch
User
Beiträge: 2718
Registriert: Montag 19. Mai 2008, 04:21
Wohnort: Berlin

Sirius3 hat geschrieben:4 Datensätze pro Sekunde hören sich so an, als ob Du keine Indizes benutzt. Welche Constraints und Indizes hast Du erzeugt?
Auch wenn Indizes und Constraints zu einem Gewissen grad hilfreich oder sogar notwendig sind, werden mehr davon sicherlich nicht die Geschwindigkeit mit der in die Tabelle geschrieben wird erhöhen.
Sirius3
User
Beiträge: 17703
Registriert: Sonntag 21. Oktober 2012, 17:20

@DasIch: du glaubst nicht, wie lahm Datenbanken werden, wenn Indizes fehlen.
__deets__
User
Beiträge: 14480
Registriert: Mittwoch 14. Oktober 2015, 14:29

Da der TE den Prozess schneller bekommt wenn er das Skript parallel laufen laesst (so verstehe ich zumindest seine Aussagen) klingt das schon danach, als ob weniger die inserts sondern irgendetwas das er da sonst so treibt das Problem darstellen. Und eine maue Abfrage laeuft dann halt auf zwei statt einem Kern oder so.
Sirius3
User
Beiträge: 17703
Registriert: Sonntag 21. Oktober 2012, 17:20

@__deets__: ich hab keine Ahnung, ob MySQL Anfragen parallelisiert.
DasIch
User
Beiträge: 2718
Registriert: Montag 19. Mai 2008, 04:21
Wohnort: Berlin

Sirius3 hat geschrieben:@DasIch: du glaubst nicht, wie lahm Datenbanken werden, wenn Indizes fehlen.
Langsam ist relativ. Ein Index hilft beim lesen aber dafür kostet es Zeit beim schreiben. Ersteres ist auch nur bei ausreichend großen Tabellen nennenswert.

Bei Größenordnungen von "mehreren tausenden Daten" wie hier bringt einem ein Index jetzt nicht so viel.
Sirius3
User
Beiträge: 17703
Registriert: Sonntag 21. Oktober 2012, 17:20

@DasIch: das ist der Unterschied, ob nun 1000 Elemente verglichen werden müssen, oder 2 bis 3. Ich würde sagen, das merkt man ziemlich schnell. Da ist die Zeit, die man braucht, um ein Element dem Index hinzuzufügen, vernachlässigbar.
Sirius3
User
Beiträge: 17703
Registriert: Sonntag 21. Oktober 2012, 17:20

Ich mach mal einen Full-Quote, damit der Beitrag aus dem falschen Thread im richtigen landet:
boeserkorn hat geschrieben:@Dasich

kannst du mir helfen?

hab heute Nacht die Daten importieren lassen und er hat innerhalb von 1.5h 20551 Datensätze in die Datenbank geschrieben.
Danach und davor und danach hat er ca. 8h für die gleiche Datenmenge gebraucht, Aufbau und Routine ist die gleiche gewesen

weisst du warum die Zeiten so unterschiedlich sind?

Code: Alles auswählen

05.10.2017 09:25:28
klaas_eisman
201703-09 dithmarsen.xls
215000019374
20551
05.10.2017 16:46:09
klaas_eisman
201703-09 immenste.xls
225010041504
20551
06.10.2017 00:59:53
klaas_eisman
201703-09 heinrichs.xls
215000047128
20551
06.10.2017 02:20:05
klaas_eisman
201703-09 113.xls
225010036614
20551
Was soll denn das Listing sagen? Was sind das für Zahlen und was für Dateien?

Die Erklärung habe ich Dir schon geliefert. Bei jedem UPDATE werden Deine Daten linear durchsucht. Solange beim Aufbau der Tabelle noch wenige Zeilen in der Tabelle stehen, geht das ziemlich schnell, später immer langsamer. Also beim Anlegen N*(N-1)/2 Vergleiche plus N INSERTS, beim Updaten N**2 Vergleiche plus N UPDATES. Wie sieht denn nun Dein Datenbankschema aus und wie der Pythoncode, den Du verwendest?
boeserkorn
User
Beiträge: 16
Registriert: Freitag 22. September 2017, 19:46

Code: Alles auswählen

Uhrzeit:      05.10.2017 09:25:28
Datenbank: klaas_eisman
Dateiname: 201703-09 dithmarsen.xls
Datensätze/Reihen: 20551
Uhrzeit:     05.10.2017 16:46:09
Datenbank: klaas_eisman
Dateiname: 201703-09 immenste.xls
Datensätze/Reihen: 20551
Uhrzeit:      06.10.2017 00:59:53
Datenbank: klaas_eisman
Dateiname: 201703-09 heinrichs.xls
Datensätze/Reihen: 20551
Uhrzeit:      06.10.2017 02:20:05
verstehe es nicht warum er für die gleiche Anzahl der Datensätze viel länger braucht
einmal schafft er es in 1,5h und danach wieder nur in 6-8h
Die Dateien enthalten 7 Monate Viertelstundenwerten also 20.551 Datensätze

ist nicht komplett vollständig

Code: Alles auswählen


Kunde.append("Hansen ")
sunnyportal_ID.append("03da8fbaa")
Datenbank.append(db)
AnlagenID.append(3)
vkn.append("****")



for x in range(0,len(Kunde)):
	cnx = mysql.connector.connect(host = "192.168.178.57", user = "eisman", passwd = "eisman", db = Datenbank[x] )
	cursor = cnx.cursor()
	print time.strftime("%d.%m.%Y %H:%M:%S")
	a ="/home/pi/pi-share/"
	print Datenbank[x]
	#print os.listdir(a)
	filelist=[]
	filelist = glob.glob("/home/pi/pi-share/*.*")
	print filelist
	#print Kunde[x]
	f = 0
	for f in filelist:
		#print f
		#print Kunde[x]
		filename = f.replace('/home/pi/pi-share/','')
		#print filename
		#shutil.move('/home/pi/pi-share/'+filename ,'/home/pi/pi-share/geladen/')
		wb = xlrd.open_workbook(f)
		sheet_xls = wb.sheet_names()
		#print sheet_xls[0]
		sh =  wb.sheet_by_name(sheet_xls[0])
		rows = []
		#for row_number in xrange(sh.nrows):
		#	rows.append(sh.row_values(row_number))
		#for row in rows[:12]:
		#	pass
		#	print(row)
		Vertragskonto = sh.cell_value(0,2)
		zpb = sh.cell_value(1,2)
		#print "VKN: ", Vertragskonto
		if Vertragskonto == vkn[x]:
			print filename
			print Vertragskonto
			#print vkn [x] , "  " , Vertragskonto
			#print "correct"
			#print AnlagenID[x]
			#rows= []
			for y  in xrange(11,sh.nrows):
				date = sh.cell_value(y,0)
				date =  datetime(*xlrd.xldate_as_tuple(date,0))
				print date
				query = ("SELECT idLastgang FROM lastgang WHERE Viertelstunde =  '%s' AND anlagenpv_idAnlagenPV = %s ;"
						 %(  str(date)   , str(AnlagenID[x]) ) ) 
				cursor.execute(query)
				rows = cursor.fetchall()
				numrows = int(cursor.rowcount)
				if not numrows == 0 :
					idlastgang = rows[0][0]
					if not idlastgang is None :
						query = ("UPDATE `lastgang` SET `Viertelstunde` = '%s', `lastgangwert` = '%s', `Geschaftspartnernummer` = '%s', `ZPB` = '%s' WHERE `idLastgang` = '%s' AND `anlagenpv_idAnlagenPV` = '%s';" 
								%(str(date), str(sh.cell_value(y,4)),str(vkn[x]),str(zpb),str(idlastgang), str(AnlagenID[x]) ))
						cursor.execute(query)
						cnx.commit()
				else:
					query = ("INSERT INTO `lastgang` (`Viertelstunde`, `lastgangwert`, `Geschaftspartnernummer`, `ZPB`, `anlagenpv_idAnlagenPV`)" 
						"VALUES ('%s', '%s', '%s', '%s', '%s');" 
							%(str(date ), str(sh.cell_value(y,4)), str(vkn[x]), str(zpb), str(AnlagenID[x]) ))
					#print query
					cursor.execute(query)
					cnx.commit()
					#print query
					pass
Antworten