We’re experiencing consistent timeout errors during purchase order creation when users search for materials. Our material master table contains over 2 million active records, and the lookup query is timing out after 30 seconds.
The issue occurs specifically during material search in transaction ME21N. Users enter partial material descriptions or characteristics, and the system performs a full table scan because the query lacks proper indexing on the search criteria.
Error message:
Database Query Timeout
Table: MARA (Material Master)
Operation: SELECT with LIKE clause
Timeout threshold: 30 seconds exceeded
Rows scanned: 2,147,863
This is blocking PO creation workflows and causing procurement delays. How can we optimize the material lookup query to handle our catalog size effectively?
I checked and we only have the primary index on MATNR (material number). No secondary indexes exist for description or material group fields. Users frequently search using partial descriptions with wildcards, which explains the full table scans.
Also review your material master data quality. If users are searching because material numbers aren’t standardized or descriptions are inconsistent, you’re treating a symptom rather than the root cause. Clean up duplicate or near-duplicate materials and implement better material numbering conventions to reduce search dependency.
Beyond indexing, consider implementing SAP’s text search functionality for material descriptions. Traditional LIKE queries with wildcards can’t use standard indexes efficiently. Text search creates specialized indexes optimized for partial string matching and can handle ‘contains’ searches that would otherwise require full table scans. This is particularly important for catalogs exceeding 1 million materials.
Don’t forget about the query timeout parameter itself. While 30 seconds is reasonable for most queries, material searches in large catalogs may legitimately need more time even with proper indexing. Check parameter rdisp/max_wprun_time and consider increasing to 60-90 seconds specifically for material lookup transactions.
Thirty seconds for 2 million rows suggests no index coverage on your search fields. You need a composite index covering the fields users commonly search: material type, material group, and description. Create index on (MTART, MATKL, MAKTX) to support typical search patterns. This should reduce query time from 30+ seconds to under 2 seconds for most searches.