The Epicor ERP database primarily uses rowstore indexes by default, as these are optimized for the transactional workloads typical of ERP systems, such as frequent CRUD (Create, Read, Update, Delete) operations. Rowstore indexes, often implemented as B-tree structures, are well-suited for the direct row access patterns common in ERP applications like Epicor, where queries often retrieve specific records using selective filters or primary keys.
However, columnstore indexes can be used in Epicor ERP for specific scenarios, particularly for reporting or analytical queries that involve large datasets and aggregations. Since SQL Server 2012, columnstore indexes have been available, and Epicor ERP systems running on SQL Server can leverage nonclustered columnstore indexes (NCCIs) for performance improvements in data warehouse-like workloads. These indexes are not part of the standard Epicor installation or upgrade process but can be added as a tuning option for large tables (typically over 10 million rows) to enhance reporting performance. Creating an NCCI in Epicor requires using the SAP report MSS_CS_CREATE, but it’s not transportable across systems and must be created separately in each environment.
Key Considerations:
- Rowstore Indexes: Default for Epicor ERP due to its transactional nature, ensuring efficient point lookups and updates.
- Columnstore Indexes: Optional for large-scale reporting or analytical queries, but they may negatively impact transactional throughput due to maintenance overhead. They are particularly useful for static data or large fact tables, similar to those in SAP BW, but not as a replacement for a dedicated data warehouse system.
- Implementation: Adding a columnstore index is a consulting project, requiring careful selection of suitable tables and performance testing, as it can affect transactional performance.
In summary, Epicor ERP primarily relies on rowstore indexes for its core transactional operations, with columnstore indexes as an optional enhancement for specific analytical reporting needs, particularly on large tables. Always test and validate the impact of columnstore indexes in a non-production environment before applying them to a live Epicor system.
