CAN-DO

[TomBascom]

Funny you should mention that... I was testing that too.

The docs say:

If a user is logged into the system as root, the AVM allows access to the procedure even if access is denied by the id-list. You must specifically deny root access by adding !root to the id-list.

And I swear it used to behave just like it says.

But I cannot seem to get it to act that way. I suspect that somebody realized that that is a really bad idea a few releases ago and fixed it. But never told the documentation people. Or any of the rest of us.

Perhaps some of those still running ancient, obsolete and unsupported releases could run some tests and we could try to find out when it changed?

In this case I find that I am not at all upset that backward compatibility seems to have been broken.

 

[TheMadDBA]

I tried on V9, V10 and V11 (all on AIX) and couldn't get it to behave differently either. The code here has over 6000 CAN-DO references (going back many many years) and they are trying to upgrade to V11 from V10. Should be interesting trying to find all of the places an @ sign can mess things up.

Anybody running V8 or earlier probably has way more issues than CAN-DO at this point

 

[Rajat Sharma]

Hello Everyone, hope you are doing good.

As per earlier discussion, I have been using lookup/index instead of CAN-DO function and conveyed this message to my colleagues but they won’t understand that much and seeking more description/proofs regarding the same.

1. As already mentioned that CAN-DO is run on client side :

Ex:

MESSAGE CAN-DO (“something”) VIEW-AS ALERT-BOX.

If I do something like that (mentioned above) then it gives me either yes or no then does that mean that value “something” is pushed into client side for comparison with current (or all) USERID (confused with client vs server side). Please give your more inputs on this.

2. If I am having scenario like that:
Ex:

FIND customer NO-LOCK
  WHERE custnum = 1 NO-ERROR.
IF CAN-DO (customer.Check, “C,D”) THEN “Do something”.

/* here, customer value “customer.check is shipped to client side along with value “c,d” or what please suggest */

Should we replace above CAN-DO with LOOKUP here or what?

Like: Lookup (customer.check,”c,d”) > 0 even if we don’t want to check the option’s position in the list and we don’t need to worry about wildcard character’s ? Please suggest.

3. Should we never use can-do function or is there any scenario where we could use this (excluding security checking).

Please give your views on this.

Thanks & Regards!
Rajat.

 

[GregTomkins]

I don't totally follow your question but I do not believe there is any no notion of "pushing" between client and server-side based on use of CAN-DO. I think of CAN-DO mostly as a string comparison function and I would no more expect "pushing" between client & server than I would with SUBSTR or whatever.

(BTW, in your #2 I think you have the order of parameters to CAN-DO reversed).

IMO there are about a million things more important than whether you use CAN-DO vs. LOOKUP; but for maximum professionalism and minimum derision, isn't that what we all want?, you're probably best off to never use CAN-DO (for other than it's intended purpose, which I doubt anybody does anymore); for one thing, it's misleading, so use LOOKUP instead.

 

[TheMadDBA]

The point about CAN-DO being resolved on the client was related to queries (especially client-server queries)... meaning that if you use CAN-DO. LOOKUP, etc in a where clause the database will send back all of the records that match the other indexed criteria and the client will do the rest of the filtering.

There is nothing magical about CAN-DO that makes that happen where a LOOKUP will not, any time you put a function in a where clause things aren't likely to perform well.

In your example #2... the entire customer record is shipped to the client and then anything else that happens is all local.

 

[Rajat Sharma]

Thanks for replying Greg, TheMadDBA,

In example #2, if i change can-do with lookup or something then does it make any difference (please suggest)? which one we should we choose ?

Thanks & Regards!
Rajat.

 

[TheMadDBA]

Again... it does not make any difference at all what you do after the FIND... the entire record is local to the client at that point.

 

[TomBascom]

Many functions can actually be resolved server-side. (If you are using a shared memory connection this doesn't matter.) CAN-DO is one of the few that cannot. (This is because of it's relationship to security and the USERID, whether those features are used in a particular call or not -- the server lacks context for resolving these issues; the appropriateness of that is a subject for another debate...) If it is part of a WHERE clause then that portion of the WHERE cannot be resolved anywhere except on the client. If the rest of the WHERE narrows it down to 1000 records all 1000 have to go to the client to be checked vs CAN-DO. If the WHERE uses LOOKUP that work can be processed on the server.

 

[Rajat Sharma]

Thanks for replying Tom!

1. If i am using can-do without where clause (like in my example #2) then nothing would go to client side for comparison purpose? so it doesn't make any difference if i replace it with lookup.

2. Based on above discussion, I think every where clause is checked that the given method/function is evaluated at client/server side (please suggest). Is there anything special with where clause because if we write method without where clause it would execute on server and doesn't make any difference (please suggest). if i am doing something like:

find customer no-lock where can-do (customer.name, "xyz") no-error.

In above code all names of customer table along with value "xyz" are shipped to client side (one by one) and if i changed my code with LOOKUP then, are they going to execute on server side?

I understood that primary purpose of using can-do is security checking not string comparison but i wanted to conclude the performance concern that we have been facing by using can-do like stuff.

Thanks & Regards!
Rajat.

 

[TomBascom]

In case #1 there is only a client. There is no "server side". You are just executing client code with no db (and hence no server) involved.

 

[TomBascom]

In case #2 things are more complicated. There is a database query and db queries involve a client making a request of the server.

Most built-in functions (such as LOOKUP) can be executed on the server because there is sufficient information available to evaluate those expressions. If you say:

define variable NorthEastStateList as character no-undo initial "ME,NH,VT,MA,CT,RI".

FOR EACH customer no-lock WHERE LOOKUP( NorthEastStateList, customer.state ) > 0:
END.

All of the information needed to evaluate that can be sent to the server (NorthEastStateList), or is available in the servers context (customer.state). So when it executes it can all execute on the server without shipping each record back to the client to do the LOOKUP(). That doesn't make any big difference with shared memory clients but with a TCP/IP connection that can get expensive.

CAN-DO() cannot run on the server. Thus every record that needs to be checked has to be passed back to the client to be evaluated. That's expensive.

This shouldn't be particularly hard to test. But make sure to test it on a slow connection. Trying it out on your local gigabit LAN with a few dozen records probably won't show much difference. When a customer gets their hands on your code out in the boonies where they have a "really fast" dial-up connection at 9,600 baud they will get a very different result.

 

[TheMadDBA]

One important thing to note is that an index will not be used for fields with functions (including LOOKUP)... that example will read every customer record even if you added an index to customer.state.

Tom: I will take your word about some functions executing on the server since it has been ages since I have actually seen a client-server app. Might have to play around with a few cases when I have time, but I usually just avoid/fix functions in where clauses because they can't be indexed.

 

[TomBascom]

define variable i  as integer no-undo.
define variable e1 as integer no-undo.
define variable e2 as integer no-undo.

define variable NEStateList as character no-undo initial "ME,NH,VT,MA,CT,RI".

etime( yes ).
do i = 1 to 100:
  for each customer no-lock where lookup( NEStateList, customer.state ) > 0:
  end.
end.
e1 = etime.

etime( yes ).
do i = 1 to 100:
  for each customer no-lock where can-do( NEStateList, customer.state ):
  end.
end.
e2 = etime.

display e1 e2 ( 1 - ( e1 / e2 )) * 100.

When run self-service (shared memory) LOOKUP is about 45% faster.

When run over TCP/IP connection, even on localhost (no actual network) LOOKUP is 55% to 65% faster (there is a lot more variation from run to run).

I attribute the difference to the explanations above.

I could probably whip up some VST code to show the difference in network traffic but I've got "work" to do now

 

[TomBascom]

Yes, definitely avoid functions in WHERE clauses -- but if you /are/ going to stick one in there at least make sure that it isn't CAN-DO. There is just no excuse for that.

 

[TheMadDBA]

Yeah, it is a little faster. If I ever get cursed with a client-server app again and I can't fix the indexes I will keep that in mind.

You do need to change the lookup( NEStateList, customer.state ) to lookup( customer.state,NEStateList) so that it actually finds records though

 

[TomBascom]

Ok, I got sucked in and write some VST code... and I fixed the state thing too (it didn't change the result very much).

LOOKUP CAN-DO %diff
513 865 168.62
msgsRecv 2,702 18,902 699.56
msgsSent 2,601 18,801 722.84
byteRecv 223,392 1,450,992 649.53
byteSent 1,935,921 17,322,221 894.78

define variable i  as integer no-undo.
define variable e1 as integer no-undo.
define variable e2 as integer no-undo.

define variable msgRecv  as int64 no-undo extent 2.
define variable msgSent  as int64 no-undo extent 2.
define variable byteRecv as int64 no-undo extent 2.
define variable byteSent as int64 no-undo extent 2.

define variable NEStateList as character no-undo initial "ME,NH,VT,MA,CT,RI".

find _ActServer no-lock where _Server-Id = 2.
assign
  msgRecv[1]  = _Server-MsgRec
  msgSent[1]  = _Server-MsgSent
  byteRecv[1]  = _Server-ByteRec
  byteSent[1]  = _Server-ByteSent
.

etime( yes ).
do i = 1 to 100:
  for each customer no-lock where lookup( customer.state, NEStateList ) > 0:
  end.
end.
e1 = etime.

find _ActServer no-lock where _Server-Id = 2.
assign
  msgRecv[2]  = _Server-MsgRec
  msgSent[2]  = _Server-MsgSent
  byteRecv[2]  = _Server-ByteRec
  byteSent[2]  = _Server-ByteSent
.

assign
  msgRecv[1]  = msgRecv[2]  - msgRecv[1]
  msgSent[1]  = msgSent[2]  - msgSent[1]
  byteRecv[1]  = byteRecv[2] - byteRecv[1]
  byteSent[1]  = byteSent[2] - byteSent[1]
.

etime( yes ).
do i = 1 to 100:
  for each customer no-lock where can-do( NEStateList, customer.state ):
  end.
end.
e2 = etime.

find _ActServer no-lock where _Server-Id = 2.
assign
  msgRecv[2]  = _Server-MsgRec  -  msgRecv[2]
  msgSent[2]  = _Server-MsgSent  - msgSent[2]
  byteRecv[2]  = _Server-ByteRec  - byteRecv[2]
  byteSent[2]  = _Server-ByteSent - byteSent[2]
.

display
  "  LOOKUP  CAN-DO  %diff" skip
  e1 to 20  e2 to 35  ( e2 / e1 ) * 100 to 45 skip
  "msgsRecv"  msgRecv[1] to 20  msgRecv[2] to 35 ( msgRecv[2]  /  msgRecv[1] ) * 100 to 45 skip
  "msgsSent"  msgSent[1] to 20  msgSent[2] to 35 ( msgSent[2]  /  msgSent[1] ) * 100 to 45 skip
  "byteRecv"  byteRecv[1] to 20  byteRecv[2] to 35 ( byteRecv[2] / byteRecv[1] ) * 100 to 45 skip
  "byteSent"  byteSent[1] to 20  byteSent[2] to 35 ( byteSent[2] / byteSent[1] ) * 100 to 45 skip
with
  no-labels
.

 

[TheMadDBA]

Shouldn't you be billing hours somewhere

Interesting stuff. I will try it out across a WAN soon and let you know how that works out

 

[TomBascom]

"Professional development". It helps keep the billing rate where I like it

 

[Rob Fitzpatrick]

I've heard the guidance about avoiding functions in WHERE clauses many times and my question is specifically about query efficiency, not coding practice. Should the guidance be "always avoid the use of functions in WHERE clauses" or "always avoid functions of the query's buffer fields in WHERE clauses"? Not trying to nit-pick; I just want to isolate the core issue.

This trivial sports DB code doesn't cause any bracketing issues that I see (looking at _TableStat):

define variable d as decimal initial 3.14159.

for each customer where cust-num > int(exp(d, 3)):
  display cust-num.
end.

In this case the function result is essentially a constant as it is only evaluated once. Correct?

 

[LarryD]

Rob,

One PUG meeting here in Philly (when they used to have them) had someone give a talk on various for each timings (was in the OE10.x days).

A function that has a constant value such as what you have in your code in the where clause, was shown to be slightly slower (in some fraction of ms) than assigning the function to a variable. They had numbers to show the difference.

The reason they gave was that if you have a function in the where clause, Progress will evaluate it on every iteration of the for each. If you assign it to a variable, there is no function calculation so that time is saved.

They did state that for small table queries ("small" amount of records), there is little difference. But for large queries that have tons of records that little ms difference can be somewhat significant.