Performance Question to Script
Performance Question to Script
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
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
-
- Posts: 729
- Joined: Thu 13 Dec 2007 10:24
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.
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.
Hey
The problem, described in the topic:
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
The problem, described in the topic:
Code: Select all
http://crlab.com/forums/viewtopic.php?t=11890
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
-
- Posts: 729
- Joined: Thu 13 Dec 2007 10:24
Yes, that's correct.
1)
2)
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");
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();
}
}
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:
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
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");
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")
-
- Posts: 729
- Joined: Thu 13 Dec 2007 10:24
If the problem doesn't occur instantly you need to breakAlexey.mdr wrote: Typically, you need to break such INSERTs into blocks, otherwise you can encounter performance issues, lost connections etc.
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 (...)(...)(...);
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?
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?
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
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
-
- Posts: 729
- Joined: Thu 13 Dec 2007 10:24
I suggest using bulk inserts: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...”.
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");
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...”.
-
- Posts: 729
- Joined: Thu 13 Dec 2007 10:24
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.
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.