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

Import of SQL-Files is very slow

Performance posted 7 years ago in Import/Export
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 posted 7 years ago
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.
Performance posted 7 years ago
Thanks. I will try to reproduce the Problem with public datas an send you the sql-file by mail.

:D
AliG posted 5 years ago
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 posted 5 years ago
Which revision of HeidiSQL? Guess you're using the 4.0 ? In that case you should update to the latest build, should be faster.

AliG posted 5 years ago
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 posted 5 years ago
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 posted 5 years ago
> but should be major

eh.. I meant "should be minor" :)
AliG posted 5 years ago
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
AliG posted 5 years ago
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 posted 5 years ago
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.
AliG posted 5 years ago
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.