Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Excessive number of queries for metadata by PDO #212

Closed
repli2dev opened this issue Nov 14, 2018 · 3 comments
Closed

Excessive number of queries for metadata by PDO #212

repli2dev opened this issue Nov 14, 2018 · 3 comments

Comments

@repli2dev
Copy link

@repli2dev repli2dev commented Nov 14, 2018

Version: 2.4.6

When analysing queries from the DB side (PostgreSQL) we have discovered that the PDO is making a excessive number of queries for pgclass and pgtype... We investigated that these queries origin from PDO extension itself (hence not visible in any query monitoring done on PHP runtime) and are caused by nette/database from Helpers::detectTypes() where PDO::getColumnMeta() is called.

The excessiveness is that we have around 500 queries on page and this generates around 1500 additional queries (depends on number of columns and custom types), which are quite fast, but still takes around 5% of whole request.

Could the method detectTypes() be efficient? Maybe caching? (However that may not be a good idea as schema can change during execution.)

@dg

This comment has been minimized.

Copy link
Member

@dg dg commented Nov 14, 2018

Are these queries similar?

@repli2dev

This comment has been minimized.

Copy link
Author

@repli2dev repli2dev commented Nov 14, 2018

@dg yes, only OID changes and in high numbers they are event duplicate... See pgsql_stmt_get_column_meta in https://www.askapache.com/pub/php/ext/pdo_pgsql/pgsql_statement.c

dg added a commit that referenced this issue Nov 20, 2018
…r of queries [Closes #212]
@dg dg closed this in 16ab1ba Nov 20, 2018
dg added a commit that referenced this issue Nov 20, 2018
…r of queries [Closes #212]
dg added a commit that referenced this issue Nov 20, 2018
…r of queries [Closes #212]
dg added a commit that referenced this issue Nov 20, 2018
…r of queries [Closes #212]
dg added a commit that referenced this issue Nov 20, 2018
…r of queries [Closes #212]
dg added a commit that referenced this issue Nov 20, 2018
…r of queries [Closes #212]
dg added a commit that referenced this issue Nov 20, 2018
…r of queries [Closes #212]
dg added a commit that referenced this issue Nov 20, 2018
…r of queries [Closes #212]
dg added a commit that referenced this issue Nov 20, 2018
…r of queries [Closes #212]
@repli2dev

This comment has been minimized.

Copy link
Author

@repli2dev repli2dev commented Nov 21, 2018

This solves our issue only partially (for the exact duplicate queries) thanks for that! ... Though I am not able to came up with any better solution as the the queries with different param values cannot be cached... The same for different queries referencing same table columns (i.e. both referencing foo.my_column) due to the fact that DBAL doesn't really know they are the same columns and asks for 0..n column`s metadata.

So this is probably all what can be done if the library doesn't want to obtain all this metadata bypassing the PDO (and with using caching)... which is probably overkill and will probably not gain anything speaking of performance.

dg added a commit that referenced this issue Nov 23, 2018
dg added a commit that referenced this issue Nov 26, 2018
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
2 participants
You can’t perform that action at this time.