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

[J D]

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.

Best regards,

J.D.

[Davin Studer · Accepted solution]

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.

[Felix Grund (Scandio)]

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?

[Christian Koch]

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

[J D]

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

[Davin Studer]

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.

[Felix Grund (Scandio)]

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!

[J D]

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

[lcc]

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

[J D]

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.

[Davin Studer]

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

[J D]

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.

[Felix Grund (Scandio)]

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!

[Christian Koch]

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

[J D]

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

[Felix Grund (Scandio)]

Yes I will.

[Felix Grund (Scandio)]

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]

[J D]

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.