I'm not an SDE admin, so I use MakeQueryTable quite a bit for various things (mainly querying/reporting our overly complex timber sale/activities tracking system).
Even without pre-indexing my fields (pre-indexing join fields and query fields would probably be a good idea), performance is exceptional in a local FGDB. The trick then is to then save the feature layer out as a .lyr file (which stores the SQL internally). Of course, all tables need to be in the same FGDB, SDE, or PGDB, otherwise the tool doesn't work.
Like Ted said: A sort of non-SDE spatial view. It's very powerful. This sample takes 1 second to return a featurelayer query composed of three related tables (600, 70000, and 50000 records respectively):
queryExp = "question_3_fma_summary.RMU_ID = rmu_polyid.RMU_ID AND rmu_polyid.RMU_POLYID = rmus_we_care_about.RMU_POLYID AND\
rmus_we_care_about.MM_POL_LBL in ('reclass-released','reclass-released-may-be-available') AND question_3_fma_summary.COMPSOLD_DATE >= 20030418"
gp.MakeQueryTable_management(question3FmaSummaryTbl + ";" + rmuPolyIdTbl + ";" + rmusWeCareAboutFC, featureLayerFL, "ADD_VIRTUAL_KEY_FIELD", "", "", queryExp); showGpMessage()