High query count crept back into 2.3 (field config)

[nickdunn]

I've spotted one part of FieldManager that causes unnecessarily high repetitive queries, and is ripe for optimisation. Looks like it was introduced in this commit:

b23cb7e

This code can induce several of the same query:

// Loop over the `ids` array, which is grouped by field type
// and get the field context.
foreach($ids as $type => $field_id) {
    $field_contexts[$type] = Symphony::Database()->fetch(sprintf(
        "SELECT * FROM `tbl_fields_%s` WHERE `field_id` IN (%s)",
        $type, implode(',', $field_id)
    ), 'field_id');
}

I'm not quite sure how or why it occurs, but these queries double up if you add more data sources to the page. For example with one data source (that has a unique input field):

SELECT SQL_CACHE * FROM `sym_fields_uniqueinput` WHERE `field_id` IN (33)

If I add another data source, pulling from another section also with a unique input field:

SELECT SQL_CACHE * FROM `sym_fields_uniqueinput` WHERE `field_id` IN (33)
...
SELECT SQL_CACHE * FROM `sym_fields_uniqueinput` WHERE `field_id` IN (1,5,33)

You can see the new unique input fields have been added to the query, but the query is repeated with the previous ID too. And if I add a third data source:

SELECT SQL_CACHE * FROM `sym_fields_uniqueinput` WHERE `field_id` IN (33)
...
SELECT SQL_CACHE * FROM `sym_fields_uniqueinput` WHERE `field_id` IN (1,5,33)
...
SELECT SQL_CACHE * FROM `sym_fields_uniqueinput` WHERE `field_id` IN (1,5,33)

Oddly third datasource doesn't even select a unique input field! One exists in the section, but I am not selecting it.

When discovering this bug I am using 13 different field types in my sections, and three data sources on the page, meaning 3x13 (39) queries, when ideally this should only be 13, right?

[brendo]

When discovering this bug I am using 13 different field types in my sections, and three data sources on the page, meaning 3x13 (39) queries, when ideally this should only be 13, right?

Correct, good spot!

[brendo]

I'm having trouble reproducing this one mate.

I've create two datasources, one with a Text Input field from one section, and the other selecting a Text Input and a Textarea from another section. The queries don't double up.

Do you have any other extensions installed? Can you post your full query log?

Running through the code block, the only way I can see this happening is when FieldManager::fetch is called by not passing $id, but passing a $section_id only. I can't see how this would happen for normal datasources, can you pastie those as well? Maybe there's an edge case with grouping or sorting.

[nickdunn]

Shall I send you a zipped up build with a demo page where this is replicable? Can do tonight.

[brendo]

Yeah, please!

[nickdunn]

Here's a demo build with everything stripped out. Install the database (config points to db s23-performance, user root, pass root) and sign in to Symphony with test and `test.

http://cl.ly/0Z2R3i3n1W3A0m2m3f1N

There are three sections: Reviews SBL linked to Books and Reviewers. The one page has three DSs: one that gets Reviews, then outputs "book" and "reviewer" SBLs for two other DSs to chain and return related entries from the other sections. Two bugs I've discovered while profiling:

1. Duplicate queries

As described above, there are duplicate queries selecting field config from each sym_fields_{type} table. I think you make some changes to FieldManager to group these lookups into one single query? Seems to be repeating them for each data source. These should occur once only. I think it's that look in FieldManager::fetch which has no memoiazation/caching perhaps, but I'm not sure how your "fetch by array" change impacted this stuff.

2. All field types are queried, not just those in DSs

Even fields that are not selected from DSs are queried here! An example: profile the page as-is and you won't see any reference to sym_fields_upload in the profiler, because this field hasn't been added to a section. Now add a file upload field to any one of the three sections and refresh the profiler. You'll see two queries to sym_fields_upload, even though it hasn't been selected in any data source.

3. Odd query that fails

Just spotted this in the output:

SELECT SQL_CACHE t1.* FROM sym_fields AS `t1` WHERE 1 AND t1.`id` IN(id)

It occurs twice, and also seems to originate from FieldManager::fetch. It occurs because sometimes $id passed to the fetch() method is actually a string 'id'. I think this is when a ds filters on the ID field, so the array of filters is a mix of strings and numbers. I don't recall ever seeing this is 2.2.5. I traced the source by adding this into FieldManager::fetch once $field_ids had been set:

if($field_ids[0] == 'id') {
    var_dump(debug_backtrace());
    die;
}

Hope this is useful!

[brendo]

TY, will give it a whirl!

[brendo]

Ah, I have a feeling all these issues originate from the Select Box Link field :)