D
dbeavon
Guest
Thank you for that very thorough example. This was not an easy technique to discover, and many people probably wouldn't find it on their own. The technique should probably be mentioned in the QUERY-PREPARE documentation. It is not obvious that there is a special way to get local session variables into our query predicates without relying on any "hard-coded" literals. Concatenating the literals into our dynamic queries seems very primitive, and I feel dirty every time I do it. 
Nowadays with PASOE running on a different machine (outside of "shared memory"), the incremental penalty for multiple "query prepare" is probably even higher than what you described. Whenever the compiler is performing the query prepare operation, it doesn't seem willing or able to reuse schema from the prior iteration of the loop, so there is lots of repetitive chatter with the remote database, simply to gather the required schema. In fact it can be more expensive than the final execution of the "query" itself. Your example is similar to Jon Brock's above. One thing I was trying to say before is that there seems to be an inconsistency in the fact that we can can use local TT records as parameters to the dynamic query but *not* the memory variables themselves. It seems to me that *both* of these things should be accessible to the query. I can see why there may be technical challenges (per Laura's comments). But there should also be technical solutions. For example, the compiler could give us some free "syntactic sugar" in the place of the TT parameter record. The compiler might generate a "hidden" TT for us using all of the local variables in scope, and then it would effectively behave like there was a single outer loop on that single TT record before processing the rest of the query (thereby bringing all of the local memory variables into the scope of the dynamic query as well.) I'm not an expert on it by any means, but some compilers do fancy things to bring memory variables into the scope of a totally different function (called a closure) and it seems like an ABL compiler should be capable of bringing memory variables into the scope of QUERY-PREPARE as well. Or maybe as a less elaborate solution, the QUERY-PREPARE just needs another set of parameters where we can explicitly tell it what local memory variables should be pulled into the scope of the query preparation. It would define the corresponding/equivalent memory variables in the new execution context, and transfer values whenever the query is opened.
Continue reading...
Nowadays with PASOE running on a different machine (outside of "shared memory"), the incremental penalty for multiple "query prepare" is probably even higher than what you described. Whenever the compiler is performing the query prepare operation, it doesn't seem willing or able to reuse schema from the prior iteration of the loop, so there is lots of repetitive chatter with the remote database, simply to gather the required schema. In fact it can be more expensive than the final execution of the "query" itself. Your example is similar to Jon Brock's above. One thing I was trying to say before is that there seems to be an inconsistency in the fact that we can can use local TT records as parameters to the dynamic query but *not* the memory variables themselves. It seems to me that *both* of these things should be accessible to the query. I can see why there may be technical challenges (per Laura's comments). But there should also be technical solutions. For example, the compiler could give us some free "syntactic sugar" in the place of the TT parameter record. The compiler might generate a "hidden" TT for us using all of the local variables in scope, and then it would effectively behave like there was a single outer loop on that single TT record before processing the rest of the query (thereby bringing all of the local memory variables into the scope of the dynamic query as well.) I'm not an expert on it by any means, but some compilers do fancy things to bring memory variables into the scope of a totally different function (called a closure) and it seems like an ABL compiler should be capable of bringing memory variables into the scope of QUERY-PREPARE as well. Or maybe as a less elaborate solution, the QUERY-PREPARE just needs another set of parameters where we can explicitly tell it what local memory variables should be pulled into the scope of the query preparation. It would define the corresponding/equivalent memory variables in the new execution context, and transfer values whenever the query is opened.Continue reading...