Go to main content

Textpattern CMS support forum

You are not logged in. Register | Login | Help

#1 2015-03-22 22:48:48

giz
Member
From: New Zealand
Registered: 2004-07-26
Posts: 259
Website

Performance Anxiety | custom_field loopiness

I’m developing a large product database (2000+ building products in all shapes and sizes), and am concerned that we might hit performance issues once all the data entry is complete.

I’m using custom_fields to store information about each product eg. a “Use for” checkbox or multi-select with values for Bathroom | Ceiling | Counter | External | Facade | Floor | Kitchen | Wall .

This is how I’m generating the client-side code for a typical “Use for” link:

<details>
	<summary>Use for</summary>
	<txp:variable name="use-for">
		<txp:article_custom section="products" wraptag="" break="" limit="2000" sort="custom_15 asc">
			<txp:if_custom_field name="Use for" value=""><txp:else />
				<txp:if_different>
					<txp:rah_repeat value='<txp:custom_field name="Use for" />' delimiter="|">
						<a href="<txp:site_url />?q=<txp:rah_repeat_value />"><txp:rah_repeat_value /></a> |
					</txp:rah_repeat>
				</txp:if_different>
			</txp:if_custom_field>
		</txp:article_custom>
	</txp:variable>
	<txp:rah_repeat value='<txp:variable name="use-for" />' delimiter="|" duplicates="1" sort="regular asc" wraptag="ul" break="li">
		<txp:rah_repeat_value />
	</txp:rah_repeat>
</details>

I end up with a nice list of clickable links:

Use for

  • Bathroom
  • Ceiling
  • Counter
  • External
  • Facade
  • Floor
  • Kitchen
  • Wall

Great! But is my code going to cause things to slow down when I’m querying thousands of articles? I repeat this process for 3 other custom fields…

Offline

#2 2015-03-23 07:24:20

gaekwad
Server grease monkey
From: People's Republic of Cornwall
Registered: 2005-11-19
Posts: 4,134
GitHub

Re: Performance Anxiety | custom_field loopiness

Paging etc_query…

This looks like a job for Oleg.

Offline

#3 2015-03-23 08:29:50

etc
Developer
Registered: 2010-11-11
Posts: 5,028
Website GitHub

Re: Performance Anxiety | custom_field loopiness

giz wrote #289291:

But is my code going to cause things to slow down when I’m querying thousands of articles?

If you want my opinion, yes, it is. Because of txp (see here) and because of the code itself. You should reduce as much as possible the number of txp tags inside loops, say, like this:

	<txp:variable name="use-for">
		<txp:article_custom section="products" use_for="_" break=" | " limit="2000" sort="custom_15 asc">
			<txp:if_different>
				<txp:custom_field name="Use for" />
			</txp:if_different>
		</txp:article_custom>
	</txp:variable>
	<txp:rah_repeat value='<txp:variable name="use-for" />' delimiter="|" duplicates="1" sort="regular asc" wraptag="ul" break="li">
		<a href="<txp:site_url />?q=<txp:rah_repeat_value />"><txp:rah_repeat_value /></a>
	</txp:rah_repeat>

Probably, dropping if_different (and sort attribute) will speed it up a little more, but you’d better replace article_custom with a db query (check custom fields names):

<txp:etc_query break=" | "
	data="SELECT DISTINCT custom_15 FROM textpattern WHERE Section='products' AND custom_15>'' AND Status=4 LIMIT 2000" />

Offline

#4 2015-03-23 08:34:17

Gocom
Developer Emeritus
From: Helsinki, Finland
Registered: 2006-07-14
Posts: 4,533
Website

Re: Performance Anxiety | custom_field loopiness

gaekwad wrote #289297:

Paging etc_query…

This looks like a job for Oleg.

It’s not magically going to make it perform any faster than rah_repeat; in fact it’s bit of the opposite kind of. Wile it’s insanely useful plugin, this is not an usage case for it unless you want the server to run out of memory.

giz wrote #289291:

Great! But is my code going to cause things to slow down when I’m querying thousands of articles? I repeat this process for 3 other custom fields…

Not great, but that is close to the best you can do with just core features.

Realistically, what you would want to do from database design perspective, is to generate an index of your values and use relational database structure instead of custom fields. Doing this would require writing a plugin that modifies the admin interface, updates database accordingly and adds template tags to render the data.

Textpattern’s custom fields in their current state don’t alone work well for that type of usage as there is no reasonable way to extract, store or select array-type data from them. They just are not designed for it; you can do it like you have, but it doesn’t scale.

Hopefully at some Textpattern gets proper custom field system and this type of things become reasonable to do with just core tags.

Offline

#5 2015-03-23 09:10:20

etc
Developer
Registered: 2010-11-11
Posts: 5,028
Website GitHub

Re: Performance Anxiety | custom_field loopiness

Gocom wrote #289301:

Wile it’s insanely useful plugin this is not an usage case for it unless you want the server to run out of memory.

Ummm… there is absolutely no xml processing here, just a plain db query (à la smd_query), how could it be heavier than article_custom?

Offline

#6 2015-03-23 09:19:37

Gocom
Developer Emeritus
From: Helsinki, Finland
Registered: 2006-07-14
Posts: 4,533
Website

Re: Performance Anxiety | custom_field loopiness

etc wrote #289302:

Ummm… there is absolutely no xml processing here, just a plain db query (à la smd_query), how could it be heavier than article_custom?

Because it isn’t. I haven’t said anything about your query, referring to DOM processing. Not that the query makes the performance acceptable; it’s still just fetching every single blob custom field and them processing them in PHP, resulting in a poor ass website.

Offline

#7 2015-03-23 09:24:59

gaekwad
Server grease monkey
From: People's Republic of Cornwall
Registered: 2005-11-19
Posts: 4,134
GitHub

Re: Performance Anxiety | custom_field loopiness

Gocom wrote #289301:

It’s not magically going to make it perform any faster than rah_repeat; in fact it’s bit of the opposite kind of. Wile it’s insanely useful plugin, this is not an usage case for it unless you want the server to run out of memory.

Fair enough, point taken. I will stop trying to be helpful.

Offline

#8 2015-03-23 09:37:18

Gocom
Developer Emeritus
From: Helsinki, Finland
Registered: 2006-07-14
Posts: 4,533
Website

Re: Performance Anxiety | custom_field loopiness

One thing you could do, that would make the performance acceptable, is to create a fake index on article updates. Each time an article is saved, collect all the values and save them into an article; that way you don’t have to go through all articles. It does require some PHP (a plugin), something along the lines of:

register_callback('abc_fake_index', 'article_saved');
register_callback('abc_fake_index', 'article_updated');
register_callback('abc_fake_index', 'articles_deleted');

/**
 * Collects values and dumps them into specified articles for storage.
 *
 * The values are saved to an article saved into section named 'product-index'
 */

function abc_fake_index()
{
    // Selects rows.

    $rs = safe_rows_start(
        'custom_1, custom_2, custom_3',
        'textpattern',
        'Status >= 4'
    );

    $collection = array();

    // Pick all possible values from articles.

    while ($a = nextRow($rs)) {
        foreach ($a as $name => $value) {
            if ($value !== '') {

                if (!isset($collection[$name])) {
                    $collection[$name] = array();
                }

                foreach (explode(' | ', $value) as $customvalue) {
                    if ($customvalue !== '' && !in_array($customvalue, $collection[$name], true)) {
                        $collection[$name][] = $customvalue;
                    }
                }
            }
        }
    }

    // Save the collected values to an article.

    $set = array("status = 4", "section = 'product-index'", "Title = 'Procuct index'");

    foreach ($collection as $name => $values) {
        sort($values, SORT_REGULAR);
        $set[] = $name."='".doSlash(implode(' | ', $values))."'";
    }

    if (safe_count('textpattern', "section = 'product-index'")) {
        safe_update('textpattern', implode(',', $set), "section = 'product-index'");
    } else {
         safe_insert('textpattern', implode(',', $set));
    }
}

After which you can find a collection of all the values from a single article saved in product-index section:

<txp:article_custom section="product-index" limit="1">
    <txp:rah_repeat value='<txp:custom_field name="Use for" />' delimiter="|" wraptag="ul" break="li">
        <a href="<txp:site_url />?q=<txp:rah_repeat_value />"><txp:rah_repeat_value /></a>
    </txp:rah_repeat>
</txp:article_custom>
gaekwad wrote #289305:

I will stop trying to be helpful.

Didn’t mean it like that, but don’t be such a baby.

Offline

#9 2015-03-23 09:52:05

gaekwad
Server grease monkey
From: People's Republic of Cornwall
Registered: 2005-11-19
Posts: 4,134
GitHub

Re: Performance Anxiety | custom_field loopiness

Gocom wrote #289306:

Didn’t mean it like that, but don’t be such a baby.

I still love you, Jukka-san.

Offline

#10 2015-03-23 09:52:32

etc
Developer
Registered: 2010-11-11
Posts: 5,028
Website GitHub

Re: Performance Anxiety | custom_field loopiness

Gocom wrote #289303:

it’s still just fetching every single blob custom field and them processing them in PHP, resulting in a poor ass website.

Agree re scalability, but the query itself is ~6 times faster (and takes less memory) than article_custom block, just wanted to make it clear. Now, doing it once is evidently better than each time, as you suggest, or with some cache plugin. Cache is terribly lacking in txp.

Offline

#11 2015-03-23 20:52:42

giz
Member
From: New Zealand
Registered: 2004-07-26
Posts: 259
Website

Re: Performance Anxiety | custom_field loopiness

Thank you all. I’m learning a lot from this thread already!

(Can someone explain why I couldn’t just wrap ask_cache around my inefficient code? Not that I’ve ever used it before…)

To give a little more context, I’m using wet_haystack to make my custom fields searchable. Are there likely to be any performance bottlenecks when searching i.e. when someone clicks on one of my “Use for” links?

Offline

#12 2015-03-23 21:09:15

etc
Developer
Registered: 2010-11-11
Posts: 5,028
Website GitHub

Re: Performance Anxiety | custom_field loopiness

giz wrote #289333:

Can someone explain why I couldn’t just wrap ask_cache around my inefficient code?

You could (and should), but optimize it anyway, to be kind with visitors who will trigger the cache updates.

Offline

Board footer

Powered by FluxBB