Import of SQL-Files is very slow

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

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.

Thanks. I will try to reproduce the Problem with public datas an send you the sql-file by mail.

:D

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

Which revision of HeidiSQL? Guess you're using the 4.0 ? In that case you should update to the latest build, should be faster.

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.

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!

> but should be major

eh.. I meant "should be minor" :)

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

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

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.

When I have time, I build it in my Delphi and try to find out

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?

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