At the Moment PocketQuery runs the SQL statements on every call-up of an page, am i right? Please correct me if not
Is it possible to get PocketQuery to run the SQL statements just at night or another custom time? Because i´m afraid of to much load on our database if many people open the pages with the macros in it.
One thing you could do if you want to cache the results without buying the cache plugin you could do it with a stored procedure. We use the SQL plugin from Bob Swift and have done this for some reports, but I imagine it could work just the same with PocketQuery. Here is how we set up the db for this.
We have two tables and a stored procedure. One table stores the times that the report was run. The other table stores the results of the query. A confluence page runs the stored procedure. The stored procedure will first check when the last time the report was run. If it was yesterday then it will truncate the results table, re-run the report, put the results into the result table, and log the time that the report was run. Then it will do a select from the results table.
So, the actual heavy query only gets run once a day by the first person to access the page. Anyone that accesses the page after that will just get the results that came about by the first person that came to the page that day.
We use the Cache for Confluence addon along with PocketQuery.
Wrap your PocketQuery with the Cache, set the refresh period to 1 day.
Then using a curl command you can trigger the cache refresh by passing the parameter refresh=true to the page. You can put the curl command in a cron job and have it refresh the cache when your database is not so busy, say 5AM in the morning. Then any page views will pull the cached version until the cache is either refreshed or the refresh period has expired.
The curl command would look something like this:
curl -u <user>:<password> https://<confluence server>/display/<SpaceName>/<Name of page>?refresh=true
To get the link to your page, hit the 'k' key and copy the content of the Link field.
Hope that helps,
Hey Lynn, Thanks for that, i saw this too. +1 for that good hint and the explanation. But its nothing for me, i just wanted to ask the developers or someone else for a solution that i can handle within the plugin, actually. Sorry i didnt wrote that into my question. Best regards J.D.
Hi J D!
The topic of caching has regularly come to our minds (and in other posts here) associated with PocketQuery. You are right with your assumption for now. We have two aspects on our roadmap to improve the plugin in this:
However, I can't really estimate how long it will take us until this is implemented and released.
We haven't heard of serious performance problems with the current versions if the macro is used in a "reasonable" way. By reasonable I'm talking e.g. of not more than a few PocketQuery macros per page and not too expensive SQL queries.
Do you have any ideas on how the request loads on the pages with PocketQuery will be?
Regards, Felix [Scandio]
Hi Felix, thanks for your quick response. I didnt tried it on our test db, yet. It just comes into my mind if we would use big, expensive queries, thats why i wanted to ask you about that. On thing i can think about is to let the queries to run when the admin sets them. Also a option would be nice where you can set for each query, how often they run in the backround and cache the data i need. As you said, you dont have any experience with bigger queries, is that right? How about you make a example of a very expensive query, which you can show on your website, to let possible users see what the can do at the maximum, or ask your customers. However i dont think we will make that expensive queries too, but it´s just a "good to know" thing for me. :) Best regards J.D.
The solution with the Cache macro is good in most cases. However, please note that the PocketQuery can run with dynamic parameters! There are wildcards like @page, @username, etc. Also, you can enable dynamic GET parameters. Thus, the result of the macro will be very different, depending on what is passed. So e.g. if the site is requested with GET parameters (there is even a template where users can change the GET parameters dynamically) or by different users and the @user wildcard is used, the Cache macro will produce incorrect and wrong data. This can become a serious problem! So only use the cache macro if the parameters of the macro will never change dynamically!
This community is celebrating its one-year anniversary and Atlassian co-founder Mike Cannon-Brookes has all the feels.Read more
Hi Community! Kesha (kay-sha) from the Confluence marketing team here! Can you share stories with us on how your non-technical (think Marketing, Sales, HR, legal, etc.) teams are using Confluen...
Connect with like-minded Atlassian users at free events near you!Find a group
Connect with like-minded Atlassian users at free events near you!
Unfortunately there are no AUG chapters near you at the moment.Start an AUG
You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs