| Product(s): | ProjectWise Caching Server | ||
| Area: | Appl Svr-Integration Svr | ||
| Original Author: | Bentley Technical Support Group |
Problem
Poor ProjectWise performance importing documents with import/export tool
Solution
The environment had document code (serial number) specified. When importing document ProjectWise checks for code uniqueness using a SELECT count(*) … [the query] which in turn takes a long time to complete if there are many records (documents) in environment table as full table scan is performed. Adding indexes to fields used in the query does not help (as the query uses NVARCHAR2 strings, and environment table contains VARCHAR2 columns).
Resolution: Change column types in all environment tables to nvarchar2, one can limit scope to columns used in the query.
In detail following actions should be taken:
1. Find out the environment that causes performance problems [ENV]
2. Identify database column names used in document code [COL_LIST]
3. Go through all environment tables (changes in all environment tables are required for search to work, see Problem # 33752, Solution # 500000060558, TR # 254711)
a. Check table if it contains any column from COL_LIST and has type (varchar2), if not go to next environment table (step 3)
b. [OPTIONAL] Disable triggers for that table (if triggers are present and disabled, make sure that no one is going to use the database, to prevent data loss [because of missing triggers])
c. Change column type from varchar2 to nvarchar2. E.g.
alter table “{SCHEMA}”.”{ENV_TABLE }” modify ({COL_NAME} nvarchar2({COL_SIZE}))
d. [OPTIONAL] Reenable triggers
4. [OPTIONAL] If indexes are not created in ENV table, create required indexes
See Also
External Links
Bentley Technical Support KnowledgeBase
Comments or Corrections?
Bentley's Technical Support Group requests that you please confine any comments you have on this Wiki entry to this "Comments or Corrections?" section. THANK YOU!