Query is running too long

1

My Database table having 1 Lakh record where as below query is not giving the output since 30 mins. Please suggest.

SELECT name, xname.value('(AttributeRequest/@value)[1]', 'VARCHAR(100)') AS MemberOfValue
FROM
(SELECT name,cast(attributes as xml) AS XCol FROM identityiq.identityiq.spt_identity_request) AS T
CROSS APPLY
XCol.nodes('//Attributes/Map/entry/value/ProvisioningProject/MasterPlan/ProvisioningPlan/AccountRequest') AS XNodes(xname)
WHERE
xname.value('(AttributeRequest/@value)[1]', 'VARCHAR(100)') LIKE '%QSDTEAMADMIN%'

2

that should take about forever.. Casting a value as XML then trying to pull out a like is horrendous. Can you throw the first part into temp table with an xml datatype and xml index? Then try to query that

1 Like