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]

Make sure there are indexes created for the fields that you are joining on.  If they are not there then create them and see if that helps.

[sainath3]

No Luck Bro😥

In both the instances there having same index true.

[DrewW]

So sap_u_asset has a database index set for it?  That is usually not the case for a text field.  I'm assuming its a text field because asset_asset_tag is a text field and I'm reasonably sure the alm_asset table has a database index for the asset_tag field.

[sainath3]

For sap table no index is set for both the instances.

alm_asset: Test instance

alm_asset: Dev instance: