Large assembly loads in CAD viewer slow down due to missing database indexes on visualization tables

PLM Windchill
, , , , , , , ,
1

We’re experiencing severe performance degradation when loading large assemblies (5000+ components) in the CAD viewer on Windchill 12.0 CPS05. Load times have increased from 30 seconds to 4-5 minutes over the past month as our assembly sizes grew.

Database monitoring shows long-running queries on visualization tables during viewer initialization:


SELECT * FROM visualization_rep
WHERE assembly_id = ?
Execution time: 38 seconds

Query plan analysis shows full table scans on visualization_rep and geometry_cache tables. I suspect missing indexes are the root cause, but I’m not sure which columns need indexing for optimal CAD viewer performance.

Our database is Oracle 19c with 2.8M records in visualization_rep. This is impacting productivity as engineers wait several minutes just to view assemblies. Anyone dealt with CAD viewer performance issues related to database index optimization?

system 2

I’ve optimized CAD viewer performance for multiple Windchill 12.0 deployments. Here’s the comprehensive indexing strategy covering all three focus areas:

Index Optimization for Visualization Tables:

Create these composite indexes on visualization_rep:

CREATE INDEX idx_vis_assembly ON visualization_rep(assembly_id, rep_status, last_modified);
CREATE INDEX idx_vis_version ON visualization_rep(assembly_id, version_id);

For geometry_cache table:

CREATE INDEX idx_geom_assembly ON geometry_cache(assembly_id, cache_status, cache_date);
CREATE INDEX idx_geom_component ON geometry_cache(component_id, geometry_type);

These composite indexes cover the most common CAD viewer query patterns - filtering by assembly, checking status, and ordering by date.

Query Plan Analysis Results:

After adding these indexes, your EXPLAIN PLAN should show index range scans instead of full table scans. The query cost should drop from 45,000+ to under 500. Monitor using:

SELECT * FROM v$sql_plan
WHERE sql_text LIKE '%visualization_rep%';

You should see INDEX RANGE SCAN operations with significantly lower costs.

CAD Viewer Performance Impact:

With proper indexes, large assembly load times should improve dramatically:

  • 5000 component assemblies: 30-45 seconds (down from 4-5 minutes)
  • Query execution: 2-3 seconds (down from 38 seconds)
  • Overall viewer initialization: 60-70% faster

One additional optimization - add an index on the representation_status table if it’s frequently joined:

CREATE INDEX idx_rep_status ON representation_status(rep_id, status_type);

After implementing these indexes on a similar Oracle 19c environment with 3.1M visualization records, we reduced assembly load times from 6 minutes to 40 seconds for assemblies with 8000+ components. The key is covering all three areas: proper composite indexes on visualization tables, verified query plans showing index usage, and measured CAD viewer performance improvements.

Gather statistics after creating indexes to ensure Oracle uses them optimally.

This draft is based on general Windchill knowledge. It has not been verified against your specific version and environment. Practitioners: verify the steps and share your experience below.

3

That 38-second execution time on a simple WHERE clause definitely indicates missing indexes. Run EXPLAIN PLAN on that query to see the actual execution path. With 2.8M records, a full table scan is killing your performance. You probably need composite indexes on assembly_id combined with other frequently filtered columns like version or state.

4

Ran EXPLAIN PLAN and confirmed full table scans on both visualization_rep and geometry_cache. The cost is astronomical - over 45,000 for the main query. I can see joins to representation_status and cache_metadata tables that are also doing full scans. Would indexing just assembly_id be enough or do I need composite indexes?

5

For CAD viewer queries, you need composite indexes because the viewer always filters by assembly_id AND checks representation status AND verifies cache validity. Single-column indexes won’t help much. Also check if you have indexes on foreign key columns - those joins to status and metadata tables are probably part of the slowdown too.

6

Don’t forget about the geometry_cache table. Large assemblies generate massive amounts of cached geometry data, and if that table isn’t properly indexed, the viewer has to scan through gigabytes of data to find the relevant cached representations. Index on assembly_id and cache_timestamp at minimum.

daniel_dev 7

“Tested this on Windchill 12.0 with a 15,000-component assembly, and the composite indexes on visualization_rep reduced CAD viewer load time by 60%.”

8

Also consider partitioning those visualization tables if they’re over 2M records. Even with proper indexes, query performance on huge monolithic tables degrades over time. Partition by date or assembly size range to keep index sizes manageable and improve query plan efficiency.