* * *

Author Topic: MySQL slow code, please what is wrong?-SOLVED  (Read 596 times)

xaver13

  • Jr. Member
  • **
  • Posts: 62
MySQL slow code, please what is wrong?-SOLVED
« on: October 15, 2016, 10:35:06 am »
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  [Select]
  1. unit uImportDMySQL;
  2.  
  3. {$mode objfpc}{$H+}
  4.  
  5. interface
  6.  
  7. uses
  8.   Classes, SysUtils, sqldb, dbf, DB, mysql56conn, FileUtil, Forms, Controls,
  9.   Graphics, Dialogs, StdCtrls, IniFiles, LConvEncoding;
  10.  
  11. type
  12.  
  13.   { TFormImport }
  14.  
  15.   TFormImport = class(TForm)
  16.     BtnImportVyr: TButton;
  17.     DataSource1: TDataSource;
  18.     Dbf1: TDbf;
  19.     edtHeslo: TEdit;
  20.     Label1: TLabel;
  21.     lblHlaseni: TLabel;
  22.     MySQLConnection1: TMySQL56Connection;
  23.     Query: TSQLQuery;
  24.     SQLTransaction1: TSQLTransaction;
  25.     procedure BtnImportVyrClick(Sender: TObject);
  26.   private
  27.     { private declarations }
  28.   public
  29.     { public declarations }
  30.   end;
  31.  
  32. const
  33.   C_DB_SECTION = 'DB-INFO';
  34.  
  35.  
  36. var
  37.   FormImport: TFormImport;
  38.   INI: TINIFile;
  39.   DBuser, DBpassword, DBlocation: string;
  40.  
  41. implementation
  42.  
  43. {$R *.lfm}
  44.  
  45. { TFormImport }
  46.  
  47. //F U N K C E
  48. function FieldValue(x: integer): string;
  49. begin
  50.   case FormImport.Dbf1.Fields[x].Value = Null of
  51.     True: Result := '';
  52.     False: Result := FormImport.Dbf1.Fields[x].Value
  53.   end;
  54. end;
  55.  
  56.  
  57. procedure TFormImport.BtnImportVyrClick(Sender: TObject);
  58. begin
  59.   lblHlaseni.caption:='Import in progress';
  60.   Application.ProcessMessages;
  61.   INI := TINIFile.Create('DMySQL.ini');
  62.  
  63.   // Put reading the INI file inside a try/finally block to prevent memory leaks
  64.   try
  65.     // Demonstrates reading values from the INI file.
  66.     DBuser := INI.ReadString(C_DB_SECTION, 'DBuser', '');
  67.     //DBpassword := INI.ReadString(C_DB_SECTION, 'DBpassword', '');
  68.     DBlocation := INI.ReadString(C_DB_SECTION, 'DBlocation', '');
  69.   finally
  70.     // After the ini file was used it must be freed to prevent memory leaks.
  71.     INI.Free;
  72.   end;
  73.  
  74.   DBpassword:=edtHeslo.Caption;
  75.   MySQLConnection1.HostName := DBlocation;
  76.   MySQLConnection1.UserName := DBuser;
  77.   MySQLConnection1.Password := DBpassword;
  78.   MySQLConnection1.Transaction := SQLTransaction1;
  79.   MySQLConnection1.DatabaseName := 'D2';
  80.  
  81.   MySQLConnection1.Open;
  82.  
  83.   if MySQLConnection1.Connected then
  84.   begin
  85.     Query.Database := MySQLConnection1;
  86.     SQLTransaction1.Database := MySQLConnection1;
  87.     Query.ParseSQL := True;
  88.     Query.ReadOnly := False;
  89.     Query.SQL.Text := 'SET CHARACTER SET `utf8`';
  90.     Query.ExecSQL;
  91.     Query.SQL.Text := 'SET NAMES `utf8`';
  92.     Query.ExecSQL;
  93.     Query.SQL.Text :=
  94.       '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)';
  95.     Query.ExecSQL;
  96.     Query.SQL.Text := 'ALTER TABLE vyr1new AUTO_INCREMENT=1;';
  97.     Query.ExecSQL;
  98.     Dbf1.FilePathFull := 'd:\J.C\Visual Foxpro Projects\D2\dta';
  99.     Dbf1.FilePathFull := 'd:\J.C\Visual Foxpro Projects\D2\dta\';
  100.     Dbf1.TableName := 'vyr11.dbf';
  101.     //DataSource1
  102.     Dbf1.Active := True;
  103.     Query.SQL.Text :=
  104.       'insert into vyr1new(ID,KONTROLA,ZAKAZKA,NZAK,VYKRES,KODOPER,OPERACE,KODSTROJ,STROJ,OPERATOR,KOD,'
  105.       + 'ZAKPOZN,DOBA,ZACATEK,TIMEZAC,KONEC,TIMEKON,KS_DOBRY,KS_ZMETEK,UKONCENO,POZNAMKA) '
  106.       + ' values((:ID),(:KONTROLA),(:ZAKAZKA),(:NZAK),(:VYKRES),(:KODOPER),(:OPERACE),(:KODSTROJ),(:STROJ),(:OPERATOR),(:KOD),'
  107.       + '(:ZAKPOZN),(:DOBA),(:ZACATEK),(:TIMEZAC),(:KONEC),(:TIMEKON),(:KS_DOBRY),(:KS_ZMETEK),(:UKONCENO),(:POZNAMKA))';
  108.     //corresponding to foxpro table vyr11.dbf
  109.  
  110.     lblHlaseni.caption:='Import in progress';
  111.     while not Dbf1.EOF do
  112.     begin
  113. //      lblHlaseni.visible:= not(lblHlaseni.visible);
  114. //      lblHlaseni.Paint;
  115.       Query.Params.ParamByName('ID').AsString := '0';
  116.       Query.Params.ParamByName('KONTROLA').AsString := FieldValue(18);
  117.       Query.Params.ParamByName('ZAKAZKA').AsString := FieldValue(2);
  118.       Query.Params.ParamByName('NZAK').AsString := CP1250ToUTF8(FieldValue(3));
  119.       Query.Params.ParamByName('VYKRES').AsString := CP1250ToUTF8(FieldValue(4));
  120.       Query.Params.ParamByName('KODOPER').AsString := FieldValue(6);
  121.       Query.Params.ParamByName('OPERACE').AsString := CP1250ToUTF8(FieldValue(7));
  122.       Query.Params.ParamByName('KODSTROJ').AsString := FieldValue(8);
  123.       Query.Params.ParamByName('STROJ').AsString := CP1250ToUTF8(FieldValue(9));
  124.       Query.Params.ParamByName('OPERATOR').AsString := CP1250ToUTF8(FieldValue(0));
  125.       Query.Params.ParamByName('KOD').AsString := FieldValue(1);
  126.       Query.Params.ParamByName('ZAKPOZN').AsString := CP1250ToUTF8(FieldValue(5));
  127.       Query.Params.ParamByName('DOBA').AsString := FieldValue(16);
  128.       Query.Params.ParamByName('ZACATEK').AsString := FieldValue(10);
  129.       Query.Params.ParamByName('TIMEZAC').AsString := FieldValue(11);
  130.       Query.Params.ParamByName('KONEC').AsString := FieldValue(12);
  131.       Query.Params.ParamByName('TIMEKON').AsString := FieldValue(13);
  132.       Query.Params.ParamByName('KS_DOBRY').AsString := FieldValue(14);
  133.       Query.Params.ParamByName('KS_ZMETEK').AsString := FieldValue(15);
  134.       Query.Params.ParamByName('UKONCENO').AsString := FieldValue(17);
  135.       Query.Params.ParamByName('POZNAMKA').AsString := CP1250ToUTF8(FieldValue(19));
  136.       //fields numbering dbf  begins by zero
  137.       Query.ExecSQL;
  138.       Dbf1.Next;
  139.       //SQLTransaction1.commitretaining;
  140.  
  141.     end;
  142.     lblHlaseni.caption:='Čekám..';
  143.     lblHlaseni.visible := true;
  144.  
  145.     ShowMessage('Výroba byla naimportována.');
  146.  
  147.     //*
  148.     Query.SQL.Text := 'DROP TABLE IF EXISTS VYR1old';
  149.     Query.ExecSQL;
  150.     SQLTransaction1.Active := True;
  151.     Query.SQL.Text := 'RENAME TABLE vyr1  TO vyr1old, vyr1new TO vyr1';
  152.     Query.ExecSQL;
  153.     Query.Close;
  154.     ShowMessage('Soubory přejmenovány.');
  155.   end;
  156.  
  157. end;
  158.  
  159. end.

Java Code
Code: Java  [Select]
  1. /*
  2.  * To change this license header, choose License Headers in Project Properties.
  3.  * To change this template file, choose Tools | Templates
  4.  * and open the template in the editor.
  5.  */
  6. package D2;
  7.  
  8. import com.hexiong.jdbf.DBFReader;
  9. import java.nio.charset.Charset;
  10. import java.sql.Connection;
  11. import java.sql.PreparedStatement;
  12. import java.sql.Statement;
  13.  
  14. /**
  15.  *
  16.  * @author Jiri Cvrk <cvrk@volny.cz>
  17.  */
  18. public class ImportVyr1 {
  19.  
  20.     public static Connection con = null;
  21.     private static Statement stat;
  22.     PreparedStatement pst = null;
  23.  
  24.     public static void ImportVyr1() throws Exception {
  25.         try {
  26.             con = DBConnect.getConnected();
  27.             stat = con.createStatement();
  28.             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,"
  29.                     + " KODOPER  char(6) null,\n"
  30.                     + " OPERACE  varchar(30) null, KODSTROJ char(4) null, STROJ    varchar(30) null, OPERATOR varchar(40) null, KOD char(4) null,\n"
  31.                     + " 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"
  32.                     + " KS_ZMETEK integer null, UKONCENO char(1) null, POZNAMKA varchar(128) null);");
  33.         } catch (Exception e) {
  34.             e.printStackTrace();
  35.             System.out.println("Chyba založení tabulky vyr11");
  36.         }
  37.        
  38.         try {
  39.             con = DBConnect.getConnected();
  40.             stat = con.createStatement();
  41.             stat.executeUpdate("ALTER TABLE vyr11 AUTO_INCREMENT=1;");
  42.         } catch (Exception e) {
  43.             e.printStackTrace();
  44.             System.out.println("Chyba autoinkrementu tabulky vyr11");
  45.         }
  46.         DBFReader dbfreader = new DBFReader(ScreensFramework.D2location+"\\dta\\vyr11.dbf");
  47.         int i;
  48.         for (i = 0; i < dbfreader.getFieldCount(); i++) {
  49.             System.out.print(dbfreader.getField(i).getName() + "  ");
  50.         }
  51.         System.out.print("\n");
  52.  
  53.         try {
  54.            
  55.             String sql = "BEGIN";
  56. //            String sql = "START TRANSACTION";
  57.             stat = con.createStatement();
  58.             stat.executeUpdate(sql);
  59.         } catch (Exception e) {
  60.                         FXOptionPane.infoBox(e.toString(), "Chyba");
  61.         }
  62.         System.out.println("Import vyr zahájen");
  63.  
  64.         //ZACATEK TRANSAKCE
  65.         for (i = 0; dbfreader.hasNextRecord(); i++) {
  66.             Object aobj[] = dbfreader.nextRecord(Charset.forName("windows-1250"));
  67.  
  68.             String sql = "INSERT INTO Vyr11(OPERATOR,KOD,ZAKAZKA,NZAK,VYKRES,ZAKPOZN,KODOPER,OPERACE,KODSTROJ,STROJ,"
  69.                     + "ZACATEK,TIMEZAC,KONEC,TIMEKON,KS_DOBRY,KS_ZMETEK,DOBA,UKONCENO,KONTROLA,POZNAMKA) values ('"
  70.                     + aobj[0] + "','" + aobj[1] + "','" + aobj[2] + "','" + aobj[3] + "','" + aobj[4]+"','" + aobj[5] + "','"+ aobj[6]
  71.                     + "','" + aobj[7] +"','"+ aobj[8] +"','"+aobj[9] +"','"+ aobj[10]+"','"+ aobj[11]+"','" + aobj[12]+ "','"+ aobj[13]
  72.                     + "','" + aobj[14]+"','"+ aobj[15]+"','"+aobj[16]+"','"+ aobj[17]+"','"+ aobj[18]+"','" + aobj[19]
  73.                     + "')";
  74.  
  75.  
  76.  
  77. //System.out.println(sql);
  78.             stat = con.createStatement();
  79.             stat.executeUpdate(sql);
  80.  
  81.             //for (int j = 0; j < aobj.length; j++) {
  82.             //    System.out.print(aobj[j] + "  |  ");
  83.             //}
  84.             //System.out.print("\n");
  85.         }
  86.  
  87.         System.out.println("Total Count: " + i);
  88.         try {
  89.             String sql = "COMMIT";
  90.             stat = con.createStatement();
  91.             stat.executeUpdate(sql);
  92.         } catch (Exception e) {
  93.                         FXOptionPane.infoBox(e.toString(), "Chyba");
  94.         }
  95.         //KONEC TRANSAKCE
  96.         System.out.println("Import dokončen");
  97.  
  98.         renameImport("vyr1");
  99.     }
  100.    
  101.     public static void renameImport(String jmeno)
  102.     {
  103.  
  104. //        try {
  105. //            String sql = "BEGIN";
  106. //            stat = con.createStatement();
  107. //            stat.executeUpdate(sql);
  108. //        } catch (Exception e) {
  109. //                        FXOptionPane.infoBox(e.toString(), "Chyba");
  110. //        }
  111.        
  112.         try {
  113.             con = DBConnect.getConnected();
  114.             String sql = "DROP TABLE IF EXISTS " +jmeno+"old";
  115.             System.out.println(sql);
  116.             stat = con.createStatement();
  117.             stat.executeUpdate(sql);
  118.  
  119.             System.out.println("Zrušena tabulka "+jmeno+"old");
  120.  
  121.          } catch (Exception e) {
  122.             e.printStackTrace();
  123.             System.out.println("Chyba zrušení tabulky "  +jmeno + "old");
  124.         }
  125.  
  126.         try {
  127.             con = DBConnect.getConnected();
  128.             String sql = "RENAME TABLE  " +jmeno +" TO "+ jmeno + "old";
  129.             System.out.println(sql);
  130.             stat = con.createStatement();
  131.             stat.executeUpdate(sql);
  132.  
  133.             System.out.println("Přejmenována tabulka "+jmeno+" na "+ jmeno + "old");
  134.         } catch (Exception e) {
  135.             e.printStackTrace();
  136.             System.out.println("Chyba rename tabulky " + jmeno+" na " +jmeno+ "old");
  137.         }
  138.  
  139.        
  140.         try {
  141.             con = DBConnect.getConnected();
  142.             String sql = "RENAME TABLE  " +jmeno+"1" +" TO "+ jmeno ;
  143.             System.out.println(sql);
  144.             stat = con.createStatement();
  145.             stat.executeUpdate(sql);
  146.  
  147.             System.out.println("Přejmenována tabulka " + jmeno + "1"+" na "+ jmeno );
  148.  
  149.         } catch (Exception e) {
  150.             e.printStackTrace();
  151.             System.out.println("Chyba rename tabulky " + jmeno + "1"+" na "+ jmeno );
  152.         }
  153.  
  154. //        try {
  155. //            String sql = "COMMIT";
  156. //            stat = con.createStatement();
  157. //            stat.executeUpdate(sql);
  158. //        } catch (Exception e) {
  159. //            FXOptionPane.infoBox(e.toString(), "Chyba");
  160. //        }
  161.  
  162.     }
  163.  
  164. }
  165.  
« Last Edit: October 15, 2016, 05:04:04 pm by xaver13 »
--
Jiri Cvrk, newbie

marcov

  • Global Moderator
  • Hero Member
  • *****
  • Posts: 5176
Re: MySQL slow code, please what is wrong?
« Reply #1 on: October 15, 2016, 03:11:41 pm »
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

  • Jr. Member
  • **
  • Posts: 62
Re: MySQL slow code, please what is wrong?
« Reply #2 on: October 15, 2016, 04:25:31 pm »
thank you very much, marcov, transaction did the trick, now the speed is the same  ;D
--
Jiri Cvrk, newbie

marcov

  • Global Moderator
  • Hero Member
  • *****
  • Posts: 5176
Re: MySQL slow code, please what is wrong?
« Reply #3 on: October 15, 2016, 04:35:43 pm »
Cool that I got it right. I'm a bit out of practice with DBs except the most basic hobby level :-)

 

Recent

Get Lazarus at SourceForge.net. Fast, secure and Free Open Source software downloads Open Hub project report for Lazarus