I have a Magnolia FreeMarker template that is repeatedly executing (1,400+ times per page load) a simple but unnecessary select by ID JCR query. For example:
select BUNDLE_DATA FROM BUNDLE WHERE NODE_ID = ?
Because of the complexity of the FreeMarker code, I'm unable to tell which line(s) is causing the repeated query.
How do I debug JCR queries in Magnolia so that I can determine the FreeMarker code that is causing the repeated queries?
Going at DB level is going too deep. What the query above is, is JCR request to get all the properties of node w/ ID equal to NODE_ID, but as you say, it is meaningless in terms of telling you where/what operation is causing it.
Could be anything from:
ctx.getJCRSession('some_workspace').getNode('some_path')
to:
node.getNode('some_subpath')
to:
searchfn.searchPages('...')
or even:
cmsfn.children(node)
and possibly few more.
To further complicate the things, you would see query on the DB only if local (in-memory) jcr cache didn't contain the item that your template has requested so you will not even catch all the requests for content at DB level reliably.
One thing is for sure, requesting 1k+ nodes for single template rendering is in most cases indication that you are doing something wrong (unless you indeed want to render some kind of summary or overview going over thousands nodes).
Easiest way forward is to stop to think about logic in your template first. If it contains multiple components, you try to nail it down to given component to limit the search space, eg by removing components one by one and observing the changes.
If it boils down to some JCR query being executed, you can configure debug level logging directly on:
info.magnolia.templating.functions.SearchTemplatingFunctions
This is assuming you were using searchfn to do the search from the template. For other usecases debugging would be bit harder as ordinary JCR Node API is used to get also all other content (e.g. configuration related), not just one that is rendered on the page.
Likely case is that it is a single command or loop in your template causing it so timing execution of different parts of template can also give you clue and help narrow down the problem. If you share the template itself, it might be possible (but not guaranteed) that we can spot in it something and give you more precise indicators.