Load data infile

Hi Bob,

I'll look into this for you and get back to you...

Cheers,
David.

David,

I've managed to load data infile however there was not any perfomance gain whatsover. As a matter of fact the speed decreased slightly.

I changed the statement to: $sql = sprintf("LOAD DATA INFILE 'filename' INTO table `".$config_databaseTablePrefix."products` fields terminated by ',' optionally enclosed by '\"' lines terminated by '\r\n' (field_1,field_2..etc).

Any idea what is causing this?

Bob

Hello Bob,

This may well be to do with the process taking place against an already populated products table, resulting in numerous dupe_hash index checks. For the fastest performance, would it be possible to try the process against an import ALL feeds only, which will mean applying the LOAD DATA INFILE to an empty products table?

Cheers,
David.

David,

I have tested it against an empty products' table. Still I am not getting through it for some reason. If I use the same $sql statement mentioned above and change '\r\n' to '\n' and test it through phpmyadmin everything works prefect and phpmyadmin would import a feed very fast..in seconds actually..but of course that's no good as I have to pre-process some large feeds before importing..I also dropped the indexes including the primary and unique keys as they are causing LOAD DATA INFILE to slow down to the point of being the same or even worse than INSERT..But still no luck. I am now running out of ideas what might be causing this. I saw it only once importing at a very fast speed while experimenting with the statement but the data imported was just one record that was duplicated into millions of records very quickly...Do you think that there might be something else to try?

Bob

Hello Bob,

You mention loading the data "infile" through phpMyAdmin "in seconds" - did that definitely work as expecte?

If so, could I recommend attempting to load the data from the file via the mysql command line tool (this would require SSH/Telnet access to your server). To do this; SSH into your server, and change directory to the folder containing the data file generated, and then execute the following

(where $ is your command prompt)

Cheers,
David.

David,

Yes I can load data infile that way as well although it was load data "local" rather than load data infile. However fields do not match and there is no much use of such data in a table as far as I can see unless I have found a way to pre-process it and match it with my tables' structure... I use dynamic categories and a few preg_match expressions and strpos for that which are slowing the import a bit but that still should not in my view make load data infile to behave in the same way as insert regarding speed...

Bob

Hello Bob,

An alternative to consider would be that rather than writing separated data to a file; would be to output the SQL statements to that file directly. This should be a straight forward modification to the import record handler in includes/admin.php by writing the $sql variable to a file rather than the comma separated data.

The above mysql command line operation would then be;

Cheers,
David.

David,

I would like to try that alternative. I spent a lot of time trying to make load data infile work but unfortunately I had no luck with it... Could you please give me a hand with modification of the import record handler in includes/admin.php?

Bob

Hello Bob,

Of course - perhaps if you wanted to email me your modified includes/admin.php, and presumably if you're creating the file in conjunction with import.php @ALL then scripts/import.php also and I'll take a look for you...

Cheers,
David.

David,

I am getting some warnings when I try to upload a feed. Other feeds seem to be OK but this one is difficult to see why it's causing troubles. The feed looks OK to me...Any idea about how to debug this further? If I turn the debug option on I still get the same warnings nothing additional...

PHP Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in ..\includes\database.php on line 27

PHP Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in ..\includes\database.php on line 32

line 27 = while($row = mysql_fetch_array($result,MYSQL_ASSOC))

line 32 = return mysql_num_rows($result);

Bob

Hi Bob,

At what point in the register / import process are you seeing the warnings?

Cheers,
David.

David,

I am getting the warnings after the feed has been "imported" aleady...at the end. When I check the database products are missing even though I can see them through phpmyadmin being imported whether through command line or script...

Bob

Hi Bob,

Can you confirm that this isn't anything to do with the writing SQL to a .sql file and importing via SSH...

Could you perhaps email me a link to the installation, any password required for /admin/ and the filename of the feed causing the problem and I'll check it out for you...

Cheers,
David.

David,

It's nothing to do with .sql file. I tried that option but the writing of an .sql file takes the same time as importing directly through the script or command line...It would be just yet another extra step...so no speed gain there whatsover I am afraid. I think it's the parser actually that is limiting the speed of import but I do not know how to tweak the parser at this point unfortunately. It's a shame I think because if I have managed to load it infile through the script then some really powerful possibilities would have been opened to me imo.

This issue is something to do with the feed in question imo. So I am going to try once more to see if I can find out what's going on...I am still working on my testing machine.

Thanks.

Bob

Hi Bob,

If you could just put the feed in question online somewhere and send me a link to it that would be a great help then I can take a look - no need for a Price Tapestry installation - or if it's not too big .zipped and attached in an email of course...

Cheers,
David.