Hello ARSListers,
I have a customer with a 7.6.04 CMDB reconciliation job that is generating a poor-performing SQL query (roughly 1,000 ms to process). I can't find any filters that generate the query, so I'm guessing it's resulting from a plug-in. There's not much to do about how the plug-in generates its queries unless some of the WHERE clause parameters are stored as data. Does anyone have any insight into whether the ReconciliationIdentity phrase of the WHERE clause below is stored as data somewhere, and if so then where? I'd sure like to replace that ...!= '0' comparison to avoid a full table scan. Here's the raw query right out of the log: 317978 SQL 0001446061 List 390696 Remedy Application Service Thu Feb 02 2017 08:19:04.3394 SELECT * FROM ( SELECT T505.C1,C400129200,C179,C400127400 FROM T505 WHERE ((1 = 1) AND (T505.C400129200 != '0') AND (T505.C400079600 = 'BMC.CORE:BMC_HOSTEDSYSTEMCOMPONENTS') AND ((T505.C530041601 = 30) OR (T505.C530041601 = 60) OR (T505.C530041601 = 50)) AND (T505.C400127400 = 'BMC.ADDM') AND (T505.C530060100 = 40) AND (T505.C400079600 = 'BMC.CORE:BMC_HOSTEDSYSTEMCOMPONENTS')) ORDER BY C400129200 ASC, 1 ASC ) WHERE ROWNUM <= 100001 319025 SQL 0001446061 List 390696 Remedy Application Service Thu Feb 02 2017 08:20:26.6110 OK ...and here's a friendly version: SELECT BMC.CORE:BMC_Component.RequestId, ReconciliationIdentity, InstanceId, DatasetId FROM BMC.CORE:BMC_Component WHERE ( (1 = 1) AND (BMC.CORE:BMC_Component.ReconciliationIdentity != '0') AND (BMC.CORE:BMC_Component.ClassId = 'BMC.CORE:BMC_HOSTEDSYSTEMCOMPONENTS') AND ( (BMC.CORE:BMC_Component.NormalizationStatus = 30) OR (BMC.CORE:BMC_Component.NormalizationStatus = 60) OR (BMC.CORE:BMC_Component.NormalizationStatus = 50) ) AND (BMC.CORE:BMC_Component.DatasetId = 'BMC.ADDM') AND (BMC.CORE:BMC_Component.ReconciliationMergeStatus = 40) AND (BMC.CORE:BMC_Component.ClassId = 'BMC.CORE:BMC_HOSTEDSYSTEMCOMPONENTS') ) ORDER BY ReconciliationIdentity ASC, 1 ASC Thanks for any thoughts, --Phil _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org "Where the Answers Are, and have been for 20 years"
