Per documentation: "The CAN-FIND function is more efficient than the FIND statement because it does not actually retrieve the database record. If the selection criteria can be satisfied just by looking at values in an index, then it doesn't look at the field values in the database at all."
Let's say you have a unique index on two fields (fieldA and fieldB) and one field not in any index (fieldC). How does the can-find resolve the non-indexed field? And in a case like this, is it more efficient to find the record and then check tableName.fieldC?
I theorize, that the index can be confirmed using the unique fields but the field values for fieldC must be looked at next. If this is the case, is this done on the server?
if can-find(first tableName no-lock where
tableName.fieldA = someValue and
tableName.fieldB = someValue and
(tableName.fieldC = someValue or
tableName.fieldC = someValue)) then.
in client-server mode can-find works as follows:
the 4GL compiler generates something called "e-code" from the where clause in the can-find statement. the e-code excludes the part of the where clause that was used for index bracketing. the compiler also generates the index bracketing information separately and puts it into the r-code along with the e-code. if the index bracketing information is sufficient, the e-code part may be empty.
at runtime, the client assembles the index bracketing information and the e-code into a network message that is sent to the server. there are some things, like function and method calls, that cannot be handed off to the server because the server gets only the e-code and not the application's r-code.
when the server gets the message, it unpacks it and searches for an index entry according to the brackets and key values sent. if an entry is found, and the e-code is not mpty, then the server has to fetch the record and evaluate the expression against the fields of the record. if no entry is found or the expression result is false, then can-find didn't. either way, the result is sent back to the client.
note that not every where caluse can be handled by the server because sometimes the client has the necessary data and the server does not.
if that is more than you wanted to know, i am sorry. if it is not sufficient, go ask laura stern. since i can't look at compiler the code anymore, i cant go see what the exact rules are.=
Hello, it is important to remember the purpose of this function.
Mainly you will use it to validate the existence of records that meet a condition, generally validate duplicates in advance.
It is faster because it does not update blocking flags and does not bring the entire record to memory.
Regarding the response time, any search that is not associated with values in index will become a slow search whole-index
This regardless of whether it is a physical search or a logic such as can-find.
In case this need arises it is important that you verify the same design of the database, because you are forcing the database manager to validate something for which it is not prepared
> Let's say you have a unique index on two fields (fieldA and fieldB) and one field not in any index (fieldC).
If you have a unique index on fieldA and fieldB, there can only be zero or one records matching your where clause. So instead of using CAN-FIND, why not just FIND the record with fieldA and fieldB in the WHERE clause and then evaluate the value of fieldC in the record buffer?
"Regarding the response time, any search that is not associated with values in index will become a slow search whole-index"
I'm not seeing a whole-index in the ref file. As far as the DB design is concerned, there's nothing I can do there. I can only work with what I have.
Maybe a better question is why "shouldn't" I use can-find in this case and instead find the record with a find and then evaluate the non-indexed field.
I had considered that. Then I started thinking about the can-find which led me to my original question. My preference is to avoid passing a record (though I could just pass a subset) which is why I considered the can-find.
This case is better for physical search and validation due to:
- The structure of the index will quickly register.
- The decision can be evaluated in a single sentence.
- Avoid the exhaustive search in the Database.
If you do the "IF CAN-FIND() THEN FIND..." thing:
1) Keep in mind that that is not an atomic sequence of events. The record could be deleted or have it's key updated between the CAN-FIND() and FIND statements so you still need to be prepared to somehow address possible errors with the FIND.
2) You will have two WHERE clauses to keep in sync. Which means extra opportunities for bugs to infest your code.
Frankly, I would just FIND the darned record. The use cases for only checking existence are, IMHO, few and far between and the benefits mostly miniscule. Keep it short and simple.
Also - in the original post, what is the purpose of FIRST?
in client-server mode can-find works as follows:
the 4GL compiler generates something called "e-code" from the where clause in the can-find statement. the e-code excludes the part of the where clause that was used for index bracketing. the compiler also generates the index bracketing information separately and puts it into the r-code along with the e-code. if the index bracketing information is sufficient, the e-code part may be empty.
at runtime, the client assembles the index bracketing information and the e-code into a network message that is sent to the server. there are some things, like function and method calls, that cannot be handed off to the server because the server gets only the e-code and not the application's r-code.
when the server gets the message, it unpacks it and searches for an index entry according to the brackets and key values sent. if an entry is found, and the e-code is not mpty, then the server has to fetch the record and evaluate the expression against the fields of the record. if no entry is found or the expression result is false, then can-find didn't. either way, the result is sent back to the client.
note that not every where caluse can be handled by the server because sometimes the client has the necessary data and the server does not.
if that is more than you wanted to know, i am sorry. if it is not sufficient, go ask laura stern. since i can't look at compiler the code anymore, i cant go see what the exact rules are.=
All great responses. In the end, I'm not sure either would really be significantly better in my situation but I believe I'll just use the "darned" find... ;)
Thanks everyone!