Query Oracle Table -- Need advice

Question

Task: query a very large Oracle table, build an XY Event layer, apply symbology with .lyr file, create GP service, and output to web app using web API.

I have this running using ModelBuilder at AGS v10 in a GP service and a JSAPI web app.  I am now trying to upgrade to Python and a Python Toolbox.

ArcGIS Desktop 10.1 SP1
ArcGIS Server Linux 10.1 SP1
ArcSDE 10 SP2
Oracle 11.2.0.2

My biggest question is how best to query the data in Oracle (I'm looking for performance).

I use TableSelect_analysis through an ArcSDE connection file in my older ModelBuilder GP service, but I only want 4 columns from this table and it has 21.

MakeTableView_management through an ArcSDE connection file doesn't honor my where clause or my fieldinfo (ESRI Support is looking into this).

I can execute my SQL query with ArcSDESQLExecute, but I'm not sure how to handle the list to use with MakeXYEventLayer_management.

I have also read that using cx_Oracle is a good way to go.

I was hoping that you gurus out there might give me some advice.

Thanks,
Glen

JamesCrandall · Answer

Glen -- something that completely slipped my thought process for this thread:

What if your Database repository is NOT ArcSDE?

This is where and why cx_Oracle is needed for our implementation.  That is, we are integrating non-spatial Oracle attirbute repositories (ancillary to spatial databases), joining this data in the in_memory space to "make it" spatial data, then processing it from there.

Hope that makes sense.  Again, if your attributes are stored in SDE then your ArcSDESQLExecute is probably a good choice --- I really have no benchmark to comment on performace differences between this and cx_Oracle.  I'd gather if I moved my SQL into procedural packages on the database server I could really boost things up (but even that may be negligable gains).

Anyway -- good luck!

GWReid · Answer

James,

As far as performance with ArcSDESQLExecute goes, my entire script consistently takes ~7.5s while the query is returned in ~2.4s (~0.8s for the connection, ~1.6s for the query) -- while changing dates to avoid query caching as well as to use different table partitions.

The time hog is looping through the records to create the featureclass (~4s).  For this reason, I'll look into using CreateTable_management and MakeXYEventLayer_management to see if going that route improves performance.

Thanks for the suggestion.
Glen

JamesCrandall · Answer

Not sure if this will help or hurt, but instead of Createfeatureclass_management like you are doing, why not create a table (CreateTable_management) and use your sdeReturn to populate it?  Then use that table in the arcpy.MakeXYEventLayer_management, which a table is the parameter it is asking for.

http://help.arcgis.com/en/arcgisdesktop/10.0/help/index.html#//00170000006z000000

Edit: forgot --- this would mean you don't need cx_Oracle at all.  How is the performance of ArcSDESQLExecute?