Forum > Database

MySQL slow code, please what is wrong?-SOLVED

(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

Go to full version