How can I improve database view performance?

[Camila Godoy]

Hello team,

I have created a database view that is producing low performance when loading.

The goal of my assignment was to tie a business app tagged in the cloud to its cloud account. The tags are created in the cmdb_key_value table. 

• This requests us to have a junction of a string field to a numerical field such: value (from cmdb_key_value) to number (from cmdb_ci_business_app)
• I also added fields to narrow down the data: key='utan' and entered the sys_id of the relationship I wanted between the CI and the data center.

Please let me know if you would have any tips that I could use to improve performance on this db view?

thank you!

Camila

[Brad Bowman]

Hi Camila,

I trust that, though delayed, you are seeing the correct results?  How many results are in the view?  Is there any or a few of these tables that have quite a lot of records?  This seems counter-intuitive, but have you tried eliminating the second part of the Where clause on kv and/or rel to see if the view itself is generated more quickly - albeit with more results than you wanted?  If that works then you can create a before Query Business Rule to incorporate key='utan' and whatever the second part of rel is doing.  The other thing that comes to mind without following the trail in a real example is if there is any way to get rid of rel2 and ca.  Keep in mind that as long as you get or can filter down to the correct results, you don't necessarily need to include every table in the view that you need to see data from.  This is because you are using the cmdb_rel_ci table that includes 2 fields that are references to the cmdb_ci table, so by configuring the List Layout you can dot-walk to display fields from the parent and/or child fields rather than displaying those same fields by adding the tables to the view.

[sainath3]

Dear Camila Goday

While dealing with Database View, make sure to understand what Database index is and what is the purpose,

Database index configuration is very important for Database view.

Majorly it reduced the loading time.

[Amit Gujarathi · Accepted solution]

HI @Camila Godoy ,
I trust you are doing great.

To improve performance on your database view, there are several things you can consider:

1. Review the indexes on the tables used in your view. Indexes can significantly improve the performance of queries. Ensure that there are indexes on the fields used in the join condition and in the WHERE clause of your view.

2. Limit the number of fields included in the view to only those that are necessary. Including unnecessary fields can cause unnecessary processing and reduce performance.

3. Ensure that the SQL generated by the view is optimized. You can use the GlideAggregate API to optimize SQL queries.

4. Avoid using the sys_view table directly. Instead, use the sys_db_view table to create database views. The sys_db_view table is optimized for database views and can provide better performance.

5. If possible, consider using the "Execute as User" option in the view definition. This option can help to reduce the number of rows returned and improve performance.

6. Consider using server-side scripting to improve performance. ServiceNow provides several server-side scripting options, including Business Rules, Script Includes, and Scheduled Jobs. These options can be used to perform complex operations and improve the performance of your views.