we recently set up a new ArcSDE 10.2 on a Posgres 9.2 Database and moved all our FeatureClasses (about 750) over from our Oracle based ArcSDE.
However, establishing the connection now takes a couple of minutes to complete whereas when we were still in the testing phase (about 10 FeatureClasses) everything ran rather quickly, connections taking a few seconds to establish.
So far we have analyzed that ArcGIS queries all FeatureClasses for the privilege to INSERT, UPDATE, DELETE and SELECT separately for each FeatureClass before finishing the connect. This seems to be the major time sink for us.
Is there any way to speed this up or avoid this sort of query all along? After all it would technically suffice if this query was done per FeatureClass right before the action is performed rather than do this for all FeatureClasses beforehand.
We are also getting this problem see my link on Stack Exchange:
I have turned up nothing yet - but if I find out anything I will let you know. Also if you have found out anything with this issue please let me too.
You didn't answer the questions in GSE's comments about hardware configuration. It's hard to determine if these issues are even related without enough information.
Hi Vince - I think I alluded to the hardware being the same for both databases by virtue of both being Hyper-V instances. See my comments in underneath the response. Cheers. Simon.
Can you please log an Esri support incident for a review of the encountered performance behavior?
In addition to answers to Vince's inquiry, please include a trace file and the PostgreSQL configuration parameters.
Please let me know the case number when you receive it from the support team.
I have attached the sql trace for a single connection via ArcCatalog to our PostgreSQL database. Also find the db config file attached too.
In summary for the sql trace:
I have also logged this problem with ESRI UK - the case number for this is: CAS-09709-L6L0C8.
I am unsure whether we will get any closure on this from the UK side after speaking to our contact, so if ESRI Inc could help out on this, or anyone to be quite frank! - we would be very grateful.
We are reviewing this case in-house and a support case will be created shortly to reference your Esri UK case number. Further review of the behavior will be handled through the support case.
As is sometimes the case with forums and customers on maintenance there are things moving in parallel here. I've already drafted an incident submission from Esri UK to Redlands Support but am currently trying to get in contact with the customer to get it factually checked before I submit it. Vince, I thinks Simon's point about hardware is fair - the two machines are both on identical Amazon AMI infrastructure and have identical GDB content but to clear up any doubt I'll give full details in my incident submission. Thanks everyone !
How many feature datasets do you have? How many total feature classes (of the 750) are in feature datasets? What is the feature class count in the largest feature dataset?
How much RAM do you have in the database server? Is it a physical host or a VM? Is your disk storage fibre-attached? Networked? What is the network speed and network topology? Are there and routers between the database server and the network clients?