Query Development

Purpose and Objective: The SISS and OIT Offices have an obligation to protect the production database performance and availability. Queries run in the production database have been proven to have a significant impact on performance for all users and it is therefore appropriate for policies to be put into place to ensure that queries are developed and run in the correct database. These policies enable the SISS office to maximize system availability and performance for the entire user community.

Query Development in the Production Database

Queries to be run in Production must meet the following Requirements:
• The query is used to troubleshoot current (same day) processes or data for corrections.
• The query is used to monitor the daily processing of data
• The query is used in decision support and requires current date data for accurate results.

Query Policies:
• Queries not meeting the criteria above must be run in the report database.
• Queries meeting the above standards should be public queries to facilitate maintenance and updates. Private queries will be limited in number and be used to protect query templates and for sensitive data.
• A small number of qualified users are given the rights to develop ad-hoc queries for the above purposes. Current procedure and best practice to ensure system availability and performance recommend all queries first be developed in the report database. The query is tested and tuned for results and performance impact. Tested queries are then migrated to the production database. Ad-hoc queries are an exception, and for that reason should be developed on an emergency basis only.
• Query development will be monitored on a regular basis. Developers are responsible for regular maintenance on their queries, which involves modifications during database upgrades and regularly deleting obsolete queries.

Query Developer Rights Production Database

Users requesting query development rights in Production must meet the following requirements:
• SISS or OIT staff member
• Other qualified users who troubleshoot or diagnose problems with processes or data where access to current business day data is required.

Policies:
• Developers must adhere strictly to the production query development requirements, developing queries in the appropriate database based upon the need for current data.
• Developers must follow the production query development procedure: first writing and testing in the report database, then requesting a migration to the production database. Ad-Hoc query development will be limited to emergency situations to minimize the risk of a performance problem.
• Developers agree to fully document the purpose and use of public queries and to make useful troubleshooting and diagnostic queries public.
• Developers agree to make periodic reviews of public and private queries and to delete queries no longer in use, or to move queries to the report database when they no longer meet the production database standards.