loadStructure is extremely slow

Version: 3.0 (although the offending code is still in master).

Bug Description

src/Database/Structure.php:loadStructure is really slow. It's performing two queries per table. I currently have near 100 tables, and on my local machine, each query takes 50ms. This easily adds up to 10 seconds to init! Because every request to my server spins up a new connection, and every insert loads the structure, every single request inserting things takes 10 seconds.

database/src/Database/Structure.php

Lines 180 to 212 in 4862e4d

    
           protected function loadStructure(): array 
        
           { 
        
           	$driver = $this->connection->getDriver(); 
        
           	$structure = []; 
        
           	$structure['tables'] = $driver->getTables(); 
        
           	foreach ($structure['tables'] as $tablePair) { 
        
           		if (isset($tablePair['fullName'])) { 
        
           			$table = $tablePair['fullName']; 
        
           			$structure['aliases'][strtolower($tablePair['name'])] = strtolower($table); 
        
           		} else { 
        
           			$table = $tablePair['name']; 
        
           		} 
        
           		$structure['columns'][strtolower($table)] = $columns = $driver->getColumns($table); 
        
           		if (!$tablePair['view']) { 
        
           			$structure['primary'][strtolower($table)] = $this->analyzePrimaryKey($columns); 
        
           			$this->analyzeForeignKeys($structure, $table); 
        
           		} 
        
           	} 
        
           	if (isset($structure['hasMany'])) { 
        
           		foreach ($structure['hasMany'] as &$table) { 
        
           			uksort($table, fn($a, $b): int => strlen($a) <=> strlen($b)); 
        
           		} 
        
           	} 
        
           	$this->isRebuilt = true; 
        
           	return $structure; 
        
           }

Steps To Reproduce

Have a database with a lot of tables.
Have a connection to said database where each query takes roughly 50ms.
Profit. It's really slow.

Expected Behavior

It should not be this slow. I'd expect the method to be implemented with some form of JOIN, to bring all the information it needs in a single query. Not to need 200 queries!

Possible Solution

I do not know how the driver works, but rather than doing:

tables = fetch tables
foreach table
    fetch columns

I would expect:

tables_and_columns = fetch tables join columns

Well the real question here is why is loadStructure called many times. Because structure should be loaded once to cache and then cached structure should be used.

Also 50ms seems suspiciously high for simple query. I just checked and on my machine it took about 0.2-0.7ms (100 times less) for each structure query. Even on quite slow machines I would expect less that few ms per query. It seems to me that there might be some hidden problem with your database speed.

Navigation Menu

Repository navigation

loadStructure is extremely slow #278

Bug Description

Steps To Reproduce

Expected Behavior

Possible Solution

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

	protected function loadStructure(): array
	{
	$driver = $this->connection->getDriver();

	$structure = [];
	$structure['tables'] = $driver->getTables();

	foreach ($structure['tables'] as $tablePair) {
	if (isset($tablePair['fullName'])) {
	$table = $tablePair['fullName'];
	$structure['aliases'][strtolower($tablePair['name'])] = strtolower($table);
	} else {
	$table = $tablePair['name'];
	}

	$structure['columns'][strtolower($table)] = $columns = $driver->getColumns($table);

	if (!$tablePair['view']) {
	$structure['primary'][strtolower($table)] = $this->analyzePrimaryKey($columns);
	$this->analyzeForeignKeys($structure, $table);
	}
	}

	if (isset($structure['hasMany'])) {
	foreach ($structure['hasMany'] as &$table) {
	uksort($table, fn($a, $b): int => strlen($a) <=> strlen($b));
	}
	}

	$this->isRebuilt = true;

	return $structure;
	}

loadStructure is extremely slow #278

Description

Bug Description

Steps To Reproduce

Expected Behavior

Possible Solution

Activity

MartinMystikJonas commented on May 10, 2021

MartinMystikJonas commented on May 10, 2021

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions