Following on from my other post, I’ve been experimenting with the Reduced Delta feature which I’ve never looked at previously.
The documentation states that the downside to reduced deltas is that separate statements will need to be prepared as the changed fields will differ in each delta change. This makes sense.
I assumed from this that a separate statement would be prepared for each individual update but, after testing, this appears not to be the case.
When I tried some updates containing multiple record changes, it appears the reduced delta system behaves intelligently and only prepares each unique statement once. So all the changes in the delta which modify the same fields would only need a single statement prepared when the first of these changes is processed, with all following changes to the same fields re-using that statement.
Is this correct? If so then it would appear that there’s very little downside to using reduced deltas, unless I’m missing something. Most large bulk updates would generally modify the same set of fields and would thus still only need one statement prepared.
Lastly, a related question. When monitoring the SQL generated (using MSSQL), I noticed that each UPDATE is followed by a SELECT which seems to just retrieve the primary key by searching for the record just updated, using the primary key. So basically “SELECT [Id] FROM Table WHERE [Id] = @P1”
What’s the purpose of this? I know that, when inserting, it has to do something similar to select @@IDENTITY to retrieve the primary key of the newly inserted record but I don’t understand what it’s doing here for UPDATE statements.
Yes. We put already generated commands to cache and reuse them.
We generate RefreshDataset before processing delta changes. It contains all field that should be returned to client:
for i := 0 to aDataset.Fields.Count - 1 do begin
fld := aDataset.Fields[i];
if not (fld.DataType in [datAutoInc, datLargeAutoInc]) and
(not fld.ServerAutoRefresh or fld.Lookup or fld.ServerCalculated or not fld.LogChanges) then Continue;
Ah ok I suspected it was using the same mechanism.
So basically what I’m seeing on UPDATEs is a by-product of the mechanism which returns AutoInc values for INSERTs.
I get that, and that this would be required if there were any other Server AutoRefresh fields but, for UPDATEs, if the only fields being returned are in the primary key, doesn’t it become a superflous overhead? Literally every UPDATE is followed by a “SELECT PrimaryKey… WHERE PrimaryKey = X”
Yeah I guess that would work, it’d just be a bit of a pain to selectively disable the RefreshDataset option depending on the change type.
Not sure if this qualifies as a feature request but why not have an option whereby it automatically disables this feature when processing an UPDATE and there are no AutoRefresh fields outside the primary key, as surely it’s superflous in such cases?