AnsweredAssumed Answered

Select Query takes longer time the bigger the leads table is

Question asked by Asaf Army on Jan 1, 2016
Latest reply on Jan 5, 2016 by Asaf Army

Hello Developers,


I wrote a simple function that open a leads csv file and insert them into sugarcrm.

there are 100,000 Leads in the file. and i loop through each row (using fget()) and create the lead bean and save()

it works perfect!


but as soon as i added a simple query that checks if the lead exist (using a special id from the file),

It takes endless hours to finish all 100,000.


it turns out the query takes longer and longer time to finish , the bigger the amount of leads in the table.

at the beginning. 500 leads are inserted  in a second. Then after i inserted 10000 leads, it takes 50 leads per second.

after 20000 leads, the system insert 10 leads per second. this is why it never ends.


This is the simple query that makes the loop get slower and slower :


global $db;

  $query = "SELECT id

   FROM leads,leads_cstm

   WHERE id = id_c and deleted = 0 and lead_prev_id_c = '" . $lead_prev_id . "';";

  $GLOBALS['log']->fatal("query = " . $query);

  $qresult = $db->query($query);

  $resrow = $db->fetchByAssoc($qresult);

  $leadID = $resrow['id'];


can anyone help me with tips on How can i make it work?


Thank you.