[Patch] Data type cache to speed up query tool
От | aiht |
---|---|
Тема | [Patch] Data type cache to speed up query tool |
Дата | |
Msg-id | 50B08B9E.1030307@gmail.com обсуждение исходный текст |
Ответы |
Re: [Patch] Data type cache to speed up query tool
|
Список | pgadmin-hackers |
Hi pgadmin hackers, I have been having trouble with the speed of the pgAdmin query tool on remote links, and I'm sure I'm not the only one. The delay is due to loading the data type information for every column: > QUERY : Scalar query (localhost:50119): SELECT format_type(oid,-1) as typname FROM pg_type WHERE oid = 1114 > QUERY : Scalar query (localhost:50119): SELECT CASE WHEN typbasetype=0 THEN oid else typbasetype END AS basetype FROM pg_type WHERE oid=1114 I have been playing around with a fix for quite a while, but I have finally sat myself down and put it on github (https://github.com/aiht/pgadmin3/commit/80a896e674bf68cc67aa3dc0dd425db9aa7372aa) (patch attached, too). I'm still trying to make sure it does exactly what I expect, so the debug logging is quite heavy, but I wanted to ask a few questions to see if I am on the right track. If this code is useful to the project, I'll trim the logging down so it isn't in the way. Code conventions: * Does the pg prefix for a class name have a specific meaning and if so, am I using it wrongly? * I have copied the i prefix on some methods, but I don't really understand what it's for; I was thinking perhaps 'internal'? Further suggestions: * This code supports pre-caching all types in one go, but currently only loads them one by one. Maybe this should be an option. * When a pgConn is Duplicate()d, its cache is copied for the new connection to use, but as far as I can see this is only called when doing File->New Window in the query tool. If more pgConns are constructed by Duplicate() then caching may be more efficient. * Quite a few other parts of the code do their own type-info loading; at least a few of them could share in this cache. However, the other code does not load types one by one so they do not have the same latency issues as the query tool. Possible problems: * Threading? As far as I have seen, the code doesn't tend to use locking. The query threads just avoid accessing each others objects while they are active. I have not added any locking, but each cache object belongs to a single connection, and is only used from code that was already using that connection, so I think it is okay. * The cache is currently not flushed, because I was not sure when to trigger it. The main object tree should probably flush its cache when the view is refreshed, but I am not sure about what kind of changes the server may make to existing types that require a flush. * I have not tested this on Postgres variants (EDB etc.) or on old versions. I have not tested the current version on Windows (although I did test an old version of the same code long ago). I have not yet patched the MSVS project file, but the only change required should be to add the pgTypeCache.{h,cpp} files. I hope it helps! Harun
Вложения
В списке pgadmin-hackers по дате отправления: