Insertion query is running very slow

[habib009pk]

Dear Friends,

 

From last few days I am in very big problem. I am the developer of kansai group and my website is kansaigroup.jp.

I am running a site in which I am displaying cars data on my site. I am getting this data from russians company from last two years in xml format.

 

I was getting more then 80 thousands records daily but now the process has become very slow and it comes on just 12~13 records. I will give you the code of insert query in the end.

 

I disscussed with russian company the data fetching on russian sever is perfect so there is no problem in data fetching the problem is in just insert query. The other reason is that the query is running perfectly on my local systerm Insertion and fetching is well on my local maching so there is just problem in on live server.

 

Query is :

while ($xml) {
$sql = '?sql='.urlencode("select l.* FROM auct_lots_full l WHERE 1=1 order by lot_date asc limit ".$i.",1000");
 $xml = @file_get_contents($url);
 $i += 1000;
 if ($is_gzip) $xml = @gzuncompress($xml); 
 if ($xml)
$xml2 = simplexml_load_string($xml);
$loopend = count($xml2);
//insert Query;
for($a=0;$a<$loopend;$a++)
                {
                if (!isset($xml2->item[$a])) continue; // it's new line
$lot_id=addslashes($xml2->item[$a]->lot_id);
$lot_date=addslashes($xml2->item[$a]->lot_date);
$bid=addslashes($xml2->item[$a]->bid);
$company=addslashes($xml2->item[$a]->company);
$model_year_en=addslashes($xml2->item[$a]->model_year_en);
$model_name_en=addslashes($xml2->item[$a]->model_name_en);
$model_detail=addslashes($xml2->item[$a]->model_detail);
$grade_en=addslashes($xml2->item[$a]->grade_en);
$model_type_en=addslashes($xml2->item[$a]->model_type_en);
$mileage_en=addslashes($xml2->item[$a]->mileage_en);
$mileage_num=addslashes($xml2->item[$a]->mileage_num);
$inspection_en=addslashes($xml2->item[$a]->inspection_en);
$equipment_en=addslashes($xml2->item[$a]->equipment_en);
$transmission_en=addslashes($xml2->item[$a]->transmission_en);
$awd=addslashes($xml2->item[$a]->awd);
$left_hd=addslashes($xml2->item[$a]->left_hd);
$truck=addslashes($xml2->item[$a]->truck);
$special_num=addslashes($xml2->item[$a]->special_num);
$special=addslashes($xml2->item[$a]->special);
$displacement=addslashes($xml2->item[$a]->displacement);
$displacement_num=addslashes($xml2->item[$a]->displacement_num);
$start_price_en=addslashes($xml2->item[$a]->start_price_en);
$start_price_usd=addslashes($xml2->item[$a]->start_price_usd);
$end_price_en=addslashes($xml2->item[$a]->end_price_en);
$end_price_usd=addslashes($xml2->item[$a]->end_price_usd);
$average_price=addslashes($xml2->item[$a]->average_price);
$color_en=addslashes($xml2->item[$a]->color_en);
$color_basic_ref=addslashes($xml2->item[$a]->color_basic_ref);
$scores_en=addslashes($xml2->item[$a]->scores_en);
$result_en=addslashes($xml2->item[$a]->result_en);
$result_num=addslashes($xml2->item[$a]->result_num);
$chassis_no=addslashes($xml2->item[$a]->chassis_no);
$company_ref=addslashes($xml2->item[$a]->company_ref);
$model_ref=addslashes($xml2->item[$a]->model_ref);
$auct_ref=addslashes($xml2->item[$a]->auct_ref);
$auct_system_ref=addslashes($xml2->item[$a]->auct_system_ref);
$auction_name=addslashes($xml2->item[$a]->auction_name);
$pics_urls=addslashes($xml2->item[$a]->pics_urls);

//Insert query
$insert="INSERT INTO auct_lots_temp (lot_id,lot_date,bid,company,model_year_en,model_name_en,model_detail,grade_en,model_type_en,mileage_en,mileage_num,inspection_en,equipment_en,transmission_en,awd,left_hd,truck,special_num,special,displacement,displacement_num,start_price_en,start_price_usd,end_price_en,end_price_usd,average_price,color_en,color_basic_ref,scores_en,result_en,result_num,chassis_no,company_ref,model_ref,auct_ref,auct_system_ref,auction_name,pics_urls)VALUES ('$lot_id','$lot_date','$bid','$company','$model_year_en','$model_name_en','$model_detail','$grade_en','$model_type_en','$mileage_en','$mileage_num','$inspection_en','$equipment_en','$transmission_en','$awd','$left_hd','$truck','$special_num','$special','$displacement','$displacement_num','$start_price_en','$start_price_usd','$end_price_en','$end_price_usd','$average_price','$color_en','$color_basic_ref','$scores_en','$result_en','$result_num','$chassis_no','$company_ref','$model_ref','$auct_ref','$auct_system_ref','$auction_name','$pics_urls')";
             
                $result=mysql_query($insert) or die(mysql_error("Invalid Query :"));
                mysql_free_result($result);
}

Dear my friends kindly give me suggestion why it is running well on local system and why it is very slow on live server.

 

Waiting for your urgent response

Best Regards

Habib

[Mchl]

1. addslashes is not the best idea to escape sql data. Use mysql_real_escape_string instead

2. Consider using extended inserts (a.k.a. multi-row inserts).

3. The fastest way to load large amounts of data to MySQL is using LOAD DATA INFILE statement to parse CSV files.

[bh]

Have you heard about normalization? Its a cool stuff...