Who is this article for?

  Database Admin responsible for the Please Review database.

  No specific access is required for this guide

This article won't provide specific rules on managing your database—that's the job of your local Database Administrator (DBA). However, it will offer guidance on maximizing your database's effectiveness for use with Please Review.

1. Overview

Addressing document download challenges requires thorough investigation, as causes can vary. During a Please Review installation, a schema is created in a database, either Microsoft SQL Server or Oracle. Please Review includes scripts to create and remove schemas for both database types. These scripts establish base tables, appropriate indexes, procedures, and populate the schema with some default data. Upgrades may modify schema objects through version-specific upgrade scripts.

The goal of this document is to guide DBAs on optimizing database performance for Please Review schemas and provide actions to maintain performance in specific environments.

1.2 Database server

Please Review supports both Microsoft SQL Server and Oracle. Hosted customers will have their schemas in a SQL Server database, while on-premise customers use both, with the majority using SQL Server. Please Review is flexible regarding the database it runs against, supporting multiple versions and types.

Whether using Oracle in a pluggable database model, or SQL Server as an instance on Windows or a managed offering over Azure or AWS, the critical requirement is support for indexes and procedures suitable for the workload. To clarify with some examples, an Azure Database as a Service or an AWS Managed Relational Database Service will also work, provided it meets those requirements mentioned above.

1.3 Database types

It's important to note that determining database suitability is not the job of Please Review or Ideagen support staff. Different customers have different needs. Some may run a small number of reviews for a limited user population, in which case a simple SQL Server Express install is sufficient. Others may run hundreds of reviews per week for thousands of users, requiring a separate database server.

1.4 Potential choke points

In a small, local database, there may be minimal DBA activity required. Network performance isn't a constraint as components are on the same machine. However, in larger environments, various factors can become choke points. If the database server is used by many applications, is it adequately resourced? Are auto-tuning settings enabled for indexes? Do local DBA policies conflict with Please Review schema creation? These questions should be addressed with the DBA before installation. If any questions arise, discuss them with Ideagen Support.

If corporate policy mandates a specific database server not co-located with the Please Review server, users may experience network delays. An under-resourced network or bandwidth contention can also lead to slower performance. In such environments, working closely with the local DBA and IT department is key to ensuring success.

2. Indexes

Please Review ships with predefined indexes for both database platforms, created through extensive internal testing and customer feedback. These indexes offer optimal performance across various customer environments.

2.1 Adding indexes

While some customers may benefit from creating additional indexes, in most cases, extra indexes can degrade performance. Indexes require resources to maintain, and every table action may need updating all indexes. Ideagen does NOT recommend adding indexes without direct instruction from Ideagen staff.

2.2 Index maintenance

In smaller systems, indexes may remain healthy throughout the Please Review installation. However, larger, busier systems may need maintenance to avoid performance decline due to index fragmentation. As entries are added and removed, fragmentation increases, and performance drops. The local DBA should monitor and defragment indexes as needed.

3. Backup and recovery

Please Review depends on its database; if lost or damaged, the product won’t work, and data will be lost. Regular database backups are essential for point-in-time restores in case of issues. This is critical during upgrades but also good practice during normal operation. Hosted customers are covered by Ideagen, but on-premise customers must ensure the local DBA has a backup strategy including the Please Review schema.

4. Summary

The Please Review database schema is designed for optimal performance in most customer situations. Years of performance testing and feedback have refined the indexes. Any necessary customizations should be done in consultation with Please Review support. Good database management will resolve most performance issues.