Textpattern CMS support forum
You are not logged in. Register | Login | Help
- Topics: Active | Unanswered
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
Re: Performance Anxiety | custom_field loopiness
Paging etc_query…
This looks like a job for Oleg.
Offline
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
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
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
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 thanarticle_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
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
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
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
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
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
Offline