Frage zur INSERT INTO Geschwindigkeit

Hi,

zu Übungszwecken experimentiere ich zur Zeit mit der MYSQL Datenbank.

Ich verwende folgenden C-Code um Daten in die Datenbak eunzufügen

char *server = "127.0.0.1"; 
	char *user = "root"; 
	char *password = "1"; 
	char *database = "MeineDb"; 

	MYSQL mysql; 
	printf("MySQL Client Version is %s\n",mysql_get_client_info()); 
	if(mysql_init(&mysql)==NULL) 
	{ 
		printf("\nFailed to initate MySQL connection"); 
		exit(EXIT_FAILURE); 
	} 

	if((mysql_real_connect(&mysql,server,user,password,database,0,NULL,0))== NULL)
	{

		std::cout << mysql_error(&mysql);
	}

	if (mysql_query(&mysql," CREATE TABLE Vertreter (`ID` INT NOT NULL  AUTO_INCREMENT PRIMARY KEY, `Name` VARCHAR(200), `Umsatz` INT, `Monat` VARCHAR(20))"))
	{
		std::cout << mysql_error(&mysql);
	}
	for (int i = 0; i < 10000; i++)
	{

		if (mysql_query(&mysql,"INSERT INTO Vertreter (Name,Umsatz,Monat) VALUES ('Hans', '5000', 'Januar')"))
		{
			std::cout << mysql_error(&mysql);
		}

		if (mysql_query(&mysql,"INSERT INTO Vertreter (Name,Umsatz,Monat) VALUES ('Otto', '4000', 'Januar')"))
		{
			std::cout << mysql_error(&mysql);
		}
	}

Es werden 20000 Datensätze eingefügt (2 in jedem Schleifendurchlauf), die sich durch die ID unterscheiden.

Dabei dauert die Schleife bei meinem Computer 3 Minuten und 27 Sekunden mit ca. 5% CPU auslastung und durchgehendem Festplattenzugriff.

Meine Frage:
Ist diese Zeit für das Einfügen normal? Oder mache ich was falsch?

SideWinder

- Wenn du jeden Datensatz einzeln einfügst ist das für die Performance nicht gut.
- Versuche möglichst viel in einen Batch zu werfen oder zumindest PreparedStatements zu verwenden
- Je nach Anzahl und Umfang der Indices kann das Einfügen auch mal etwas dauern, ja
- Ob die Zeit normal ist kann ich dir nicht sagen, wird aber auch sehr stark von der Hardware abhängen

MfG SideWinder

tntnet

INSERT ist in der Regel nicht so schnell. Ich weiß nicht wie es bei Mysql ist, aber normalerweise läuft INSERT in einer Transaktion schneller, da die Datenbank nach dem INSERT nicht sicher stellen muss, dass die Daten auch auf der Platte landen, sondern erst beim COMMIT. Bei Postgresql und Oracle macht das mächtig was aus.

Auch sind prepared Statements immer eine gute Idee. Da muss die Datenbank das Statement nur ein mal parsen. Bei Deinem Beispiel hast Du so was nicht, aber prepared Statements verhindern auch SQL injections (zumindest wenn Du es richtig machst).

Und noch ein Hinweis: ich finde es prima, dass Du Fehler abfragst, aber so wie Du es machst ist doch ein wenig komisch. Wenn Du keine Verbindung bekommst, dann gebe die Fehlermeldung aus und mache mit "CREATE TABLE" weiter. Und wenn das auch nicht klappt, dann mache ein "INSERT". Wäre es nicht angebracht, nach einem Fehler abzubrechen?

hustbaer

@tntnet
Was du schreibst ist grundsätzlich richtig. Wobei es Datenbanken gibt wo mit oder ohne Transaktion keinen relevanten Unterschied macht (z.B. MSSQL).

Und den richtig guten Speed-Boost bekommst du bei INSERT erst, wenn du mehrere Zeilen mit dem selben INSERT einfügst - sagen wir >= 1000 auf einmal.
Das gilt vermutlich auch für alle Datenbanken (ganz sicher für MSSQL und MySQL).

tntnet

Ich habe das mal auf die schnelle bei mir ausprobiert. Mit meiner postgresql-Datenbank auf Linux dauert das einfügen von 2000 Sätzen ohne Transaktion ca 19 Sekunden und mit Transaktion 0,4 Sekunden!!! Also etwa 50 mal so schnell.

Das ist unabhängig davon, ob ich ein prepared statement verwende oder nicht.

hustbaer

Wer schreibt denn was über prepared statements?
(EDIT: OK, du selber, hihi /EDIT)

Ich meine mehrere Zeilen in einem INSERT Befehl, also z.B. so (Syntax je nach DB unterschiedlich):

INSERT myTable (a, b, c)
VALUES
   (1, 2, 3),
   (1, 2, 4),
   (1, 2, 5),
   (1, 2, 6),
   (1, 2, 7),
   (1, 2, 8),
   (1, 2, 9),
   (1, 2, 10),
...
   ;

Das ist zumindest das einzige was bei MSSQL hilft (wobei man bis Version 2008 leider noch zu einem grässlichen Workaround greifen muss, weil es bis dahin keine "mehrere zeilen" Syntax gibt). Und wie hier kürzlich erst jmd. bestätigt hat bringt es bei MySQL auch sehr viel (hab ich aber nicht ausprobiert, plappere ich jetzt einfach mal nach *g*).

Wenn PostgreSQL auch gut Performance mit einzelnen INSERTs bringt ist das ne feine Sache!

Würde mich aber interessieren wie lange PostgreSQL braucht wenn du alle 2000 Zeilen in dem selben INSERT einfügst - also ob er dann nochmal schneller wird (bzw. 0,4 Sekunden ist schon sehr wenig, vielleicht sollte man dann nochmal mit mehr Zeilen testen).

tntnet

Bei Postgresql gibt es noch den COPY-Befehl. Der ist noch viel schneller. Zum laden von 20000 Zeilen benötige ich damit nur noch 0,4 Sekunden. Mit einzelnen inserts in einer Transaktion eben ca. 4 Sekunden.

Der COPY-Befehl dient zum schnellen laden oder exportieren von Daten. So etwas ähnliches gibt es auch unter Oracle und anderen Datenbanken. Aber das kommt in normalen Applikationen normalerweise nicht zum Einsatz.

hustbaer

Also ich hab sowas (schnelles einfügen/updaten von vielen Zeilen) schon öfter in einer "normalen" Anwendung gebraucht. (Beim Updaten meine ich jetzt wirklich Updaten von vielen Zeilen mit Werten die die Applikation vorgibt, wo man mit einem einfachen Bulk-UPDATE also nicht auskommt.)

Hi,

könnte mir Jemand ein Beispiel aufschreiben wie man mit prepared statement einfügt?

Wenn ja, bitte das Beispiel auch als Antwort posten (Sonst kommen wieder nur "Ja" Antworten )

nman

Prepared Statements werden idR. eher selten Flaschenhaelse beseitigen. Probier lieber mal die von hustbaer vorgeschlagenen Multi-Inserts, das ist aussichtsreicher.

Prepared Statements werden auch hier erklaert:
http://dev.mysql.com/doc/refman/5.0/en/sql-syntax-prepared-statements.html

hustbaer

Japp.
Multi-insert mit Transaktion drumrum. Damit solltest du auf so ziemlich allen DBMS' nahe an die optimale Geschwindigkeit kommen.

Zu toppen ist das dann vermutlich nur noch mit Speziallösungen (wie z.B. System.Data.SqlClient.SqlBulkCopy oder anderen non-Standard Tools/Erweiterungen wie dem von tntnet erwähnten COPY Befehl).

nman

Siehe auch hier:
http://dev.mysql.com/doc/refman/5.6/en/insert-speed.html

Danke!

Also "Transaction" wirkt schon mal Wunder

Ich habe jetzt zum Test diese Version mit Transaction umgesetzt.
(Für Verbesserungsvorschläge bin ich dankbar)

if (mysql_query(&mysql,"START TRANSACTION"))
	{
		std::cout << mysql_error(&mysql);
	}

	for (int i = 0; i < 10000; i++)
	{

		if (mysql_query(&mysql,"INSERT INTO Star VALUES (NOW(), 'Michael Jackson', 48, 'm')"))
		{
			std::cout << mysql_error(&mysql);
			if (mysql_query(&mysql,"ROLLBACK"))
				{
					std::cout << mysql_error(&mysql);
				}
		}

		if (mysql_query(&mysql,"INSERT INTO Star VALUES (NOW(), 'Danny DeVito', 62, 'm')"))
		{
			std::cout << mysql_error(&mysql);
			if (mysql_query(&mysql,"ROLLBACK"))
			{
				std::cout << mysql_error(&mysql);
			}
		}
	}

	if (mysql_query(&mysql,"COMMIT"))
	{
		std::cout << mysql_error(&mysql);
	}

Vorher hat es 201,474 Sekunden (3.358 Minuten) gedauert.
Mit Transaction nur noch 1,124 Sekunden (0.018 Minuten) .

Als nächstes werde ich versuchen die beiden INSERT INTO in der Schleife zu einem zusammenzufassen. Die Ergebnisse poste ich dann später.

Auch das Zusammenfassen von INSERT INTO bringt viel:

if (mysql_query(&mysql,"START TRANSACTION"))
	{
		std::cout << mysql_error(&mysql);
	}

	for (int i = 0; i < 10000; i++)
	{

		if (mysql_query(&mysql,"INSERT INTO Star VALUES (NOW(), 'Michael Jackson', 48, 'm'), (NOW(), 'Danny DeVito', 62, 'm')"))
		{
			std::cout << mysql_error(&mysql);
			if (mysql_query(&mysql,"ROLLBACK"))
				{
					std::cout << mysql_error(&mysql);
				}
		}
	}

	if (mysql_query(&mysql,"COMMIT"))
	{
		std::cout << mysql_error(&mysql);
	}

Mit der Zusammenfassung der beiden Einfügungen in der Schleife mit zusätzlichem
TRANSCATION dauert es nur noch 0,718 Sekunden (0.011967 Minuten)

und was willst du mit so einem scheiß benchmark? bulk insert's kommen in real world apps doch nie vor

inserts

kellerassel schrieb:

und was willst du mit so einem scheiß benchmark? bulk insert's kommen in real world apps doch nie vor

Du musst es ja wissen . Aber deswegen brauchst du es noch lange nicht als "Scheiß" bezeichnen. Hast wohl kein Benehmen gelernt

dann ist es eben ein blender benchmark :p

zwutz

je nachdem, wie groß bei dir ein Datensatz sein kann und was dein DBMS zulässt kannst du pro Query paar hundert bis paar tausend Rows gleichzeitig einfügen

Oh und du solltest wirklich abbrechen, wenn es Fehler gibt

nman

kellerassel schrieb:

und was willst du mit so einem scheiß benchmark? bulk insert's kommen in real world apps doch nie vor

Bloedsinn. In vielen Anwendungen kommen die dauernd vor.