You already identified your problem. This notion of "information hiding" doesn't work well when performance is an issue. Embedded SQL is a good enough performance feature to use vs native database I/O (assuming you're retrieving multiple records). There is little reason to move the I/O not only into SQL but into subprocedures in service programs that are effectively eating up your system.

So the 2 things you're doing poorly are:

1. ACTGRP(*NEW) on a *SRVPGM is almost NEVER recommended.
2. SELECT * FROM... is NEVER recommended, and is in fact contrary to why you would hide the data I/O; isn't it?

For activation groups, I recommend either Named or *CALLER. For programs either Named or *NEW--rarely would I use *CALLER for a program, but if it works, that's fine too.

Remember, SQL is not native, so it does a call to another program that calls another program that calls another program etc... native I/O would be faster for single-record I/O in 99.9% of the cases.

But to be blunt, I do find that the number one issue with poor performing routine, recently written are due to Programmer Pride of Ownership/Authorship. In most cases they don't want to look at how many variables they've declared or how much data is being returned by a subprocedure (as a return value, not a parameter) and think just because it compiles its "good enough".

Other Things to Consider:

Do your subprocedures that access SQL declare lots of variables or large (huge) data structures to store the data in? just because RPG lets you declare 32k fields doesn't mean it will create/destroy them in a time that is favorable to performance. In most cases, when several variables are declared, they should NOT be declared in the Subprocedure but at the global level--or better yet, redesign the routine so it doesn't need so many variables, or consider using User Spaces.

Are you returning large data structures on Subprocedures? I mean the actual Return Value (using the RETURN option to send the data back to the caller--as a "Factor 2" of the RETURN opcode)? Anything larger than 10 bytes or so (16 is maximum) is crappy performance and shouldn't done until V7r1 is installed and you include the RTNPARM keyword. Otherwise, only do that on one-off subprocedure. For example, in cozTools we have an EDITDATE procedure that returns a character string. That string is more than 16 bytes on its declaration, but is often less when it is returned. However, if you are returning 200-byte data structures, or worse, an array, your performance will suck.

That last thing is recursion. While subprocedures are cool in that that they can be called recursively, as are service programs, this is huge performance hit. Should only be used when formulas call for it, and when they do, try to rewrite them so they do not.

A thing I tend to do is, group procedures into families, declare the files (or fields globally), and each business function will begin with something like: 

p GetPatternSize  b                   export                   
d GetPatternSize  pi                  extproc('GetPatternSize')
d  PartNo                        8p 0 const 
  // Return parms

 /free
     if PartNo <> File.PartNo;
       LoadInventoryData(PartNo);
     endif;

  // Business logic to load pattern size data to return parms

     return;
 /end-free
p GetPatternSize  e

 There is only one place I get the data from file, and that is generally private to the family module source. Now the LoadInventoryData procedure may be a chain or sql fetch (most likely a chain in the above case). This style allows me to call many related "get" procedures in sequence, where the first called will trigger a data load, and each subsequent will use the already loaded data. I'm also not batting around large data areas of data between programs - the business logic is all contained in the procedure, the calling program does not need to receive the file data, and selectively pass it off to procedures to make decisions for it (because when the rules change, you have to add more parms in addition to the coding changes - when the data is self contained in the procedure and it's relatives, the program merely asks a question using a key field).

ACTGRP *NEW is killing you, all that activation/destroying on every round trip call, all those expensive file opens/closes. We just use a named AG with our company name for *SRVPGM's. *PGM are *CALLER or a named AG. And we keep files open until no longer needed.

And did you code your SQL so the ODP's can be reused on the 3rd subsequent SQL OPEN? If you're not seeing "ODP Reused." in the jog log, you want to investigate. That's a time saver too.

Chris R