Level 4

Solved

Adobe Aem 6.4.8.4 - SQL2 Queries

Forum|Forum|2 years ago
January 12, 2024
5 replies
3202 views

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.

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.

Best answer by Raja_Reddy

Hi @robertol6836527

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

Kamal_Kishor

Community Advisor

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

robertol6836527Author

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.

Kamal_Kishor

Community Advisor

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

Raja_Reddy

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

robertol6836527Author

Level 4

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

Raja_Reddy

Accepted solution

Community Advisor

Hi @robertol6836527

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

Community Advisor

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

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

robertol6836527Author

Level 4

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

Community Manager

@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

A

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?

Sign up

Login with SSO

Login to the community

Login with SSO

Scanning file for viruses.

This file cannot be downloaded