Cisco Cisco Unified Customer Voice Portal 11.0(1)

Writing Efficient SQL when Creating Reports

The following guidelines should be kept in mind.

•

When writing SQL, developers must organize their WHERE clauses and put the most
important join first. The most important join is the one that will reduce the size of the dataset
to the least amount of rows.

•

Reports must be written so that every field in the WHERE and ORDER BY clauses uses an
indexed field.

•

The second column in a composite index should never be used in a JOIN statement without
the first column.

•

Engineers writing database code should treat database, table, and column names as case
sensitive, even though the current database is case insensitive, to ensure that the application
is portable.

•

Many operations hold database locks; therefore, reports should use a wait time of 30 seconds,
if possible.

•

Reports and SQL queries should set Isolation level to dirty read. This is much more efficient
for the database as it utilizes Informix Virtual shared memory instead of the resident memory
portion of shared memory.

•

Do not ever set isolation level to repeatable read.

•

Do not ever write a SQL statement that selects into temp without specifying the 'no log'
option.

•

The internal ID generator limits the amount of total VXML subsystems to 8,000 per
deployment.

Database Sizing Issues

See the discussion in 

. Also see the Planning Guide for Cisco Unified

Customer Voice Portal and the Cisco Unified Customer Voice Portal (CVP) Release 4.0 Solution
Reference Network Design (SRND) document.

Reporting Guide for Cisco Unified Customer Voice Portal Release 4.1(1)