Query Optimization in IFS: The Hidden Performance Killer

saikat bose — Sun, 12 Jul 2026 19:37:39 +0000

3 PM on a Monday. Support tickets pouring in. Users can't open the customer form—it just hangs.

You know that feeling? I do.

I spent the next 2 hours digging through SQL traces, refreshing coffee, muttering to myself, only to find the culprit: one query scanning 2 million rows to pull back 200 records. One. Query.

That was years ago. I was terrible at query optimization. But it forced me to learn—out of pure frustration, honestly.

Here's what I wish someone had told me back then: query optimization isn't some dark art that only database admins understand. It's not even that complicated. But it does matter. A lot. Because that one bad query doesn't just tank one screen—it cascades through your entire system, grinding everything to a halt.

I'm not gonna lie—this post isn't groundbreaking stuff. But I've seen these same three mistakes kill performance in almost every IFS project I've touched. So if you're dealing with slow forms or reports that take forever to run, keep reading. These principles have genuinely transformed how I work.

**** Filter at the Database, Not in Your Code****

Okay, this one's obvious. But I still see developers doing it wrong constantly.

The biggest culprit I run into? Someone writes code that grabs all records from a table, then filters them in a loop. All the records. Then filters them. You can probably guess how this ends at scale.

// ❌ DON'T—I've seen this exact code crash production
fetch all customers into array
for each customer in array
  if customer.status = 'active'
    process customer
  end if
end for

This is fine with 500 customers. With 5 million? Your server's gonna have a bad time.

Compare that to this:

// ✅ DO THIS
fetch customers where status = 'active'
for each customer
  process customer
end for

Night and day difference.

Here's why: the database is designed to filter data efficiently. It uses indexes, query plans, all kinds of optimization under the hood. Your code? Not so much. Let the database do its job.

In practical IFS terms:

Put your filtering logic in the WHERE clause, not after you've fetched the data

If you're building a report, apply your conditions in the SQL, not in the report logic

Check what default filters are already baked into your customizations (you might be double filtering without realizing it)

Seriously. This one change alone has fixed more performance issues than I can count.

**** Index Smart Not Hard****

I learned this lesson the hard way.

Years back, I was brought in to "fix" a database that was running terribly. After some investigation, I found this absolute monster of a custom table with 47 indexes. I'm not exaggerating—47.

Reads were blazing fast. But inserts? Inserts crawled. And every update was painful because the database had to update all 47 indexes. It was a classic case of someone thinking "more indexes = faster queries" without understanding the trade-off.

We trimmed it down to 8 strategic indexes. Inserts went back to normal. Reads stayed fast. Everyone was happy.

Here's what actually works:

Index what you filter by. If your query says "WHERE customer_type = 'Automotive' ", make sure there's an index on that column. Without it, the database scans the entire table. That's called a full table scan, and it's slow.

Think about order in composite indexes. If you're indexing (status, region) but you mostly query by region, you might be wasting that index. Put your most-used column first.

Don't go crazy with indexes. Every index you add makes writes slower because the database has to maintain all of them. I generally aim for 5-10 indexes per heavily-used table. If you need more, something's probably wrong with your queries.

Actually look at the query plan. Don't guess. Use the SQL trace in IFS, see what's happening, then index strategically. It takes 5 minutes and saves you from doing pointless work.

**** Join With Intent (or You'll Create a Monster)****

This is where things get messy fast.

I got called in to debug a report once that was taking 45 minutes to run. No exaggeration. 45 minutes. When I looked at the SQL, I saw this beautiful disaster:

Orders → OrderLines → Customers → Inventory → Suppliers

All joined together. No filtering. None.

The Cartesian product was so massive that the query was basically multiplying the entire dataset against itself multiple times over. The database was working its butt off for nothing.

Here's what I do now:

Filter first, join second. Don't join everything and then filter. Apply your WHERE conditions before joining. This shrinks your dataset down to what you actually need, so the joins have less work to do.

Join on indexed columns. Always, always join on primary and foreign keys. That's what they're there for. If you're joining on some weird custom column, reconsider.

Don't join the same table twice unless you really need to. I see this more often than I'd like. If you already pulled customer data, don't join customers again 3 rows later.

Sometimes denormalization beats complex joins. I know, I know—purists hate this. But for heavily-used reports, a pre-calculated summary table can be way faster than joining 6 tables together.

// ✅ GOOD: Filter first, then join
SELECT o.order_id, c.customer_name, SUM(ol.quantity)
FROM orders o
WHERE o.order_date >= '2024-01-01'  -- Filter early
JOIN order_lines ol ON o.order_id = ol.order_id
JOIN customers c ON o.customer_id = c.customer_id
  AND c.status = 'active'  -- More filtering
GROUP BY o.order_id, c.customer_name

That 45-minute report? We rewrote it with proper filtering and joins. It ran in 8 seconds.

That's It. That's Literally It.

I'm gonna be honest—query optimization isn't the sexiest topic. You can't demo it to stakeholders. There's no "new feature" to show off at sprint reviews. But I've never met a developer who regretted spending time on this.

Because here's the reality: most of your performance problems are sitting in 20% of your queries. That's it. Fix those queries, and suddenly forms open fast, reports complete in seconds, and users stop complaining.

I've seen queries go from 30 seconds to 3 seconds just by fixing filters. From 2 minutes to 15 seconds by cleaning up joins. These aren't edge cases—this happens constantly.

So here's what I'd suggest: Pick your slowest form or report this week. Run a trace. Look at the longest-running query. Apply one of these principles—filter better, check your indexes, or simplify a join.

Then compare. You'll see a difference. Guaranteed.

Real talk though—what's the worst query performance issue you've run into? I'm genuinely curious. Leave a comment and let me know what you've dealt with. I might even write a post about it. 👇

Next week I'm diving into BPMN workflows and the design mistakes that silently murder performance. If you've ever wondered why a BPMN is running slow, that one's for you.

IFS #Development #Performance #SQL #ERP #DeveloperLife

DEV Community: saikat bose

Query Optimization in IFS: The Hidden Performance Killer

IFS #Development #Performance #SQL #ERP #DeveloperLife