Can i get PocketQuery to run SQL statements not every call-up of an page?

At the Moment PocketQuery runs the SQL statements on every call-up of an page, am i right? Please correct me if not smile

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.

 

Best regards,

J.D.

5 answers

1 accepted

This widget could not be displayed.
Davin Studer Community Champion Nov 12, 2014

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.

Hi Davin! This sounds like a very promising approach and it should work well with PocketQuery too! Do I understand you correctly that this is solely a database configuration topic and you don't have to implement anything in your plugin?

this is a database centric caching approach. I like that. But eventually we have to think about an PocketQuery internal caching.

That would be a nice workaround, till the guys from scandio impemented a internal caching, like Christian said.

Davin Studer Community Champion Nov 13, 2014

Yeah, it's totally just a database thing. The report we were running took about five seconds and I didn't want the page to seem slow. And now it renders lickity split.

It's cool! Like Christian said it's a very good solution for many scenarios where you can configure this on the database level. It doesn't replace a caching feature in PocketQuery itself, however. Thanks for contributing!

Marked this as the answer, because its a nice solution for our non-existent (yet) problem. :) Thank you Davin.

This widget could not be displayed.

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,

Lynn

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.

This widget could not be displayed.
Davin Studer Community Champion Nov 11, 2014

I think the Cache for Confluence addon could do this for you.

Hey David, Thanks for that, i saw this too. 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.

This widget could not be displayed.

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:

  • Implementation of a pooled JDBC connection
  • Implementation of a DB result cache

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.

This widget could not be displayed.

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!

It's always the same. If you try to cache dynamic queries results - thinking about that is a prerequisite :-)

Thank you for this contribution. You should mention this in your docs, as advance warning. So that the people are aware of this.

Suggest an answer

Log in or Sign up to answer
Community showcase
Posted Monday in Confluence

Why start from scratch? Introducing four new templates for Confluence Cloud

Hi my Community friends!  For those who don't know me, I'm a product marketer on the Confluence Cloud team - nice to meet you! For those of you who do, you know that I've been all up in your Co...

469 views 6 6
Join discussion

Atlassian User Groups

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!

Find my local user group

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

Groups near you