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.

[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.