Cisco Cisco Customer Voice Portal 8.0(1)

Since Unified CVP components do not themselves synchronize machine times, you must deploy

a cross-component time synchronization mechanism, such as NTP.

Writing Efficient SQL Queries when Creating Reports

Keep these guidelines 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.

•

Write reports so that every field in the WHERE and ORDER BY clauses uses an indexed
fi

eld.

•

A subset of the data that satisfies any given query can protect the user and the database from

generating massive data results. Including the word FIRST in select statements will return

only the amount of data requested. For example: 

.

•

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.

•

It is possible to capture gigabytes of Unified CVP data in a single day. Any query against

the database should target time ranges and subsets of data that will return in a reasonable

time. Datetime columns are crucial selections. Sorting or grouping large quantities of data

may exceed the capacity of the reporting server database as delivered.

•

All sessions that connect to the reporting database should initiate with two statements:

Examples: 

; 

.

This prevents reporting queries from interfering with CallServer message persistence and

improves the performance of reporting queries.

•

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.

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