You experience slow performance when you select a view in the Project Center in Project Server 2002 (821801)

WORKAROUND

1. Start Microsoft SQL Query Analyzer.
2. In the database drop-down box, select the Project Server database.
3. In the new query, paste the following query:

   /* ~~~~~~~~~~~~~~~~
   
               QFE 8568 - ProjectCenter view optimization
   
      ~~~~~~~~~~~~~~~~ */
   
    
   
   /* Table MSP_WEB_CONVERSIONS */
   
   -- Remove old primary key
   
   if 0 < ( select count(*)
   
               from INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE
   
               where TABLE_NAME = 'MSP_WEB_CONVERSIONS'
   
                           and CONSTRAINT_NAME = 'PK_MSP_WEB_CONVERSIONS_STRING_TYPE_ID_CONV_VALUE_LANG_ID'
   
               )
   
               alter table MSP_WEB_CONVERSIONS drop constraint PK_MSP_WEB_CONVERSIONS_STRING_TYPE_ID_CONV_VALUE_LANG_ID
   
   go
   
   -- Add new primary key
   
   alter table MSP_WEB_CONVERSIONS
   
               add constraint PK_MSP_WEB_CONVERSIONS primary key clustered (CONV_VALUE, LANG_ID, STRING_TYPE_ID)
   
   go
   
    
   
   /* Table MSP_WEB_WORKGROUP_FIELDS_INFO */
   
   -- Remove old primary key
   
   if 0 < ( select count(*)
   
               from INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE
   
               where TABLE_NAME = 'MSP_WEB_WORKGROUP_FIELDS_INFO'
   
                           and CONSTRAINT_NAME = 'PK_MSP_WEB_WORKGROUP_FIELDS_INFO_CUSTFIELD_INFO_ID'
   
               )
   
               alter table MSP_WEB_WORKGROUP_FIELDS_INFO drop constraint PK_MSP_WEB_WORKGROUP_FIELDS_INFO_CUSTFIELD_INFO_ID
   
   go
   
   -- Remove old clustered index
   
   if 0 < ( select count(*)
   
               from sysindexes
   
               where id = object_id(N'MSP_WEB_WORKGROUP_FIELDS_INFO')
   
                           and name = 'I_CFINFO_FIELDID'
   
               )
   
               drop index MSP_WEB_WORKGROUP_FIELDS_INFO.I_CFINFO_FIELDID
   
   go
   
   -- Add new primary key
   
   alter table MSP_WEB_WORKGROUP_FIELDS_INFO
   
               add constraint PK_MSP_WEB_WORKGROUP_FIELDS_INFO primary key clustered (CUSTFIELD_INFO_ID)
   
   go
   
   -- Add new non-clustered index
   
   create nonclustered index I_MSP_WEB_WORKGROUP_FIELDS_INFO_FIELD_ID on MSP_WEB_WORKGROUP_FIELDS_INFO (FIELD_ID)
   
   go
   
   

4. On the Query menu, click Execute.
5. When the query has completed, you may quit SQL Query Analyzer.

/* ~~~~~~~~~~~~~~~~

            Rollback QFE 8568 - ProjectCenter view optimization

   ~~~~~~~~~~~~~~~~ */

 

/* Table MSP_WEB_CONVERSIONS */

-- Remove new primary key

if 0 < ( select count(*)

            from INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE

            where TABLE_NAME = 'MSP_WEB_CONVERSIONS'

                        and CONSTRAINT_NAME = 'PK_MSP_WEB_CONVERSIONS'

            )

            alter table MSP_WEB_CONVERSIONS drop constraint PK_MSP_WEB_CONVERSIONS

go

-- Add old primary key

alter table MSP_WEB_CONVERSIONS

            add constraint PK_MSP_WEB_CONVERSIONS_STRING_TYPE_ID_CONV_VALUE_LANG_ID primary key nonclustered (STRING_TYPE_ID, CONV_VALUE, LANG_ID)

go

 

/* Table MSP_WEB_WORKGROUP_FIELDS_INFO */

-- Remove new primary key

if 0 < ( select count(*)

            from INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE

            where TABLE_NAME = 'MSP_WEB_WORKGROUP_FIELDS_INFO'

                        and CONSTRAINT_NAME = 'PK_MSP_WEB_WORKGROUP_FIELDS_INFO'

            )

            alter table MSP_WEB_WORKGROUP_FIELDS_INFO drop constraint PK_MSP_WEB_WORKGROUP_FIELDS_INFO

go

-- Remove new non-clustered index

if 0 < ( select count(*)

            from sysindexes

            where id = object_id(N'MSP_WEB_WORKGROUP_FIELDS_INFO')

                        and name = 'I_MSP_WEB_WORKGROUP_FIELDS_INFO_FIELD_ID'

            )

            drop index MSP_WEB_WORKGROUP_FIELDS_INFO.I_MSP_WEB_WORKGROUP_FIELDS_INFO_FIELD_ID

go

-- Add old primary key

alter table MSP_WEB_WORKGROUP_FIELDS_INFO

            add constraint PK_MSP_WEB_WORKGROUP_FIELDS_INFO_CUSTFIELD_INFO_ID primary key nonclustered (CUSTFIELD_INFO_ID)

go

-- Add old clustered index

create clustered index I_CFINFO_FIELDID on MSP_WEB_WORKGROUP_FIELDS_INFO (FIELD_ID)

go