Very long execution of AM Query OSIM Computer
search cancel

Very long execution of AM Query OSIM Computer

book

Article ID: 390742

calendar_today

Updated On:

Products

CA Client Automation - IT Client Manager CA Client Automation

Issue/Introduction

An AM Query for OSIM Computer is created with OSIM Configurations like Current OS installation - State exist since :



Execution of this query is taking very long time and could affect the overall performance.

Environment

Client Automation 14.5

Cause

The SQL Query generated contains an extra join "csm_property c_pr" which is not used :

SELECT * FROM csm_v_computer WHERE csm_v_computer.uuid in (select distinct c.uuid from csm_object c,csm_property c_pr,csm_link l_bc, csm_object bc,csm_v_property bc_pr,csm_property bc_bs where c.class=102 and l_bc.parent = c.id and l_bc.child = bc.id and bc.class = 1004 and bc_bs.object = bc.id and bc_bs.name = 'bootstatus' and bc_bs.value='1000' and bc_pr.object = bc.id and bc_pr.name = 'configstatetime' and bc_pr.i_value  >=  1104534000)

This extra join is causing SQL performance problem as table csm_property contains a lot rows.

This problem affects queries for following OSIM Configurations items :

Activated OS installation>Activate state exist since
Activated OS installation>OS image properties
Current OS installation>Current state exist since
Current OS installation>OS image properties
Planned OS installation>Planned State exist since
Planned OS installation>OS image properties

Resolution

  • Execute following SQL Queries in mdb database :
    UPDATE RPDATFLD SET flddef=N'Crit0PseudoTemplate=State exist since $CritOperator$ ''$CritValueLabel$'';'+CHAR(13)+CHAR(10)+N'DisplayEnum=;'+CHAR(13)+CHAR(10)+N'Crit0Type=CT_DATE;'+CHAR(13)+CHAR(10)+N'Usage=0;'+CHAR(13)+CHAR(10)+N'CriteriaCount=1;'+CHAR(13)+CHAR(10)+N'PseudoTemplate=($Crit0Pseudo$);'+CHAR(13)+CHAR(10)+N'Crit0Name=State exist since;'+CHAR(13)+CHAR(10)+N'Crit0ValueSelector=VS_EDITABLELIST;'+CHAR(13)+CHAR(10)+N'Category=OSIM Configurations>Activated OS installation;'+CHAR(13)+CHAR(10)+N'Table=csm_object;'+CHAR(13)+CHAR(10)+N'Column=dname;'+CHAR(13)+CHAR(10)+N'Crit0PossibleValues=1/1/2005=1/1/2005|;'+CHAR(13)+CHAR(10)+N'Name=Activated OS installation > Activate state exist since;'+CHAR(13)+CHAR(10)+N'From=csm_object;'+CHAR(13)+CHAR(10)+N'SqlTemplate=$target.primarykeyfield$ in ($Crit0Sql$);'+CHAR(13)+CHAR(10)+N'Crit0SqlTemplate=select distinct c.uuid from csm_object c,csm_link l_bc, csm_object bc,csm_v_property bc_pr,csm_property bc_bs where c.class=102 and l_bc.parent = c.id and l_bc.child = bc.id and bc.class = 1004 and bc_bs.object = bc.id and bc_bs.name = ''bootstatus'' and bc_bs.value in (''2000'',''3000'',''4000'',''8000'',''11000'',''20000'',''21000'',''22000'') and bc_pr.object = bc.id and bc_pr.name = ''configstatetime'' and bc_pr.i_value $CritOperator$ $CritValue$;'+CHAR(13)+CHAR(10)+N'Where=;'+CHAR(13)+CHAR(10)+N'ProcessSiblings=0;'+CHAR(13)+CHAR(10)+N'' WHERE srcid='ITRM_OSIM_11' AND locid='ENU' AND fldname='Activated OS installation>Activate state exist since'

    UPDATE RPDATFLD SET flddef=N'Crit0PseudoTemplate=Activated OS image property $CritOperator$ ''$CritValueLabel$'';'+CHAR(13)+CHAR(10)+N'Crit1PossibleValues=;'+CHAR(13)+CHAR(10)+N'DisplayEnum=;'+CHAR(13)+CHAR(10)+N'Crit0Type=CT_OBJECT;'+CHAR(13)+CHAR(10)+N'Usage=0;'+CHAR(13)+CHAR(10)+N'CriteriaCount=2;'+CHAR(13)+CHAR(10)+N'Crit1SqlTemplate=and oi_pr.value $CritOperator$ ''$CritValue$'';'+CHAR(13)+CHAR(10)+N'PseudoTemplate=($Crit0Pseudo$$Crit1Pseudo$);'+CHAR(13)+CHAR(10)+N'Crit0Name=OS image properties;'+CHAR(13)+CHAR(10)+N'Crit0ValueSelector=VS_LIST;'+CHAR(13)+CHAR(10)+N'Crit1Type=CT_STRING;'+CHAR(13)+CHAR(10)+N'Category=OSIM Configurations>Activated OS installation;'+CHAR(13)+CHAR(10)+N'Table=csm_object;'+CHAR(13)+CHAR(10)+N'Crit1PseudoTemplate= AND OS image properties value $CritOperator$ ''$CritValueLabel$'';'+CHAR(13)+CHAR(10)+N'Column=dname;'+CHAR(13)+CHAR(10)+N'Crit0PossibleValues=batchfile=batchfile|sdcomment=sdcomment|sdversion=sdversion|sdname=sdname|locale=locale|;'+CHAR(13)+CHAR(10)+N'Crit1Required=0;'+CHAR(13)+CHAR(10)+N'Name=Activated OS installation > OS image properties;'+CHAR(13)+CHAR(10)+N'From=csm_object;'+CHAR(13)+CHAR(10)+N'SqlTemplate=$target.primarykeyfield$ in ($Crit0Sql$ $Crit1Sql$);'+CHAR(13)+CHAR(10)+N'Crit0SqlTemplate=select c.uuid from csm_object c,csm_link l_bc, csm_object bc,csm_property bc_bs,csm_link l_oi, csm_object oi,csm_property oi_pr where c.class=102 and l_bc.parent = c.id and l_bc.child = bc.id and bc.class = 1004 and bc_bs.object = bc.id and bc_bs.name like ''bootstatus'' and bc_bs.value in (''2000'',''3000'',''4000'',''8000'',''11000'',''20000'',''21000'',''22000'') and l_oi.parent = bc.id and l_oi.child = oi.id and oi.class = 1008 and oi_pr.object = oi.id and oi_pr.name $CritOperator$ ''$CritValue$'';'+CHAR(13)+CHAR(10)+N'Crit1Name=OS image properties value;'+CHAR(13)+CHAR(10)+N'Crit1ValueSelector=VS_EDIT;'+CHAR(13)+CHAR(10)+N'Where=;'+CHAR(13)+CHAR(10)+N'ProcessSiblings=0;'+CHAR(13)+CHAR(10)+N'' WHERE srcid='ITRM_OSIM_11' AND locid='ENU' AND fldname='Activated OS installation>OS image properties'

    UPDATE RPDATFLD SET flddef=N'Crit0PseudoTemplate=State exist since $CritOperator$ ''$CritValueLabel$'';'+CHAR(13)+CHAR(10)+N'DisplayEnum=;'+CHAR(13)+CHAR(10)+N'Crit0Type=CT_DATE;'+CHAR(13)+CHAR(10)+N'Usage=0;'+CHAR(13)+CHAR(10)+N'CriteriaCount=1;'+CHAR(13)+CHAR(10)+N'PseudoTemplate=($Crit0Pseudo$);'+CHAR(13)+CHAR(10)+N'Crit0Name=State exist since;'+CHAR(13)+CHAR(10)+N'Crit0ValueSelector=VS_EDITABLELIST;'+CHAR(13)+CHAR(10)+N'Category=OSIM Configurations>Current OS installation;'+CHAR(13)+CHAR(10)+N'Table=csm_object;'+CHAR(13)+CHAR(10)+N'Column=dname;'+CHAR(13)+CHAR(10)+N'Crit0PossibleValues=1/1/2005=1/1/2005|;'+CHAR(13)+CHAR(10)+N'Name=Current OS installation > Current state exist since;'+CHAR(13)+CHAR(10)+N'From=csm_object;'+CHAR(13)+CHAR(10)+N'SqlTemplate=$target.primarykeyfield$ in ($Crit0Sql$);'+CHAR(13)+CHAR(10)+N'Crit0SqlTemplate=select distinct c.uuid from csm_object c,csm_link l_bc, csm_object bc,csm_v_property bc_pr,csm_property bc_bs where c.class=102 and l_bc.parent = c.id and l_bc.child = bc.id and bc.class = 1004 and bc_bs.object = bc.id and bc_bs.name = ''bootstatus'' and bc_bs.value=''1000'' and bc_pr.object = bc.id and bc_pr.name = ''configstatetime'' and bc_pr.i_value $CritOperator$ $CritValue$;'+CHAR(13)+CHAR(10)+N'Where=;'+CHAR(13)+CHAR(10)+N'ProcessSiblings=0;'+CHAR(13)+CHAR(10)+N'' WHERE srcid='ITRM_OSIM_11' AND locid='ENU' AND fldname='Current OS installation>Current state exist since'

    UPDATE RPDATFLD SET flddef=N'Crit0PseudoTemplate=Current OS image property $CritOperator$ ''$CritValueLabel$'';'+CHAR(13)+CHAR(10)+N'Crit1PossibleValues=;'+CHAR(13)+CHAR(10)+N'DisplayEnum=;'+CHAR(13)+CHAR(10)+N'Crit0Type=CT_OBJECT;'+CHAR(13)+CHAR(10)+N'Usage=0;'+CHAR(13)+CHAR(10)+N'CriteriaCount=2;'+CHAR(13)+CHAR(10)+N'Crit1SqlTemplate=and oi_pr.value $CritOperator$ ''$CritValue$'';'+CHAR(13)+CHAR(10)+N'PseudoTemplate=($Crit0Pseudo$$Crit1Pseudo$);'+CHAR(13)+CHAR(10)+N'Crit0Name=OS image properties;'+CHAR(13)+CHAR(10)+N'Crit0ValueSelector=VS_LIST;'+CHAR(13)+CHAR(10)+N'Crit1Type=CT_STRING;'+CHAR(13)+CHAR(10)+N'Category=OSIM Configurations>Current OS installation;'+CHAR(13)+CHAR(10)+N'Table=csm_object;'+CHAR(13)+CHAR(10)+N'Crit1PseudoTemplate= AND OS image properties value $CritOperator$ ''$CritValueLabel$'' ;'+CHAR(13)+CHAR(10)+N'Column=dname;'+CHAR(13)+CHAR(10)+N'Crit0PossibleValues=batchfile=batchfile|sdcomment=sdcomment|sdversion=sdversion|sdname=sdname|locale=locale|;'+CHAR(13)+CHAR(10)+N'Crit1Required=0;'+CHAR(13)+CHAR(10)+N'Name=Current OS installation > OS image properties;'+CHAR(13)+CHAR(10)+N'From=csm_object;'+CHAR(13)+CHAR(10)+N'SqlTemplate=$target.primarykeyfield$ in ($Crit0Sql$ $Crit1Sql$);'+CHAR(13)+CHAR(10)+N'Crit0SqlTemplate=select c.uuid from csm_object c,csm_link l_bc, csm_object bc,csm_property bc_bs,csm_link l_oi, csm_object oi,csm_property oi_pr where c.class=102 and l_bc.parent = c.id and l_bc.child = bc.id and bc.class = 1004 and bc_bs.object = bc.id and bc_bs.name like ''bootstatus'' and bc_bs.value=''1000'' and l_oi.parent = bc.id and l_oi.child = oi.id and oi.class = 1008 and oi_pr.object = oi.id and oi_pr.name $CritOperator$ ''$CritValue$'';'+CHAR(13)+CHAR(10)+N'Crit1Name=OS image properties value;'+CHAR(13)+CHAR(10)+N'Crit1ValueSelector=VS_EDIT;'+CHAR(13)+CHAR(10)+N'Where=;'+CHAR(13)+CHAR(10)+N'ProcessSiblings=0;'+CHAR(13)+CHAR(10)+N'' WHERE srcid='ITRM_OSIM_11' AND locid='ENU' AND fldname='Current OS installation>OS image properties'

    UPDATE RPDATFLD SET flddef=N'Crit0PseudoTemplate=Planned OS image property $CritOperator$ ''$CritValueLabel$'';'+CHAR(13)+CHAR(10)+N'Crit1PossibleValues=;'+CHAR(13)+CHAR(10)+N'DisplayEnum=;'+CHAR(13)+CHAR(10)+N'Crit0Type=CT_OBJECT;'+CHAR(13)+CHAR(10)+N'Usage=0;'+CHAR(13)+CHAR(10)+N'CriteriaCount=2;'+CHAR(13)+CHAR(10)+N'Crit1SqlTemplate=and oi_pr.value $CritOperator$ ''$CritValue$'';'+CHAR(13)+CHAR(10)+N'PseudoTemplate=($Crit0Pseudo$$Crit1Pseudo$);'+CHAR(13)+CHAR(10)+N'Crit0Name=OS image properties;'+CHAR(13)+CHAR(10)+N'Crit0ValueSelector=VS_LIST;'+CHAR(13)+CHAR(10)+N'Crit1Type=CT_STRING;'+CHAR(13)+CHAR(10)+N'Category=OSIM Configurations>Planned OS installation;'+CHAR(13)+CHAR(10)+N'Table=csm_object;'+CHAR(13)+CHAR(10)+N'Crit1PseudoTemplate= AND OS image properties value $CritOperator$ ''$CritValueLabel$'';'+CHAR(13)+CHAR(10)+N'Column=dname;'+CHAR(13)+CHAR(10)+N'Crit0PossibleValues=batchfile=batchfile|sdcomment=sdcomment|sdversion=sdversion|sdname=sdname|locale=locale|;'+CHAR(13)+CHAR(10)+N'Crit1Required=0;'+CHAR(13)+CHAR(10)+N'Name=Planned OS installation > OS image properties;'+CHAR(13)+CHAR(10)+N'From=csm_object;'+CHAR(13)+CHAR(10)+N'SqlTemplate=$target.primarykeyfield$ in ($Crit0Sql$ $Crit1Sql$);'+CHAR(13)+CHAR(10)+N'Crit0SqlTemplate=select c.uuid from csm_object c,csm_link l_bc, csm_object bc,csm_property bc_bs,csm_link l_oi, csm_object oi,csm_property oi_pr where c.class=102 and l_bc.parent = c.id and l_bc.child = bc.id and bc.class = 1004 and bc_bs.object = bc.id and bc_bs.name like ''bootstatus'' and bc_bs.value=''10000'' and l_oi.parent = bc.id and l_oi.child = oi.id and oi.class = 1008 and oi_pr.object = oi.id and oi_pr.name $CritOperator$ ''$CritValue$'';'+CHAR(13)+CHAR(10)+N'Crit1Name=OS image properties value;'+CHAR(13)+CHAR(10)+N'Crit1ValueSelector=VS_EDIT;'+CHAR(13)+CHAR(10)+N'Where=;'+CHAR(13)+CHAR(10)+N'ProcessSiblings=0;'+CHAR(13)+CHAR(10)+N'' WHERE srcid='ITRM_OSIM_11' AND locid='ENU' AND fldname='Planned OS installation>OS image properties'

    UPDATE RPDATFLD SET flddef=N'Crit0PseudoTemplate=State exist since $CritOperator$ ''$CritValueLabel$'';'+CHAR(13)+CHAR(10)+N'DisplayEnum=;'+CHAR(13)+CHAR(10)+N'Crit0Type=CT_DATE;'+CHAR(13)+CHAR(10)+N'Usage=0;'+CHAR(13)+CHAR(10)+N'CriteriaCount=1;'+CHAR(13)+CHAR(10)+N'PseudoTemplate=($Crit0Pseudo$);'+CHAR(13)+CHAR(10)+N'Crit0Name=State exist since;'+CHAR(13)+CHAR(10)+N'Crit0ValueSelector=VS_EDITABLELIST;'+CHAR(13)+CHAR(10)+N'Category=OSIM Configurations>Planned OS installation;'+CHAR(13)+CHAR(10)+N'Table=csm_object;'+CHAR(13)+CHAR(10)+N'Column=dname;'+CHAR(13)+CHAR(10)+N'Crit0PossibleValues=1/1/2005=1/1/2005|;'+CHAR(13)+CHAR(10)+N'Name=Planned OS installation > Planned State exist since;'+CHAR(13)+CHAR(10)+N'From=csm_object;'+CHAR(13)+CHAR(10)+N'SqlTemplate=$target.primarykeyfield$ in ($Crit0Sql$);'+CHAR(13)+CHAR(10)+N'Crit0SqlTemplate=select distinct c.uuid from csm_object c,csm_link l_bc, csm_object bc,csm_v_property bc_pr,csm_property bc_bs where c.class=102 and l_bc.parent = c.id and l_bc.child = bc.id and bc.class = 1004 and bc_bs.object = bc.id and bc_bs.name = ''bootstatus'' and bc_bs.value=''10000'' and bc_pr.object = bc.id and bc_pr.name = ''configstatetime'' and bc_pr.i_value $CritOperator$ $CritValue$;'+CHAR(13)+CHAR(10)+N'Where=;'+CHAR(13)+CHAR(10)+N'ProcessSiblings=0;'+CHAR(13)+CHAR(10)+N'' WHERE srcid='ITRM_OSIM_11' AND locid='ENU' AND fldname='Planned OS installation>Planned State exist since'
    .
  • Open a new DSM Explore and recreate the AM Query for OSIM Computers.

Additional Information

This database update should be included in next major release of Client Automation

Powered by Wolken Software