nette database large number of rows

driici
Člen | 5
+
0
-

Ahoj

potrebuju projít tabulku s cca 400k zaznamu – je mi jasny ze nemuzu udelat select * from table, ale musim strankovat.
Narazim ale na problem s mnozstvim pameti (vic nez 4GB nejsem ochoten skriptu dat). Konkretne nad radkem delam nejdrive nejake ref na jinou tabulku + dalsi zpracovani a nasledne ulozeni vysledku.

Mam pocit, ze ve foreach cyklu kde je join na $delivery pres vice tabulek se nacita do pameti a pri dalsim zavolani se neuvolni. Nasledne dostanu error s velikosti pameti ve Caching/Storages/FileStorage.php:313 (unserialize v metode readMetaAndLock). Je jedno jestli mam velikost stranky 100 nebo 10k zaznamu.

Pouzivam nette/database:2.4.11

konkretne

		for ($i = 0; $i <= $iterations; $i++) {
            $this->database->beginTransaction();
            $orders = $this->database->table("orders")
                ->order("id")
                ->limit($step, $step * $i)
                ->fetchAll();
            /**
             * @var ActiveRow$order
             */
            foreach ($orders as $order) {
				$delivery = $database->table("deliveries")
					->where(...)
					->where(":deliveries2zip.zip_id.zip = ?", $order->zip)
					->fetch();
				if($delivery){
                    $order->update(['delivery_id' => $delivery->id]);
				}
            }
            $this->database->commit();

            echo $i."/".$iterations.PHP_EOL;
        }

Diky za postrceni spravnym smerem…

Editoval driici (15. 2. 2022 15:21)

dsar
Backer | 53
+
-3
-

Unfortunately Nette Explorer doesn't support these kind of bulk operations and (ab)using it for something similar is inefficient or even overkilling.

If your database supports the MERGE statement (it's SQL standard), use it.

MERGE INTO orders
  USING (SELECT id, zips.zip FROM deliveries JOIN zips ON deliveries.zip_id = zips.id WHERE [...] AND zips.zip = ?) deliveries2
  ON orders.zip = deliveries2.zip
WHEN MATCHED
  UPDATE SET orders.delivery_id = deliveries2.id

(I didn't test it but should give you the idea)

If your database doesn't support it, look for an exended syntax for UPDATE (PostgreSQL supports MERGE, however UPDATE can have JOIN conditions)

Editoval dsar (15. 2. 2022 16:10)

driici
Člen | 5
+
-1
-

Hi,

well thats quite an issue for me, since getting $delivery variable is little bit more complitated and requires some additional processing on php side. I have simplified code for sake of clarity… But I think I should be able to refactor this query to use plain sql and/or split it to multiple queries. I don`t care about performance, this is one time job.