0

I'm creating an API which will allow queries to be performed on my GeoDatabase. I'm using ArcSDESQLExecute to connect to ArcSDE (backed with an Oracle database). Creating the connection object is very slow (~1.5 s):

sde_conn = arcpy.ArcSDESQLExecute(r"data\Connection to GPSERVER3.sde")

So doing this before while handling an API request makes response times very slow.

However, if I create sde_conn once, for the life of the API, and re-use it indefinitely, queries are much faster (~20ms in some cases), since it's not recreating sde_conn each time.

Is it bad practice to keep sde_conn alive? I suspect that creating the object creates a persistent connection. Does anyone know what it's doing under the hood? (is it opening and closing the connection during each call to execute ?

1

Some of ArcSDE operations are pretty heavy and it is mentioned in the documentation. ESRI is improving it. Check out cursors for example (classical vs arcpy.da).

One big con I can think right off the bat is that how you manage mutual exclusion and load balancing on your connection(s).

Also, you may run into transactional issues, locks, etc.

Note that SQLExecute is only there for providing flexibility and compatibility and you should do work as much as possible with other arcpy stuff (namely cursors, editor module, etc). It is very tempting b/c arcpy's API leaves a lot to desire in terms of functionality compared to underlying DBMS's.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.