search

Esri / local-government-desktop-addins

A series of ArcGIS Desktop Add-ins used in the ArcGIS for Local Government editing maps.
Apache License 2.0
61 stars 57 forks source link

BUG-000113151 Running the ‘Valve Isolation Trace’ command in Water Utility Network tools bar takes more time in a specific enterprise geodatabase. #304

Closed MikeMillerGIS closed 5 years ago

MikeMillerGIS commented 5 years ago
  1. Install the Add in ‘Water Utility Network Tools’ which can be downloaded from the following web link, http://solutions.arcgis.com/utilities/water/help/network-editing/
  2. Extract the attached zip file and restore the attached database backup file to the SQL Server instance.
  3. Run the following SQL Query to resync SDE user to the restored geodatabase, EXEC sp_change_users_login 'Update_One', 'sde', 'sde'
  4. In File explorer, navigate to the location <C:\Users[User_Profile]\AppData\Roaming\ArcGISSolutions\ConfigFiles> and remove the existing configuration files and copy/paste the configuration files provided with the defect attachment.
  5. In ArcCatalog, create a connection to the restored geodatabase with sde user.
  6. Connect to the folder extracted and right click on the MXD > Select Set Data source > Click on Select all > Click on Replace all > Provide the ‘New data source’ to the database connection in step 5 > Hit OK to create a new MXD.
  7. Open the new MXD created in Step 6 > Enable the toolbar ‘Water Utility Network Reporting’ > Select the command ‘Valve Isolation Trace’ > Select any line feature.
  8. Observe that the isolation trace command runs around 2 minutes 30 seconds.

Notes: • Attached Sde intercepts, SQL Traces, database backup file, MXD and configuration files. • Issue is specific to the enterprise geodatabase attached to the defect. • Observed in the SDE Intercepts that, ExecuteSpatialQuery takes around 100 seconds QueryWithinfo command takes around 14 seconds. • When all the data is copied to an Oracle enterprise geodatabase, the Valve Isolation trace takes around 1 minute 15 seconds. • When all the data is copied to a new SQL Server enterprise geodatabase, the Valve Isolation trace takes around 1 minute 15 seconds. Sde Intercepts (sde_intercept_newgeodatabase_notregisteredasversioned_data) for this testing are attached to the defect. ExecuteSpatialQuery takes around 39 seconds QueryWithinfo command takes around 15 seconds. • Following are the duration taken in different versions of ArcGIS Desktop, ArcGIS Desktop 10.6 - around 2 minutes ArcGIS Desktop 10.5.1 - around 2 minutes ArcGIS Desktop 10.4.1 - around 2 minutes 26 seconds ArcGIS Desktop 10.3.1 - around 3 minutes 19 seconds ArcGIS Desktop 10.2.1 UTUP8 - around 1 minute 23 seconds • In file geodatabase valve isolation trace command completes around 6 to 15 seconds. • Issue is not reproducible with in house data.

Environment:

  1. User: ArcGIS Desktop 10.4.1 + SQL Server 2014

  2. Analyst: Database used for testing: SQL Server 2014, Oracle 12.1.0.2

Desktop 10.6 – Issue Reproducible. Desktop 10.5.1 – Issue Reproducible. Desktop 10.4.1 – Issue Reproducible. Desktop 10.3.1 – Issue Reproducible. Desktop 10.2.1 UTUP8 – Issue Reproducible.

Update from Case #02181771 Please see attached zip file with the same name as the case # for data, tools, and intercept files

Testing Environment: ArcGIS Desktop 10.6.1 SQL Server 2016 10.6.1 EGDB

In testing for this case, it was observed that there is a difference in performance when using the current Water Geometric Network Editing and Analysis tool and those from 2014. There was also a difference in testing the sample data that comes with the current tools vs. the customer's data attached to this case.

Using the sample data and the sample MXD provided with the tools, both the current and 2014 tools perform as expected, taking about a minute or a little under to run a Valve Isolation Trace depending on which Water Main the trace is run on.

Using the attached user's data and MXD, the 2014 tools perform similar to what is seen with the sample data. However, with the current tools, I am seeing the trace process stuck on the first step of Setting Trace Flags and it does not move off that step after running for up to 10 minutes. In comparing intercept files (attached), I see that more time is spent on the ExecuteSpatialQuery command with the current tools than the 2014 tools. I also see that (as an example) in the intercept for the 2014 tools, we query the sde.wFitting table and there is a where clause that includes many OID values at once. In the intercept for the current tools, I don’t see this. Rather it seems we query a lot of OID’s individually. So there are a lot more queries run with the current tools. This seems to match up with a lot more time spent executing spatial queries.

Salesforce ID: BUG-000113151 Salesforce Submitter: KrishnaRohit Yejju Salesforce Submit Date: 4/12/2018 5:26 AM Salesforce Bug Type: Performance Salesforce Severity: Medium Repro Data: \esri.com\sf_filestore\PRD\Attachments\Defects\BUG-000113151 Work Around: (n/a) Product: ArcSDE/Enterprise Geodatabase Functional Category: ArcGIS for Electric, Gas, Water and Telecommunications Client Platform: (n/a) Version Found: 10.4.1 Planned Version Fixed: (n/a) Comment: Traced the issue to the Closed Valve query. In the sample data provided by the user, this took 1 min 40 sec to barrier 927 valves. Need to rework to use the batch load barriers function

MikeMillerGIS commented 5 years ago

Comment: Traced the issue to the Closed Valve query. In the sample data provided by the user, this took 1 min 40 sec to barrier 927 valves. Need to rework to use the batch load barriers function

MikeMillerGIS commented 5 years ago

fix checked in https://github.com/Esri/local-government-desktop-addins/commit/1fc0204c28c37f845e3c6631c1230926d3b19b93