The mysteries of query efficiently

J

jmac12

Member
Hi all

I'm currently working on a query taking a long time on site. I’m just wondering if anyone could point me in the right direction for documentation on query efficiently. As its a bit of a dark art for me. I’m currently working on open edge 10.2 b.
There isn’t currently an index that fits the queries criteria. Is there any point i using index if all the criteria doesn’t use it? any other suggestions for speeding it up would be help

for each sophdr where
sophdr.compNo = syscon.compno and
sophdr.ordAck = true and
sophdr.ordstat = "N":u and
sophdr.ordNum >= syscon.sordno and
sophdr.ordNum <= syscon.sordno-max
use-index main2 /*index doesnt have ordnum in*/
no-lock
break by sophdr.custslno descending
by sophdr.ordnum descending:
 
If I understand you correctly:

There is almost no point in using the USE-INDEX option on a query. IMHO, there are only a few very rare scenarios where the usage of this might speed up things. The compiler will pick the index for you - and out of my experience it does a pretty good job. Whenever I see USE-INDEX in Progress code I get suspicious ...

Every query that can't be satisfied by an index will cause a table scan because Progress can't bracket. Most of the times it will end up table scanning using the primary index. For Progress to be able to bracket it is essential that the chosen index contains the fields from the where clause from the beginning. As soon as a field is not present in the middle Progress can't bracket and a table scan will be the result.

Searching for "index bracket" in the knowledge base will give you a lot of results to start with.


HTH, RealHeavyDude.
 
1. Create many indexes with 1 indexed field (for each field in your query).
2. Remove use-index and place "by" phrase if you need records to be sorted.
3. Progress can use many indexes in 1 query if you dont specify use-index phrase.
4. Use compile value (filename.p) xref 1.txt and check what indexes are really used (in log look at SEARCH and line number)

Try use sophdr.ordNum > syscon.sordno - 1 and
sophdr.ordNum < syscon.sordno-max + 1
instead
sophdr.ordNum >= syscon.sordno and
sophdr.ordNum <= syscon.sordno-max
(Remove =)
Progress dont use any index when <= or >= are used in query, but if you put < or > index can by applied.
 
Thanks guys I'll have a look at those suggestions and see what I get. Another quick question, is it a bad idea to use a OR on sophdr.ordstat do OR's cause a slow down?
 
cheers I'm just working my way through the documentation see if i can do anything.. thanks for the replies
 
OR has not broken bracketing since v7...

The "one index per field" idea doesn't generally work out very well.

The "ideal" situation is one where every field in the WHERE clause is an equality match and where all of the leading components of some index are used.

USE-INDEX essentially throws away all of the v7+ improvements in index utilization and forces the old style v6 rules to be used. You're telling Progress that you believe that you are smarter than the compiler. In my experience there are very few people who are routinely smarter than the compiler and, so far as I know, they all work at Progress (and their job is writing the compiler).

Code: 
for each sophdr no-lock where                      /* put lock status right after table name, it's cleaner */
    sophdr.compNo = syscon.compno and       /* equality match, good */
    sophdr.ordAck = true and                        /* equality match, good */
    sophdr.ordstat = "N" and                         /* equality match, good -- although "N" seems like a strange value */

/* so, at this point, you want an index to exist where compNo, ordAck and ordStat are the leading components.  More components won't hurt.  */
/* if only some of the fields are leading components of an index that is still better than none at all */

/* but even if all of these fields appear somewhere in the index if there are leading components that are unreferenced it does you no good */

    sophdr.ordNum >= syscon.sordno and    /* range match, bracketing breaks here and an index scan will start within however much of a bracket the equality matches leave us with */
    sophdr.ordNum <= syscon.sordno-max

  break
    by sophdr.custslno descending               /* these fields aren't in the selection criteria so a sort phase will be needed, not ideal */
    by sophdr.ordnum descending:              /* but forcing an index to support the sort order will result in a table scan which would be very, very bad */
 
TomBascom said:
OR has not broken bracketing since v7...

The "one index per field" idea doesn't generally work out very well.
[/code]
Click to expand...

Agree.
Find first .... cant use many indexes, only one.
For each .... can
Thus we need create "complex index" for "find first" optimization.
 
FIND FIRST is about as likely to be a good idea as USE-INDEX.

FIND FIRST is, IMHO, a perversion that should be stamped out. At best it is simply a bad habit parroted from bad code perpetrated on unsuspecting novices in the dim reaches of the distant past. It is difficult for me to decide among USE-INDEX, FIND FIRST or CAN-DO as candidates for "Worst abuse of the Progress 4gl".
 
IMHO, Progress is (and always was) notorious for incorporating features into their product that should have never been used. And for the sake of backwards compatibility we will have to live with 'em forever.

<duck-and-hide>
Though, I have to admit that I have one use case for a SHARE-LOCK ...
</duck-and-hide>

LOL, RealHeavyDude.
 
Yeah, and I can think of lots of legit cases for FIND FIRST, and I don't really have a problem with USE-INDEX, to be honest. Sometimes I think it's nice to be clear about it rather than having to compile the code to see which index it picked and/or try to remember all the little rules about index selection. Plus, you gotta give Progress top top top marks for backward compatability, you really do.

Anyway, when I think of code horror, I don't really think of P4GL syntax itself, more of the overall code base and duplication and inefficient queries and stuff like that. If my worst problem were misuse of CAN-DO, I'd be a happy happy camper !!
 
Find first works ok in case of selection exactly one record. It is good optimized on unix terminal platforms. It works good as part of web application when you place Application server and DB server on 1 physical server (-db /path/dbname option). But if you write client-server application (-H -S) a lot of 1 record selection slow down entire system.
 
If you are finding a unique record FIRST adds no value and misleads people into thinking that the potential for multiple records exists. Which eventually leads to bugs in your code.

If you are using FIRST to resolve ambiguous FIND result sets you are playing with fire. And the bugs are already in your code.

If you think that FIRST is a performance improved then you are simply wrong.

Sure, there are times when FIRST is appropriate. But they are not so ubiquitous as to justify the frequency of its use.
 
To be honest - I don't have anything against a particular ABL (4GL, it was back then) statement or function. Some things, like the ability to define arrays on database fields for example, are just not future proof if you use them.

Mostly I am concerned with bad coding practice (this it what other would call coding horror, I think) of developers which are not familiar with essential things like buffer and transaction scope. That is, IMHO, what applications really hurt ...

Just my 2 cents, RealHeavyDude.
 
TomBascom said:
If you are finding a unique record FIRST adds no value.
If you are using FIRST to resolve ambiguous FIND result sets you are playing with fire. And the bugs are already in your code.
If you think that FIRST is a performance improved then you are simply wrong.
Sure, there are times when FIRST is appropriate. But they are not so ubiquitous as to justify the frequency of its use.
Click to expand...

I understand your opinion and totally agree with everything you just said.
It is just my habbit always use "Find first " instead Find.
 
Well I've taken the breaks out as that seem to slow it down the most and it a temp table is created anyway so if can just sort that. Also removed the use-index as that index only has compno ordack and ordstat in it. I’ll have to put a database request in for an index with compno ordack ordstat and ordernum which there isn’t one at the moment. I’ve also added an OR as was doing two for eachs before and surely more efficiency to do it this way. Is there any benefit to putting the no-lock after the name? I only ask cause not sure my boss would be happy with doing that style.

I’ll have to put it on site and see if that improves the speed as I haven’t got as much data here. Thanks for all your help hopeful this will help me with other ones I’m bound to come across in this system. I may be back on if it’s still slow. (and yes "N" is a strange value not my choice)
for each sophdr where
sophdr.compNo = syscon.compno and
sophdr.ordAck = true and
sophdr.ordstat = "N":u or
sophdr.ordstat = "E":u and
sophdr.ordNum >= syscon.sordno and
sophdr.ordNum <= syscon.sordno-max
no-lock:
 
I think you will want to add some parentheses around your "or"

Code: 
for each sophdr no-lock where      
                 sophdr.compNo  = syscon.compno     and     
                 sophdr.ordAck  = true              and     
                 (sophdr.ordstat = "N":u             or
                 sophdr.ordstat = "E":u)             and
                 sophdr.ordNum >= syscon.sordno     and      
                 sophdr.ordNum <= syscon.sordno-max:

Yes, I'm in the camp of preferring the lock status after the file name.
 
You must log in or register to reply here.
Back
Top