Query to count files by context (improved)

[avillanueva]

This is a share not a question. As you can tell, I am revaulting my system to enable automatic folder creation and move data to a new volume. I've opted to feed the beast on off hours by altering vaulting rules and then revaulting so as not to impact users during the day. I needed a query to estimate the size of the job I was kicking off by context so I can control it. I started with this query from knowledge base:

How to find vault file name and location of all objects in vault for specific context in Windchill PDMLink 

I generalized to just give me a count but this only handles 3 object types, something was missing. Looking at HolderToContent table, I can see it was DerivedImage where most of my files are from which makes sense.

Note this chart is in log scale so published content is about 10x everything else. Makes me want to consider flipping on publish as .pvz. Everything else is small potatoes so I did not include in my query so this gets the top 4 content items. I ran this on my test server and it executed in 100 seconds.

SELECT pdm.nameContainerInfo, fv.name vault_name ,count(fv.ida2a2) file_count
FROM
(SELECT b.idA3A5 a5, b.idA2A2 id, b.filename fileName, b.filesize fileSize FROM ApplicationData b,HolderToContent c WHERE b.idA2A2 = c.idA3B5) ad,
HolderToContent htc,
FvItem fi,
FvMount fvm,
FvFolder ff,
FvVault fv,
(SELECT idA2A2, idA3masterReference From EPMDocument UNION ALL
SELECT idA2A2, idA3masterReference From WTDocument UNION ALL
SELECT idA2A2, idA3masterReference From WTPart UNION ALL
(SELECT d.idA2A2, derived_doc.idA3masterReference From DerivedImage d,
    (SELECT idA2A2, idA3masterReference From EPMDocument UNION ALL
     SELECT idA2A2, idA3masterReference From WTDocument UNION ALL
     SELECT idA2A2, idA3masterReference From WTPart) derived_doc
     where d.ida3therepresentablereferenc = derived_doc.ida2a2)) doc,
(SELECT idA2A2,nameContainerInfo FROM ExchangeContainer UNION ALL
SELECT idA2A2, nameContainerInfo FROM OrgContainer UNION ALL
SELECT idA2A2, nameContainerInfo FROM WTLibrary UNION ALL
SELECT idA2A2, nameContainerInfo FROM PDMLinkProduct UNION ALL
SELECT idA2A2, nameContainerInfo FROM Project2) pdm,
(SELECT idA2A2, idA3containerReference From EPMDocumentMaster UNION ALL
SELECT idA2A2, idA3containerReference From WTDocumentMaster UNION ALL
SELECT idA2A2, idA3containerReference From WTPartMaster) m
WHERE
fi.ida3a4 = ff.idA2A2 AND 
fvm.idA3A5= ff.idA2A2 AND
ff.idA3A5 = fv.idA2A2 AND 
ad.a5 = fi.idA2A2 AND
ad.id = htc.idA3B5 AND 
htc.idA3A5 = doc.idA2A2 AND
m.idA2A2 = doc.idA3masterReference AND 
pdm.idA2A2 = m.idA3ContainerReference
group by pdm.nameContainerInfo, fv.name

Thoughts and improvements welcome.

[avillanueva · Accepted solution]

This was very useful in breaking up my move to nightly runs by groups of products. So far things are going well.