distal-attribute
distal-attribute
distal-attribute
distal-attribute

Import of SQL-Files is very slow

User, date Message
Written by Performance
7 years ago
Category: Import/Export
2 posts since Tue, 11 Dec 07
Hallo,

there is a performance problem: I use heidisql in the latest version, mysql 5 and windows vista. Now i have a *.sql file with 60 tabes and 25000 datasets i want to import. The import is very slow so round about 10 datasets per second. Are there any options to get a better performance? I tried to switch off the firewall and the virus-scanner, but it was not successful :-(

thanks for your help
Written by ansgar
7 years ago
4973 posts since Fri, 07 Apr 06
Normally the latest versions should be really fast while exporting to SQL files. But there are so many circumstances which can lead to different, weird effects. So, we won't be able to reproduce this until we have a dump of these tables and surely the data. I know this could be highly private data or even more sensible. It's your decision. You could send it to me via mail, zipped (see the mail-link on the footer on the "Home" page). If there is private data in it, I will certainly handle it carefully.
Written by Performance
7 years ago
2 posts since Tue, 11 Dec 07
Thanks. I will try to reproduce the Problem with public datas an send you the sql-file by mail.

:D
Written by AliG
5 years ago
20 posts since Tue, 12 Jan 10
Hi,
I know this is 2 years old issue ... but I think this hasn't been solved :(

Table

CREATE TABLE `db` (
`ip` TINYTEXT NOT NULL,
`date` DATE NOT NULL,
`text1` TEXT NOT NULL,
`text2` TEXT NULL,
`hash` VARCHAR(32) NULL DEFAULT NULL,
UNIQUE INDEX `hash` (`hash`)
)
ENGINE=MyISAM
ROW_FORMAT=DEFAULT

and inserting 100 000 lines like this
INSERT IGNORE INTO `db` VALUES('111.111.111.111', '2010-01-01', 'wwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwww wwwwwwwwwwwwwwwwwwwwwww', 'wwwwwwwwwwwwwwwwwwwww wwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwww', '11111111111111111111111111111111');

And now comparation
HeidiSQL ... 5000 lines 2 minutes
phpMyAdmin ... 100 000 lines 1 minute
Written by ansgar
5 years ago
4973 posts since Fri, 07 Apr 06
Which revision of HeidiSQL? Guess you're using the 4.0 ? In that case you should update to the latest build, should be faster.
Written by AliG
5 years ago
20 posts since Tue, 12 Jan 10
I have just tried the latest build ;)
25 000 lines, 4 minutes ...

I think that your app just updates everything too much - last query, log ... for every line. Maybe you should update it after every 100 lines.
Written by ansgar
5 years ago
4973 posts since Fri, 07 Apr 06
Yes, that may be one reason for slowness but should be major. Reducing output won't make it significantly faster.

HeidiSQL executes a mysql_ping() before executing a query, to ensure the connection is alive. Probably that is decreasing also performance.

Anyway. Do you have the chance to recreate that SQL file? If yes, just try it using HeidiSQL (mysqldump is also very ok). The problem with such dumps is that they contain only one single VALUES clause, so they produce 100,000 times the overhead of one larger, extended INSERT, which concatenates many VALUES clauses into one query. You will see that dramatically speeds up reading/executing these dumps from some minutes to some seconds!
Written by ansgar
5 years ago
4973 posts since Fri, 07 Apr 06
> but should be major

eh.. I meant "should be minor" :)
Written by AliG
5 years ago
20 posts since Tue, 12 Jan 10
ok then ;)
I use one line one query system because when I had tried to use extended INSERT, mysql froze.

Thanks for info, I will try and report you results
Written by AliG
5 years ago
20 posts since Tue, 12 Jan 10
Great :)

Now HeidiSQL is frozen for 1 minute, then it reports that 10 000 lines inserted in 0,5sec ;)

Still, you can make it for example optionable to execute the mysql_ping. I know that it is possible that the server can shutdown suddenly - many people will take the risk :D

Thanks
Written by ansgar
5 years ago
4973 posts since Fri, 07 Apr 06
Probably, if mysql_ping() is the cause of slowness alone - that was just a guess from me. If there are more causes that tends to get difficult. But who knows.
Written by AliG
5 years ago
20 posts since Tue, 12 Jan 10
When I have time, I build it in my Delphi and try to find out smile
 

Please login to leave a reply, or register at first.