Adobe Aem 6.4.8.4 - SQL2 Queries

[robertol6836527]

Good morning,

I have a problem related to the query. I have two environments, one local and one production with the same "content" and "applications".

When I run the query, shown below, from the "Query" tools on the "/crx/de/index.jsp" page using the admin account on the local machine it extracts all the data, when I repeat the query in production on the same data and with the admin account it extracts only a subset of the data.

The query is as follows:

SELECT * FROM [nt:base] AS node WHERE ISDESCENDANTNODE([/content/myproject/pages/]) AND (node.[sling:resourceType] = 'myproject/components/structure/page' )

How can I align production behavior with local behavior ?

Thank you.

[Kamal_Kishor]

@robertol6836527 : It looks like a permission issue but then you have mentioned that you are using admin account. Is the user you are saying for prod the administrator or just a custom user which is part of admin group?

To rule out the access issue, can you try this-
From your subset which is not appearing in your production instance query, identify few result items and manually try to access them to see if you can access them using your admin account. This should at least rule out if it is due to access on prod.

[robertol6836527]

HI,

I'm using the CMS "admin" user.

I checked and it has all the permissions.

I noticed another thing: the query result is the same if I eliminate the following condition: AND (node.[sling:resourceType] = 'myproject/components/structure/page' )

Thank you.

[Kamal_Kishor]

@robertol6836527: Thanks for providing more details. If you are seeing matching results after changing the condition then it is not access issue.

Is it possible for you to run this in querybuilder debugger- http://localhost:4502/libs/cq/search/content/querydebug.html

(replace localhost:4502 with your prod instance url). Your query will change but essentially mean the same thing.

path=/content/myproject/pages
type=cq:PageContent
property=sling:resourceType
property.value=myproject/components/structure/page

This will search for all pages under "/content/myproject/pages" which are creating using component "myproject/components/structure/page".

If it still shows a difference, then try to find some pages from subset and check if somehow this property is missing at their jcr:content node level, which would then mean that your content is not in sync b/w both environments.
Please try and do let me know if it works.

[robertol6836527]

Hi,

I ran the test from the "libs/cq/search/content/querydebug.html" brush and I get the same results as the "Crx Query" panel.

In production environment, using the same development environment query, I always get less results than development environment.

Thanks for your answer.

[Kamal_Kishor]

@robertol6836527 : If you can identify few result items which are missing in production, get the resource/page path of them and check in prod to see what is the value of 'sling:resourceType' property.
for eg: /content/myproject/pages/missing-page-in-query-on-prod/jcr:content (look for property value, it should be exactly 'myproject/components/structure/page'). If it is not, then there is some issue with your content.

[robertol6836527]

HI,
I checked and the contents are correct.
Thanks for the reply

[Raja_Reddy]

Hi @robertol6836527 
Use Query builder and check it
http://localhost:4502/libs/cq/search/content/querydebug.html 
type=nt:unstructured
path=/content/myproject/pages
group.p.or=true
group.1_property=sling:resourceType
group.1_property.value=myproject/components/structure/page
group.1_property.operation=like
p.limit=-1

Thanks
Raja

[robertol6836527]

HI,
I carried out the test and unfortunately the behavior does not change, the problem persists.
Thanks for the reply

[Raja_Reddy · Accepted solution]

Hi @robertol6836527 

1. Query Performance: Check if there are performance differences between the environments. It's possible that the production environment is taking longer to execute the query and reaching a timeout. Check AEM logs for any performance-related issues.

2. Query Execution Plan: In some databases, you can analyze the query execution plan. Although AEM's JCR (Java Content Repository) is not a traditional database, you might find some clues in the logs or performance monitoring tools.
   
   Thanks.

[narendragandhi]

Hi @robertol6836527 There could be differences in the indexes it uses between different environments.

Explain plan would help to check more on this. Please refer the below article for details on the query explain plan - - https://sourcedcode.com/blog/aem/optimizing-debugging-search-queries-explain-query-tool-aem

[Sudheer_Sundalam]

Hi @robertol6836527 ,

As @narendragandhi stated, it could be mostly due to the differences in the indexes between local and prod servers.

1) Use the Explain Query to check which indexes are used to run your query in both local and prod. http://localhost:4502/libs/granite/operations/content/diagnosistools/queryPerformance.html

2) Create the index if required and re-index the content again.

As per your query, your index should look like below

  - compatVersion = 2
  - async = "async"
  - queryPaths = [/content/myproject/pages]
  - includedPaths = [/content/myproject/pages]
  - jcr:primaryType = oak:QueryIndexDefinition
  - evaluatePathRestrictions = true
  - type = "lucene"
  + indexRules 
   + nt:base 
    + properties 
     + resourceType 
      - name = "sling:resourceType"
      - propertyIndex = true

You may use this utility to generate the required indexes based on the query: https://oakutils.appspot.com/generate/index

[robertol6836527]

HI,
I also created an index on the field used by the query, I restarted the cms and waited a day for indexing, but the result doesn't change.
Thanks for the reply.

[kautuk_sahni]

@robertol6836527 Did you find the suggestions from users helpful? Please let us know if more information is required. Otherwise, please mark the answer as correct for posterity. If you have found out solution yourself, please share it with the community.

[alex89raso1]

Hi, i have the same problem.

the query not match correctly resourceType.

With the following query i have partial result

SELECT * FROM [nt:base] AS node WHERE ISDESCENDANTNODE([/content/myproject/pages/]) AND (node.[sling:resourceType] = 'myproject/components/structure/page' )

If i edit like this i retrieve all results

SELECT * FROM [nt:base] AS node WHERE ISDESCENDANTNODE([/content/myproject/pages/]) AND (node.[sling:resourceType] LIKE '%myproject/components/structure/page' )

Same behavior using query builder or crx query tool.

If i run a query execution plan i've the same wrong number of result.

Is there any bundle that if corrupted can interfere with the correct functioning of queries?