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