Make Query Table tool

TedCronin

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.

TedCronin

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()

I want to try what you are doing here Chris, because this looks pretty cool.  My thinking, I am tired of having to use sde command line.  Its SO old.  Its everything against what I believe in with technology, so perhaps Esri can just optimize these tools, so we can throw huge datasets in the mix and get great perf.

@Chris have you played with Mapping with the same lyr files generated from the Make Query Tool, or have you tried to create an MSD file using these lyr files, then serving them up on Server?

@Rich, yep, I will be stopping by, lots to catch up on.