Skip to content
العربية

Suggest Indexes for Large Detail Tables

This article provides guidance on identifying large detail tables in your database that are missing essential indexes on their foreign key columns, which may lead to performance issues when selecting records.

Why This Matters

In Nama ERP and other systems with highly normalized schemas, many tables contain detail lines (child records) linked to parent headers via a foreign key column (e.g., qtyTrans_id, stockAgesTrans_id). If these columns are not indexed, queries involving these detail lines—especially during report generation or document loading—can be significantly slower.

To optimize performance, it's recommended to create a non-clustered index on such foreign key columns only if the detail table contains a significant number of rows.

How to Use This Query

The following SQL query searches for tables in your database that:

  • Contain large numbers of records
  • Have a known foreign key column to the parent table
  • Do not already have an index on that foreign key column

The query outputs a list of tables with suggestions for creating the needed index.

You should only apply the suggested indexes to tables where row_count exceeds your threshold, e.g., 50,000 or 100,000 records.

table_namecolumn_namerow_countcreate_index_sql
StockAgesTransLinestockAgesTrans_id37537276CREATE NONCLUSTERED INDEX IX_StockAgesTransLine_stockAgesTrans_id ON dbo.StockAgesTransLine(stockAgesTrans_id);
QtyTransLineqtyTrans_id31629670CREATE NONCLUSTERED INDEX IX_QtyTransLine_qtyTrans_id ON dbo.QtyTransLine(qtyTrans_id);
CostOutTransLinecostOutTrans_id26216977CREATE NONCLUSTERED INDEX IX_CostOutTransLine_costOutTrans_id ON dbo.CostOutTransLine(costOutTrans_id);
StockTakingDetailsElecLinestockTakingDetails_id2365CREATE NONCLUSTERED INDEX IX_StockTakingDetailsElecLine_stockTakingDetails_id ON dbo.StockTakingDetailsElecLine(stockTakingDetails_id);

From this result:

  • You should create the index for StockAgesTransLine because it has 37 million rows.
  • You should not create the index for StockTakingDetailsElecLine because it only has 2,365 rows.

Recommendations

  • Review the result of the query periodically, especially after new document types or detail tables are introduced.
  • Do not create indexes on small tables (e.g., less than 10,000 rows) unless you observe actual performance issues.

SQL Query