Usually its a common question in Configurator Development like how large the model is. Model size is usually determined by number of nodes created under it and its referenced models. When model performance is raised as a question, first question asked by analyzer would be how big the model is. Here is the query you can use for the same. This query returns the node count of all the models which are under given folder. You can modify the query to get node count for given project.
Q1) Count of nodes
in model which are directly created under model
SELECT rpf.name folder_name,
p.name model_name,
p.devl_project_id,
COUNT(*) node_count
FROM cz_devl_projects p,
cz_ps_nodes pn,
cz_rp_entries rpp,
cz_rp_entries rpf
WHERE
1
=1
AND
rpp.object_id =p.devl_project_id
AND rpp.enclosing_folder =
rpf.object_id
AND
pn.devl_project_id = p.devl_project_id
AND
p.deleted_flag =0
AND
pn.deleted_flag =0
AND
rpp.object_type ='PRJ'
AND
rpf.object_type = 'FLD'
--AND
P.DEVL_PROJECT_ID=1037298
AND RPF.NAME LIKE 'Division A Models'
GROUP BY rpf.name ,
p.name,
p.devl_project_id;
Q2) Count of nodes in
model which are directly created under this model + nodes created in all the
referenced models of this model
SELECT rpf.name folder_name,
p.name model_name,
p.devl_project_id,
COUNT(*) node_count
FROM cz_devl_projects p,
cz_ps_nodes pn,
cz_rp_entries rpp,
cz_rp_entries rpf
WHERE
1
=1
AND
rpp.object_id =p.devl_project_id
AND rpp.enclosing_folder =
rpf.object_id
AND pn.devl_project_id IN
(SELECT component_id
FROM cz_model_ref_expls
mr
WHERE mr.model_id
=p.devl_project_id
AND mr.deleted_flag=0
)
AND p.deleted_flag =0
AND pn.deleted_flag =0
AND rpp.object_type ='PRJ'
AND rpf.object_type = 'FLD'
--AND
P.DEVL_PROJECT_ID=1037298
AND RPF.NAME LIKE '
Division A Models'
GROUP BY rpf.name ,
p.name,
p.devl_project_id
--order by node_count
desc
;
Why do you need 1 = 1 in the WHERE block?
ReplyDeleteIn my opinion number of rules a better indication of model size. And the query for that would be for example:
ReplyDeleteSELECT * FROM
( SELECT rpf.name folder_name,
nodes.name model_number,
p.name model_name,
p.devl_project_id,
COUNT(*) rules_total
FROM cz_devl_projects p,
cz_ps_nodes nodes,
cz_rules r,
cz_rp_entries rpp,
cz_rp_entries rpf
WHERE
rpp.object_id =p.devl_project_id
AND nodes.ps_node_id = p.devl_project_id
AND rpp.enclosing_folder = rpf.object_id
AND r.devl_project_id = p.devl_project_id
AND p.deleted_flag =0
AND r.deleted_flag =0
AND rpp.object_type ='PRJ'
AND rpf.object_type = 'FLD'
--AND P.DEVL_PROJECT_ID=1037298
AND RPF.NAME LIKE 'RMT Models – In Process'
GROUP BY rpf.name,
nodes.name,
p.name,
p.devl_project_id ) models
ORDER BY models.rules_total DESC