Coded something up in Couch in an interesting way? Have a snippet or shortcode to share? Post it here for the community to benefit.
11 posts Page 1 of 2
Hi guys, I'm using couchcms a lot and one tag that I always found very useful is <cms:pages> to list the pages or to get data from other pages. As I learned in the docs and here in this forum, pages tag can be very slow as it makes lots of queries and when you are logged. Also the couchcms won't cache when you are logged in and depending on your implementation, you'll get a very slow page-load while working on the content.

I also learned that <cms:get_custom_field> is faster as it is more straight foward, but sometimes (usually) I just need to get a field from all pages of a clonable template. Thinking of that, I created a "helper" that can do this in a much faster way and it's being very useful to me (I had a page that took 20 seconds to load, now it takes 600-800 ms)

I created a file "get-page-list.php" inside my include folder:

Code: Select all
<cms:set arr_page_names="" "global" />
<cms:query sql="
  select p.id, p.name
  from <cms:php>echo K_TBL_TEMPLATES;</cms:php> p
  where p.name='<cms:show template_name />'"
  limit='1'>
  <cms:set template_id=id "global" />
</cms:query>

<cms:query sql="
  SELECT p.template_id, p.page_name
  FROM <cms:php>echo K_TBL_PAGES;</cms:php> p
  WHERE p.template_id='<cms:show template_id />'">
  <cms:if arr_page_names == "">
    <cms:set arr_page_names=page_name "global" />
  <cms:else />
    <cms:set arr_page_names="<cms:concat arr_page_names '|' page_name />" "global" />
  </cms:if>
</cms:query>


And when I need to get the page list I do this:

Code: Select all
<cms:set template_name="name_of_the_template.php" />
<cms:embed "get-page-list.php" />


It will define a variable "arr_page_names" that I can loop and get only the field values I want:

Code: Select all
<cms:each arr_page_names>
  <cms:get_custom_field var="field_name" page=item masterpage=template_name />
</cms:each>


It's not "pretty" but very effective. I looked at couchcms source code and this was the best solution I could find to get the list of clonable pages (with something around 50 entries) multiple times in a page without having performance issues.

Do anyone has a better idea to do this? Maybe something more "native"?
Made the helper code a little shorter and better by changing it to only one query:

Code: Select all
<cms:set arr_page_names="" "global" />
<cms:query sql="
   SELECT p.page_name FROM <cms:php>echo K_TBL_PAGES;</cms:php> p WHERE p.template_id IN
   (SELECT t.id FROM <cms:php>echo K_TBL_TEMPLATES;</cms:php> t WHERE t.name='<cms:show template_name />');
">
   <cms:if arr_page_names == "">
      <cms:set arr_page_names=page_name "global" />
   <cms:else />
      <cms:set arr_page_names="<cms:concat arr_page_names '|' page_name />" "global" />
   </cms:if>
</cms:query>


The only thing is that I can't get "k_" vars (eg.: "k_page_title") via <cms:get_custom_field>. I'm thinking about how doing it without <cms:pages>... any ideas?
Hi @mcarneiro, thanks for your post.

Can you also test something for us and share the compared results?

Code: Select all
<cms:pages masterpage='your-template.php' skip_custom_fields='1' >
   
     <cms:get_custom_field var="field_name" page=k_page_name masterpage=k_template_name />

</cms:pages>


Thanks ;)
Mmmmm, much better to do this way! Didn't know about "skip_custom_fields". This is like a hidden feature or easter-egg :P

Now I see on the source code that KWebpage is called and inside it'll call all custom fields. Now everything makes sense.

Thanks a lot!
It's nothing, really :) Always welcome!

Another thing is to update your query to fetch exactly what you need. I'm not a pro with cms:query, but probably you could make it load whatever field is thrown at it. A helping hand here is return_sql='1' of cms:pages tag (and related siblings). This parameter can really help a lot with digging into it! cms:pages is a mere 'sql query constructor' and therefore relevant cms:query shouldn't have a noticeable difference with it. Hope you share more topics about this :D Good luck!



Would like to comment upon some quotes
pages tag can be very slow as it makes lots of queries
This is probably true for nested_pages, but cms:pages is pretty fast in itself. I am working with large datasets now and most things happen as expected. We'd love to see a template here which loads data for 20 sec. That's really interesting and you can profit from some suggestions from other experienced guys, especially @KK.

and when you are logged
When logged as super-admin, Couch expects some changes in cms:template block, as super-admin is the main ond only developer :D So, it refreshes cms:template block in a template upon every reload. A huge folder tree can hold this process and make a significant delay. I have experienced up to 15-20 sec in my virtualbox playground machine with over 2000 folders in a 3-level tree. It is weird though, and will be moved to 'relations' editable to make it fast again.
Hi :)

I'd also like to say that I don't agree with the observation that cms:pages tag is necessarily slow.
A 20 sec load time is inordinate and, I suspect, there would be other factors at play too.

That said, as you noticed in your custom query that it does not make available the 'k_' variables. You can, of course, tweak your query further to fetch all that data but then what you'll end up is the same query that cms:pages creates for you (and most certainly in a much easier manner) :)
I love this "speed" topic :D Also, thanks for coming down, @KK :)

I am going to run this test soon:
1. related_pages (titles only) VS simple text editable with multiple values
2. count pages, related to a given page with aggregate_by VS count by cms:pages with custom_field

Accepting bets till tomorrow noon :D
Some tests didn't show much improvement, but I managed to test the approach, suggested by @mcarneiro. 1000 pages, with many editable fields in the template (around 30) and the task was set to get some basic info from it (6 fields total + k_page_link, k_page_title).

Worst result was achieved with cms:pages and usual fetching all data and showing only some of it. Overall result: 3000 queries, 7-8 sec.
Code: Select all
<cms:pages masterpage='index.php' skip_custom_fields='0' >

<td><cms:show sale_status /></td>
....




Now, tested with the suggested approach. No fetching data, but much more requests for single fields.
Code: Select all
<cms:pages masterpage='index.php' skip_custom_fields='1' >

The best result was with skip_custom_fields and fetching all 6 fields like this.
Code: Select all
<cms:set crm_status = "<cms:get_custom_field 'sale_status' masterpage=k_template_name page=k_page_name />" />


Total queries: 13000, Page generated in about 4 sec.

Practically the same result without improvement showed cms:query (no wonders here :| )
Code: Select all
<cms:set my_sql="
     SELECT p.id, p.template_id FROM couch_pages p WHERE p.template_id='45' AND p.publish_date < '2016-05-21 16:17:40' ORDER BY p.publish_date desc
     "    />
<cms:query sql=my_sql paginate='0' fetch_pages='1' skip_custom_fields='1' >



As a result, we can use both :D If number of required fields is about 4 times more than the needed number, it's okay to get results twice as fast at the expense of db-load. Thanks :)
Nice tests. Actually the solution using <cms:pages> with skip_custom_field worked perfectly for me. As KK told, most of the performance issue was propably related to something else.

I saw that issue I had was related with <cms:thumbnail> tag because it always open the files to get the width and height, and the images have around 3500x900px. I' actually opened a issue on the github project with a sugestion :)

Thanks for the help!
Hi @mcarneiro,

I've got some more to add to this thread. It's about the effect of pagination.

As it turns out in my recent test, paginated list of pages can be slowed down if ordered by page_name, page_title and any custom editable. This applies to some huge number of pages, my sample range was way over 100k pages, but the effect can show itself pretty badly on 10k and above. No wonder, mysql has to sort a big table :)

Orderby publish_date is the best option and never lags. 900k paginated pages still load as if nothing happens. random and modification_date bear the task comparatively well.

If some slowness happen somewhere, then order is a thing to pay attention to. So far ORDER BY doesn't look optimized to withstand the heavy load. Thanks.
11 posts Page 1 of 2