I recently tried to use the Make Query Table, thinking that its kinda like the Spatial Views in sde command line, but in ArcMap, and I was using a layer with over 700,000 parcels, with multiple tables, which each have around a million records. The process ran for 23 hours before I killed it because it sucked up about 23 GBs of memory in my Local Setting Temp directory. Is this the expected behavior of the Make Table Query or perhaps I am misusing this tool, and should stay with sde command line, which takes a few seconds to process this much data.
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()
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()