Find x of Y vs Find x where xyz

[stokefc22]

Hi Guys,
Can some one tell me why there seems to be such a big speed difference between the following two code snippets.

find loadlsth of loadlstd no-lock no-error.

and

find    loadlsth where
         loadlsth.compno        = loadlstd.compno       and
         loadlsth.plantno        = loadlstd.plantno      and
         loadlsth.lh-number     = loadlstd.lh-number    and
         loadlsth.times-picked = loadlstd.times-picked
         use-index main2
         no-lock no-error.

The find using the of is a lot slower than the where and I was just curious as to why? main2 is the unique index on loadlsth and as u can see all fields exist in both tables??

Also, although the find using the where is much quicker than the of it is still adding a huge load to my routine and as mentioned above main2 is the unique index so i cannot see why it should slow things down so much???

Any ideas greatly appreciated!!

I'm using OE10.2b Service pack 3 in a windows 7 environment.

Cheers

 

[Cringer]

Did you run A and then run B immeiately afterwards on the same session? The data was probably cached in memory for the second run if you did - much quicker.


Edit: Also, a single run of the query is not a good way of establishing which is quicker. You shoul dtry running each a large number of times and average out the times.

 

[stokefc22]

Hi Cringer, thanks for your response. I did close the session and restart a new one for each test. Also the snippet I have provided is part of a much larger routine that is taking a long time to run and after much investigation i found it is this little find that is causing the issues

 

[Cringer]

Create a .p with both pieces of code in and compile with an xref. Do they both use the same indexes?

 

[TomBascom]

OF is the sort of syntax that makes a good demo when using the "sports" database.

It should never be used in real code. It obfuscates the WHERE clause.

Was your test session a single user session or are you connecting to a server? Cringer's point about the cache extends to the server. The first time a query is run everything must be read from disk. But after that the server is buffering it. And even if there is no db server the OS will be buffering the data in its filesystem cache. So to get a true test of first time performance you need to reboot between tests (and if the data is on a SAN you need to reboot the SAN). A better indicator of query efficiency is the ratio of logical reads to records returned. It should be close to 2 (unless you do a FIND by RECID, the 4GL always reads an index block and a data block -- so a perfect query has a ratio of 2). You can obtain that information from various VSTs but the details are a bit too complex for this posting. You might, however, enjoy this: Investigating Suspicious Progress 4GL Code. You could also just download ProTop and use that to watch the behind the scenes activity.

The "main" index is not always the best index. You are over-riding the index the compiler would pick by tossing USE-INDEX in there. The chance that you are smarter than the compiler is vanishingly small (that's not an insult, it applies to all of us). Remove USE-INDEX and test again.

If you are still confused publish the indexes available for this table and we can comment in more detail about how your query might be improved.

 

[stokefc22]

Thanks guys, I shall indeed take on board your comments, even if some of tom's go straight over my head and investigate.

As usual this issue has been usurped by others for the time being but I will report back as soon as I can get back to it.

 

[stokefc22]

So I finally got round to tying this one up! I used the xref option on compile and managed to spot that the find of was accessing the fields on the db multiple times for one find??? So this at least proved that the find of is not a good idea as its unpredictable.

I have to hold my hands up and admit that the real issue here was the number of loadlstd records that were being processed in the routine, the adding of the loadlsth find just compounded the issue. Having spotted this i managed to get a routine down from 20 minuts to around 2 hence making the powers that be happy.

I also have to admit after having a little look at the links provided by Tom this was a little over my head and although it does interest me to know about performance of the db I didn't really know where to start!!

Thanks again guys I did at least learn about the xref option and the dangers of using find of!!