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.

[Chaitanya Redd1]

Hi,

To troubleshoot the issue, you can try the following steps:

Check the server and database configuration of both instances to see if there are any differences that can affect the performance of the queries.

Check the query execution plan to see if there are any differences in the way the database is executing the queries.

Check the data distribution of the tables to see if there are any differences that can affect the performance of the queries.

Check the workload of both instances to see if there are any differences that can affect the performance of the queries.

By analyzing these factors, you can identify the root cause of the performance issue and take appropriate actions to improve the performance of the database queries.

[sainath3]

Sorry Chaitanya,

Can you guide me how to check/test the last 3 steps.

[Amit Gujarathi]

Hi @sainath3 ,
I trust you are doing great.

1. Analyzing the database query and identifying areas for optimization. This could involve adding indexes to the database tables being queried, rewriting the query to use more efficient join conditions, or ensuring that the query is optimized for the database engine being used.

2. Using the GlideRecord API to limit the number of records being queried and displayed on the screen. This could involve setting a limit on the number of records returned by the query, or using pagination to display records in smaller batches.

3. Using the GlideAggregate API to aggregate data and display summary information instead of displaying all records. This could involve using functions such as COUNT, SUM, and AVG to display summary information, rather than displaying all records.

4. Using the Performance Analytics module to analyze the performance of the database view table and identify areas for optimization. This could involve analyzing the performance of the query and identifying areas where the query is taking too long to execute, or identifying areas where the database view table is being used inefficiently.

By following these steps, it should be possible to optimize the performance of the database view table and reduce the amount of time it takes to load.