DBI 语句句柄可以使用缓存的execute() 调用吗？

发布于 2024-12-07 12:28:09 字数 427 浏览 0 评论 0原文

我有一个应用程序，其中数据库很少更改，并且该应用程序需要从数据库进行多次读取，这会显着降低性能。其中许多读数完全相同。所以我想让DBI缓存数据库读取的结果。

例如，

$sth = $dbh->prepare('SELECT a, b FROM a_table WHERE c = ?');
$sth->execute(5);
$sth->execute(2);
$sth->execute(5); # this call loads the cached result set

我首先想到这就是 prepare_cached 的作用，但我意识到它只缓存语句句柄本身，而不缓存语句句柄的实际执行。

我想我可以通过将语句执行包装在记忆子中来实现我想要的。但我只是想看看 DBI 本身是否有捷径。

原文

I have an application where the database rarely changes, and the application requires many reads from the database that is slowing down the performance quite significantly. Many of these reads are exactly the same. So I want to get DBI to cache the results of a database read.

For example,

$sth = $dbh->prepare('SELECT a, b FROM a_table WHERE c = ?');
$sth->execute(5);
$sth->execute(2);
$sth->execute(5); # this call loads the cached result set

I first thought this is what prepare_cached does, but I realised that it only caches the statement handle itself and not actual executions of the statement handle.

I suppose I could achieve what I want by wrapping the statement execution inside a memoized sub. But I'm just seeing if there is a shortcut within DBI itself.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

无力看清 2024-12-14 12:28:09

正如你所说，prepare_cached与语句句柄相关，并且需要缓存执行结果。 Memoize 很好，但您可能需要时不时地使缓存无效，然后重新执行查询以从数据库获取新副本。
我会使用缓存（http://search.cpan.org/perldoc?Cache）模块。我刚刚从介绍中复制了这个片段：

use Cache::File;

my $cache = Cache::File->new( cache_root => '/tmp/cacheroot' );
my $customer = $cache->get( $name );

unless ($customer) {
   $customer = get_customer_from_db( $name );
   $cache->set( $name, $customer, '10 minutes' );
}

return $customer;

您可以在内存缓存中使用而不是文件。此示例使用缓存中的 $customer 值（如果存在且有效），否则获取新值并存储在缓存中（有效期为 10 分钟）。

希望这有帮助。

as you said, the prepare_cached is related to the statement handle, and you need to cache the results of the execution. Memoize is good, but probably you need invalidate the cache from time to time, and re-execute the query to get a fresh copy from database.
I'd use the Cache (http://search.cpan.org/perldoc?Cache) module. I've just copied this fragment from the introduction:

use Cache::File;

my $cache = Cache::File->new( cache_root => '/tmp/cacheroot' );
my $customer = $cache->get( $name );

unless ($customer) {
   $customer = get_customer_from_db( $name );
   $cache->set( $name, $customer, '10 minutes' );
}

return $customer;

You can use in memory cache instead of File. This example uses the $customer value from cache if exists and it's valid, otherwise gets a fresh value and store at cache (with 10 minutes of life).

Hope this helps.

回复收藏 0 原文

~没有更多了~