在Symfony2或PHP / MySQL中进行批量/批量更新的最佳方法是什么？

时间：2022-10-22 16:54:32

I have a function in a service that basically pulls the data from a few tables and builds an index table.

我在服务中有一个函数,它基本上从几个表中提取数据并构建一个索引表。

The problem is that I have over 6000 records, so it just takes forever and sometimes reaches the memory limit.

问题是我有超过6000条记录,所以它只需要永远,有时达到内存限制。

Is there a design pattern or a method where I can achieve this either by a batch process or a web service? Ideally I want to be able to keep updating the user with the progress.

是否有设计模式或方法可以通过批处理或Web服务实现此目的?理想情况下,我希望能够不断更新用户的进度。

Any ideas or pointers would be really appreciated.

任何想法或指示都会非常感激。

Update:

The tables I have are basically tables for use with products on an e-commerce site. I have the following tables:

我所拥有的表格基本上是用于电子商务网站上的产品的表格。我有以下表格:

  • Product (basic product information)

    • 产品(基本产品信息)

  • Product Description (descriptions and meta information)

    • 产品描述(描述和元信息)

  • Product Price (price information on a product)

    • 产品价格(产品价格信息)

  • Product Image (image information on a product)

    • 产品图片(产品图片信息)

  • Product Features (any product features)

    • 产品功能(任何产品功能)

  • Product Options (any product options)

    • 产品选项(任何产品选项)

I then have a Product Index table that pulls some of the information from all these tables for the purpose of making it much quicker and easier to search and filter results without the need of lots of joins.

然后我有一个产品索引表,从所有这些表中提取一些信息,以便更快,更容易地搜索和过滤结果,而无需大量的连接。

I need some guidance on the best way to go about developing a method / function / process / service for rebuilding the index table.

我需要一些关于开发用于重建索引表的方法/函数/进程/服务的最佳方法的指导。

1 个解决方案

#1

2  

I can't tell you how to rebuild your index but I can show you a method I've been using for dealing with large result sets.

我不能告诉你如何重建你的索引,但我可以告诉你一个我用来处理大型结果集的方法。

$em = $this->getContainer()->get('doctrine')->getEntityManager();
$repo = $em->getRepository('MyBundle:Item');
$qb = $repo->createQueryBuilder('i') // build query ...

// get iterator for query; this way only a single row is
// hydrated at a time.
$it = $qb->getQuery()->iterate();
foreach ($it as $row) {
    // $row[0] will be the fetched entity.
    $ent = $row[0];

    // do stuff with entity ...
    // ...

    // Once you're done with the entity detach it to
    // save memory and speed up the doctrine ORM.
    // make sure you DO NOT use the detached entity
    // in any way after this call (unless you re-merge() it).
    $em->detach($row[0]);
}

#1

2  

I can't tell you how to rebuild your index but I can show you a method I've been using for dealing with large result sets.

我不能告诉你如何重建你的索引,但我可以告诉你一个我用来处理大型结果集的方法。

$em = $this->getContainer()->get('doctrine')->getEntityManager();
$repo = $em->getRepository('MyBundle:Item');
$qb = $repo->createQueryBuilder('i') // build query ...

// get iterator for query; this way only a single row is
// hydrated at a time.
$it = $qb->getQuery()->iterate();
foreach ($it as $row) {
    // $row[0] will be the fetched entity.
    $ent = $row[0];

    // do stuff with entity ...
    // ...

    // Once you're done with the entity detach it to
    // save memory and speed up the doctrine ORM.
    // make sure you DO NOT use the detached entity
    // in any way after this call (unless you re-merge() it).
    $em->detach($row[0]);
}
标签：

相关文章