Topic: Large Numbers of branches hangs and fails the editor
/sales/manage/customer_branches.php
will fail and hang on large numbers of branches for clients ( 745 branches)
fetch time for any client increases if more branches are made even for other clients. I think the issue might be is our same crm persons are listed on the other customer branches in crm contacts.
Example
Debtor (MHN Health Net Insurance)
Branch (MHN Patient Jim) -> Crm Person (Patient Jim)
Debtor (Anthem Blue Cross)
Branch (Anthem Patient Jim) -> Crm Person (Patient Jim)
Person is the same that both branches are referencing so they both point to the same ID.
SQL Called on page
SET sql_mode = 'STRICT_ALL_TABLES'
SELECT name, value FROM 0_sys_prefs
SELECT value FROM 0_sys_prefs WHERE name='version_id'
SELECT name, value FROM 0_sys_prefs
SELECT COUNT(*) FROM 0_debtors_master
SELECT COUNT(*) FROM 0_salesman
SELECT COUNT(*) FROM 0_areas
SELECT COUNT(*) FROM 0_shippers
SELECT COUNT(*) FROM 0_tax_groups
SELECT debtor_no, debtor_ref, curr_code, inactive FROM 0_debtors_master ORDER BY debtor_ref
SELECT COUNT(*) FROM 0_cust_branch WHERE debtor_no='757'
SELECT COUNT(*) FROM (SELECT b.branch_code, b.branch_ref, b.br_name, p.name as contact_name, s.salesman_name, a.description, p.phone, p.fax, p.email, t.name AS tax_group_name, b.inactive
FROM 0_cust_branch b LEFT JOIN 0_crm_contacts c
ON c.entity_id=b.branch_code AND c.type='cust_branch' AND c.action='general'
LEFT JOIN 0_crm_persons p on c.person_id=p.id,0_areas a, 0_salesman s, 0_tax_groups t
WHERE b.tax_group_id=t.id
AND b.area=a.area_code
AND b.salesman=s.salesman_code
AND b.debtor_no = '757' AND !b.inactive GROUP BY b.branch_code ORDER BY branch_ref) tmp_count
SELECT b.branch_code, b.branch_ref, b.br_name, p.name as contact_name, s.salesman_name, a.description, p.phone, p.fax, p.email, t.name AS tax_group_name, b.inactive
FROM 0_cust_branch b LEFT JOIN 0_crm_contacts c
ON c.entity_id=b.branch_code AND c.type='cust_branch' AND c.action='general'
LEFT JOIN 0_crm_persons p on c.person_id=p.id,0_areas a, 0_salesman s, 0_tax_groups t
WHERE (b.tax_group_id=t.id
AND b.area=a.area_code
AND b.salesman=s.salesman_code
AND b.debtor_no = '757' AND !b.inactive) GROUP BY b.branch_code ORDER BY branch_ref LIMIT 0, 10
SELECT name, address, debtor_ref
FROM 0_debtors_master WHERE debtor_no = '757'
SELECT salesman_code, salesman_name, inactive FROM 0_salesman ORDER BY salesman_name
SELECT area_code, description, inactive FROM 0_areas ORDER BY description
SELECT id, description, inactive FROM 0_groups ORDER BY description
SELECT loc_code, location_name, inactive FROM 0_locations WHERE fixed_asset=0 ORDER BY location_name
SELECT shipper_id, shipper_name, inactive FROM 0_shippers ORDER BY shipper_name
SELECT id, name, inactive FROM 0_tax_groups ORDER BY id
SELECT chart.account_code, chart.account_name, type.name, chart.inactive, type.id
FROM 0_chart_master chart,0_chart_types type
WHERE chart.account_type=type.id ORDER BY type.class_id,type.id,account_code
SELECT chart.account_code, chart.account_name, type.name, chart.inactive, type.id
FROM 0_chart_master chart,0_chart_types type
WHERE chart.account_type=type.id ORDER BY type.class_id,type.id,account_code
SELECT chart.account_code, chart.account_name, type.name, chart.inactive, type.id
FROM (0_chart_master chart,0_chart_types type) LEFT JOIN 0_bank_accounts acc ON chart.account_code=acc.account_code
WHERE acc.account_code IS NULL
AND chart.account_type=type.id ORDER BY type.class_id,type.id,account_code
SELECT chart.account_code, chart.account_name, type.name, chart.inactive, type.id
FROM 0_chart_master chart,0_chart_types type
WHERE chart.account_type=type.id ORDER BY type.class_id,type.id,account_code