Hello

I use the following code to calculate the remainig budget of a supplier on the fly:

Code: Select all

<cms:php>
   echo number_format (
      <cms:show restbudget /> - (
      <cms:reverse_related_pages masterpage='app/data/leads.php' custom_field="status=Aktiv,Verrechnet|testlead!=Ja|datum_verteilt=<cms:show monat />" field='anbieter_relation'>
            <cms:show preis /> + 
      </cms:reverse_related_pages>
   0), 2, ".", "'");
</cms:php>

The template leads.php holds the shipped items and its prize.
Unfortunately when listing all the suppliers (hundreds) and adding the hundreds of item prizes couch gets slow.

Does anyone know how to add up the prizes (editable 'preis') just within the sql statement?

The corresponding sql statement is:

Code: Select all

SELECT p.id, 
       p.template_id 
FROM   couch_pages p 
       INNER JOIN couch_relations rel 
               ON rel.pid = p.id 
       INNER JOIN couch_data_text t0 
               ON t0.page_id = p.id 
       INNER JOIN couch_data_text t1 
               ON t1.page_id = p.id 
       INNER JOIN couch_data_text t2 
               ON t2.page_id = p.id 
WHERE  rel.cid = 13751 
       AND rel.fid = 308 
       AND p.template_id = '54' 
       AND p.publish_date < '2018-01-13 09:28:13' 
       AND NOT p.publish_date = '0000-00-00 00:00:00' 
       AND p.parent_id = 0 
       AND ( t0.field_id = 343 
             AND t1.field_id = 341 
             AND t2.field_id = 345 ) 
       AND ( ( t0.search_value LIKE '%Aktiv%' 
                OR t0.search_value LIKE '%Verrechnet%' ) 
             AND t1.search_value NOT LIKE '%Ja%' 
             AND t2.search_value LIKE '%2018-01%' ) 
ORDER  BY rel.weight DESC 

Regards,
Olliwalli

[trendoman]

At first, I would recommend to avoid %LIKE% operations by using double equal:

custom_field="status==Aktiv,Verrechnet|testlead!==Ja|datum_verteilt==<cms:show monat />"

In theory this should speed up significantly.
I agree though that constructing a custom SQL query is the next level albeit more complicated one. If the above speed improvement is not sufficient, attach an sql dump to play with the db and updated sql query.

Thanks, I will have a look at it and replace like by double equal.

Hi trendoman

I tested the performance with an without double quotes. It speeds up the loading time:

custom_field with "=" / anbieter_ids -> page: 4.379s
custom_field with "=" / anbieter_ids -> each / get_custom_field: 1.927s
custom_field with "==" / anbieter_ids -> page: 3.203s
custom_field with "==" / anbieter_ids -> each / get_custom_field: 1.835s

But the performance relevant part is not the cycling through the "anbieter.php" cloned pages, but in them summing up all the related cloned pages. Here is my code:

Code: Select all

<tr>
   <cms:set time_start="<cms:php>echo microtime(true);</cms:php>" scope='global' />
   <td>
   <cms:set anbieter_ids="<cms:pages masterpage='app/data/anbieter.php' ids_only='1' custom_field='status==5 Aktiv|typ==Direkt' orderby='name' order='asc' limit='20' />" scope='global' />
   <cms:each anbieter_ids sep=','>

      <cms:capture into='restbudget'>
         <cms:set budget="<cms:get_custom_field 'budget' masterpage='app/data/anbieter.php' id=item />" />

         <cms:set restbudget="<cms:show budget />" scope='global' />
         <cms:set monat="<cms:php>date_default_timezone_set('Europe/Zurich'); echo date('Y-m');</cms:php>" scope='global' />
         <cms:php>
            echo number_format (
               <cms:show restbudget /> - (
               <cms:pages masterpage='app/data/anbieter.php' id=item skip_custom_fields='1'>
                  <cms:reverse_related_pages masterpage='app/data/leads.php' custom_field="status==Aktiv,Verrechnet|testlead!==Ja|datum_verteilt=<cms:show monat />" field='anbieter_relation'>
                        <cms:show preis /> + 
                  </cms:reverse_related_pages>
               </cms:pages>
            0), 2, ".", "'");
         </cms:php>

         <cms:embed 'helpers/restbudget.html' />
      </cms:capture>
      <cms:show item />=<cms:show restbudget /> | 
   </cms:each>
   </td>
   <cms:set time_end="<cms:php>echo microtime(true);</cms:php>" scope='global' />

   <td>custom_field with "==" / anbieter_ids -> each / get_custom_field</td>

   <td><cms:php>echo number_format("<cms:sub time_end "<cms:show time_start />" />", 3, ".", "'")."s";</cms:php></td>
</tr>

The relevant part I need to speed up is:

Code: Select all

<cms:php>
   echo number_format (
      <cms:show restbudget /> - (
      <cms:pages masterpage='app/data/anbieter.php' id=item skip_custom_fields='1'>
         <cms:reverse_related_pages masterpage='app/data/leads.php' custom_field="status==Aktiv,Verrechnet|testlead!==Ja|datum_verteilt=<cms:show monat />" field='anbieter_relation'>
               <cms:show preis /> + 
         </cms:reverse_related_pages>
      </cms:pages>
   0), 2, ".", "'");
</cms:php>

So how can I put the calculation within the pages tag into a query that internally sums up all "preis"-editables from the selected cloned pages of "leads.php"?

Something like:

Code: Select all

<cms:php>
   echo number_format (
      <cms:show restbudget /> - (
      <cms:query sql="SELECT SUM(preis) FROM ... WHERE ..." limit='1'>
         <cms:show sum />
      </cms:query>
   0), 2, ".", "'");
</cms:php>

[trendoman]

I'll help with SQL query and code if you could provide sql dump to run tests.

Hi Trendoman

I cannot provide a SQL dump since it is productive data.

But instead I created a labor case for testing the performance. See attached zip file.

It consists of 5 templates. Initialize them in the following order:

1. anbieter.php -> Creates the clonable anbieter template that holds the anbieter data
2. leads.php -> Creates the clonable leads template that holds the leads data that are related to the anbieter
3. initialize_anbieter.php ->Creates 100 cloned pages of anbieter
4. initialize_leads.php ->Creates 100 x 10 cloned pages of leads, 10 leads for each anbieter, related to the anbieter, with a random price value
5. index.php -> measures the performance of 1: count the number of leads for each anbieter, and 2: calculates the sum of the lead prices for each anbieter

If you run initialize_leads more than once, the number of leads increases each time.

The performance of count is about 6 times faster than calculating the sum of the values.

Perofmance results for 10 anbieter with 10, 20, 30, ... leads so far:

anbieter leads per anbieter count sum
10 10 0.062 0.363
10 20 0.069 0.650
10 30 0.073 0.997
10 40 0.076 1.184
10 50 0.076 1.481
10 60 0.066 1.707
10 70 0.069 2.062

The performance of calculating the sum increases linearly with the number of leads whereby counting the number does not get slower.

I tested it with couch 2.0.

Now the question is, is it possible to calculate these sums within a SQL statement with better performance?

Regards,
Olliwalli

Anyone an idea how to optimize the following statement

Code: Select all

<cms:php>
   echo number_format (
      (
      <cms:reverse_related_pages masterpage='leads.php' field='anbieter'>
            <cms:show preis /> + 
      </cms:reverse_related_pages>
      0), 2, ".", "'");
</cms:php>

into an sql query that sums it up faster?

[trendoman]

[quote="olliwalli"][/quote]
Hi, I finally have some time to write an SQL statement for you.

Analyzing your posted SQL statement from the first post, I see that your price field is defined as text one, not number (see http://docs.couchcms.com/tags-reference ... earch_type ), so this definitely slows db a bit to convert text value to numeric. CouchCMS stores text values in a different table, couch_data_text and numeric data in couch_data_numeric, so it is important for sql and speed, because columns and indexes in those tables are defined differently.

Next, in your attachment sample there is no custom_field expression, so I could not see the difference between == and =.

Also custom_field addition will affect the resulting SQL query. This is why I needed a sample db with exact structure of your production fields - because work would be wasted.
I don't need sensitive data, so feel free to remove everything and leave only 1 or 2 pages with any values. Therefore I'd have a complete structure with fields, relations and one or two sample pages.
I don't need php templates to write SQL, I don't want to have to register them in my playground website and create pages / fill sample data, because this job is for db, not coding. I fully reviewed attachment and even registered templates and initialized them only to find they are fake and not helpful to answer question in the first post..

If you want to play with sql yourself, the basic structure for SUM is like following:

Code: Select all

SELECT SUM(column_name)
FROM table_name
WHERE condition;

Hi trendoman

I got a solution with sql_query and sum() ...:

Code: Select all

            <cms:set anbieter_ids="<cms:pages masterpage='anbieter.php' ids_only='1' orderby='page_name' order='asc' limit=limit />" scope='global' />
            <cms:set time_start="<cms:php>echo microtime(true);</cms:php>" scope='global' />
            <cms:pages masterpage='anbieter.php' id=anbieter_ids >
               <cms:set sql_query="
                  SELECT SUM(d.value) as 'summe', p.id, p.template_id 
                  FROM 
                     couch_pages p 
                     INNER JOIN couch_data_text d ON d.page_id=p.id AND d.field_id='2' 
                     INNER JOIN couch_relations rel on rel.pid = p.id 
                  WHERE 
                     rel.cid=<cms:show k_page_id /> 
                     AND rel.fid=1 
                     AND p.template_id='5' 
                     AND NOT p.publish_date = '0000-00-00 00:00:00' 
                     AND p.parent_id=0 
                  " />
               <cms:query sql=sql_query limit='1' fetch_pages='0'>
                  <cms:show k_page_id />=<cms:number_format summe decimal_precision='2' thousands_separator='\'' /> | 
               </cms:query>
            </cms:pages>

This leads to a major performance increase, see attached screenshot.

I also implemented this to my productive app and it improved the speed also.

Regards,
olliwalli