FWD » Core Development » Database

The goal is to achiave a different query implementation for CAN-FIND construct instead of leveraging the FindQuery and resolving the distinction at run-time. This would allow us to have a dedicated machinery that implements CAN-FIND, to avoid buffer loading or DMO hydrating that may be tempting for RandomAccessQuery.

Part of this task, we need to change conversion to separate CanFindQuery. At run-time, we need to extract the RAQ relevant methods and have them somewhere to be reused by CanFindQuery. Maybe pull some RAQ logic in AbstractQuery and reuse in CanFindQuery. Or create an abstract class "AbstractLookupQuery" that can be implemented by RAQ or CanFindQuery. The point is to reuse logic (locking, FFC, etc.) from RAQ to CanFindQuery without extending it.

From a conversion perspective, I would greatly prefer if we implement this similar to how we implement functions (e.g. a static method call like RecordBuffer.canFind()) instead of a constructor + method call (e.g. new CanFindQuery().exists()). This is more consistent with the concept that this is not really a query but a built-in function in the 4GL.

To have a base to start from, I will describe current state.

For stand-alone function, there are two kind of it: unique and universal. In the former case the 4GL query looks like this:

CAN-FIND(book WHERE book-id EQ 8008)

CAN-FIND(FIRST book WHERE book-id EQ 9009)

new FindQuery(book, "book.bookId = 8008", null, "book.bookId asc", "book-id", LockType.NONE).hasOne();
new FindQuery(book, "book.bookId = 9009", null, "book.bookId asc", "book-id", LockType.NONE).hasAny();

To note that the same SQL query is executed for BOTH of these:

select book__impl0_.recid as col0_0_ from book book__impl0_ where book__impl0_.book_id = 9009 limit ? offset ?

The ResultSet returned is processed by SQLQuery.uniqueResult() and, because the rowStructure has a single field/column requested, the returned value is not a (partially) hydrated Record, but just the Long value representing the rowid of the target record.

Going up the stack, the Persistence.load() will use it to fully load the record using session.get(dmoClass, id, fields). Because partialFields is empty (not null), the returned value is just an empty 'shell': a record with only PK set. The FindQuery.hasAny() and FindQuery.hasOne() only checks if the dmo is not null. The difference is that the 'unique' variant will check the size of the ResultSet and return an error if it is not 1.

Conclusion
I think the current implementation is pretty fast. The only information fetched from database is at most 2 PKs, enough to detect if the existence and unicity of the record. There is no actual hydration of the record (as noted above, an empty (all fields nulls) is created if the record is not found in Session.cache). Of course, we can improve the flow locally (maybe not create the record at all?), but I am not that optimistic to see a visible improvement.

At the same time, implementing a CanFindQuery will possibly simplify the code (in both new class and the existing RAQ / FindQuery) by extracting specific processing and allow more flexible usage of the new class for particular syntax (I am also thinking of sub-queries, not analysed in this note).

There is no actual hydration of the record (as noted above, an empty (all fields nulls) is created if the record is not found in Session.cache).

This is what I mentioned as "incomplete hydration" in the meeting today. It was never about the actual SQL, but rather about its output. RAQ.executeImpl and execute returns Record and the caller will do the right work around it: load in buffer, throw exceptions, whatever. To implement CAN-FIND, we created a stub record that is incompletely hydrated. It was a "workaround". We also thought of returning Object (Long or Record), but there were many things on the API to be refactored at the expense of complexity and type checking.

This task was suggesting a separate implementation to avoid this workaround to start with and simply return true/false instead of a record != null. This saves us one heap allocation and some CPU overhead to hydrate it, look it up in the session cache, save it in the session cache, etc. The goal is to optimize some CPU cycles to start with.

As for SQL, I can't think of a faster one. There is no exists(table where ...) in SQL; we have to use SELECT and from my pov we do it optimally.

Of course, we can improve the flow locally (maybe not create the record at all?), but I am not that optimistic to see a visible improvement.

I am sharing your view on the short-run. But who knows what potential optimizations we may think of for CAN-FIND alone (e.g. if the table is empty, always return false). Maybe we can even use the Bloom filters to respond to it fast. This doesn't apply to FIND FIRST for instance where we would actually need the record. I think this can be an "enabler" of further optimizations.

The reason for me not to be that optimistic is that we did a good job previously 🤠, based on the existing constraints (using the workaround to fit in the current persistence API).

An alternative way to implement the exist in SQL would be

SELECT count(*) FROM <table> WHERE <predicate>;