Time for insertion Fox DB v.s MySQL DB

Question

0.00/5 (No votes)

See more:

Hello everybody,

I have a C# aplication that inserts data into MySQL server and
I have an application in Fox 9 that inserts the same data with Fox DB engine.

* The time for the c# application is very much longer than for the fox application (2000 seconds) vs (5 seconds)
* The time in the fox aplication is very quick (5 sec)

Why is that so? :confused:
How I can reduce the time to insert the data into MySQL DB.

Features of data file I use:

1) type: csv
2) size: 900 KB
3) number of records: 9000

Thanks in advance!

Posted 20-Dec-10 6:02am

leocode7

Updated 20-Dec-10 7:05am

Manfred Rudolf Bihy

v3

Add a Solution

Comments

Manfred Rudolf Bihy 20-Dec-10 13:05pm

Edited for grammar and spelling.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Manfred Rudolf Bihy · Accepted Answer · 2010-12-20T07:23:00

Solution 2

There are a couple of things you have to consider.
1. The Fox 9 application is probably working with a local DB as opposed to the C# application that communicates with MySQL DB over a TCP connection.

2. Are you getting a new sql connection for each insert operation in your C# application? That would slow down things considerably if you're not using the connection pool.

3. MySql DB might have more work to do than the Fox DB engine: Checking column constraints, index updates, triggers etc.

Which brings us to:

4. Work multi threaded and have say 20 threads in pool. When an item was read from the file get an available worker thread and have it process the DB stuff. When the file reading thread runs out of available worker threads you wait unitl one comes available. When a worker thread has accomplished it work it puts itself back into the queue of available workers. By having more threads doing DB stuff in parallel will get the work done quicker because there is much time wasted by waiting for IO (to and from MySQL server) to be completed.

Regards,

Manfred

Posted 20-Dec-10 7:23am

Manfred Rudolf Bihy

Comments

#realJSOP 20-Dec-10 14:49pm

The connection between the C# app and the database shouldn't have anything to do with it. 2000 seconds (33 minutes) to do something is TOO LONG. I think he may have just made a type and meant to say 20 seconds or something. Insertign a rcord into a MySQL database table shouldn't take 33 minutes (or even 20 seconds for that matter).

leocode7 20-Dec-10 15:32pm

Hello Manfred,

I will modify the my.cnf in mysql server
the variables that I will be modify are:

1) innodb_thread_concurrency=8 to innodb_thread_concurrency=27
2) thread_cache_size=8 to thread_cache_size=27

You would belive that this changes will be increment the threads in the pool???
This changes will make reduce the time to the process the inserts??.

I will make a test. but please tell me if this change, would can improve the time of my c# aplication

Thaks in advance

Leonardo Ayala R.

Manfred Rudolf Bihy 20-Dec-10 15:41pm

@John: I thought he meant the whole batch of 9000 records took 2000 seconds which would amount to a rate of one insertion per 0.22 second. The typo also would make sense. If it were 20 seconds it could have to do with establishing a separate connection for each insert. Without the use of a connection pool this can be quite costly, time wise.

Manfred Rudolf Bihy 20-Dec-10 15:47pm

@Leonardo: I didn't mean the threads in the MySQL server, but rather using multiple threads in your application. Can you please shed some light on what John mentioned? Did the whole batch take 2000 seconds or one insert? Are you creating a new connection for every record in your application and if yes are you using a connection pool. It would be great if you could modifiy your question and post the relevant parts of your C# application that does the insertion into the DB. Thanks!

leocode7 20-Dec-10 17:19pm

Manfred and John:
I make a mistake when I sayed 2000s, that wrong it's 240s. inserting nearly 9000 records
but in Fox 9 take 5 seconds.

thankyou for you explanation, is interesting... Now I will to try: create an auxiliar csv file with the format of the table in my DB(MYSQL) and next Import this csv to MySql from a command in c#.

Pleae give your opinion about that.. and please if you could give more explanations "about fox database engine and mysql engine your diferences, ect..."

Regards,

Leonardo Ayala R.

Manfred Rudolf Bihy 20-Dec-10 17:56pm

So that would be 26ms per insertion. That doesn't seem to bad!
If I'm guessing right from your comment you want to do a bulk import to MySQL now? I would say that sounds promising as it should speed things up.

One more thing:
Another thing you can try is using insert statements with more than one row:
INSERT INTO tableName (col1, col2, Col3) VALUES
("value01","value02","value03"),
("value11","value12","value13"),
("value21","value22","value23"), ...

So you'd collect data for say 50 rows and insert them using one sql insert operation.

leocode7 21-Dec-10 9:30am

Manfred: Thanks a lot!!!
;-)

Manfred Rudolf Bihy 21-Dec-10 9:49am

Did you reach an acceptable solution?
You can vote, if my answer helped you!

#realJSOP · Accepted Answer · 2010-12-20T07:15:00

Without seeing your C# code AND your databaase schema, there's no way we can help you, short of just posting a WAG. Are you using a stored procedure for the MySql database? Are your indexes and keys correctly setup? "Insert"ing a record into MtyySQL is simply appending the record to the end of the affected table, so 2000 seconds seems - well - absurd.

You do know that FoxPro is a dedecated database application, like Access, and that you don't really have to do anything special for it to appear to be speedy. 9000 records ain't no thang, after all...