Adding indexes to sample tables
UNICOM Intelligence Interviewer - Server indexes any sample tables that it creates to provide improved performance on projects that use sample management. If you create sample tables manually outside UNICOM Intelligence Interviewer - Server, you can set the same indexes manually on those tables to achieve the same performance gains. If you have sample tables created with UNICOM Intelligence Interviewer - Server 3.1 or earlier, you might want to add the indexes to those tables too.
You can also use these instructions as guidelines if you want to index new fields that you add to sample tables.
1 Start the SQL query analyzer: Open SQL Server Management Studio, and then select the New Query feature.
2 Connect to your UNICOM Intelligence Interviewer - Server database server.
3 In the list of databases, select the sample database whose tables you want to index.
4 In the Query window, type the following commands. Repeat the commands for each sample table you have created manually.
For tables that are used for Web (inbound) and/or telephone interviewing (outbound) projects:
CREATE NONCLUSTERED INDEX <name>_idx_id_active ON <table_name> (Id, Active)
CREATE NONCLUSTERED INDEX <name>_idx_queue ON <table_name> (Queue)
CREATE NONCLUSTERED INDEX <name>_idx_active ON <table_name> (Active)
Tables that are used for telephone interviewing projects need the following additional indexes:
CREATE NONCLUSTERED INDEX Participants_idx_id_active ON (Id, Active)
CREATE NONCLUSTERED INDEX Participants_idx_queue_sortid ON <table_name> (Queue, SortId, ExpirationTime)
CREATE NONCLUSTERED INDEX Participants_idx_queue_appointmenttime ON <table_name> (Queue, AppointmentTime, ExpirationTime)
CREATE NONCLUSTERED INDEX Participants_idx_queue_recalltime ON <table_name> (Queue, RecallTime, ExpirationTime)
5 If you also want to index the associated history tables, type the following for each history table in the database:
CONSTRAINT Participants_<project_name>_HistoryTable_pk PRIMARY KEY CLUSTERED (Id)
CREATE NONCLUSTERED INDEX Participants_<project_name>_HistoryTable_idx ON <table_name> (SampleId)
CREATE NONCLUSTERED INDEX Participants_<project_name>_HistoryTable_idx_starttime ON <table_name> (StartTime)
6 Press F5 or click the Execute Query toolbar button (the green triangle).
7 If you have sample tables in more than one database, select the next database from the list and repeat these commands for the tables in that database.
8 Save the changes and exit.
See also