Friday, July 27, 2012

Configurator Model Size - Node count of the model

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
  ;

2 comments:

  1. Why do you need 1 = 1 in the WHERE block?

    ReplyDelete
  2. In my opinion number of rules a better indication of model size. And the query for that would be for example:
    SELECT * 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

    ReplyDelete