DISCLAIMER: the following is provided as-is. You should always properly backup your data before performing any actions directly against your SQL Server database

Recently, while at a customer site, we had to mass recalculate the available quantities for their fiber optic cables. We used to suggest touching each fiber strand's availability field to force the auto updater to recalculate the counts. But, we did update that auto updater in our last release for performance and it will now only "adjust" counts by 1 based on your action. In the coming release, we will also update that auto updater to not recalculate if the value did not change, so that work around is also going away.

However, we were able to run the following SQL script against their data:

UPDATE arcfm.FIBEROPTICCABLE
SET    SinglemodeAvailabilityQty = q.count
FROM   arcfm.FIBEROPTICCABLE c1
       INNER JOIN (SELECT c .GlobalID,
                          Count(*) AS count
                   FROM   arcfm .FIBEROPTICCABLE AS c
                          INNER JOIN arcfm.F_BUFFERTUBE AS b
                                  ON b.FIBERPARENT = c.GlobalID
                          INNER JOIN arcfm.F_FIBER AS f
                                  ON f.FIBERPARENT = b.GlobalID
                   WHERE  f.Available = 1
                   GROUP  BY c.GlobalID) q
               ON c1.GlobalID = q.GlobalID;

 

The above script ran against their data (~10k cables, ~350k strands) in just a couple of seconds. Hopefully the names are simple to relate to your data.

 

This solution obviously has some limitations that you should be aware of:

  • as-is, it can only run against an unversioned set of tables (cables, buffer tubes and strands). We did try to run it against versioned views, but the complex inner query with joins violates Esri's limitations for updating data via versioned views. If you are interested in customizing this script to work against versioned views, I would love to learn how you got it to work. My first recommendation would be to load the inner query results in a separate table and try a simpler update query.
  • The provided implementation does not care about single mode vs. multi mode counts. However, it would be pretty easy to customize the inner where clause to filter only single mode or multi mode fibers and update the corresponding field on the cable. You would have to run two different queries for each mode.
  • The provided query is based on our recommended data model: Cables => Buffer Tubes => Strands

 

Thinking maybe someone like Ed Blair, Brian Higgins or Skye Perry could use this at times.