Performance Question to Script

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for MySQL
Post Reply
Zero-G.
Posts: 398
Joined: Sat 09 Dec 2006 12:20

Performance Question to Script

Post by Zero-G. » Mon 14 Apr 2008 08:00

Hey

As I told you i an other topic, I generate a script with for/next loops. - The script has now about 30000 entries.

The execution of the script uses up to 10 minutes.

Is there a way to speed it up?

THX

Alexey.mdr
Posts: 729
Joined: Thu 13 Dec 2007 10:24

Post by Alexey.mdr » Mon 14 Apr 2008 11:29

Did you solve the problem described in the previous post?
http://crlab.com/forums/viewtopic.php?t=11890

Could you please describe in few words the program architecture?
Why do you need to make an insert with MySqlScript component?

I think it takes ten minutes because of the heavy network load.
Basically, for each update command MySQL server must issue a confirmation/rejection command.
To speed the application up you can consider:
- INSERT DELAYED ...
This SQL command doesn't require a confirmation for each insert
- INSERT INTO ... VALUES (...), (...), (...),(...), (...)
You can use bulk INSERT, which is a recommended way.
Typically, you need to break such INSERTs into blocks, otherwise you can encounter performance issues, lost connections etc.
- use MySqlLoader component.

Zero-G.
Posts: 398
Joined: Sat 09 Dec 2006 12:20

Post by Zero-G. » Mon 14 Apr 2008 12:39

Hey

The problem, described in the topic:

Code: Select all

http://crlab.com/forums/viewtopic.php?t=11890 
is not solved yet. - Because I have not had time to install the new Server. - But in the evening I will try out and then I will let you know!

The problem of this topic is recommended to the previous problem.

So, I have to load data from given files into the DB. There are all in all 14 files, which I have to encode and add to the DB.
What I tried to do now is, to create Insert Statements through For/Next Loops. - I write this statements into a StringBuilder Class. The files are "encoded" in about 4 or 5 seconds. Then I have in the StringBuilder Class each statement like:
Insert Into Table Field1, Field2, Values ("a", "b");
Insert Into Table Field1, Field2, Values ("a", "c");
...

So, when I understand your tip correct, you ment that I could use the mySQLScript as follows:
Insert Into Table Field1, Field2, Values("a", "b"), ("a", "c")...
This would speed up the Script
OR
With the mySQLLoader (There I would not need any StringBuilder Class):
Do
Loader.SetValue("Field1", "a")
Loader.SetValue("Field2", "b")
Loop

Am I right?
THX

Alexey.mdr
Posts: 729
Joined: Thu 13 Dec 2007 10:24

Post by Alexey.mdr » Mon 14 Apr 2008 14:26

Yes, that's correct.
1)

Code: Select all

create table if not exists loadsw(
id int,
name varchar(20)
);
insert into loadsw values (1,"deo"),(2,"leo"),(3,"feo");
2)

Code: Select all

public void LoadData(MySqlConnection myConnection) 
{ 
  myConnection.Open(); 
  myConnection.Database = "Test"; 
  MySqlLoader loader = new MySqlLoader(); 
  loader.Connection = myConnection; 
  loader.TableName = "load_table"; 
  try 
  { 
    loader.CreateColumns(); 
    loader.Open(); 
    for (int i = 1; i <= 10000; i++) 
    { 
      loader.SetValue("id", i); 
      loader.SetValue(1, "test string"); 
      loader.SetValue("date_field", DateTime.Now); 
      loader.NextRow(); 
    } 
    loader.Close(); 
  } 
  finally 
  { 
    myConnection.Close(); 
  } 
}

Zero-G.
Posts: 398
Joined: Sat 09 Dec 2006 12:20

Post by Zero-G. » Tue 15 Apr 2008 10:51

Hey

OK - I do have some problems at all...

So, I can't use Insert Delayed, because my tables are type of InnoDB.
What I tried to do, was this code:

Code: Select all

create table if not exists loadsw( 
id int, 
name varchar(20) 
); 
insert into loadsw values (1,"deo"),(2,"leo"),(3,"feo");
By executing the command with my correct values, I alway get an LostConnection error.

How is it possible to make a transaction to the loader, by using up to 14 different For/Next Loops?

Can I change the tablename? - Like

Code: Select all

Dim Load As New CoreLab.MySQL.MySQLLoader
Load.TableName = "Test1"
Load.SetValue("a", "1")
Load.TableName = "Test2"
Load.SetValue("b", "1")

Alexey.mdr
Posts: 729
Joined: Thu 13 Dec 2007 10:24

Post by Alexey.mdr » Tue 15 Apr 2008 11:50

Alexey.mdr wrote: Typically, you need to break such INSERTs into blocks, otherwise you can encounter performance issues, lost connections etc.
If the problem doesn't occur instantly you need to break
the whole INSERT command into several smaller blocks.
See the pseudo code:

Code: Select all

INSERT INTO t VALUES (...)(...),(...) ................................. (...);
->
INSERT INTO t VALUES (...)(...)(...);
INSERT INTO t VALUES (...)(...)(...);
INSERT INTO t VALUES (...)(...)(...);
Otherwise you have to configure timeout properties of MySqlConnection and MySQL server.

Zero-G.
Posts: 398
Joined: Sat 09 Dec 2006 12:20

Post by Zero-G. » Tue 15 Apr 2008 12:18

Hey again
So, I tried the Loader now. - But there I get the following exception:
CoreLab.MySql.MySqlException was unhandled by user code
ErrorCode=-2147467259
Message="Table storage engine for 'iprocombination' doesn't have this option"
Source="CoreLab.MySql"
StackTrace:
bei CoreLab.MySql.a9.n()
bei CoreLab.MySql.a9.c()
bei CoreLab.MySql.b.a(e[]& A_0, Int32& A_1)
bei CoreLab.MySql.b.a(Byte[] A_0, Int32 A_1, Boolean A_2)
bei CoreLab.MySql.MySqlLoader.c()
bei CoreLab.MySql.MySqlLoader.NextRow()
bei Ipro_v6.Form1.bWorker_DoWork(Object sender, DoWorkEventArgs e) in C:\Users\Intelli Soft\Desktop\VS2005\IPRO\Ipro v6\Ipro v6\Form1.vb:Zeile 315.
bei System.ComponentModel.BackgroundWorker.OnDoWork(DoWorkEventArgs e)
bei System.ComponentModel.BackgroundWorker.WorkerThreadStart(Object argument)

I tried to call the Loader.NextRow() - There I get the error

What am I doing wrong? - Or is the loader not working with InnoDB Storages?

Zero-G.
Posts: 398
Joined: Sat 09 Dec 2006 12:20

Post by Zero-G. » Tue 15 Apr 2008 19:47

I do have a really big problem with the speed

So, I have changed the tables from InnoDB to MyIsam to check, how fast Insert Delayed statements are.
With Insert Delayed the update sequence of the 30000 rows uses about 30-40 seconds. - This is a very nice speed.

But, I can't use MyIsam Tables in real. - Because I have to make this insert statements transaction safe.

So please, tell me what will be the fastest way with InnoDB - Or with another transaction safe engine

THX

Alexey.mdr
Posts: 729
Joined: Thu 13 Dec 2007 10:24

Post by Alexey.mdr » Wed 16 Apr 2008 08:01

I suggest using bulk inserts:

Code: Select all

create table if not exists loadsw(
id int,
name varchar(20)
)ENGINE = INNODB;

insert into loadsw values (1,"deo"),(2,"leo"),(3,"feo");
Do not insert the whole amount of 30000 rows in one INSERT command.
Split the load command into blocks, for example 1000 rows per insert.
Otherwise the server or client may throw an exception like “Connection was lost during the execution...”.

Zero-G.
Posts: 398
Joined: Sat 09 Dec 2006 12:20

Post by Zero-G. » Wed 16 Apr 2008 08:08

Hey

OK... So I am going to try this out. - It will be a little bit tricky to program in an For/Next Loop, but I will let you know.

THX

Zero-G.
Posts: 398
Joined: Sat 09 Dec 2006 12:20

Post by Zero-G. » Wed 16 Apr 2008 15:14

So, now I changed my code to get blocks of 500 Statements / Insert

This was a really good information

THANK YOU

PS: Have you seen the error, I got with MySQLLoader 6posts above?

Alexey.mdr
Posts: 729
Joined: Thu 13 Dec 2007 10:24

Post by Alexey.mdr » Thu 17 Apr 2008 09:21

We are pleased to hear that the information was helpful.
Additionally, you can try varying the insert block size to find out the best point for size/performance.
As to the MySqlLoader exception,
please send me (alexeyman*crlab*com) the project to reproduce the problem.
We'll investigate the problem.

Post Reply