Sysparm_limit causing long running queries

[AndyKK]

Hello, 

I am running into a strange issue where adding sysparm_limit to a query for an integration is causing extended query times and timeout issues. Here are the queries and their respective response times. I am using Postman for testing as shown in the screenshots. 

• https://<instance>.service-now.com/api/now/v2/table/incident?sysparm_query=correlation_idISNOTEMPTY^sys_updated_on>2025-05-15T04:57:27.281Z^correlation_idLIKERQ^sys_updated_by!=<particularUser>^ORDERBYsys_updated_on&sysparm_limit=100&sysparm_offset=0
  • This has a response time of over 1 minute
• https://<instance>.service-now.com/api/now/v2/table/incident?sysparm_query=correlation_idISNOTEMPTY^...
  • Response time of about 2 seconds

Side Notes: We use this same configuration for several other SN - SN integrations and only this one is having issues with sysparm_limit.

[Maik Skoddow]

Hi @AndyKK 

Performance issues often stem from inefficient queries, lack of indexing, or heavy filters/sorting. Your query uses multiple filters, a LIKE clause, and an ORDERBY on sys_updated_on, which can be expensive if the fields are not indexed or if the dataset is very large. If fields are not indexed the query will require a full table scan, which becomes much slower when combined with ORDERBY and sysparm_limit (since the database must sort and then limit the result set).  And adding sysparm_limit may change the way the database engine executes the query, especially with complex filters and ordering, potentially leading to inefficient execution paths.

Recommendations:

• Check Indexes: Ensure that all fields used in filters and ordering (correlation_id, sys_updated_on, etc.) are indexed. Lack of indexing is a common cause of slow queries with limits and sorting.

• Simplify Query: Test the query by removing one filter at a time (especially LIKE and ORDERBY) to isolate which condition is causing the slowdown.

• Test Without ORDERBY: Sorting is expensive. Try removing ORDERBYsys_updated_on to see if performance improves. If so, consider adding an index on sys_updated_on.

• Monitor Query Performance: Use the ServiceNow Performance Analytics or the "Slow Queries" module to analyze how the query is being executed and which part is slow.

• Reduce Limit Further: Although 100 should be fine, try with sysparm_limit=10 to see if the behavior changes

Maik