Need help on SQL2 under AEM 6.5

[FarzanaA]

Hello All,

We are having lot of folders under AEM / DAM / Assets folder. To retrieve the folders we are firing a query under SQL2 through CRDXLite.

"select * from [nt:unstructured] where ISDESCENDANTNODE([/content/dam/all-campaigns]) AND [OWNEDBY]='{name}' and [CAMPAIGNNAME] IS NOT NULL order by [CAMPAIGNNAME]ASC

But when we execute this query we are getting an error " The query read or traversed more than 100000 nodes. To avoid affecting other tasks, processing was stopped". (snapshot attached for reference with the error).

But if we fire "select * from [dam:Asset]" it returns the result with more that 100000 records.

Not sure what we are missing here. Can someone please help.

Thanks you all in advance.

Regards.

[aanchal-sikka]

Hello @FarzanaA 

The issue might be with the properties that are used. Please cross-check that they are part of the index for dam:Asset. Example OWNEDBY and CAMPAIGNNAME

[FarzanaA]

Hi Aanchal,

Thanks for the quick reply. We will definitely try this out. And if any query will let you know.

Thanks once again.

[arunpatidar · Accepted solution]

Try to improve query with exact node name or path

e.g.

select * from [dam:Asset] where ISDESCENDANTNODE([/content/dam/all-campaigns]) AND [jcr:content/metadata/OWNEDBY]='{name}' and [jcr:content/metadata/CAMPAIGNNAME] IS NOT NULL order by [jcr:content/metadata/CAMPAIGNNAME] ASC

[FarzanaA]

Hi Arun,

Thank for your help it worked fine.

Thanks & Regards

Farzana.

[kautuk_sahni]

@FarzanaA I hope you found the AEM community helpful. We look forward to your return as either a learner or a contributor. The community thrives with SMEs like you. Please encourage your AEM peers to join and contribute. Happy AEM learning!