Import of SQL-Files is very slow

[expired user #3272]'s profile image [expired user #3272] posted 16 years ago in Import/Export Permalink
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
ansgar's profile image ansgar posted 16 years ago Permalink
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.
[expired user #3272]'s profile image [expired user #3272] posted 16 years ago Permalink
Thanks. I will try to reproduce the Problem with public datas an send you the sql-file by mail.

:D
[expired user #4614]'s profile image [expired user #4614] posted 14 years ago Permalink
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
ansgar's profile image ansgar posted 14 years ago Permalink
Which revision of HeidiSQL? Guess you're using the 4.0 ? In that case you should update to the latest build, should be faster.
[expired user #4614]'s profile image [expired user #4614] posted 14 years ago Permalink
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.
ansgar's profile image ansgar posted 14 years ago Permalink
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!
ansgar's profile image ansgar posted 14 years ago Permalink
> but should be major

eh.. I meant "should be minor" :)
[expired user #4614]'s profile image [expired user #4614] posted 14 years ago Permalink
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
[expired user #4614]'s profile image [expired user #4614] posted 14 years ago Permalink
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
ansgar's profile image ansgar posted 14 years ago Permalink
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.
[expired user #4614]'s profile image [expired user #4614] posted 14 years ago Permalink
When I have time, I build it in my Delphi and try to find out smile
yodann's profile image yodann posted 4 years ago Permalink

hi there Ansgar, I just received some splitted sql files and would like to import them to local server. But the process is running so slow when importing.

Can you help me examine the sql file?

ansgar's profile image ansgar posted 4 years ago Permalink

Maybe, you could probably post some lines of it here.

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