Best Practices for Writing Efficient Queries - Support and Troubleshooting

Best Practices for Writing Efficient Queries .ns-kb-css-body-editor-container { p { font-size: 12pt; font-family: Lato; color: var(--now-color--text-primary, #000000); } span { font-size: 12pt; font-family: Lato; color: var(--now-color--text-primary, #000000); } h2 { font-size: 24pt; font-family: Lato; color: var(--now-color--text-primary, black); } h3 { font-size: 18pt; font-family: Lato; color: var(--now-color--text-primary, black); } h4 { font-size: 14pt; font-family: Lato; color: var(--now-color--text-primary, black); } a { font-size: 12pt; font-family: Lato; color: var(--now-color--link-primary, #00718F); } a:hover { font-size: 12pt; color: var(--now-color--link-primary, #024F69); } a:target { font-size: 12pt; color: var(--now-color--link-primary, #032D42); } a:visited { font-size: 12pt; color: var(--now-color--link-primary, #00718f); } ul { font-size: 12pt; font-family: Lato; } li { font-size: 12pt; font-family: Lato; } img { display: ; max-width: ; width: ; height: ; } } Best Practices for Writing Efficient Queries and Code Customer-facing guidelines for building fast lists, reports, business rules, and scripts on the ServiceNow platform. Summary Slow pages, reports, and background jobs are often caused by inefficient queries or scripts. This article explains the concepts behind query performance in plain terms and gives actionable best practices that any ServiceNow administrator or developer can use inside the platform. You do not need database access to apply these recommendations; ServiceNow manages the underlying database engine, whether it is MariaDB or Postgres (RaptorDB) . Key Concepts What is an index? An index is like the index at the back of a book. Instead of reading every page to find a topic, the database looks at the index and jumps straight to the right page. Fields such as sys_id , number , sys_created_on , sys_updated_on , and reference fields are typically indexed by default. When your query uses one of these fields, the database finds matching records quickly. What is a B-tree? A B-tree is the most common type of index structure. It organizes values in a balanced tree so the database can find, insert, and delete entries quickly. Think of it as a sorted list that the database can search in steps instead of scanning one item at a time. ServiceNow uses B-tree indexes on most fields automatically; you generally do not need to manage them directly. What is a full scan? If a query cannot use an index, the database has to read every row in the table. This is called a full table scan. It works fine for small tables but becomes slow as data grows. The goal of efficient querying is to give the database enough information to use an index so it avoids reading every row. What does ANALYZE mean? ANALYZE is a database command that updates statistics about the data in a table, such as how many rows exist and how unique values are distributed. The database uses these statistics to choose the fastest way to run a query. On ServiceNow, this maintenance is handled by the platform; customers do not run it themselves. However, performance issues after a large data import or upgrade can sometimes be related to stale statistics, and support can request a refresh. What is an EXPLAIN plan? An EXPLAIN plan is a preview of how the database intends to run a query. It shows which indexes the database plans to use, how many rows it expects to read, and the order in which it will access tables. It is like looking at a map before taking a trip. EXPLAIN does not actually run the query, so it is fast and safe. What is the difference between EXPLAIN and EXPLAIN ANALYZE? The difference is whether the query actually runs. EXPLAIN only estimates the plan. It shows what the database thinks will happen. EXPLAIN ANALYZE runs the query and shows what actually happened, including real timings and real row counts. Because it executes the query, it can take longer and use more resources. Both are internal support tools. Customers do not run them directly on a ServiceNow instance, but support may use them when investigating a performance case. What is a query hint? A query hint is a special instruction added to a SQL statement that tells the database engine how to run the query. For example, a hint might say "use this specific index" or "join tables in this order." Hints are powerful, but they can also cause problems if the data changes over time. In ServiceNow, query hints are used only by support engineers in specific situations; customers do not add hints themselves. Best Practices 1. Filter on indexed fields first Always start your list, report, or script query with a selective filter on an indexed field. This tells the database exactly where to look and avoids reading every record. Good starting filters include active , state , dates such as sys_created_on , and reference fields. var gr = new GlideRecord('incident'); gr.addQuery('active', true); gr.addQuery('sys_created_on', '>', gs.daysAgo(30)); gr.setLimit(100); gr.orderByDesc('sys_updated_on'); gr.query(); How to check if a field is indexed You can check indexing directly in the platform without database access: Navigate to System Definition > Tables . Open the table you want to check. Click Columns in the related links, or open a column from the list. In the Dictionary record, look for the Has index field. If it is checked, the field is indexed. You can also scroll to the Indexes related list at the bottom of the Dictionary record to see the exact indexes that include this field. If you frequently filter or sort by a custom field that is not indexed, consider opening a support case or working with your ServiceNow contact to evaluate whether an index is appropriate. Adding too many indexes can also hurt write performance, so it is a balance. 2. Make your filters as selective as possible The more specific your query, the fewer rows the database has to examine. Combine these habits to keep result sets small and fast. Filter on active records when you can. active = true is selective on most tables and removes closed or inactive rows from the result set. Use specific date ranges. Prefer sys_created_on > last 30 days over All time . Open-ended date filters force the database to consider the entire history of the table. Be careful with null or empty checks. Searching for is empty on an unindexed field often causes a full scan. Pair empty checks with another indexed filter such as active or a date range. Keep the row count low. Use setLimit() in scripts, keep list views under a few thousand rows, and paginate reports. Returning more rows than the user needs wastes database, network, and browser resources. var gr = new GlideRecord('incident'); gr.addActiveQuery(); // active = true gr.addQuery('caller_id', callerId); gr.addQuery('sys_created_on', '>', gs.daysAgo(7)); gr.setLimit(50); gr.query(); 3. Avoid overly broad searches and leading wildcards A search for contains text without any other filter often scans large amounts of data. A leading wildcard, such as searching for *error , is especially expensive because the database cannot use an index. Use exact or starts-with filters when possible, and always pair text searches with another indexed filter such as a date or active flag. 4. Keep list views focused Long lists with many columns, large text fields, or millions of rows slow down both the database and the browser. Use filters, related lists, and breakdown boards to keep the data set small. Avoid displaying description , work_notes , or other long text fields as list columns. 5. Avoid sorting by text or unindexed fields Sorting by short_description , description , or journal fields often forces the database to read and sort all matching rows in memory. Sort by indexed fields such as number , sys_created_on , or sys_updated_on instead. 6. Do not nest GlideRecord queries inside loops A query inside a loop issues one database call per loop iteration. If the outer loop has 1,000 records, the inner query runs 1,000 times. Rewrite this as a single query using addQuery('sys_id', 'IN', ids) or a related-list approach. // Avoid: one query per parent record while (parentGR.next()) { var childGR = new GlideRecord('task'); childGR.addQuery('parent', parentGR.sys_id); childGR.query(); while (childGR.next()) { ... } } // Prefer: one query for all related records var childGR = new GlideRecord('task'); childGR.addQuery('parent.sys_id', 'IN', parentIds); childGR.query(); 7. Use GlideAggregate for counts and summaries Counting or summing by looping through every GlideRecord loads all rows into memory. GlideAggregate asks the database to do the math and return only the result. var agg = new GlideAggregate('incident'); agg.addQuery('active', true); agg.addAggregate('COUNT'); agg.query(); if (agg.next()) { gs.info('Active incidents: ' + agg.getAggregate('COUNT')); } 8. Use reports and Performance Analytics instead of heavy scripts For dashboards and read-only reporting, prefer out-of-the-box reports, report sources, and Performance Analytics indicators over custom scripts. These features are optimized by the platform and are easier to maintain. 9. Batch bulk updates and suppress unnecessary processing Updating thousands of records one at a time fires business rules, workflows, audits, and email notifications for every record. When you perform a bulk update from a background script or fix script, consider disabling workflow and system-field updates if they are not required. Test in a sub-production instance first and document why it is disabled. gr.setWorkflow(false); gr.autoSysFields(false); gr.setUseEngineer(false); gr.updateMultiple(); 10. Avoid synchronous external calls in real-time rules Business rules or UI actions that wait for an external REST or SOAP response hold database transactions open and block the user's page. Move these calls to asynchronous business rules, scheduled jobs, or integrations that use the Import Set or Transform Map flow. 11. Be careful with complex OR conditions A long list of OR conditions, especially on different fields, can make it hard for the database to use an index. Break the query into smaller, focused filters or use report sources and database views when the logic becomes complex. 12. Keep business rules and workflows lightweight Business rules run on record load, insert, update, or delete. A single heavy query inside a business rule can slow down every form save or list load. Query business rules run for every record displayed in a list, so they should be especially selective. Move expensive logic to asynchronous business rules, scheduled jobs, or flows when real-time processing is not required. 13. Separate client-side from server-side slowness Not all slowness comes from the database. If the server response time in Transaction Logs is low but the page still feels slow, the delay is likely in the browser. Common client-side causes include too many related lists, embedded reports, large forms, and client scripts that make many server calls. Use your browser's developer tools to inspect network timings and compare them to the server-side transaction log. MariaDB and Postgres on ServiceNow ServiceNow manages the database engine for you. Your instance may run on MariaDB or Postgres (RaptorDB), but the platform best practices above are the same for both. The database engine, index structures, and statistics maintenance happen behind the scenes. If you open a support case for a slow query, ServiceNow support can inspect the engine-specific details and request any needed maintenance. The main differences customers may notice are: Case sensitivity: Some text searches may behave differently. Test filters and reports after an instance move or upgrade if search results change unexpectedly. Full-text search: Both engines support platform full-text search, but the relevance ranking and supported operators can differ slightly. JSON fields: Postgres has stronger native support for JSON data, but most ServiceNow functionality abstracts this away. Diagnosing Slow Performance Identify slow lists and reports by reviewing transaction times in System Diagnostics > Transaction Logs . Check whether slow pages have many related lists, embedded reports, or client scripts making server calls. Review scheduled jobs and business rules that run on the same table; a single expensive script can affect the whole instance. If a query was fast before and is now slow after a data import or upgrade, mention that in your support case. The issue may be related to database statistics that support can refresh. Testing and measuring changes Test filter, script, and business rule changes in a sub-production instance first. Measure response times before and after the change using the same list, report, or form. Compare server time in Transaction Logs with browser network timings from developer tools. Validate that the change does not break existing functionality, especially for integrations and workflows. When to open a support case Open a support case when you have ruled out common platform-level causes and the issue persists. Include as much of the following as possible: The exact list, form, report, or script that is slow. The table involved and any filters or query conditions being used. Transaction IDs or screenshots from System Diagnostics > Transaction Logs . Whether the slowness is constant or happens at specific times, and whether it affects all users or only some. Any recent changes such as upgrades, data imports, new business rules, scheduled jobs, or integrations. Whether the issue started after an instance move, upgrade, or database maintenance window. Related Resources KB1117320 - Creating Good Database Indexes KB0963717 - StatsNow Pattern Dashboards KB1431403 - Postgres Training KB0859424 - ALERT: Abnormal SQL Response Time ServiceNow Product Documentation Data Management KB2157922 - DM Table Cleaner KB2023435 - DB Compaction KB1640606 - Table Cleaner KB1313254 - Glide List: Array Denormalization KB1122144 - Data Archiving KB0547695 - Archiving - FAQ