Database view table taking too much time to load

[sainath3]

Hi Team,

I have created a Database view of combing 3 tables.

In Dev instance it is taking 5 sec to load the records but in test instance it is taking more than 5 minutes.

Note: In both the instances no.of records are same in those 3 tables, even though.it is taking more time in Test instance.

Can you please help me here.

Dev instance: 

Test instance:

Here in 3rd table matching fields are String data type.

[DrewW · Accepted solution]

Check for database indexes on the field you are joining on.  Ultimately the fastest way to find out is to log a case with support and ask them to recommend ways to make the view faster.  They have tools to analyze the query and tell you what indexes will speed things up if any.

[DrewW]

I'm not talking about text index for the table.  I'm referring to field level database indexes.

https://support.servicenow.com/kb?id=kb_article_view&sysparm_article=KB0870445

https://docs.servicenow.com/en-US/bundle/utah-platform-administration/page/administer/table-administ...

If you are unfamiliar with database indexes and ServiceNow a google search will bring up a number of videos and blogs that talk about them.

[sainath3]

Thanks Drew.

I will refer the docs & Videos.

I tried with different table (billing table) instead of sap table.

now it is taking 30 sec to load.

in both the tables (billing & sap) am referring same field asset_tag.

what would be the issue.

why sap table is taking more time.

No.of records in sap table: 9k.

No.of records in billing table 12k.

Note: Requirement is to take sap table.

[DrewW · Accepted solution]

Check for database indexes on the field you are joining on.  Ultimately the fastest way to find out is to log a case with support and ask them to recommend ways to make the view faster.  They have tools to analyze the query and tell you what indexes will speed things up if any.

[sainath3]

Ok Drew.

I will check the database indexes and will let you know.

I have one more day to find out the solution, even if I don't find the solution, I will raise a HI ticket.

[sainath3 · Accepted solution]

Thanks Drew, Its working now.

As you said I have gone through videos on Database indexes, and I configured in the instance.

Now the table is taking less than 5seconds.