Pricing management module experiences slow database queries when loading customer-specific price lists

ERP Infor CloudSuite
, , , , , , , ,
1

We’re experiencing significant performance degradation in our pricing management module on ICS 2023-1. When sales reps try to load customer-specific price lists, queries are taking 30-45 seconds to return results. This is causing major delays during customer calls and quote generation.

The slow query problem seems to get worse as our price list database grows. We now have over 2 million active price records across 500+ customer-specific lists. When I check the query execution in the database monitoring tools, I see the pricing lookup query doing full table scans:

SELECT * FROM PRICE_LIST_ITEMS
WHERE customer_id = 'C12345'
AND effective_date <= CURRENT_DATE

The query plan shows it’s scanning millions of rows even though the result set is only 200-300 items. I suspect we need better index optimization, but I’m not sure which indexes would help most. The stats update schedule might also be a factor - I’m not sure when the database statistics were last refreshed. Any advice on improving this query performance would be greatly appreciated.

system 2

I’ve optimized this exact scenario multiple times for pricing management in ICS 2023-1. Your performance issues come from three distinct problems that need coordinated solutions:

Query Plan Optimization: The immediate fix is creating a proper composite index. Execute this on your PRICE_LIST_ITEMS table:

CREATE INDEX idx_price_customer_date
ON PRICE_LIST_ITEMS(customer_id, effective_date, expiry_date)
INCLUDE (item_id, price_amount, currency_code);

The INCLUDE clause adds covering index benefits - the query won’t need to access the base table at all for standard price lookups. This should reduce your query time from 30-45 seconds to under 2 seconds immediately.

Index Optimization Strategy: Beyond the main lookup index, add these supporting indexes for common query patterns:

  • Index on (item_id, effective_date) for item-based price searches
  • Index on (price_list_id, customer_id) for price list management operations

Review your existing single-column customer_id index - if it’s not being used after adding the composite index, drop it to reduce index maintenance overhead during updates.

Stats Update Configuration: Your statistics are almost certainly stale given the query optimizer’s poor choices. Implement an automated statistics refresh schedule:

Navigate to Database Maintenance > Statistics Management and configure:

  • PRICE_LIST_ITEMS: Update statistics every 3 days (high-change table)
  • PRICE_LISTS: Weekly updates sufficient
  • Set sampling rate to 30% for faster stats collection

Manually update statistics immediately after creating new indexes:

UPDATE STATISTICS PRICE_LIST_ITEMS
WITH FULLSCAN;

Data Archival: Your 8 million record table with only 2 million active prices needs cleanup. Implement a monthly archival process:

  1. Archive price records older than 2 years to PRICE_LIST_ITEMS_HISTORY table
  2. Keep 2 years of history in the main table for audit and comparison purposes
  3. Partition the main table by effective_date year if your database supports it

Create an archival job in System Scheduler to run on the first of each month. This will keep your active table lean and maintain fast query performance as data grows.

Query Modification: Update the pricing module query to be more selective:

SELECT item_id, price_amount, currency_code,
       effective_date, expiry_date
FROM PRICE_LIST_ITEMS
WHERE customer_id = 'C12345'
AND effective_date <= CURRENT_DATE
AND (expiry_date IS NULL OR expiry_date >= CURRENT_DATE)

Adding the expiry_date filter and removing SELECT * will leverage your new covering index perfectly. This query should execute in under 1 second even with millions of records.

After implementing these changes, monitor query performance for a week. You should see consistent sub-2-second response times for price list loads. If specific customer queries still run slowly, use database query profiling to identify if there are unusual data patterns (like customers with 10,000+ price records) that need additional optimization.

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

3

That SELECT * is definitely part of your problem. You’re pulling all columns when you probably only need a subset. But more importantly, do you have an index on (customer_id, effective_date)? A composite index on those two columns would dramatically improve lookup performance.

4

I checked our index configuration and we only have a single-column index on customer_id. There’s no composite index including effective_date, which would explain why the query optimizer isn’t using an efficient lookup path. I’ll need to work with our DBA team to add that index, but I want to make sure we’re covering all the performance issues before making changes.

5

Beyond indexing, consider if your effective_date filter is causing issues. If you have historical price records that are never purged, your table might be bloated with expired prices. We had a similar situation where 80% of our price records were historical data that should have been archived. Cleaning that up improved query performance significantly even before we optimized indexes.

6

Good point about historical data. I just ran a count and we have 8 million total price records, with only 2 million currently active. That means 75% of the table is old data that’s slowing down our queries. We definitely need a data cleanup strategy along with the index improvements.

7

Don’t forget about statistics updates. If your database stats are stale, the query optimizer might choose suboptimal execution plans even with proper indexes in place. In ICS 2023-1, you should be running stats updates weekly for high-transaction tables like PRICE_LIST_ITEMS. Check when statistics were last gathered on that table - it might explain why the optimizer is choosing table scans over index seeks.

danieldata 8

Tested this on ICS 2023-1 pricing module and the composite index on PRICE_LIST_ITEMS with the INCLUDE clause dropped our customer price list load times from 12 seconds to under 800ms.

9

Michelle’s right about stats being critical. Also, that SELECT * needs to change. Modify the pricing module query to only fetch the columns you actually display to users. Reducing the data transfer volume will help even after you fix the indexing and statistics issues.