What I noticed
I was running a large sort in Connect ETL and watching it take significantly longer than expected. CPU was not maxed. Memory was not saturated. The job was just slow. When I looked more closely, I found the answer was not in the data but in how the job was structured. The sort was running sequentially when it didn't need to be.
IX and User-Defined MapReduce are particularly recommended topics for Connect ETL practitioners to explore, as they are specifically called out in the Connect support onboarding materials as self-led learning priorities alongside DataFunnel and DMXMMSRT. Most users never get there. They run sorts as linear tasks and leave throughput on the table.
For Connect ETL (DMX) in particular, temporary sortwork files need to be written to disk during a sort if the size of the input file is too large to fit into memory. Sorting can also occur in join and aggregate tasks. If disk space is low, DMX may need to wait until enough disk space is available to continue writing the temporary sortwork files in order to complete the sort. If DMExpress is using workspace and the amount of available disk space is low during execution, you can specify more workspace for the sort either by providing a disk location with more available space, or by freeing up space on the disk where the jobs run.
That is the single-threaded picture. The IX step changes it.
The IX step acts as an in-memory pipe between tasks inside a job it allows one task to fan-out work across multiple parallel processing threads, and another task to fan-in the results. Instead of one sort running against the full dataset, you split the data, sort each partition in parallel, and merge all inside the same job execution. The performance difference on large datasets is not incremental. It is structural.
Performance log messages to watch for in Connect ETL include MOREMEMFORCPUS which specifically indicates that more memory would allow more CPU threads to be used. This message is a direct signal that the job has room for parallelism but is memory-constrained. When you see that message, the job is telling you it is not using the available CPU cores it could be using if given more memory or a better parallel design.
Why this design works
- Predictability: Fan-out and fan-in patterns make the parallelism explicit. You define the partition boundaries. You control how many threads process simultaneously. The sort behavior becomes predictable per partition rather than unpredictable against the full file.
- Reliability: For CPU-bound workloads, the recommended degree of parallelism is generally aligned with the number of available CPU cores. Running more concurrent jobs than available cores often produces diminishing returns and may negatively impact performance for CPU-intensive processing due to CPU contention and context switching overhead. Aligning thread count to core count keeps the sort stable and avoids memory swapping.
- Operational Safety: Resources may appear sufficient before or after the process runs, but may be scarce at runtime due to memory usage and available disk space. It is important to monitor available resources during the process execution, not before or after. IX-based parallelism makes this easier each parallel thread has a bounded dataset, so memory usage per thread is predictable.
What's different for Hadoop / Big Data targets
There is one extra step.
- When specified to use the Connect for Big Data Sort, Hadoop automatically invokes the highly efficient Connect ETL runtime sort engine, which executes in parallel on all nodes as an integral part of the Hadoop framework, thereby increasing the performance, scalability, and throughput of the Hadoop cluster. In the Big Data context, the parallelism is handled by the cluster. In on-premise Connect ETL jobs, you are responsible for designing that parallelism explicitly through the IX step.
- For single-node jobs, the IX fan-out pattern with workspace configuration controls where sortwork files land. Directing each partition's workspace to a separate disk location removes I/O contention between parallel threads.
- For jobs that include join or aggregate tasks, the same fan-out principle applies sorting occurs inside those tasks too, and the same disk pressure exists.
Why this matters in production
A slow sort that looks like a capacity problem is often a design problem. Adding memory or disk without restructuring the job frequently produces modest gains. Restructuring the job to use parallel sort paths frequently produces step-change gains.
The Connect ETL log contains messages that identify performance issues and tuning opportunities specifically INMLFJ, MOMEMAGG, DBIOCDFM, MOREMEMFORCPUS, and NOTRTPER. These messages are not noise. They are the job telling you exactly what is limiting throughput.
If you ever see a sort job consuming moderate resources but taking disproportionately long, it is not random. It is Connect ETL processing data one thread at a time when it was designed to do more.
What changed for me
I used to treat sort performance as a hardware tuning problem add memory, add disk, increase workspace. I now treat it as a job design problem first. The IX step exposes parallelism that was always available at the hardware level but invisible at the job level. Once I understood that the sort engine can run in parallel across partitions when the job is structured for it, I stopped looking at resource monitors first and started looking at job design first.
Takeaway
Connect ETL's sort engine is built for parallelism, but that parallelism does not activate automatically for large on-premise jobs. The IX step is the mechanism that makes it explicit fan-out the data, sort in parallel, fan-in the result. Watching for log messages like MOREMEMFORCPUS tells you when the job has unused parallelism capacity. Redesigning for fan-out patterns is almost always a better response than simply adding hardware.
Have you used IX-based parallelism in your Connect ETL sort jobs, and what design patterns or log signals guided you to the right thread and workspace configuration? *Precisely Software Inc.