Having a product catalogue is like having a pet, it needs attendance.

You are however on the right track:

• Remove duplicates: Clearing the 29K duplicate product-category relations will immediately reduce node load.
• Prune unused content: Archive or delete discontinued products, orphaned categories, and other outdated entries.
• Regularly audit the catalogue tree.
• Use tags or flags to identify unused/archivable items.
• Implement deduplication logic in your ERP import pipeline.

Content structure is sometimes tricky. It is important to remember that structure IS metadata. As you mentioned your structure is split between site-specific and shared. But in general

• Keep shared products in a "Global Products" catalog.
• Sites reference global products via relations or links, not duplication. Use content relations or tagging for associating products with multiple sites.
• Use the catalogue tree as your logical taxonomy and break things apart where it makes sense. E.g.
  • Electronics, Apparel
  • North America, Europe, Asia

Good luck!