Code Performance enhancement question, Find First VS. For Fi - OpenEdge Development - Forum

All Replies

Posted by Admin on 11-Feb-2012 01:59

tbaxter9999 wrote:

For first record. end.

is more efficient than a

Find first record.

I’ve proven it with enhanced program speed, but… I don’t recall the
explanation of the mechanics which make the “for first” statement
faster than the “find first”… could some kind soul out there jog this
old man’s memory about the “inner workings” of this statement vs. the
find statement? (they both used the same "where" criteria AND the
same index, but with substantial and noticeable performance
differences.)

I guess that would be the 'index bracketing' feature that only work in for/open query and not on a simple find, the later only use one index no matter what while the query can bracket on multiple indexes and could retrieve the records much faster for some where clauses.

This is generally true if there are multiple where conditions and the database was designed with multiple single field indexes as opposed to the composed multiple-fields indexes that were the norm before index bracketing was introduced.

As a side not, I prefer `for each: leave` to `for first` to avoid buffer scoping...

Posted by gus on 13-Feb-2012 10:54

Can you provide specifics of your code that you measured?

What were the differences in performance you observed?

I would have expected to see only a small difference.

Posted by ChUIMonster on 14-Feb-2012 08:45

Get yourself a copy of sports2000. Then try this:

for each customer no-lock by discount:

  display custNum name discount.

end.

Take note of the "first" customer.

Now try this:

for first customer no-lock by discount:

  display custNum name discount.

end.

And lastly:

for first customer no-lock break by discount:

  display custNum name discount.

end.

Do you still think that FOR FIRST is a good way to get a meaningful "first" record?

Posted by gus on 14-Feb-2012 09:13

Please excuse me but aren't you the one who was recently attempting to determine which record was the LAST one?

Just kidding Tom, I couldn't resist. You are correct.

Posted by ChUIMonster on 14-Feb-2012 09:24

Ah, well, I wanted to know when I am on the last iteration of a block -- I didn't really care which record it happened to be.

I will treasure that "You are correct." bit forever - it has a nice ring to it

Posted by ChUIMonster on 14-Feb-2012 09:40

marianedu wrote:

... if there are multiple where conditions and the database was designed with multiple single field indexes as opposed to the composed multiple-fields indexes that were the norm before index bracketing was introduced.

This is puzzling.

Index bracketting isn't something that is exclusive to single component indexes.

And I wouldn't say that the norm has changed at all.

There was a brief period of irrational exuberence shortly after v7 was released when it seemed like the capability to use multiple indexes to resolve queries meant that multi-component indexes could be replaced with lots of single component indexes. But, in practice, it turns out that that idea just doesn't work and that multi-component indexes are still very appropriate for all but a handful of usages.

Mike Lonski is reprising his excellent talk on the topic of index selection and bracketting at PUG Challenge Americas in May. It is worth the price of admission all on its own. http://pugchallenge.org/agenda.html

Posted by ChUIMonster on 14-Feb-2012 09:59

One more thing...

The only time that FIRST is going to make a difference from a performance persepctive is when your WHERE clause doesn't result in a unique result that is supported by an appropriate unique index.

And if that happens you really have to wonder -- is using FIRST really the right way to solve this problem? Or are you masking a more serious problem and potentially introducing subtle bugs (see the 3 different versions of a "first" record shown above) by doing that?

Once in a while you really do need the FIRST (or LAST) record in a set of records. When you do really need to do that you need to be careful how you go about it -- unexpected results are easy to come by and even easier to overlook.

Automatically adding FIRST to every FIND as a way of life is never warranted. (I know that isn't what Terry was asking about but far too many people do it.)

Posted by tbaxter9999 on 14-Feb-2012 11:43

Thanks for your post, Gus... I'll put together some code snippets as well

as the data dictionary

info for this simple query... this was used to build temp-table records for

a report, NOT to update

the database, so I was a bit more "gutsy" in experimenting with alternate

solutions to speed up

the code... funny thing, I pulled an XREF compilation on the "Find First"

(VERY SLOW... consistently

24 seconds to produce the report on screen) code vs. my modified "For

First" (VERY FAST, less

than 1 second to return the same data set for screen display) code... in

order to see what the

XREF listing said about the index(es) being used, and from the XREF

listing, the SAME SINGLE

index was used for BOTH code structures... so this REALLY puzzled me, and

prompted me to

post the original question, as the speed increase was just phenomenal, and

consistent....

I have to say that the program was written by a less experienced

programmer, so there WAS a lot

of redundancy in the code...I changed 37 of the "Find First" statements to

"For First" and of course

I had to remove the no-error statement, since it isn't used in the "For"

structure... Since the XREF

listing said that both statements were using the same index, I also felt

more confident with using

this structure... And of course I compared the before and after report

results, and they were

identical... so I ran with this new solution.... I hope that this email

makes it into the proper

posting "slot"... in looking at the posted threads I don't see any "obvious

way" to respond to

a specific response to my post and to post my thanks (and more info)

directly under a specific

persons response to my original post.

regards,

Terry Baxter

On Mon, Feb 13, 2012 at 8:55 AM, Gus Bjorklund <

Posted by Tim Kuehn on 14-Feb-2012 13:15

Terry -

That's odd - I'd expect that kind of behavior from a FIND as it did a table scan to determine if a record was AMBIGUOUS or not.

A FIND FIRST -should- stop after it finds a single matching record, unless it has to do a near-tablescan to do it.

Posted by gus on 14-Feb-2012 13:28

Thats why we have to see the code. There is more here than a simple difference between find first and for first.

Posted by tbaxter9999 on 14-Feb-2012 16:23

OK, Folks, here are 2 code snips, the first named

test1.p uses find first while the second, named test2.p

uses the for first. I've also tacked on the compilation

XREF listings under each one of these and I put the

pertinent data definitions and index definitions at the

end of this post.

/* test1.p */

find first simple.reasons no-lock

where reasons.rc = "x"

and reasons.category = "abc" no-error.

find first trackinguser no-lock

where trackinguser.usr = "tlb" no-error.

find first simple.serial-temp no-lock

where simple.serial-temp.ser-num = "ABC" no-error.

find first simple.empmaster no-lock

where empmaster.loginname = "tbaxter"

and empmaster.email1 gt "" no-error.

./test1.p 1 COMPILE test1.p

./test1.p 1 CPINTERNAL ISO8859-1

./test1.p 1 CPSTREAM ISO8859-1

./test1.p 1 STRING "reasons" 7 NONE UNTRANSLATABLE

./test1.p 1 ACCESS reasons rc

./test1.p 1 ACCESS reasons category

./test1.p 1 STRING "x" 1 NONE TRANSLATABLE

./test1.p 1 STRING "abc" 3 NONE TRANSLATABLE

./test1.p 1 SEARCH reasons pi-rc

./test1.p 5 STRING "trackingUser" 12 NONE UNTRANSLATABLE

./test1.p 5 ACCESS trackingUser usr

./test1.p 5 STRING "tlb" 3 NONE TRANSLATABLE

./test1.p 5 SEARCH trackingUser id-usr

./test1.p 8 STRING "serial-temp" 11 NONE UNTRANSLATABLE

./test1.p 8 ACCESS serial-temp ser-num

./test1.p 8 STRING "ABC" 3 NONE TRANSLATABLE

./test1.p 8 SEARCH serial-temp pi-ser-num

./test1.p 11 STRING "empMaster" 9 NONE UNTRANSLATABLE

./test1.p 11 STRING "Employee master" 15 NONE TRANSLATABLE

./test1.p 11 ACCESS empMaster loginName

./test1.p 11 ACCESS empMaster email1

./test1.p 11 STRING "tbaxter" 7 NONE TRANSLATABLE

./test1.p 11 SEARCH empMaster si-login

./test1.p 11 STRING "pi-rc" 5 NONE UNTRANSLATABLE

./test1.p 11 STRING "id-usr" 6 NONE UNTRANSLATABLE

./test1.p 11 STRING "pi-ser-num" 10 NONE UNTRANSLATABLE

./test1.p 11 STRING "si-login" 8 NONE UNTRANSLATABLE

/* test2.p */

for first simple.reasons no-lock

where reasons.rc = "x"

and reasons.category = "abc".

end.

for first trackinguser no-lock

where trackinguser.usr = "tlb".

end.

for first simple.serial-temp no-lock

where simple.serial-temp.ser-num = "ABC".

end.

for first simple.empmaster no-lock

where empmaster.loginname = "tbaxter"

and empmaster.email1 gt "".

end.

./test2.p 1 COMPILE test2.p

./test2.p 1 CPINTERNAL ISO8859-1

./test2.p 1 CPSTREAM ISO8859-1

./test2.p 1 STRING "reasons" 7 NONE UNTRANSLATABLE

./test2.p 1 ACCESS reasons rc

./test2.p 1 ACCESS reasons category

./test2.p 1 STRING "x" 1 NONE TRANSLATABLE

./test2.p 1 STRING "abc" 3 NONE TRANSLATABLE

./test2.p 1 SEARCH reasons pi-rc

./test2.p 6 STRING "trackingUser" 12 NONE UNTRANSLATABLE

./test2.p 6 ACCESS trackingUser usr

./test2.p 6 STRING "tlb" 3 NONE TRANSLATABLE

./test2.p 6 SEARCH trackingUser id-usr

./test2.p 10 STRING "serial-temp" 11 NONE UNTRANSLATABLE

./test2.p 10 ACCESS serial-temp ser-num

./test2.p 10 STRING "ABC" 3 NONE TRANSLATABLE

./test2.p 10 SEARCH serial-temp pi-ser-num

./test2.p 14 STRING "empMaster" 9 NONE UNTRANSLATABLE

./test2.p 14 STRING "Employee master" 15 NONE TRANSLATABLE

./test2.p 14 ACCESS empMaster loginName

./test2.p 14 ACCESS empMaster email1

./test2.p 14 STRING "tbaxter" 7 NONE TRANSLATABLE

./test2.p 14 SEARCH empMaster si-login

./test2.p 17 STRING "pi-rc" 5 NONE UNTRANSLATABLE

./test2.p 17 STRING "id-usr" 6 NONE UNTRANSLATABLE

./test2.p 17 STRING "pi-ser-num" 10 NONE UNTRANSLATABLE

./test2.p 17 STRING "si-login" 8 NONE UNTRANSLATABLE