Fast Query, Connection To Database.

[tovirio]

[sOLVED]

I have a big problem, i have to insert lots of values into a table when mymodules is installed.

The prooblem is that this inserction take too much time (6/7 minutes).
 

I use this command:

$directory = dirname(__FILE__).'/taxonomy_en.txt';
$file = fopen($directory, 'r');
$i=0;
while(! feof($file))
{

$a = fgets($file);
Db::getInstance()->execute('
INSERT INTO ps_GoogleTaxonomy (`id_category`, `name`)
VALUES ( '.$i.' , \' '.$a.' \') ');
$i++;
}
fclose($file);

 

i think the problem is that the prestashop function execute() each time has to connect to database and so it takes lots of time.

Probably i have to establish just one time the connection insert into file my values and than close the connection.

Any ideas how to do it?

Edited December 14, 2015 by tovirio (see edit history)

[gytske]

Hello,

 

this is a very common rookie mistake: you should never to any queries inside a loop. If you have N lines, then you will execute N statements. Your goal is to execute as little statements as possible. Your options for fixing this are:

 

- Build one big INSERT statement (You can insert more that one row at once, google SQL BATCH INSERT).

- Build batch INSERT statements, but in chunks: insert 100 rows in one statment, then build another statement for 100 rows, then so on. I wouldn't recommend inserting anything more than a 1000 rows at once.

- Open SQL transaction mode. When you open SQL transacton mode, the queries will be much, much faster. I can't get into details right now why it's so fast, but it's very useful for batch operations. Transaction are not really supported by PrestaShop, but you could technically open it if you know your way around PS.

- Since you're loading rows from a file, you may want to look at  LOAD DATA INFILE  SQL statement. It would be by far the fastest way to insert your. I can predict that it would probably take 1-2secsonds instead of 7mins. It works well when you have exact table values ready go inside your file rows.

 

Cheers

[tovirio]

I've already tried with command LOAD DATA INFILE but I thought that prestashop doesn't support it because it returns me this error:

 

DB::getInstance()->execute('LOAD DATA INFILE \' '.dirname(__FILE__).'/taxonomy_en.txt  \' INTO TABLE ps_salesoar
FIELDS TERMINATED BY \',\'
LINES TERMINATED BY \'\r\n\'
IGNORE 1 LINES');

Fatal error: Uncaught ccess denied for user 'prestashopuser'@'localhost' (using password: YES)

 

that's why i used a loop to insert values.
I' can't build one big INSERT statement because are more than 10000 lines and so it will be quite long.

The best option it would be using the LOAD DATA INFILE. It's possible to use it without get FATAL ERROR?

 

 

 

[gytske]

This error is not a PrestaShop error, it's an error from MySQL. This means that you should be looking to fix the permission for the user inside MySQL/phpMyAdmin.

 

As for the insert statement, you can split it into chunks (lets say 500 per statement), that's what I've written in the second option. Make a `for` cycle and use a conditional: `if ($i %500)`, then take the 500 rows and build one big INSERT query. 10000 : 500 = 20 big INSERT queries.

[tovirio]

I'm sorry to ask again, but after resolved mysql error now i have this error :

Uncaught File '/var/www/html/prestashop/modules/mymodules/taxonomy_en.txt' not found (Errcode: 13 - Permission denied)

<br /><br /><pre>LOAD DATA INFILE '/var/www/html/prestashop/modules/salesoar/taxonomy_en.txt' INTO TABLE ps_GoogleTaxonomy FIELDS TERMINATED BY '-' LINES TERMINATED BY '\n' IGNORE 1 LINES</pre> thrown in /var/www/html/prestashop/classes/db/Db.php on line 635

 

I tried to give al permission to file chmod 777 but error remains.

Have any solutions ?

 

 

[gytske]

@tovirio I think I know what this is, `mysql` program can't read the file, because the (default) settings of mysql don't allow it (for security reasons). I think you have to specify the filepath/folder where mysql is able to read files. I can't recall the specific error, just google `load data infile permissions`