MySQL slow code, please what is wrong?-SOLVED

Forum > Database

(1/1)

xaver13:
Hello, I have two code snippets one in Java, one in Free Pascal. Both do the same work. It is simple import from FoxPro dbf table (about 41000 records) to MySQL table.

My Java program takes 8 seconds, but my Free Pascal code takes 3minutes (and use much less, I can say no, memory and resources), please what I am doing wrong? I think, that I must use some streams in pascal to do this work, maybe something as

var
BinStream:TMemoryStream;
DBFStream : TStream;

....

BinStream := TMemoryStream.Create;
DBFStream := TStream.Create;

but how to connect it with TDbf object?? I didn't find any usable sample yet..

Lazarus code:

--- Code: Pascal [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---unit uImportDMySQL; {$mode objfpc}{$H+} interface uses Classes, SysUtils, sqldb, dbf, DB, mysql56conn, FileUtil, Forms, Controls, Graphics, Dialogs, StdCtrls, IniFiles, LConvEncoding; type { TFormImport } TFormImport = class(TForm) BtnImportVyr: TButton; DataSource1: TDataSource; Dbf1: TDbf; edtHeslo: TEdit; Label1: TLabel; lblHlaseni: TLabel; MySQLConnection1: TMySQL56Connection; Query: TSQLQuery; SQLTransaction1: TSQLTransaction; procedure BtnImportVyrClick(Sender: TObject); private { private declarations } public { public declarations } end; const C_DB_SECTION = 'DB-INFO'; var FormImport: TFormImport; INI: TINIFile; DBuser, DBpassword, DBlocation: string; implementation {$R *.lfm} { TFormImport } //F U N K C Efunction FieldValue(x: integer): string;begin case FormImport.Dbf1.Fields[x].Value = Null of True: Result := ''; False: Result := FormImport.Dbf1.Fields[x].Value end;end; procedure TFormImport.BtnImportVyrClick(Sender: TObject);begin lblHlaseni.caption:='Import in progress'; Application.ProcessMessages; INI := TINIFile.Create('DMySQL.ini'); // Put reading the INI file inside a try/finally block to prevent memory leaks try // Demonstrates reading values from the INI file. DBuser := INI.ReadString(C_DB_SECTION, 'DBuser', ''); //DBpassword := INI.ReadString(C_DB_SECTION, 'DBpassword', ''); DBlocation := INI.ReadString(C_DB_SECTION, 'DBlocation', ''); finally // After the ini file was used it must be freed to prevent memory leaks. INI.Free; end; DBpassword:=edtHeslo.Caption; MySQLConnection1.HostName := DBlocation; MySQLConnection1.UserName := DBuser; MySQLConnection1.Password := DBpassword; MySQLConnection1.Transaction := SQLTransaction1; MySQLConnection1.DatabaseName := 'D2'; MySQLConnection1.Open; if MySQLConnection1.Connected then begin Query.Database := MySQLConnection1; SQLTransaction1.Database := MySQLConnection1; Query.ParseSQL := True; Query.ReadOnly := False; Query.SQL.Text := 'SET CHARACTER SET `utf8`'; Query.ExecSQL; Query.SQL.Text := 'SET NAMES `utf8`'; Query.ExecSQL; Query.SQL.Text := 'create table if not exists vyr1new(ID MEDIUMINT NOT NULL AUTO_INCREMENT PRIMARY KEY,KONTROLA char(1) null, ZAKAZKA char(10) null, NZAK varchar(254) null, VYKRES varchar(20) null,' + ' KODOPER char(6) null,' + ' OPERACE varchar(30) null, KODSTROJ char(4) null, STROJ varchar(30) null, OPERATOR varchar(40) null, KOD char(4) null,' + ' ZAKPOZN varchar(254) null, DOBA float null, ZACATEK CHAR(1), TIMEZAC varchar(20) null, KONEC CHAR(1), TIMEKON varchar(20) null,KS_DOBRY integer null,' + ' KS_ZMETEK integer null, UKONCENO char(1) null, POZNAMKA varchar(128) null)'; Query.ExecSQL; Query.SQL.Text := 'ALTER TABLE vyr1new AUTO_INCREMENT=1;'; Query.ExecSQL; Dbf1.FilePathFull := 'd:\J.C\Visual Foxpro Projects\D2\dta'; Dbf1.FilePathFull := 'd:\J.C\Visual Foxpro Projects\D2\dta\'; Dbf1.TableName := 'vyr11.dbf'; //DataSource1 Dbf1.Active := True; Query.SQL.Text := 'insert into vyr1new(ID,KONTROLA,ZAKAZKA,NZAK,VYKRES,KODOPER,OPERACE,KODSTROJ,STROJ,OPERATOR,KOD,' + 'ZAKPOZN,DOBA,ZACATEK,TIMEZAC,KONEC,TIMEKON,KS_DOBRY,KS_ZMETEK,UKONCENO,POZNAMKA) ' + ' values((:ID),(:KONTROLA),(:ZAKAZKA),(:NZAK),(:VYKRES),(:KODOPER),(:OPERACE),(:KODSTROJ),(:STROJ),(:OPERATOR),(:KOD),' + '(:ZAKPOZN),(:DOBA),(:ZACATEK),(:TIMEZAC),(:KONEC),(:TIMEKON),(:KS_DOBRY),(:KS_ZMETEK),(:UKONCENO),(:POZNAMKA))'; //corresponding to foxpro table vyr11.dbf lblHlaseni.caption:='Import in progress'; while not Dbf1.EOF do begin// lblHlaseni.visible:= not(lblHlaseni.visible);// lblHlaseni.Paint; Query.Params.ParamByName('ID').AsString := '0'; Query.Params.ParamByName('KONTROLA').AsString := FieldValue(18); Query.Params.ParamByName('ZAKAZKA').AsString := FieldValue(2); Query.Params.ParamByName('NZAK').AsString := CP1250ToUTF8(FieldValue(3)); Query.Params.ParamByName('VYKRES').AsString := CP1250ToUTF8(FieldValue(4)); Query.Params.ParamByName('KODOPER').AsString := FieldValue(6); Query.Params.ParamByName('OPERACE').AsString := CP1250ToUTF8(FieldValue(7)); Query.Params.ParamByName('KODSTROJ').AsString := FieldValue(8); Query.Params.ParamByName('STROJ').AsString := CP1250ToUTF8(FieldValue(9)); Query.Params.ParamByName('OPERATOR').AsString := CP1250ToUTF8(FieldValue(0)); Query.Params.ParamByName('KOD').AsString := FieldValue(1); Query.Params.ParamByName('ZAKPOZN').AsString := CP1250ToUTF8(FieldValue(5)); Query.Params.ParamByName('DOBA').AsString := FieldValue(16); Query.Params.ParamByName('ZACATEK').AsString := FieldValue(10); Query.Params.ParamByName('TIMEZAC').AsString := FieldValue(11); Query.Params.ParamByName('KONEC').AsString := FieldValue(12); Query.Params.ParamByName('TIMEKON').AsString := FieldValue(13); Query.Params.ParamByName('KS_DOBRY').AsString := FieldValue(14); Query.Params.ParamByName('KS_ZMETEK').AsString := FieldValue(15); Query.Params.ParamByName('UKONCENO').AsString := FieldValue(17); Query.Params.ParamByName('POZNAMKA').AsString := CP1250ToUTF8(FieldValue(19)); //fields numbering dbf begins by zero Query.ExecSQL; Dbf1.Next; //SQLTransaction1.commitretaining; end; lblHlaseni.caption:='Čekám..'; lblHlaseni.visible := true; ShowMessage('Výroba byla naimportována.'); //* Query.SQL.Text := 'DROP TABLE IF EXISTS VYR1old'; Query.ExecSQL; SQLTransaction1.Active := True; Query.SQL.Text := 'RENAME TABLE vyr1 TO vyr1old, vyr1new TO vyr1'; Query.ExecSQL; Query.Close; ShowMessage('Soubory přejmenovány.'); end; end; end.
Java Code

--- Code: Java [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---/* * To change this license header, choose License Headers in Project Properties. * To change this template file, choose Tools | Templates * and open the template in the editor. */package D2; import com.hexiong.jdbf.DBFReader;import java.nio.charset.Charset;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.Statement; /** * * @author Jiri Cvrk <cvrk@volny.cz> */public class ImportVyr1 { public static Connection con = null; private static Statement stat; PreparedStatement pst = null; public static void ImportVyr1() throws Exception { try { con = DBConnect.getConnected(); stat = con.createStatement(); stat.executeUpdate("create table if not exists vyr11(ID MEDIUMINT NOT NULL AUTO_INCREMENT PRIMARY KEY,KONTROLA char(1) null, ZAKAZKA char(10) null, NZAK varchar(254) null, VYKRES varchar(20) null," + " KODOPER char(6) null,\n" + " OPERACE varchar(30) null, KODSTROJ char(4) null, STROJ varchar(30) null, OPERATOR varchar(40) null, KOD char(4) null,\n" + " ZAKPOZN varchar(254) null, DOBA float null, ZACATEK CHAR(1), TIMEZAC varchar(20) null, KONEC CHAR(1), TIMEKON varchar(20) null,KS_DOBRY integer null,\n" + " KS_ZMETEK integer null, UKONCENO char(1) null, POZNAMKA varchar(128) null);"); } catch (Exception e) { e.printStackTrace(); System.out.println("Chyba založení tabulky vyr11"); } try { con = DBConnect.getConnected(); stat = con.createStatement(); stat.executeUpdate("ALTER TABLE vyr11 AUTO_INCREMENT=1;"); } catch (Exception e) { e.printStackTrace(); System.out.println("Chyba autoinkrementu tabulky vyr11"); } DBFReader dbfreader = new DBFReader(ScreensFramework.D2location+"\\dta\\vyr11.dbf"); int i; for (i = 0; i < dbfreader.getFieldCount(); i++) { System.out.print(dbfreader.getField(i).getName() + " "); } System.out.print("\n"); try { String sql = "BEGIN";// String sql = "START TRANSACTION"; stat = con.createStatement(); stat.executeUpdate(sql); } catch (Exception e) { FXOptionPane.infoBox(e.toString(), "Chyba"); } System.out.println("Import vyr zahájen"); //ZACATEK TRANSAKCE for (i = 0; dbfreader.hasNextRecord(); i++) { Object aobj[] = dbfreader.nextRecord(Charset.forName("windows-1250")); String sql = "INSERT INTO Vyr11(OPERATOR,KOD,ZAKAZKA,NZAK,VYKRES,ZAKPOZN,KODOPER,OPERACE,KODSTROJ,STROJ," + "ZACATEK,TIMEZAC,KONEC,TIMEKON,KS_DOBRY,KS_ZMETEK,DOBA,UKONCENO,KONTROLA,POZNAMKA) values ('" + aobj[0] + "','" + aobj[1] + "','" + aobj[2] + "','" + aobj[3] + "','" + aobj[4]+"','" + aobj[5] + "','"+ aobj[6] + "','" + aobj[7] +"','"+ aobj[8] +"','"+aobj[9] +"','"+ aobj[10]+"','"+ aobj[11]+"','" + aobj[12]+ "','"+ aobj[13] + "','" + aobj[14]+"','"+ aobj[15]+"','"+aobj[16]+"','"+ aobj[17]+"','"+ aobj[18]+"','" + aobj[19] + "')"; //System.out.println(sql); stat = con.createStatement(); stat.executeUpdate(sql); //for (int j = 0; j < aobj.length; j++) { // System.out.print(aobj[j] + " | "); //} //System.out.print("\n"); } System.out.println("Total Count: " + i); try { String sql = "COMMIT"; stat = con.createStatement(); stat.executeUpdate(sql); } catch (Exception e) { FXOptionPane.infoBox(e.toString(), "Chyba"); } //KONEC TRANSAKCE System.out.println("Import dokončen"); renameImport("vyr1"); } public static void renameImport(String jmeno) { // try {// String sql = "BEGIN";// stat = con.createStatement();// stat.executeUpdate(sql);// } catch (Exception e) {// FXOptionPane.infoBox(e.toString(), "Chyba");// } try { con = DBConnect.getConnected(); String sql = "DROP TABLE IF EXISTS " +jmeno+"old"; System.out.println(sql); stat = con.createStatement(); stat.executeUpdate(sql); System.out.println("Zrušena tabulka "+jmeno+"old"); } catch (Exception e) { e.printStackTrace(); System.out.println("Chyba zrušení tabulky " +jmeno + "old"); } try { con = DBConnect.getConnected(); String sql = "RENAME TABLE " +jmeno +" TO "+ jmeno + "old"; System.out.println(sql); stat = con.createStatement(); stat.executeUpdate(sql); System.out.println("Přejmenována tabulka "+jmeno+" na "+ jmeno + "old"); } catch (Exception e) { e.printStackTrace(); System.out.println("Chyba rename tabulky " + jmeno+" na " +jmeno+ "old"); } try { con = DBConnect.getConnected(); String sql = "RENAME TABLE " +jmeno+"1" +" TO "+ jmeno ; System.out.println(sql); stat = con.createStatement(); stat.executeUpdate(sql); System.out.println("Přejmenována tabulka " + jmeno + "1"+" na "+ jmeno ); } catch (Exception e) { e.printStackTrace(); System.out.println("Chyba rename tabulky " + jmeno + "1"+" na "+ jmeno ); } // try {// String sql = "COMMIT";// stat = con.createStatement();// stat.executeUpdate(sql);// } catch (Exception e) {// FXOptionPane.infoBox(e.toString(), "Chyba");// } } }

marcov:
I see multiple things, but I don't use mysql, so I don't know what "hurts" it.

First, I see the Java code explicitly wrapping the core insert in a transaction, and the pascal code not. So it might commit a transaction for each record. My bet would be that this is the biggy, that somehow doing it in one transaction makes it a bulkinsert.

Second looking up the field by fieldbyname each time is slowing, the java code doesn't do this. But I guess that shouldn't be the problem with these magnitudes.

(usually one saves the index of the parameter, and reuses the index to assign each part of a tuple)

xaver13:
thank you very much, marcov, transaction did the trick, now the speed is the same ;D

marcov:
Cool that I got it right. I'm a bit out of practice with DBs except the most basic hobby level :-)

Navigation

[0] Message Index