Hi Everyone,
We have multiple commserve’s available, in one of the commserve when I tried to execute below SQL query to fetch the data it’s taking more than 7 mins to complete where environment is smaller in size. But when I executed the same SQL query in other 6 to 7 commserve environment’s it was completing in 1 Sec only.
Even I’ve performed the Full DB Maintenance as well, but the issue is still same.
Can someone let me know what could be issue here.
SQL Query:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT A.agentId, A.agentName, A.clientId, A.clientName,
agentStatus =
CASE
WHEN A.clientStatus IN ('uninstalled', 'hidden (unknown)') THEN 'uninstalled'
ELSE A.agentStatus
END,
A.agentBkpEnable, A.agentRstEnable, A.refTime, A.modified
FROM (
SELECT
agentId = iDA.id, agentName = iDAT.name, clientId = iDA.clientId, clientName = C.name,
clientStatus =
CASE
WHEN (C.specialClientFlags & 1) = 1 AND C.id NOT IN (SELECT distinct VMclientId FROM APP_VMProp) THEN 'hidden (unknown)'
WHEN (C.specialClientFlags & 1) = 1 AND C.id IN (SELECT distinct VMclientId FROM APP_VMProp) THEN 'hidden'
WHEN CP.attrval = '1' THEN 'uninstalled'
ELSE 'installed'
END,
agentStatus = ISNULL(
CONVERT(VARCHAR(11), (
SELECT 'installed'
FROM APP_IDAName iDA
WHERE iDA.clientId = C.id
AND iDA.appTypeId = iDAT.type
AND iDA.status & 2 = 0)),
'uninstalled'),
agentBkpEnable =
CASE ( SELECT JA.action FROM JMJobAction JA WHERE C.id = JA.clientid and JA.apptype = iDAT.type and JA.optype = 4 )
WHEN 2 THEN CAST(1 AS bit) --True
--WHEN 1 THEN 0 --NO
--ELSE NULL
ELSE CAST(0 AS bit) --False
END,
agentRstEnable =
CASE (SELECT JA.action FROM JMJobAction JA WHERE C.id = JA.clientid and JA.apptype = iDAT.type and JA.optype = 5)
WHEN 2 THEN CAST(1 AS bit) --True
--WHEN 1 THEN 0 --NO
--ELSE NULL
ELSE CAST(0 AS bit) --False
END,
iDA.refTime,
iDA.modified
FROM APP_iDAName iDA
LEFT JOIN APP_iDAType iDAT ON iDAT.type = iDA.appTypeId
LEFT JOIN APP_Client C ON C.id = iDA.clientId
LEFT JOIN APP_ClientProp CP ON C.id = CP.componentNameId AND CP.attrname = 'PlatformDeleted 4' AND CP.modified = 0
) A
