ProjectWise SELECTseries 3 Database Upgrade Time

How Long Should a ProjectWise Database Upgrade Take? 

Upgrading to ProjectWise V8i (SELECTseries 3) requires that you convert the database schema of your existing ProjectWise database to meet the requirements of ProjectWise V8i (SELECTseries 3).

Database conversion may be relatively quick (about 15 minutes or less), or it could take much longer due to the following 3 things:

• The database has a large number of audit trail records 
• The database has a large number of thumbnail image records (if upgrading from V8 XM Edition)
• The database statistics are not up-to-date

1) Audit Trail Records
Audit trail conversion time primarily depends on the total number of audit trail records in the database which includes:
a) The records in the dms_audt table
b) The records in the audit trail truncation table (if you have one).

In general, you can expect about 1 to 3 minutes for each 1 million audit trail records in the database.

A secondary factor that may further impede audit trail conversion time is the number of document and/or user records in the database. In other words, a large number of document and/or user records in the database will cause the audit trail conversion to take longer than if there were not a large number of document and/or user records in the database.

• Please Note: The number of document and/or user records alone will not add time to the general database conversion time. Only when combined with a large number of audit trail records will the number of document and/or user records play a role in the database conversion time.

2) Thumbnail Image Records 
When upgrading from ProjectWise V8 XM Edition, thumbnail image record conversion may also add to the database conversion time. Thumbnail conversion time depends solely on the number of rows in dms_thumb table. In general, you can expect about 100 minutes for each 1 million thumbnail image records in the database.

3) Statistics
Out-of-date database statistics can turn what would otherwise be a relatively quick upgrade into one that takes hours to complete. Always make sure you update your database statistics prior to upgrading the database.

• Please Note: It is always recommended to run the dmsconv process in a test environment before your upgrade to determine if there will be any issues. In certain cases the new dmsconv process has been known to utilize 100% CPU usage while running the dmsconv process. This will cause a performance issue if other systems are using databases located on the same database server.

For example, when running the DMS Convert on a SQL Server database which contains a very large Audit Trail Truncation table (to the order of magnitude of: 15,000,000 or more) you could see the database consuming 100% of the CPU on the database server, giving the perception of a hung DMS Convert Process.

The offending SQL Statement is:

insert into dms_audt_convN select rv.o_objguid, rv.o_name, rv.o_desc, rv.o_parentguid 
from dms_remv rv 
where rv.o_objguid is not null and not exists 
(select null from dms_proj p where p.o_projguid=rv.o_objguid) and not exists 
(select null from dms_doc d where d.o_docguid=rv.o_objguid) and exists 
(select null from dms_audt_convBckp a where rv.o_objguid=a.o_objguid)

Solution:

There are two possible workarounds depending on the need to retain the Archived Audit Trail data.

1) If this data is no longer needed or only needs to be kept for audit purposes, backup this table and then remove it from the database. Removing it from the database will prevent the conversion process trying to convert this table.

2) If the table is actively used and needs to be retained the following index should be added to the table prior to attempting the DMS Convert.

CREATE NONCLUSTERED INDEX [i_dms_audt_4] ON [dbo].[TABLENAME] 
(
[o_objguid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

Replace the with the actual table name. If the server is already shut down for conversion purposes this table name can be found in the dms_gcfg table with the following query:

select o_textval from dms_gcfg where o_paramno = 119

The following tables illustrate conversion times taken from real-world databases: