STARTSWITH Query vs CONTAINS Query

[DylanBlumenberg]

Hi all, my question is: why does ServiceNow default to a STARTSWITH query as opposed to a CONTAINS query?

For some background:

We often hear complaints that users cannot find what they're looking for when using reference variables on the employee portal. Teaching the wildcard method is not effective, and quite frankly, as a design decision, users expect CONTAINS. 

Today I find out that there's a system property named glide.ui.ref_ac.startswith that "disables "contains" searches for reference autocompleters whether done with a wildcard (*) or the autocomplete.contains user preferences." If you set this property to false, you still have to create user preference records to specify that a certain table uses a CONTAINS autocomplete. 

I can't think of one good reason where STARTSWITH would be a better default than CONTAINS. Can anyone help me understand why the CONTAINS functionality would not be the default?

[M Iftikhar · Accepted solution]

Hi there,

Great question. The main reason is database performance.

• STARTSWITH queries are fast because they can use database indexes.

• CONTAINS queries (*word*) are much slower because they often require a full scan of the table, which is resource-intensive on large tables.

ServiceNow defaults to STARTSWITH to keep the instance fast and stable for everyone. The system property and user preferences allow admins to enable CONTAINS for specific cases where usability is critical and the performance impact is manageable.

Thanks & Regards,
Muhammad Iftikhar
If my response helped, please mark it as the accepted solution so others can benefit as well.

[anurampalli]

The assumption is likely that users already know exactly what they’re searching for. However, you’re absolutely right—remembering every request, incident, or assignment number, and knowing precisely how a particular field begins, isn’t realistic in this day and age.

Using “Contains” as the default makes far more sense, and I agree with you. I’d also be interested to know if there’s a deeper or more meaningful reason behind the current setup.

[M Iftikhar · Accepted solution]

Hi there,

Great question. The main reason is database performance.

• STARTSWITH queries are fast because they can use database indexes.

• CONTAINS queries (*word*) are much slower because they often require a full scan of the table, which is resource-intensive on large tables.

ServiceNow defaults to STARTSWITH to keep the instance fast and stable for everyone. The system property and user preferences allow admins to enable CONTAINS for specific cases where usability is critical and the performance impact is manageable.

Thanks & Regards,
Muhammad Iftikhar
If my response helped, please mark it as the accepted solution so others can benefit as well.

[Bhuvan · Accepted solution]

@DylanBlumenberg 

Below article explains in detail where & how STARTSWITH & CONTAINS work,

https://www.servicenow.com/community/developer-articles/performance-hacks-reference-fields-4-of-4-co...

If this helped to answer your query, please mark it helpful & accept the solution. 

Thanks,

Bhuvan

[DylanBlumenberg]

Thanks for sharing that, it provided great insight!

My takeaway from the article is if you want to have a table default to contains, you should weigh the potential performance impact against the realistic need.