The following guidelines will assist you in creating an optimized query for your reports:
- Create a universe with only the focus area and forms that you need for a specific query. Although the maximum limit is 20, limiting the total number of kernels to around 10 or less will speed up performance.
- Do not mix assessments or efforts/Points of Service with TouchPoint or collections data in the same query.
- When reporting on TouchPoints using the flattened universe, include no more than one or two TouchPoints in each query. Follow the same logic when reporting on collections, referrals, and approvals.
- Flattened demographics should be used whenever possible as opposed to unflattened demographics.
- Avoid pulling data from multiple folders into the same query.
- When creating a query filter, use the indexed fields over non-indexed fields when possible.
Indexed fields are identifiers like Site ID, Program ID and standard fields like Date Taken in a TouchPoint. Any names or custom fields in the system are not indexed and are not optimized for reporting.
7. Avoid requeries (results from another query) and sub queries as much as possible. Requeries are OK for performance when only one to ten responses are being passed from the primary query to the secondary and when using an indexed field.
8. Avoid running reports at peak usage times. If this is difficult, schedule reports to run between 4:00 am - 7:00 am or 6:00 pm - 9:00 pm EST.
For more information on Scheduling Reports, click here.