How to manage CRM performace when dealing with huge amount of data?
Note:-
Data in below tables is increasing day by day
patient_table------------->(Rows count 862000)
patient_table_cstm count–>(Rows count 862000)
Below is query created from beans to load data in suitecrm
SELECT patient_table.id , patient_table.last_name , patient_table.first_name ,
patient_table.birth_date , patient_table.age , patient_table.phone_home ,
patient_table.phone_mobile , patient_table.eaddress , patient_table.address1 ,
patient_table.city , patient_table.state1 , patient_table.postal_code
,patient_table.insurer_plan , patient_table.assigned_user_id
FROM patient_table
LEFT JOIN patient_table_cstm ON patient_table.id = patient_table_cstm.id_c
where patient_table.deleted=0
ORDER BY patient_table.last_name ASC,patient_table.first_name ASC
OFFSET 0 ROWS FETCH NEXT 50 ROWS ONLY
2.How to convert left join to sub query using beans in suitecrm?
In suite/sugar CRM no need to use join for parent and its custom table i:e; patient_table and patient_table_cstm tables, you can directly get all the list of data from both the tables using beans method called get_full_list.
Here is the example with account table you can refer to:
$accountBean = BeanFactory::newBean(‘accounts’);
/$beanList = $accountBean->get_list(
//Order by the accounts name
‘name’,
//Only accounts with industry ‘Media’ where condition
“accounts.industry = ‘Media’”,
//Start with the 30th record (third page)
30,
//No limit - will default to max page size
-1,
//10 items per page
);/
$beanList = $accountBean->get_full_list(
‘name’,
“accounts.deleted = ‘0’”,
30,
-1);
Hi,
We have not customized/return this query this is generated from Suitecrm itself so we have include Suitecrm ListViewData file for the module and through that the list view, detail view operation gets executed. You can refer below file its inbuilt file. Please find below file and its present on the root on each module.patie_patientsListViewData.zip (8.4 KB)