Empty values
Depending on the method of creation and/or modification, text fields that appear empty may hold an empty string (´´) or nothing (NULL). Even within the same list a field can hold an empty string in one record and the NULL value in another record, the difference is not visible. Empty numeric fields always hold NULL.
Always specify if you want to include or exclude empty values.
In the Filter designer, there are 2 operators 'Is empty' and 'Is not empty'
- For numeric fields NULL values are checked.
- For text fields, NULL and '' values are checked.
The difference is important for negative queries, such as <> or NOT IN.
Example:
optout<>0 => does not include empty optout values. To take these into account use: optout<>0 and optout IS NOT EMPTY
name<>'Andrew' => includes the '' values but not 'NULL values'. To take these into account as well use: name<>'Andrew' or name IS EMPTY
COUNTRY NOT IN ('FR','BE') => includes '' but not NULL. To take these into account use: COUNTRY NOT IN ('FR','BE') OR COUNTRY IS EMPTY
Negative queries
If possible, do not use negative queries with NOT or EXCLUDE. They do not use indexes and slow down performance.
If it cannot be avoided, make sure to put the negative condition at the end.
Ranges
Use the BETWEEN operator when filtering on a range of values.
Example:
Age BETWEEN 10 AND 20
Avoid Age>10 AND Age <20
Functions
Avoid functions when possible. Functions ignore indexes.
If functions are needed regularly, consider creating a computed column to the table.
Like
When using the LIKE operator provide as many characters as possible and place the wildcard ‘%’ as far as possible to the end of the search string.
The speed of the query when using a wildcard is directly related to the position of the wildcard. If the wildcard is in front of the string e.g. ‘a%cd’ it takes longer to process than when the wildcard is in the middle or end of the string ‘abc%’.
Combining queries with AND and OR
You can use the AND operator without reservation since it will always result in a smaller result set, thus improving performance.
Using the OR operator doesn’t hurt performance if the indexes used for the left and right condition are the same (e.g. query on the same field or field combination). If your left and right condition are not based on the same index an OR operator will cause all indexes to be ignored, resulting in a slower query.
Example:
ID=10 OR Name='Andrew' => the indexes are ignored
ID=10 OR ID=11 => the indexes are used.
