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!

[andy m_1]

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

• No, I'd like to be able to create filters for where the created date of the record in the custom watcher table was created after the planned start date of the change request, and before the planned end date.

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.

• I'm testing the view filtered down to only a week or two of data at a time.  Question - I wonder if it's be helpful to attempt to perform this filtering in the where clause of my tables in the dbview?
• What do you mean by a bad path?  can I control this and create a better path?
• I found examples in the Slow Queries tab.  Tx, btw.  never knew that existed.

Here's the example query from the slow queries record from a query I performed with a filter on 10 day window

SELECT ci.`sys_class_name` AS ci_sys_class_name, watcher.`sys_created_by` AS watcher_sys_created_by, watcher.`u_field` AS watcher_u_field, ci.`ip_address` AS ci_ip_address, watcher.`sys_created_on` AS watcher_sys_created_on, chg.`sys_domain` AS chg_sys_domain, chg.`state` AS chg_state, chg.`u_subcategory` AS chg_u_subcategory, ci.`serial_number` AS ci_serial_number, watcher.`u_ci` AS watcher_u_ci, chg.`end_date` AS chg_end_date, watcher.`u_change_type` AS watcher_u_change_type, chg.`short_description` AS chg_short_description, chg.`number` AS chg_number, chg.`sys_id` AS chg_sys_id, ci.`u_environment` AS ci_u_environment, chg.`sys_class_name` AS chg_sys_class_name, ci.`sys_id` AS ci_sys_id, chg.`assignment_group` AS chg_assignment_group, task.`sys_id` AS task_sys_id, ci.`install_status` AS ci_install_status, watcher.`u_new_value` AS watcher_u_new_value, task.`ci_item` AS task_ci_item, watcher.`u_old_value` AS watcher_u_old_value, chg.`start_date` AS chg_start_date, watcher.`sys_id` AS watcher_sys_id, ci.`name` AS ci_name, ci.`sys_domain` AS ci_sys_domain, chg.`category_1` AS chg_category, ci.`sys_domain_path` AS ci_sys_domain_path, ci.`u_desired_operational_state` AS ci_u_desired_operational_state, chg.`approval` AS chg_approval, watcher.`u_ciwatcher` AS watcher_u_ciwatcher, watcher.`u_incident` AS watcher_u_incident, ci.`u_associated_mnemonic` AS ci_u_associated_mnemonic, task.`task` AS task_task, sys_user_group1.`name` AS chg_assignment_group_name FROM ((((((cmdb ci  INNER JOIN cmdb$par2 ci_cmdb_par2 ON ci.`sys_id` = ci_cmdb_par2.`sys_id` )  INNER JOIN cmdb$par1 ci_cmdb_par1 ON ci.`sys_id` = ci_cmdb_par1.`sys_id` )  INNER JOIN task_ci task ON task.`ci_item`  = ci.`sys_id`  )  INNER JOIN u_ci_watcher_log watcher ON watcher.`u_ci`  = ci.`sys_id`  )  INNER JOIN task chg ON chg.`sys_id`  = task.`task`  AND chg.`sys_class_name` IN ('change_request','u_permit','u_permit_to_design2','u_technology_standards_sub_committee','u_pnc_ptx_cyber','u_pnc_ptx_exception','u_pnc_ptx_test_strategy','u_pnc_ptx_infrastructure_specs','u_pnc_ptx_design','u_permit_to_operate2','u_permit_to_build2','change_request_imac') )  LEFT JOIN sys_user_group sys_user_group1 ON chg.`assignment_group` = sys_user_group1.`sys_id` )  WHERE ci.`sys_class_path` LIKE '/!!%' AND (watcher.`sys_created_on` >= '2020-10-18 04:00:00' AND watcher.`sys_created_on` <= '2020-10-30 03:59:59') limit 0,100

[Adam Stout]

1) Try creating a view just with u_ci_watcher and task_ci and dot walk in the rest of the data.  Since neither table has a unique key being referenced, is there a possibility that you are get some default value (like blank) being joined to many records?

2) If you can filter in the view as part of the join, do it.  However, date isn't dynamic so that can be challenge

3) Bad plan, meaning the database isn't optimizing well.  There is not much we can do here.

4) An important tool for optmization.

[andy m_1]

Unfortunately, I can not go without the change request table & the watcher table in order to solve my problem.

I tried another route to reduce the amount of records.  I removed the cmdb_ci table, but unfortunately it didn't seem to help much.  I'm beginning to think that it's not possible to do what I'm trying to do within the current sn capabilities.

[Adam Stout]

I'm not following, why do you need change_request?  You can dot-walk to all the changes via task_ci.  All of the change_request (and cmdb_ci) fields should be available.

Can you share the schema for u_ci_watcher_log?  The word log in that table concerns me.  How many entries for each CI are there in that table?

[andy m_1]

I'm not sure that I'm allowed to share the schema, but I should be able to help you understand with a little background of our custom Unauthorized Change program.

CI Watcher Log table is a custom table used to store unauthorized changes (changes made outside of CR window) to CI attributes.  We determine which fields to monitor for changes based on a separate monitoring rule table. 

Here's a simplified rundown of the ci watcher table & it's purpose.

Mon rule table fields:  class name, attribute name, and environment pick list.  Ex:  win server, name, production.  If any changes are made to a production win server's name field, it gets logged into the ci watcher log table (u_ci field stores the reference to the win server ci).  A script runs the following morning to check whether or not all the logs created were made during a change request window.  If it was created outside the change window, then an unauthorized change task is created.

That's a very simplified explanation of all the in's and out's of the ci watcher table, but there's troubleshooting that needs to be performed, and comparing the date of the CI Watcher log creation vs change request windows which the CI was added is the goal of the db view.

simply joining the watcher table and the task_ci table won't allow me to gain that visibility.