FWD » Core Development » Database

While working on a related issue I noticed the following stack trace:

        at org.h2.jdbc.JdbcConnection.prepareStatement(JdbcConnection.java:734)
        at com.goldencode.p2j.persist.orm.TempTableDataSourceProvider$DataSourceImpl$1.prepareStatement(TempTableDataSourceProvider.java:226)
        at com.goldencode.p2j.persist.orm.SQLQuery.scroll(SQLQuery.java:279)
        at com.goldencode.p2j.persist.orm.Query.scroll(Query.java:231)
        at com.goldencode.p2j.persist.Persistence.scroll(Persistence.java:1280)
        at com.goldencode.p2j.persist.PreselectQuery.executeScroll(PreselectQuery.java:5546)
        at com.goldencode.p2j.persist.PreselectQuery.executeQuery(PreselectQuery.java:5520)
        at com.goldencode.p2j.persist.AdaptiveQuery.executeQuery(AdaptiveQuery.java:3472)
        at com.goldencode.p2j.persist.PreselectQuery.execute(PreselectQuery.java:5176)
        at com.goldencode.p2j.persist.AdaptiveQuery.execute(AdaptiveQuery.java:3359)
        at com.goldencode.p2j.persist.AdaptiveQuery.stateChanged(AdaptiveQuery.java:2896)
        at com.goldencode.p2j.persist.ChangeBroker.stateChanged(ChangeBroker.java:645)
        at com.goldencode.p2j.persist.RecordBuffer.reportChange(RecordBuffer.java:8587)
        at com.goldencode.p2j.persist.RecordBuffer.delete(RecordBuffer.java:7902)
        at com.goldencode.p2j.persist.TemporaryBuffer.delete(TemporaryBuffer.java:6773)
        at com.goldencode.p2j.persist.RecordBuffer.delete(RecordBuffer.java:7711)
        at com.goldencode.p2j.persist.TemporaryBuffer.lambda$loopDelete$15(TemporaryBuffer.java:6482)
        at com.goldencode.p2j.util.Block.body(Block.java:636)
        at com.goldencode.p2j.util.BlockManager.processForBody(BlockManager.java:8662)
        at com.goldencode.p2j.util.BlockManager.forEachWorker(BlockManager.java:10424)
        at com.goldencode.p2j.util.BlockManager.forEach(BlockManager.java:4357)
        at com.goldencode.p2j.persist.TemporaryBuffer.loopDelete(TemporaryBuffer.java:6473)
        at com.goldencode.p2j.persist.TemporaryBuffer.copyAllRows(TemporaryBuffer.java:3482)
        at com.goldencode.p2j.persist.TemporaryBuffer.copyAllRows(TemporaryBuffer.java:3277)
        at com.goldencode.p2j.persist.TemporaryBuffer.performOutputCopy(TemporaryBuffer.java:6215)
        at com.goldencode.p2j.persist.OutputTableCopier.finished(OutputTableCopier.java:102)
        at com.goldencode.p2j.util.ProcedureManager$WorkArea.scopeFinished(ProcedureManager.java:5020)
        at com.goldencode.p2j.util.TransactionManager.processScopeNotifications(TransactionManager.java:7432)
        at com.goldencode.p2j.util.TransactionManager.popScope(TransactionManager.java:4607)
        at com.goldencode.p2j.util.TransactionManager.access$7000(TransactionManager.java:654)
        at com.goldencode.p2j.util.TransactionManager$TransactionHelper.popScope(TransactionManager.java:8536)
        at com.goldencode.p2j.util.BlockManager.topLevelBlock(BlockManager.java:8310)
        at com.goldencode.p2j.util.BlockManager.internalProcedure(BlockManager.java:611)
        at com.goldencode.p2j.util.BlockManager.internalProcedure(BlockManager.java:597)
        at <customer code: end of block>

The customer code calls a internal procedure which has an input-output table parameter. You can see that the procedure block end and the OutputTableCopier is doing its job of populating the table parameter. Because it is not in append mode, the old content is deleted, in a loop. For each deleted record the state listeners are invoked and, in this case, the AdaptiveQuery.stateChanged() does the following (around line 2888): invalidates components, results and invokes execute() to repopulate the preselected result. In a worst case scenario, this happens for each deleted record in the table parameter. Which means a lot of SQL queries which are executed but the result is immediately dropped.

I am aware that this is a preselect query and the backing result set must be there, but I wonder whether we could act lazy in this case: to delay the effective re-fetch of the query only after the delete operation is finished on the table parameter. Or even better, to delay the execute() up to when the next row is requested. Of course, its buffers might be in an undefined state, but this should only happen during the 'internal' house-keeping and they should bot be exposed to ABL converted code.

More details in #6061-75.

Danut, I recall I changed the AdaptiveQuery from TemporaryBuffer.loopDelete into a PreselectQuery. Basically, there is no invalidation happening anymore.
Please try to recreate the scenario in #6630 (maybe based on #6061-75) and check if this is optimal. If PreselectQuery is acceptable there performance-wise, we can close this.

I recreated a similar scenario where TemporaryBuffer.loopDelete is called using a class field that has a destructor. There is no invalidation happening anymore as it was specified and I found no problems while testing, so the change fits this case correctly. This task can be closed.

• 100 records, 5 and 10 iterations
• 1000 records, 5 and 10 iterations
• 2500 records, 5 and 10 iterations
• 5000 records, 5 and 10 iterations
• 10000 records, 5 and 10 iterations

From what I've tested, AdaptiveQuery doesn't invalidate at all. AdaptiveQuery.invalidateIfReferenceRowExists is called, but there is no cursor available, other invalidation methods are not called at all. The results are overall better for AdaptiveQuery, what do you think Alexandru?