CODEWITHSUNDEEP
openedgeprogress-4gl
Latika Sharma
Latika Sharma

Reputation: 15

Openedge : an If..then condition in Open Query - Performance issue

open query q for each notepad no-lock where notepad.ctrl-code = pCode and (if pE-num = 0 then notepad.k-num = pk-num else notepad.e-num =  pe-num)

my question is that - will the if condition in where clause causes the query to run slow?

Upvotes: 1

Views: 170

Answers (2)

Tom Bascom
Tom Bascom

Reputation: 14020

Probably.

As written that WHERE clause is going to use the primary index to do a WHOLE-INDEX search ("table scan") and run the IF logic on each record.

To see that use "COMPILE program.p XREF program.xrf" and look for lines in the XREF output containing SEARCH.

Whether it is slow or not depends on your definition of slow and how many records are in the table.

Upvotes: 5

Mike Fechner
Mike Fechner

Reputation: 7192

Simple answer is yes - assuming it's a large enough table.

You should consider writing a dynamic query instead:

IF pE-num = 0 THEN 
   QUERY q:QUERY-PREPARE ("for each notepad no-lock where notepad.ctrl-code = pCode and notepad.k-num = pk-num") .
ELSE 
   QUERY q:QUERY-PREPARE ("for each notepad no-lock where notepad.ctrl-code = pCode and notepad.e-num = pe-num") .

QUERY q:QUERY-OPEN() .

The dynamic query is eliminating the IF condition from the queries and allows to make better use of available indexes.

Upvotes: 7

Related Questions