PHP - 每次迭代查询单个值或在开始时获取所有数据，并从数组中检索？ [英] PHP - Query single value per iteration or fetch all at start and retrieve from array?

问题描述

我有一个看起来像这样的函数：

  //迭代比例
 foreach as $ scale）
 {
 $ surveyItems = $ scale-> findDependentRowset（'SurveyItems'）; 
 
 //在缩放的项目上嵌套迭代
 foreach（$ surveyItems as $ item）
 {
 //从结果表中检索单个值，并执行一些操作stuff 
 //取决于$ item / $ scale中的某些参数
} 
} 
  

b $ b

QUESTION ：最好是对内部foreach中的每个值执行一个数据库查询，还是更好地将所有结果值提取到数组中并从中获取值？

解决方案

一个返回十几个数据的查询几乎比返回1个数据的12个查询快12倍。 p>

哦，永远不要把一个SQL放在一个循环中，它总是会导致灾难。

根据您的应用程序的工作原理，可能会为每个查询打开一个新连接，这是特别糟糕的，因为每个DB服务器都有连接数量的限制。然后也意识到这将发生对每个用户，所以50查询与5个用户，你已经有250个查询在任何给定的时刻。但是即使所有的查询只共享一个连接，你在DB服务器上的数量增加了X倍，减少了其他所有页面，因为用户在这个页面上占用DB服务器，每个人都必须共享。

我看到过去的整个应用程序失败，因为这个设计缺陷，只是不这样做。

I have a function that looks something like this:

//iteration over scales
foreach ($surveyScales as $scale)
{
    $surveyItems = $scale->findDependentRowset('SurveyItems');

    //nested iteration over items in scale
    foreach ($surveyItems as $item)
    {
        //retrieve a single value from a result table and do some stuff
        //depending on certain params from $item / $scale
    }
}

QUESTION: is it better to do a db query for every single value within the inner foreach or is it better to fetch all result values into an array and get the value from there?

解决方案

One query that returns a dozen pieces of data is almost 12x faster than 12 queries that return 1 piece of data.

Oh, and NEVER EVER NEVER put a SQL inside a loop, it will always lead in a disaster.

Depending on how your app works, a new connection might be opened for each query, this is especially bad as every DB server has a limit on the number of connections. Then also realize this will happen for each user, so 50 queries with 5 users and you already have 250 queries at any given moment. But even if all the queries do share just 1 connection, you're taxing the DB server X times more, slowing it down for everything else, every page, because users are hogging the DB server on this page, and everybody has to share.

I've seen an entire application fail in the past because of this 1 design flaw, just don't do it.

这篇关于PHP - 每次迭代查询单个值或在开始时获取所有数据，并从数组中检索？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！