Make Query Table tool

[Riverside]

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.

[rfairhur24]

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.

Ted:

I would definitley stick with views using sde command line for performance on an FC of this size.

I have used this tool with the 700K features you are talking about to see a join to Building permits (only about 36K records). It took about 7 minutes to process after I indexed the criteria fields on both the FC and the table (without indexed fields I killed it after 30 minutes). If I had not specified any criteria for how the fields of the table relate to each other, the tool would have cross-multiplied every record in the FC and the table (700,000 * 36,000 = 25,200,000,000 combinations). With criteria it only keeps matching records which reduces it to about the number of records within the smaller table. There is no way to get the tool to behave like a Keep All Records join, where every unmatched record displays Null values, so any unmatched features in your tables would cause the unmatched features to disappear in the result.

I don't think it is optimized for memory usage any where near as well as an sde view. (I used to work with the combination of all the tables you are talking about as a view and it performed fairly well, but the current version my agency uses is actually processed so that the view is exported to a permenent FC to improve performance by doing such things as adding field indexes) So again, I think your SDE view is going to out perform this tool and give you better results.

[Riverside]

Thank you Richard.

Yep, I agree, I have everything else all scripted, so it works as expected. I too, have a script that takes different views to fc as well for use on the web, since the views don't quite perform as I would want on the web. The views are mainly for desktop type analysis.

So, when are you coming back in to the office.

[rfairhur24]

I am back on Mondays and Wednesdays and telecommuting Tuesdays and Thursdays to cut down on commute time. So come by and see me on Monday.

[csny490]

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

[Riverside]

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.