<style>p { margin: 0; }span.fr-emoticon.fr-emoticon-img { background-repeat: no-repeat !important; font-size: inherit; height: 1em; width: 1em; min-height: 20px; min-width: 20px; display: inline-block; margin: -0.1em 0.1em 0.1em; line-height: 1; vertical-align: middle; } span.fr-emoticon { font-weight: normal; font-family: "Apple Color Emoji", "Segoe UI Emoji", "NotoColorEmoji", "Segoe UI Symbol", "Android Emoji", "EmojiSymbols"; display: inline; line-height: 0; } blockquote { border-left: solid 2px #5e35b1; color: #5e35b1; margin-left:0; padding-left:5px;}blockquote blockquote{ border-color: #00bcd4; color: #00bcd4;}blockquote blockquote blockquote{ border-color: #43a047; color: #43a047;} table.grid{ border-collapse: collapse;} table.grid td, table.grid th { border: 1px solid #ddd;} .fr-fic.fr-dib{ display: block; margin: 5px auto;}.fr-fic.fr-dib.fr-fir{ text-align: right; margin: 5px 0 5px auto;}.fr-fic.fr-dib.fr-fil{ text-align: left; margin: 5px auto 5px 0;}.fr-fic.fr-dii{ float: none; margin: 5px auto;}.fr-fic.fr-dii.fr-fil{ float: left; margin: 5px auto;}.fr-fic.fr-dii.fr-fir{ float: right; margin: 5px auto;}img.fr-dib.fr-fir { margin-right: 0; text-align: right;}img.fr-dib.fr-fil { margin-left: 0; text-align: left;}img.fr-dib { margin: 5px auto; display: block; float: none;}img.fr-bordered { box-sizing: content-box; border: solid 5px #CCC;}img.fr-shadow { box-shadow: 10px 10px 5px 0px #cccccc;}img.fr-rounded { border-radius: 10px; -moz-border-radius: 10px; -webkit-border-radius: 10px; -moz-background-clip: padding; -webkit-background-clip: padding-box; background-clip: padding-box;}</style><p><strong>In this guide we will cover:</strong></p><p><strong>- Selective Data Retrieval</strong></p><p><strong>- Join Recommendations</strong></p><p><strong>- Subquery Alternatives</strong></p><p><strong>- Minimising Use of Wildcard Characters</strong></p><p><strong>- Use of $-filters</strong></p><p><br></p><p id="isPasted"><br></p><p>Larger or more complex reports can potentially have slower loading times. This guide outlines a few key points to consider when writing reports to help combat this.</p><p><br></p><p><strong><span style="font-size: 14pt;">Selective Data Retrieval</span></strong></p><p>Using a <strong>SELECT * </strong>statement can slow down performance because it returns all columns in a table, including those that are not needed for the query. To optimize SQL queries, it is important to only select the columns that are needed for the query.</p><p><br></p><p>Additionally where possible, use indexed columns for filters, e.g. <strong>faultid </strong>or <strong>datecleared</strong>.</p><p><br></p><p><strong><span style="font-size: 14pt;">Join Recommendations</span></strong></p><p>Some joins are more efficient than others. <strong>JOIN</strong> is more efficient to run than a <strong>LEFT JOIN</strong> or <strong>RIGHT JOIN</strong> for example.</p><p><br></p><p id="isPasted"><strong>JOIN </strong>statements can also be used instead of filtering with a <strong>WHERE </strong>clause. In the example below, the data can be filtered to exclude deleted tickets earlier than if the same was contained in a <strong>WHERE </strong>clause further on in the query.</p><p><br></p><p><img src="https://halo.haloservicedesk.com/api/attachment/image?token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6IjVlNjM0MzE5LTk0NDktNDBhYi04NjM5LTEyOGI5MGQyOGJiNyJ9.DaI8gSd8WpDyw2v1-UGL0v_7mdvoVpGHfwl2oa75-6A" class="fr-fic fr-fil fr-dib" width="609" height="417"></p><p><strong><span style="font-size: 10pt;">Fig 1. Example report with an efficient JOIN.</span></strong></p><p><br></p><p><strong><span style="font-size: 14pt;">Subquery Alternatives<br></span></strong></p><p>Subqueries can slow down performance, especially when used in <strong>WHERE</strong> clauses. It is recommended to avoid subqueries whenever possible, and replace with some type of <strong>JOIN</strong>.</p><p><br></p><p>Both these example queries give identical results, but the latter will run more efficiently. </p><p><br></p><p><img src="https://halo.haloservicedesk.com/api/attachment/image?token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6ImJlZDdjNzNjLTM4YzktNDNhMS1iODFjLTM5OTM4ZmM0NDJhYiJ9.9fQmCYqi7fCmNs2Tuu1XlpGgpTQsaCT1sKB-rZHB4Rk" class="fr-fic fr-fil fr-dib" width="327" height="220"></p><p><strong><span style="font-size: 10pt;">Fig 2. Query for last action date on tickets with a subquery.</span></strong></p><p><br></p><p><img src="https://halo.haloservicedesk.com/api/attachment/image?token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6IjEyZmE5NWEyLTA3ODktNDAwMi05ZjJjLTAzNmE4YzE1YzY1YSJ9.Ahw7uXxAgvTFf_0ujUSu0n1QTr3q5g3-krt82caNNIk" class="fr-fic fr-fil fr-dib" width="331" height="241"></p><p id="isPasted"><strong><span style="font-size: 10pt;">Fig 3. Query for last action date on tickets with a JOIN.</span></strong></p><p><br></p><p><strong><span style="font-size: 14pt;">Minimising Use of Wildcard Characters</span></strong></p><p>The use of wildcard characters, such as <strong>%</strong>, can slow down performance as the database has to scan the entire table to find the relevant data. It is important to minimize the use of wildcard characters and to use them only when absolutely necessary. If possible, replace these with greater/less than or equal comparison operators. </p><p><br></p><p>The below example is an inefficient report. Although it only queries the required columns, it has to search each user's name.</p><p><br></p><p><img src="https://halo.haloservicedesk.com/api/attachment/image?token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6ImM1MDZjZjg3LWEyODAtNDY5OC05NTQ3LWVkMTMwOTEzYTE0MyJ9.r57gtN_CeorQdiSFMkAuUjzTG0pktRprDgN-Eif-kuk" class="fr-fic fr-fil fr-dib" width="288" height="69"></p><p><strong><span style="font-size: 10pt;">Fig 4. Query for tickets where the end-user has the name "James".</span></strong></p><p><br></p><p>Using a <strong>CONTAINS </strong>query instead will help the efficiency here. However, if looking for tickets from only one user, it would be better to use their user ID instead of name to further improve this.</p><p><br></p><p><img src="https://halo.haloservicedesk.com/api/attachment/image?token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6ImUwOTU0ZTEzLTg3YzQtNDhhZC1iMDViLTcwZjYxYmQ3N2Y0OSJ9.9-K8h6D3OlUI1POKWbdDQbVK1JrGgU7kYzvzCdGaJu8" class="fr-fic fr-fil fr-dib" width="318" height="73"></p><p id="isPasted"><strong><span style="font-size: 10pt;">Fig 5. Improved query for tickets where the end-user has the name "James".</span></strong></p><p><br></p><p><span style="font-size: 14pt;"><br></span></p><p data-pasted="true"><strong><span style="font-size: 14pt;">Use of $-filters </span></strong></p><p>It is now possible to specifically place where in your query the filters will be applied, both for when filters are applied manually on the report's UI or when filtering on a report during an API call using "Filter_". </p><p><br></p><p><img src="https://halo.haloservicedesk.com/api/attachment/image?token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6IjU3ODZkOGFlLTJjNmUtNGNlMC1iY2UwLTJjZmNlZDBkOWQ2ZSJ9.KNJQoJM2NFLALR4w5JJZ5lONAKpGAfCb1ZIUYMIaFOU" width="627" style="width: 629px; height: 331.701px;" height="332" class="fr-fic fr-dii"></p><p><strong><span style="font-size: 10pt;">Fig 6. The $-filters variable that allows filters to be inserted directly into a report's SQL</span></strong></p><p><br></p><p>To be able to utilise this functionality, ensure you have enabled the setting in Configuration > Reporting > General Settings to "use improved filters in the report builder".</p><p><br></p><p><img src="https://halo.haloservicedesk.com/api/attachment/image?token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6IjdmNDgxMjUzLWU2ZDgtNDMyNC1hYjI5LWY4NTczYzcwMzk5OSJ9.3eEJixFdlbIIQNXLd6J7Fm8ljq4sEG_YM6Ti0eActLU" width="707" style="width: 709px; height: 621.461px;" height="621" class="fr-fic fr-dii"></p><p><strong><span style="font-size: 10pt;">Fig 7. Setting to use new filtering functionality in the reporting suite</span></strong></p><p><br></p><p>Use of this variable greatly improves performance - especially for large reports - as it inserts the UI filters or API parameters directly into the WHERE clause of the report's SQL instead of applying them after the initial report has loaded, causing the report to load twice (and once without filters).</p><p><br></p><p><strong>Important: This variable will only work if the names of the columns being selected in your query match the names in the database and/or query builder. If they do not match, the filters will still be applied but not using this method, which could result in performance issues.</strong></p>
