Q. I have encountered a performance problem. I have a TPSQLQuery that returns all records in a
postgresql table and displays them in a DBGrid. I also have a TPSQLUpdateSQL that is used to write
changes back to the table. If I change a field in the table, using the DBGrid, the TPSQLQuery seems
to reload the entire dataset again. I would expect that the component would just update itself with
the new record, instead of reloading the entire table. For this small table it is not too much of a
problem, but for tables with a large number of records it is a significant performance hit. The other
components I am evaluating, the entire table is not reloaded after the update.
A. Your expectations are absolutely right for single user local Database Systems (e.g. MsAccess),
but PostgreSQL is a multiuser server. This means, that each client works not with data, but with a
snapshot of data at the moment. This also called transaction schema.
Each user application must have most fresh (actual) snapshot of data, otherwise it may cause
warnings by its actions, e.g. UPDATE rows which were deleted or changed by another user etc.
The developer's task is to solve this problem. One of the approaches is to use timeouts to get
fresh result set. However, this is not the subject of this answer.
> I would expect that the component would just update itself with the new record,
instead of reloading the entire table.
But data may be changed a lot. Moreover, even the record posted to server may be changed by
server logic (e.g. by triggers or rules). Data in this record may also affect on other records,
for example, in a table which holds tree structure: deleting some parent node will cause deleting of
all descendants. That's why we reload whole result set.
> For this small table it is not too much of a problem, but for tables with a large
number of records it is a significant performance hit.
Users need a huge result set very seldom. More often they prefer to work with "pages", which may
be done by LIMIT and OFFSET clauses of SELECT statement.
> The other components I am evaluating, the entire table is not reloaded after the
We believe, that this is an extension of such products, but not standard state, i.e. some
properties allow this to be done.
By the way, our TPSQLTable component has BatchModify property that allows such mode to be
emulated. Please refer the following link for details: