PHP Laravel - Improving and refactoring code to Reduce Queries

Question

Improve Request to Reduce Queries

I have a web application, where users can upload Documents or Emails, to what I call a Strema. The users can then define document fields email fields to the stream, that each document/email will inherit. The users can then furthermore apply parsing rules to these fields, that each document/email will be parsed after.

Now let's take the example, that an user uploads a new document. (I have hardcoded the ID's for simplicty).

$stream = Stream::find(1);
$document = Document::find(2);

$parsing = new ApplyParsingRules;
$document->storeContent($parsing->parse($stream, $document));

Below is the function that parses the document according to the parsing rules:

    public function parse(Stream $stream, DataTypeInterface $data) : array
    {
        //Get the rules.
        $rules = $data->rules();
        
        $result = [];
        foreach ($rules as $rule) {

            $result[] = [
                'field_rule_id' => $rule->id,
                'content' => 'something something',
                'typeable_id' => $data->id,
            ];
        }

        return $result;
    }

So above basically just returns an array of the parsed text.

Now as you probably can see, I use an interface $DataTypeInterface. This is because the parse function can accept both Documents and Emails.

To get the rules, I use this code:

//Get the rules.
$rules = $data->rules();

The method looks like this:

class Document extends Model implements DataTypeInterface
{
    public function stream()
    {
        return $this->belongsTo(Stream::class);
    }
    public function rules() : object
    {
        return FieldRule::where([
            ['stream_id', '=', $this->stream->id],
            ['fieldable_type', '=', 'App\DocumentField'],
        ])->get();
    }
}

This will query the database, for all the rules that is associated with Document Fields and the fields, that is associated with the specific Stream.

Last, in my first request, I had this:

$document->storeContent($parsing->parse($stream, $document));

The storeContent method looks like this:

class Document extends Model implements DataTypeInterface
{
    // A document will have many field rule results.
    public function results()
    {
        return $this->morphMany(FieldRuleResult::class, 'typeable');
    }
    // Persist the parsed content to the database.
    public function storeContent(array $parsed) : object
    {
        foreach ($parsed as $parse) {
            $this->results()->updateOrCreate(
                [
                    'field_rule_id' => $parse['field_rule_id'],
                    'typeable_id' => $parse['typeable_id'],
                ],
                $parse
            );
        }
        return $this;
    }
}

As you can probably imagine, everytime a document gets parsed, it will create be parsed by some specific rules. These rules will all generate a result, thus I am saving each result in the database, using the storeContent method.

However, this will also generate a query for each result.

One thing to note: I am using the updateOrCreate method to store the field results, because I only want to persist new results to the database. All results where the content was just updated, I want to update the existing row in the database.

For reference, above request generates below 8 queries:

select * from `streams` where `streams`.`id` = ? limit 1
select * from `documents` where `documents`.`id` = ? limit 1
select * from `streams` where `streams`.`id` = ? limit 1    
select * from `field_rules` where (`stream_id` = ? and `fieldable_type` = ?)
select * from `field_rule_results` where `field_rule_results`.`typeable_id` = ? and...
select * from `field_rule_results` where `field_rule_results`.`typeable_id` = ? and...    
insert into `field_rule_results` (`field_rule_id`, `typeable_id`, `typeable_type`, `content`, `updated_at`, `created_at`) values (..)
insert into `field_rule_results` (`field_rule_id`, `typeable_id`, `typeable_type`, `content`, `updated_at`, `created_at`) values (..)

Above works fine - but seems a bit heavy, and I can imagine once my users starts to generate a lot of rules/results, this will be a problem.

Is there any way that I can optimize/refactor above setup?

Answer 1

This is not a complete answer, but it is too long to put in a comment, so I placed it here.

I read your question, and given that it is unclear what the overall purpose of this code is (what kind of rules?), that's not easy. As far as I can tell there's no easy way to reduce the load on the database. Most queries simply have to be done.

That said: The best ways to unload a database are:

• Get all the rows you need in one request.
• Don't update what hasn't changed (obviously).
• Do all inserts with one query (up to a certain amount of rows).

Can this be applied to your code?

I think that Document::storeContent() is an good method to start with, because the last two points, I mentioned above, apply to it. You do an updateOrCreate() for each rule result, generating two queries for each result.

What you could do is make this more efficient by handling all the rules in a few queries, like this:

1. Store the new results of the rules in a variable: $newResults = $this->results();
2. Read all existing results rows from the database with one query: $oldResults = $this->"read database()";
3. Compare these two results and: A. Eliminate what hasn't changed. B. Update what needs updating. C. Insert, with one query, all new results.

It is very hard for me to give you real code examples, given the abstract nature of the code you've given, but I don't think it is beyond your capabilities to implement the above algorithm. I think a new 'rules' class, that encapsulates this behavior, would be appropriate.

This way you assure that, when a new parse is done, only those queries are executed that are really necessary. You should, of course, never parse a document or email unnecessarily.

I hope this helps a bit.