MySQLDump restore takes too long - Read for tips on reducing time

MySQLDump restore takes too long - Read for tips on reducing time

Postby newyuppie » Mon 10 Jul 2006 16:04

hi,

im issuing a backup and restore dumps on a database, and timing the results.
the database has 3 tables, of which only 1 has 50 records or so. the other 2 tables are empty and have only the schematics.

the bakup dump takes less than 1 second, but the restore dump takes over 10s.

my question is: is there any way to improve time on restore dump or is it a problem with the algorithm? should i use MySQLScript to call the dump directly on MySQL server?

thanks

ny
Last edited by newyuppie on Sun 17 Sep 2006 08:08, edited 1 time in total.
newyuppie
 
Posts: 13
Joined: Fri 07 Jul 2006 05:48

update

Postby newyuppie » Mon 10 Jul 2006 16:20

an update on the situation:

i have set the UseExtSyntax to TRUE and now have dropped the time from +10s to approx 2s (sometimes even 1.5s). amazing! is there a downside to this?

are there any more tricks like this one to improve speed?

thanks

ny
newyuppie
 
Posts: 13
Joined: Fri 07 Jul 2006 05:48

Postby Alexey » Tue 11 Jul 2006 09:31

There are two more "tricks": Unicode and Compress properties of MySqlConnection. Setting Unicode to true reduces speed, while setting Compress to true may increase it.
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

INCREDIBLE TRICKS TO REDUCE TIME!

Postby newyuppie » Sun 17 Sep 2006 08:06

i have just recently tried out the additional trick you advised me of turning Unicode property to True for MySqlConnection.

I have benchmarked (i am not PCLabs... but anyway) the True vs False setting of this property and i present some preliminary results tested on my computer only.

Unicode setting to TRUE vs FALSE times elapsed, divided by various tasks:

- Issue a MySqlConnection Open statement ==> 10650% improvement
- Issue a MyScript script to create database ==> 165% improvement
- Issue a MyScript script creating stored procedure ==> 600% improvmt
- Issue a MySqlDump.Restore statement (coupled with its UseExtSyntax) ==> 15% increase in time
- Issue a MySqlDump.Backup statement (coupled with its UseExtSyntax) ==> 71% improvement

amazing no??

there HAS to be a downside to the Unicode thing. anybody can think of a reason why you shouldnt turn it on, or even let the user decide? (just leave it on for default forever)
newyuppie
 
Posts: 13
Joined: Fri 07 Jul 2006 05:48

Postby Alexey » Wed 20 Sep 2006 08:40

Influence of Unicode=true might become obvious only while fetching big amounts of data.
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43


Return to dotConnect for MySQL