Should Laravel database connections use cached prepared statements?

[tlaverdure]

I was just looking at Illuminate\Database\Connection and noticed that prepared statements are not being reused. It seems like good practice to use an existing prepared statement to avoid recompiling a query and improve performance.

This is also stated in the PHP PDO docs:
https://www.php.net/manual/en/pdo.prepare.php

I've tested this with Laravel Vapor (Octane) and have seen 50-70% improvement on query latency with recurring statements.

	No Cache	With Cache	+/- %
Query 1	7.3ms	2.4ms	-67.1%
Query 2	6.6ms	2.1ms	-68.2%
Query 3	6.5ms	2ms	-69.2%

Benefits

• Reuse prepared statements within the lifecycle of normal single request applications
• Reuse prepared statements with long running applications (Octane)

Implementation:

    /**
     * The prepared statements for the connection.
     *
     * @var array<string, \PDOStatement>
     */
    protected $statements = [];


    /**
     * Prepare an SQL statement object to be executed by PDOStatement::execute().
     * This method optimizes the performance of the connection by negotiating
     * client side caching of the query plan and meta information on reuse.
     *
     * @return \PDOStatement
     */
    public function prepare(string $query, bool $forSelect = false)
    {
        $key = sha1($query);

        if (isset($this->statements[$key]) === false) {
            $this->statements[$key] = $this->getPdoForSelect($forSelect)->prepare($query);
        }

        return $this->statements[$key];
    }

- $statement = $this->prepared(
-    $this->getPdoForSelect($useReadPdo)->prepare($query)
- );
+ $statement = $this->prepared($this->prepare($query, $useReadPdo));

Are there any known side effects that would discourage this from being part of Laravel?

[Angel5a]

Hi, @tlaverdure.

1. Please, do not forget about different connections. They should be take into account as well.
2. It's a bad thing for long running applications (Octane) to cache every query. Single whereIn() can produce multiple prepared queries, depending on array size, which could lead to memory leaks. This cache should be limited somehow (by size, or by weakmap).

I think, well prepared and tested implementation could be part of the framework, but nobody just wants to deal with caching.

[tlaverdure]

Please, do not forget about different connections. They should be take into account as well.

Thanks for the feedback. Prepared statements only exist on a single connection.

It's a bad thing for long running applications (Octane) to cache every query. Single whereIn() can produce multiple prepared queries, depending on array size, which could lead to memory leaks. This cache should be limited somehow (by size, or by weakmap).

Yes, there should be a limit on the number of items added to the cache.

[Sophist-UK]

Yes, there should be a limit on the number of items added to the cache.

No - there should be no limit on what is added to the cache, but cached items older than (say) 1 day should be candidates for dropping entries based on the number of entries and the least recently used or least frequently used items (which implies retaining some stats).

[Sophist-UK]

Prepared statements only exist on a single connection.

The point is that the same statement prepared on different connections should be individually cached as the prepare may not be compatible between different connections i.e. the connection should be part of the cached id (or id hash).

[Sophist-UK]

I looked into Wordpress caching a few years ago and I can say that getting it right is not easy.

1. You need to ensure that the cache key is unique (hence the comment from @Angel5a Pavel).
2. You need to know when and how to remove infrequent statements from the cache to prevent it growing forever.
3. You need to know when and how to invalidate a cache entry if you need to (not so much of a problem for SQL prepares, but e.g. caching web pages you need to invalidate the cache when underlying data changes).
4. You need to know when and how automatically to invalidate the entire cache (e.,g. when the SQL version changes, or when the data definitions change) and provide an artisan command to do so (and maybe include this command by default in certain workflows).

[tlaverdure]

Thanks for the feedback. Cache invalidation for DDL seems like the trickiest part. Something to think about 👍🏾

[shaedrich]

Well, the cache could be flushed whenever php artisan migrate is run (except for when there are no pending migrations)

[thamulot]

Please note, it seems that the database could be already caching prepared statements, as per MySQL 8.x documentation - https://dev.mysql.com/doc/refman/8.0/en/statement-caching.html

So maybe the benchmark is misleading here.

[675076143]

I think this is a very useful change.

Many loop commands can benefit from this.

Preparestatement cause 3 times database command (prepare, execute, deallocate)

DB::table('example')
            ->select('id','name')
            ->chunkById(1, fn()=>true); 
//  prepared statement not being reused

May be PDO::ATTR_EMULATE_PREPARES=true is the best option

[fredericgboutin-yapla]

I would strongly advise against using the emulate option as it introduces in production security risks and vulnerabilities - aka it is not as good as the database itself to sanitize query arguments.

[TheLevti]

That's not true, PDO::ATTR_EMULATE_PREPARES is by default set to true by php/pdo for good reasons. In fact Laravel is the only framework that overwrites this default and sets it to false, which enforces server side prepared statements for every statement the ORM is running. This is very inefficient and just wastes resources as none of the prepared statements are reused.

The security aspect is not valid here for MySQL at least as its by default emulated for a reason. PDO's escaping logic is basically 1:1 to what the server does, so there is no need to enforce a 2 round trip per query. Proof me wrong with a single example where you can inject anything bypassing PDO's emulated escaping.

Setting back PDO::ATTR_EMULATE_PREPARES back to true, slices away a big amount of latency overall, and reduces DB cpu/memory usage on laravel projects.

Only when server side prepared statements are properly reused many times (e.g. batch processing), you see a benefit. Even when you only reuse them from time to time, its not worth keeping server side prepared statements alive as it holds too many resources on server side for no reason. Setting PDO::ATTR_EMULATE_PREPARES to false by laravel has been a mistake since day one.

[fredericgboutin-yapla]

I just read this interesting post and its references - https://www.sitepoint.com/community/t/does-pdo-with-php-and-mysql-default-to-emulated-prepared-statements-and-is-this-a-security-issue/395230/7 - needless to say, this topic is controversial and it has been so for the last decade.

[jeffdafoe]

Whether or not caching prepared statements is advantageous depends on the specific query and its usage. Decent database software will handle statement caching internally and it will be done more efficiently.

[TheLevti]

Good idea, but as some already mentioned, Eloquent is not built to properly exploit server side prepared statements. Some query construction logic is quite badly designed (example fetching a has many relationship by running a separate query with an IN + huge id list clause).

For sure possible, but you need to ensure that the cache size is limited/controlled as those dynamically generated queries could cause a memory exhaustion.

Better set PDO::ATTR_EMULATE_PREPARES to true and avoid those 2 round trips per query. Only use server side prepared statements when you have big batches to process.

Note:

By default emulation is disabled for postgresql. If you enable it, laravel is missing proper boolean casting logic. You need to ensure that you add support for that first.

[Sophist-UK]

If Eloquent doesn't already do this, then that is a major mistake.

The whole idea of prepared statements is to prepare them without the variable data (and the IN clause is variable data).