Database Views Best Practices

[andy m_1]

I'm looking for best practices with creating database views in terms of performance in respect to the order of the tables and/or performance of different where clause options. 

The best article I could find was THIS ONE, but no one really answered the person's #2 question with the specifics I'm looking for.  What's the best way to order the tables in a view with many tables?  Largest table to smallest?  Smallest to largest?

The OOB database view "Change Request Affected CIs" performs reasonably well.  It has the following tables and joins

TableName	Order	Variable Prefix	Join Query	Number of Records in Table
cmdb_ci	100	ci		11.2 million
task_ci	200	task	task_ci_item=ci_sys_id	19.8 million
change_request	300	chg	chg_sys_id=task_task	0.36 million

 I added a 4th table to this view and it negatively impacted the performance significantly.  Actually, it nearly tripled or quadroupled the response time.  Below is how I configured it on my first attempt. 

*Note* the field "u_ci" is a reference field to the cmdb_ci.

u_ci_watcher

400

watcher

watcher_u_ci=task_ci_item

0.5 million

I then tried different combinations of orders between the tables and haven't yet had any luck.   

Hoping to find some suggestions on if and how I can make this view perform better.

Thanks in advance!

[Community Alums]

Have you added the columns to be exposed for the fourth record? If not do that or you cant se them. Also try to join to the table with the common key to the rest of the tables.

Joro

[andy m_1]

yes, i've added columns to be exposed for the fourth record.  

What do you mean by "common key"?  the tables do not all share a single common key.

[Community Alums]

Ok,

I see. 

u_ci_watcher

400

watcher

watcher_u_ci=task_ci_item

0.5 million

[Adam Stout]

Why do you need a database view at all?  Can you just report from the task_ci table?

The performance has the potential to be b will be bad if you are trying to render all of this data with no filters since the database may take bad path.   You can check the path in Slow Queries.