2. VReports & dashboard extension is working slow

Issue

Vreports are working slowly, Vreports dashboard is taking too long to load

Reasons

There can be several reasons behind the slow working of the Vreports & Dashboards. Here are some of the possible issues that are causing the slowness in Vreports working

• Overall CRM system is experiencing speed issues and it is reflected in the Vreports as well

• Reports do not have conditions (Or have very few), and because of that, the report must query a lot of rows in the database, therefore it takes more time to generate the output.

• Reports are being fetched by using temporary tables for table joins

Solution

Following are some of the potential solutions to this issue

• Try adding more conditions (where it makes sense) and see if the speed improves.

• Make sure all uitype10 fields have indexes, if not then add it

• Add indexes to the most commonly used "condition fields". For example, if you use Lead.Lead_Source picklist field in your reports - you should add an index. Indexes are very powerful and can really help in speeding up the system

• Database optimization by VTExperts. It will improve the speed of the whole CRM including the reports. CRM optimization is done by the developers and takes about 10-12 hours. We fix different areas of the database, that boost the speed. Feel free to schedule a call for further discussion on this

• Optimize individual reports. We will check the individual report and optimize it to improve its speed. It is a billable task and depends upon the number of reports that needs optimization.

Diagnostic Queries

Here are some SQL queries using which you can diagnose what is causing the slowness in the VReports and CRM system as a whole

1. SELECT COUNT(*) num, setype FROM vtiger_crmentity WHERE deleted='0' GROUP BY setype ORDER BY num DESC; This query selects data from the "vtiger_crmentity" table and returns the number of active records (that are not deleted) for each distinct "setype" value. Using it you can evaluate how many records are active and do large number of records in the database is causing the slowness.

2. ELECT COUNT(*) num, setype FROM vtiger_crmentity WHERE deleted='1' GROUP BY setype ORDER BY num DESC;

   This query selects data from the "vtiger_crmentity" table and returns the number of records that have been deleted for each distinct "setype" value. Using it you can evaluate how many records are deleted and based on it determine whether deleted records are causing slowness in the Vreports. Finally, you can configure a condition accordingly to skip querying the deleted records

3. SELECT table_name, table_rows

   FROM INFORMATION_SCHEMA.TABLES

   WHERE TABLE_SCHEMA IN (SELECT DATABASE())

   AND table_rows>50

   ORDER BY table_rows

   DESC LIMIT 400;

   This SQL query selects the names of tables and their corresponding number of records from the "INFORMATION_SCHEMA.TABLES" table. To further specify the results, we apply a filter to show tables having 50+ records. This query can help in determining the table with the most records and then configuring the report filters accordingly.

4. SELECT table_name AS "Tables", ROUND(((data_length + index_length) / 1024 / 1024), 2) "Size in MB"

   FROM information_schema.TABLES

   WHERE table_schema IN (SELECT DATABASE())

   ORDER BY (data_length + index_length) DESC;

   This query selects the names and sizes (in MB’s) of all the tables in the current database and sorts them in descending order of size. Using it you can identify the tables with extensive size and then take actions accordingly to improve them so they can query fast.

5. SELECT table_schema "DB Name",

   ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB"

   FROM information_schema.tables

   GROUP BY table_schema;

   This query is used to check the sizes of all databases in the database server (in MB’s). You can identify the substantial-sized databases using it and then improve them.

6. SELECT * FROM (

   SELECT 'Users Active' AS col1, COUNT(*) AS col2 FROM vtiger_users WHERE status='Active' UNION ALL

   SELECT 'Users InActive' AS col1, COUNT(*) AS col2 FROM vtiger_users WHERE status='InActive' UNION ALL

   SELECT 'Workflows Active' AS col1, COUNT(*) AS col2 FROM com_vtiger_workflows WHERE status='1' UNION ALL

   SELECT 'Workflows InActive' AS col1, COUNT(*) AS col2 FROM com_vtiger_workflows WHERE status='0' UNION ALL

   SELECT 'Fields Active' AS col1, COUNT(*) AS col2 FROM vtiger_field WHERE presence!='1' UNION ALL

   SELECT 'Fields Inactve' AS col1, COUNT(*) AS col2 FROM vtiger_field WHERE presence='1' UNION ALL

   SELECT 'Profiles' AS col1, COUNT(*) AS col2 FROM vtiger_profile UNION ALL

   SELECT 'Roles' AS col1, COUNT(*) AS col2 FROM vtiger_role UNION ALL

   SELECT 'Lists' AS col1, COUNT(*) AS col2 FROM vtiger_customview UNION ALL

   SELECT 'seactivity_rel' AS col1, COUNT(*) AS col2 FROM vtiger_seactivityrel UNION ALL

   SELECT 'crmentity_rel' AS col1, COUNT(*) AS col2 FROM vtiger_crmentityrel UNION ALL

   SELECT '',''

   ) system_info;

   This query is used to check different parameters of a CRM system.

Extension	Vreports & Dashboards
Decription	VReports & dashboard extension is working slow
Created by	Salman (April 06, 2023)