Some customers have advanced access that allows them to create their own SQL queries on the relational tables in their Dialog Insight project. These queries offer great flexibility but must be carefully designed to ensure good performance and stability. This guide outlines best practices for creating queries and the elements to check before submitting a query for validation by the Dialog Insight team.
If you do not have access to the query creation interface and do not master SQL, our team can set up a query for you.
Tables, Structure and Joins
Use simple and effective structures
- Prioritize indicator or scoring tables that already contain aggregated data. This reduces, or even eliminates, 1:N joins, which often require more resources.
- Limit the number of joins and avoid complex table chains whenever possible.
Use reliable and high-performance joints
- Use joins on key fields (primary or foreign identifiers) to ensure relational integrity.
- Choose numeric identifiers: joins on numeric values are much more efficient than those on strings.
- Avoid multiple 1:N connections in the same request: this can lead to duplicates or an explosion in data volume.
Filters and Performance
Apply the filtering to the right place
Always apply a global filter in the main query to limit the amount of data processed from the start. If you apply a filter to a specific table, it should be used primarily in the context of a left join, where the goal is to keep rows without a match.
Avoid resource-intensive filters
Limit the use of the Like and Contains operators, especially on large tables or unindexed fields. When possible, use equality comparisons (=) or pre-processed values.
Data Volume and Relevance
Reduce the volume processed
Select only the fields necessary for the intended use (targeting, personalization or export).
Verify the temporal relevance of the data
Ensure the data used will remain relevant over time. For example, preferences or statuses that haven't been updated in a long time may become outdated. You could apply time filters where necessary (update date, recent period).