Microsoft Knowledge Base Article
This article contents is Microsoft Copyrighted material.
©2005-©2007 Microsoft Corporation. All rights reserved.
Terms
of Use |
Trademarks
Article ID: 821801 - Last Review: January 16, 2004 - Revision: 1.2
You experience slow performance when you select a view in the Project Center in Project Server 2002
When you log on to a Microsoft Project Server Web Access
(PWA) Web site by using an Administrator account, it may take longer than you
expect to view the Project Center Web pages.
To work around this behavior, you must run the following
script. To do so, follow these steps:
- Start Microsoft SQL Query Analyzer.
- In the database drop-down box, select the Project Server
database.
- 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
- On the Query menu, click
Execute.
- When the query has completed, you may quit SQL Query
Analyzer.
Note You can roll back the change. To do so, use the following query:
/* ~~~~~~~~~~~~~~~~
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
Microsoft
has confirmed that this is a problem in the Microsoft products that are listed
in the "Applies to" section of this article.
APPLIES TO
- Microsoft Project Server 2002
Community Feedback System
Very often, it takes hours to solve a problem. Very often, you've looked high
and low, and have tried a lot of solutions. When you finally found it, chances
are, it was because someone else helped you. Here's your chance to give back.
Use our community feedback tool to let others know what worked for you and what
didn't.
Please also understand that the community feedback system is not warranted to be
correct, it's simply a system that we've built to let people try and help each
other. If something in a feedback response doesn't make sense to you, or you're
not comfortable making changes that the feedback talks about (like registry
edits), please consult a professional.
Thank you for using kbAlertz.com Feedback System.
-- Scott Cate