Q. I have encountered a performance problem. I have a TMySQLQuery (or TMySQLTable) that returns all records in a MySQL table and displays them in a DBGrid. I also have a TMySQLUpdateSQL that is used to write changes back to the table. If I change a field in the table, using the DBGrid, the TMySQLQuery 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. MS Access), but MySQL 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 update.
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 TMySQLTable component has BatchModify property that allows such mode to be emulated.