How can I speed this up

**wazo** · Oct 27, 2009 06:53

Hi guys

This script is importing slowly is there any way I can speed it up

PHP Code:


<?



if(isset($_POST['submit'])) {













$filename=$_POST['filename'];

     

// Where the file is going to be placed 

$target_path = "files/$filename";











//////////////////////////////////////////////////////////////////////////////



$fd = fopen ("$target_path", "r");

$handle = fopen ("$target_path", "r");

$counter = 0;

 

while ( ! feof ( $fd ) )

{     

     if ( $counter === 1 )

         break;

       $buffer = fgetcsv ( $fd, 500 ); 

       

$a1="".mysql_real_escape_string($buffer[0])."";

$a2="".mysql_real_escape_string($buffer[1])."";

$a3="".mysql_real_escape_string($buffer[2])."";

$a4="".mysql_real_escape_string($buffer[3])."";

$a5="".mysql_real_escape_string($buffer[4])."";

$a6="".mysql_real_escape_string($buffer[5])."";

$a7="".mysql_real_escape_string($buffer[6])."";

$a8="".mysql_real_escape_string($buffer[7])."";

$a9="".mysql_real_escape_string($buffer[8])."";

$a10="".mysql_real_escape_string($buffer[9])."";

$a11="".mysql_real_escape_string($buffer[10])."";

$a12="".mysql_real_escape_string($buffer[11])."";

$a13="".mysql_real_escape_string($buffer[12])."";

$a14="".mysql_real_escape_string($buffer[13])."";

$a15="".mysql_real_escape_string($buffer[14])."";

$a16="".mysql_real_escape_string($buffer[15])."";





++$counter;

 }

  

 fclose ($fd); 

//////////////////////////////////////////////////////////////////////////



if($a1=='merchant_id'){$Retailer_ID="0";}        

elseif($a1=='aw_product_id'){$Product_ID="0";}        

elseif($a1=='product_name'){$Product_Name="0";}        

elseif($a1=='description'){$Product_Description="0";}        

elseif($a1=='category_name'){$Product_Category="0";}    

elseif($a1=='aw_deep_link'){$Link="0";}        

elseif($a1=='aw_image_url'){$Product_Image_1="0";}        

elseif($a1=='search_price'){$Price="0";}        

elseif($a1=='merchant_image_url'){$Product_Image_1="0";}    



if($a2=='merchant_id'){$Retailer_ID="1";}        

elseif($a2=='aw_product_id'){$Product_ID="1";}        

elseif($a2=='product_name'){$Product_Name="1";}        

elseif($a2=='description'){$Product_Description="1";}        

elseif($a2=='category_name'){$Product_Category="1";}    

elseif($a2=='aw_deep_link'){$Link="1";}        

elseif($a2=='aw_image_url'){$Product_Image_2="1";}        

elseif($a2=='search_price'){$Price="1";}        

elseif($a2=='merchant_image_url'){$Product_Image_1="1";}    



if($a3=='merchant_id'){$Retailer_ID="2";}        

elseif($a3=='aw_product_id'){$Product_ID="2";}        

elseif($a3=='product_name'){$Product_Name="2";}        

elseif($a3=='description'){$Product_Description="2";}        

elseif($a3=='category_name'){$Product_Category="2";}    

elseif($a3=='aw_deep_link'){$Link="2";}        

elseif($a3=='aw_image_url'){$Product_Image_3="2";}        

elseif($a3=='search_price'){$Price="2";}        

elseif($a3=='merchant_image_url'){$Product_Image_1="2";}    



if($a4=='merchant_id'){$Retailer_ID="3";}        

elseif($a4=='aw_product_id'){$Product_ID="3";}        

elseif($a4=='product_name'){$Product_Name="3";}        

elseif($a4=='description'){$Product_Description="3";}        

elseif($a4=='category_name'){$Product_Category="3";}    

elseif($a4=='aw_deep_link'){$Link="3";}        

elseif($a4=='aw_image_url'){$Product_Image_4="3";}        

elseif($a4=='search_price'){$Price="3";}        

elseif($a4=='merchant_image_url'){$Product_Image_1="3";}    



if($a5=='merchant_id'){$Retailer_ID="4";}        

elseif($a5=='aw_product_id'){$Product_ID="4";}        

elseif($a5=='product_name'){$Product_Name="4";}        

elseif($a5=='description'){$Product_Description="4";}        

elseif($a5=='category_name'){$Product_Category="4";}    

elseif($a5=='aw_deep_link'){$Link="4";}        

elseif($a5=='aw_image_url'){$Product_Image_5="4";}        

elseif($a5=='search_price'){$Price="4";}        

elseif($a5=='merchant_image_url'){$Product_Image_1="4";}    



if($a6=='merchant_id'){$Retailer_ID="5";}        

elseif($a6=='aw_product_id'){$Product_ID="5";}        

elseif($a6=='product_name'){$Product_Name="5";}        

elseif($a6=='description'){$Product_Description="5";}        

elseif($a6=='category_name'){$Product_Category="5";}    

elseif($a6=='aw_deep_link'){$Link="5";}        

elseif($a6=='aw_image_url'){$Product_Image_6="5";}        

elseif($a6=='search_price'){$Price="5";}        

elseif($a6=='merchant_image_url'){$Product_Image_1="5";}    





if($a7=='merchant_id'){$Retailer_ID="6";}        

elseif($a7=='aw_product_id'){$Product_ID="6";}        

elseif($a7=='product_name'){$Product_Name="6";}        

elseif($a7=='description'){$Product_Description="6";}        

elseif($a7=='category_name'){$Product_Category="6";}    

elseif($a7=='aw_deep_link'){$Link="6";}        

elseif($a7=='aw_image_url'){$Product_Image_7="6";}        

elseif($a7=='search_price'){$Price="6";}        

elseif($a7=='merchant_image_url'){$Product_Image_1="6";}    





if($a8=='merchant_id'){$Retailer_ID="7";}        

elseif($a8=='aw_product_id'){$Product_ID="7";}        

elseif($a8=='product_name'){$Product_Name="7";}        

elseif($a8=='description'){$Product_Description="7";}        

elseif($a8=='category_name'){$Product_Category="7";}    

elseif($a8=='aw_deep_link'){$Link="7";}        

elseif($a8=='aw_image_url'){$Product_Image_8="7";}        

elseif($a8=='search_price'){$Price="7";}        

elseif($a8=='merchant_image_url'){$Product_Image_1="7";}    



if($a9=='merchant_id'){$Retailer_ID="8";}        

elseif($a9=='aw_product_id'){$Product_ID="8";}        

elseif($a9=='product_name'){$Product_Name="8";}        

elseif($a9=='description'){$Product_Description="8";}        

elseif($a9=='category_name'){$Product_Category="8";}    

elseif($a9=='aw_deep_link'){$Link="8";}        

elseif($a9=='aw_image_url'){$Product_Image_9="8";}        

elseif($a9=='search_price'){$Price="8";}        

elseif($a9=='merchant_image_url'){$Product_Image_1="8";}    





if($a10=='merchant_id'){$Retailer_ID="9";}        

elseif($a10=='aw_product_id'){$Product_ID="9";}        

elseif($a10=='product_name'){$Product_Name="9";}        

elseif($a10=='description'){$Product_Description="9";}        

elseif($a10=='category_name'){$Product_Category="9";}    

elseif($a10=='aw_deep_link'){$Link="9";}        

elseif($a10=='aw_image_url'){$Product_Image_10="9";}        

elseif($a10=='search_price'){$Price="9";}        

elseif($a10=='merchant_image_url'){$Product_Image_1="9";}    



if($a11=='merchant_id'){$Retailer_ID="10";}        

elseif($a11=='aw_product_id'){$Product_ID="10";}        

elseif($a11=='product_name'){$Product_Name="10";}        

elseif($a11=='description'){$Product_Description="10";}        

elseif($a11=='category_name'){$Product_Category="10";}    

elseif($a11=='aw_deep_link'){$Link="10";}        

elseif($a11=='aw_image_url'){$Product_Image_11="10";}        

elseif($a11=='search_price'){$Price="10";}        

elseif($a11=='merchant_image_url'){$Product_Image_1="10";}    



if($a12=='merchant_id'){$Retailer_ID="11";}        

elseif($a12=='aw_product_id'){$Product_ID="11";}        

elseif($a12=='product_name'){$Product_Name="11";}        

elseif($a12=='description'){$Product_Description="11";}        

elseif($a12=='category_name'){$Product_Category="11";}    

elseif($a12=='aw_deep_link'){$Link="11";}        

elseif($a12=='aw_image_url'){$Product_Image_12="11";}        

elseif($a12=='search_price'){$Price="11";}        

elseif($a12=='merchant_image_url'){$Product_Image_1="11";}    



if($a13=='merchant_id'){$Retailer_ID="12";}        

elseif($a13=='aw_product_id'){$Product_ID="12";}        

elseif($a13=='product_name'){$Product_Name="12";}        

elseif($a13=='description'){$Product_Description="12";}        

elseif($a13=='category_name'){$Product_Category="12";}    

elseif($a13=='aw_deep_link'){$Link="12";}        

elseif($a13=='aw_image_url'){$Product_Image_13="12";}        

elseif($a13=='search_price'){$Price="12";}        

elseif($a13=='merchant_image_url'){$Product_Image_1="12";}    



if($a14=='merchant_id'){$Retailer_ID="13";}        

elseif($a14=='aw_product_id'){$Product_ID="13";}        

elseif($a14=='product_name'){$Product_Name="13";}        

elseif($a14=='description'){$Product_Description="13";}        

elseif($a14=='category_name'){$Product_Category="13";}    

elseif($a14=='aw_deep_link'){$Link="13";}        

elseif($a14=='aw_image_url'){$Product_Image_14="13";}        

elseif($a14=='search_price'){$Price="13";}        

elseif($a14=='merchant_image_url'){$Product_Image_1="13";}    



if($a15=='merchant_id'){$Retailer_ID="14";}        

elseif($a15=='aw_product_id'){$Product_ID="14";}        

elseif($a15=='product_name'){$Product_Name="14";}        

elseif($a15=='description'){$Product_Description="14";}        

elseif($a15=='category_name'){$Product_Category="14";}    

elseif($a15=='aw_deep_link'){$Link="14";}        

elseif($a15=='aw_image_url'){$Product_Image_15="14";}        

elseif($a15=='search_price'){$Price="14";}        

elseif($a15=='merchant_image_url'){$Product_Image_1="14";}    





if($a16=='merchant_id'){$Retailer_ID="15";}        

elseif($a16=='aw_product_id'){$Product_ID="15";}        

elseif($a16=='product_name'){$Product_Name="15";}        

elseif($a16=='description'){$Product_Description="15";}        

elseif($a16=='category_name'){$Product_Category="15";}    

elseif($a16=='aw_deep_link'){$Link="15";}        

elseif($a16=='aw_image_url'){$Product_Image_16="15";}        

elseif($a16=='search_price'){$Price="15";}        

elseif($a16=='merchant_image_url'){$Product_Image_1="15";}    

























$deletesql = "DELETE FROM Products WHERE Number = '$filename'"; 





if (!mysql_query($deletesql)) { die('Error: ' . mysql_error()); }



while(($data = fgetcsv($handle, 200, ",")) !== FALSE) {





    

    // Skip first row

    if($num > 0){





$id="".mysql_real_escape_string($data[2])."";









$insertsql = "INSERT INTO Products



    (Product_ID, Product_Name, Retailer_ID,Product_Manufacturer, Product_Category, Price, Product_Description, Product_Image_1, Link,Number)



VALUES



    ('".mysql_real_escape_string($data[$Product_ID])."','".mysql_real_escape_string($data[$Product_Name])."',

  '".mysql_real_escape_string($data[$Retailer_ID])."','".mysql_real_escape_string($data[$Product_Manufacturer])."'

  ,'".mysql_real_escape_string($data[$Product_Category])."','".mysql_real_escape_string($data[$Price])."'

  ,'".mysql_real_escape_string($data[$Product_Description])."',

  '".mysql_real_escape_string($data[$Product_Image_1])."','".mysql_real_escape_string($data[$Link])."','$filename')";





if (!mysql_query($insertsql)) { die('Error: ' . mysql_error()); }

        }

        $num++;

}



fclose($handle);





















} 





 

?>

Thanks in advance

**Dan Grossman** · Oct 27, 2009 17:43

You can batch the INSERT queries instead of issuing them one at a time. A single INSERT query can have many sets of VALUES() lists corresponding to many rows to insert. Single INSERTs with many sets of VALUES() will run faster than one INSERT query per row.

**wazo** · Oct 28, 2009 04:15

Thank you Dan

How would I go about doing this?

**r937** · Oct 28, 2009 07:08

change this --

Code:

INSERT INTO foo (fap,qux) VALUES ( 'one','two' );
INSERT INTO foo (fap,qux) VALUES ( 'frick','frack' );
INSERT INTO foo (fap,qux) VALUES ( 'up','down' );
INSERT INTO foo (fap,qux) VALUES ( 'in','out' );

to this --

Code:

INSERT INTO foo (fap,qux) VALUES 
 ( 'one','two' )
, ( 'frick','frack' )
, ( 'up','down' )
, ( 'in','out' );

single INSERT is more efficient than multiple

User Tag List

Thread: How can I speed this up

Thread Tools

Display

How can I speed this up

Bookmarks

Bookmarks

Posting Permissions