Optimizing a Query Builder query via the batch sizeCertain Query Builder queries can take a long time to process or may timeout completely due to a combination of factors; the factors can include the type of query itself, number of records in the classes selected as well as the number of relationships between those classes. When such long running queries are experienced, there are a number of steps to iterate through in an attempt to optimize and lessen the query processing time. In the Quebec release, we introduced a feature called ‘Batch Size Per Query’ which will give the Admin role additional control over how the queries are processed. The batch size is a value which is used to retrieve and return a subset of the query results. The flexibility in which one can construct the queries can lend itself to extremely large result sets, hence, queries are executed in batches to alleviate potential performance and memory constraint issues. Tuning this batch size value can have a significant effect on the efficiency of the query as it controls how many records are retrieved per iteration of a single query. Optimizing the batch size for a query is the last step recommended as it is a tuning process which can be more efficiently applied when some of the following options are attempted first. Review the classes in the query and determine if any classes can be refined to a more specific subclass versus using a more-broad class up the hierarchy. For example, a user may just need Unix Servers instead of using the broader Hardware class in their query, thus eliminating the need to traverse an additional & unnecessary large number of records.Apply filters to the queries where applicable. Adding filters can potentially lessen the amount of data retrieved and can give a sizeable performance boost.Investigate the node and relationship arrangement and determine if there is an opportunity to change the node direction. For example, if Class A is connected to Class B and Class A has 100x more records, it will speed up the query if you reverse the ordering to lessen the amount of data traversed by the query.If timeouts are experienced, increase the query timeout via the following system properties:• glide.cmdb.query.batch_time_limit_in_sec (for Run and Load More Results)• glide.cmdb.query.query_time_limit_in_sec (for Load All Results)Convert the long running query to a scheduled query – this is useful if the query is just taking too long to run for the customer. This change will make it so the customer is not waiting on the query to complete and instead they can save it and run it on a schedule in the background. If the query processing time is still unsatisfactory after trying the above steps, attempt to fine tune the batch size by following the guidelines below. It is encouraged to test the process of changing the batch size in sub-prod instances before making any changes to production instances. Steps to determine the proper Batch Size Step 1. Check if an error was raised during processing of the query as it will determine in what direction the batch size value will be adjusted: Time-out error – Increase the batch sizeOut of memory exception – Decrease the batch sizeNo errors/slow to process – Increase the batch size Step 2. Identify the query type as either a ‘Chained Query’ or ‘Operator Query’ If a query contains any AND or OR operators, it is classified as an ‘Operator Query’. All other queries without operators are classified as a ‘Chained Query’. Step 3. For chained queries, determine the starting batch size and the values to increment by, by finding the ratio of – the number of cmdb_rel_ci records relative to cmdb_ci If the ratio for cmdb_ci / cmdb_rel_ci size is below 5M Start with a batch size of 100 and increment it by 200 until the batch size reaches 1000 or the timeout error disappears If the ratio for cmdb_ci / cmdb_rel_ci size is below 20M Start with a batch size of 1000 and increment it by 2000 until the batch size hits 10K or the timeout error disappears If the ratio for cmdb_ci / cmdb_rel_ci size is below 50M Start with a batch size of 10K and increment it by 2000 until batch size hits 20K or the timeout error disappears Note – Incrementing the batch size for Chained Queries can be more aggressive and can potentially go even higher than indicated above. Step 4. If a query has Operators, it is recommended to be conservative on the batch size. Start with a batch size of 300 and increment in batches of 200. No upper limit is provided to the queries that have operators as query results are multiplexed and they could provide exponential results which could lead to Out of memory exceptions. Tuning the Batch Size The batch size value can be set in one of three ways at either a global level (single batch size value for all queries) as well as at a query-specific level (each query has its own batch size). The three ways in which the batch size is set is identified below: Default value: By default, the global batch size is set to 100. This value is set within the code and can be overridden by the subsequent ways identified below.Modified globally: The batch size can be modified globally from the default 100 value to any value specified via a system property - glide.cmdb.query.batch.size. Once set, this property value applies to all queries regardless of how the query run was initiated.Change batch size per query (only applies to saved queries): Modify the ‘Execution Batch Size’ field in the Saved Queries table (qb_saved_query table). If set, this value applies only to that specific saved query, and has priority over the global value of 100 and the value of the glide.cmdb.query.batch.size system property. In other words, this value will be used for the query specified for only. All other queries will respect the global batch size value if they do not also have a query-specific value specified. Set the batch size for a specific saved query Role required: admin In the Filter navigator, enter qb_saved_query.list and navigate to the Saved Queries table.In the Saved Queries list view, locate the saved query record which you want change batch size.Set or modify the value of the ‘Execution Batch Size’ field. Set the value to be greater than the global value in the glide.cmdb.query.batch.size property, or increase any existing value.