Expand my Community achievements bar.

Dive into Adobe Summit 2024! Explore curated list of AEM sessions & labs, register, connect with experts, ask questions, engage, and share insights. Don't miss the excitement.
SOLVED

Adobe Aem 6.4.8.4 - SQL2 Queries

Avatar

Level 4

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.

1 Accepted Solution

Avatar

Correct answer by
Community Advisor

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.

View solution in original post

14 Replies

Avatar

Level 8

@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.

Avatar

Level 4

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.

 

 

Avatar

Level 8

@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.

 

Avatar

Level 4

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.

Avatar

Level 8

@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.

Avatar

Community Advisor

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

Avatar

Level 4

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

Avatar

Correct answer by
Community Advisor

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.

Avatar

Level 7

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

Avatar

Community Advisor

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

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.

Avatar

Administrator

@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.



Kautuk Sahni

Avatar

Level 1

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?