loadStructure is extremely slow

[ghost]

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

1. Have a database with a lot of tables.
2. Have a connection to said database where each query takes roughly 50ms.
3. 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

[MartinMystikJonas]

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.

[MartinMystikJonas]

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.

[ghost]

Yes, I should note (but I figured this out after posting the issue; sorry about that) that using a cache storage other than memory cache indeed solves the problem (I'm not sure if I can configure our server setup in any way so that the memory cache persists across different requests to the server, or if our PHP code will inevitably create a new, clean database instance without cache every time; but that's no longer an issue with Nette). A persistent cache does indeed solve this issue for us.

I think the 50ms delay occurs because our MySQL database is hosted in an Amazon AWS cluster, so there's probably a lot of latency from our local machines to it.

Still, if possible, I think it would be nice if this method could be implemented in a more efficient way (as I suggested, with a JOIN, perhaps with some database-specific knowledge on which "magic tables" to look for, but I have not dug up Nette's code, so I don't even know if this is possible).

Thank you for the prompt response. I fully understand if this is considered to be a non-issue.