[MGNLUI-7739] System properties and non string values could use EQUALS instead of LIKE in JCR query Created: 18/Jan/23  Updated: 12/Dec/23  Resolved: 08/Feb/23

Status: Closed
Project: Magnolia UI
Component/s: None
Affects Version/s: 6.2.27
Fix Version/s: 6.3.0, 6.2.29

Type: Improvement Priority: Neutral
Reporter: Antonín Juran Assignee: Antonín Juran
Resolution: Fixed Votes: 0
Labels: None
Σ Remaining Estimate: Not Specified Remaining Estimate: Not Specified
Σ Time Spent: 1d Time Spent: Not Specified
Σ Original Estimate: Not Specified Original Estimate: Not Specified

Issue Links:
Issue split
split from MAGNOLIA-8347 JcrQueryBuilder performance issues. Closed
Problem/Incident
causality
is causing MGNLUI-8573 App column filtering by mgnl:lastModi... Open
Sub-Tasks:
Key
Summary
Type
Status
Assignee
MGNLUI-7805 Implementation Sub-task Completed Antonín Juran  
MGNLUI-7806 Code review Sub-task Completed Sang Ngo Huu  
MGNLUI-7807 Pre-integration QA Sub-task Completed Sang Ngo Huu  
MGNLUI-7808 Final QA Sub-task Completed Jaromir Sarf  
Template:
Acceptance criteria:
Empty
Task DoD:
[X]* Doc/release notes changes? Comment present?
[X]* Downstream builds green?
[X]* Solution information and context easily available?
[X]* Tests
[X]* FixVersion filled and not yet released
[ ]  Architecture Decision Record (ADR)
Release notes required:
Yes
Date of First Response:
Epic Link: [JCR] Search Performance issues
Sprint: Nucleus 29
Story Points: 2
Team: Nucleus
Work Started:

 Description   

Steps to reproduce

  1. Go to https://nightly.magnolia-cms.com/.magnolia/admincentral#app:pages-app:browser
  2. Filter via page template dropdown
  3. Inspect/debug the constructed JCR query performance/timing

Expected results

SELECT * FROM [nt:base] AS t WHERE ISDESCENDANTNODE(t, [/]) AND t.[jcr:primaryType] = 'mgnl:page' AND LOWER(t.[mgnl:template]) = 'mtk2:pages/basic' AND LOWER(t.title) LIKE '%test%' AND LOWER(LOCALNAME(t)) LIKE '%test%'

Actual results

SELECT * FROM [nt:base] AS t WHERE ISDESCENDANTNODE(t, [/]) AND t.[jcr:primaryType] = 'mgnl:page' AND LOWER(t.[mgnl:template]) LIKE '%mtk2:pages/basic%' AND LOWER(t.title) LIKE '%test%' AND LOWER(LOCALNAME(t)) LIKE '%test%'

Workaround

Development notes

JcrQueryBuilder doesn't know about the source field (was it textField or a comboBoxField) but one can assume any system property (prefixed with mgnl:) should be search by equality, therefore the usage of LIKE is redundant.



 Comments   
Comment by Roman Kovařík [ 18/Jan/23 ]

Discovery:

 
Original query:

SELECT * FROM [nt:base] AS t WHERE ISDESCENDANTNODE(t, [/]) AND t.[jcr:primaryType] = 'mgnl:page' AND LOWER(t.[mgnl:template]) LIKE '%mtk2:pages/basic%' AND LOWER(t.title) LIKE '%test%' AND LOWER(LOCALNAME(t)) LIKE '%test%'

14384 nodes returned in 1435ms
14384 nodes returned in 1801ms

Query with "=" if prefix "mgnl:":
SELECT * FROM [nt:base] AS t WHERE ISDESCENDANTNODE(t, [/]) AND t.[jcr:primaryType] = 'mgnl:page' AND LOWER(t.[mgnl:template]) = 'mtk2:pages/basic' AND LOWER(t.title) LIKE '%test%' AND LOWER(LOCALNAME(t)) LIKE '%test%'

14384 nodes returned in 1646ms
14384 nodes returned in 2091ms

ajuran Could you elaborate what is the first number (1435ms) and the second number (1801ms)? Also aren't the results swapped as it looks like the fix takes longer than the original query?

Comment by Antonín Juran [ 18/Jan/23 ]

rkovarik both the numbers are execution times of the original query (2 attempts). Yes if you compare them with execution times of the "query with '=' if prefix 'mgnl'" the original query can take longer then the fix.   

Generated at Mon Feb 12 09:48:54 CET 2024 using Jira 9.4.2#940002-sha1:46d1a51de284217efdcb32434eab47a99af2938b.