As a rule, replacing the front section of your vehicle's exhaust has the biggest potential impact on performance. While replacing any section of your original exhaust with better performing pipework will yield some increase in performance, if this is downstream of more restrictive pipework at the front of the system it’s always going to be limited by this somewhat.
Sports Catalysts replace your original restrictive catalytic converter with a higher performing cat made from higher end material. Downpipes connect the front pipework to the rest of the exhaust. On a turbocharged car, they sit directly behind the turbo and have a huge impact on potential performance.
A De-Cat Pipe replaces a catalytic converter with a straight through pipe. While this isn’t legal for road use, it offers the least possible restriction to gas flowing through the exhaust system. A manifold connects directly to the engine block. This means it has a big impact on how the engine breathes and performs, particularly on a normally aspirated engine.
Generally, the original catalytic converter sits in this pipework and replacing it will normally require remapping the ECU to take advantage of the suddenly less restrictive pipework.