Hi everyone,

Simple question :

I'm verifying 4GL code in baan related to Programs, Reports and scripts (in regards of solution 156806) 'Refers to' keyword in where statement.
[list=1] where tcedi751._index1 = {:tcedi750.btno, :tcedi750.brec}
where tcedi751.btno = tcedi750.btno and tcedi751.brec = tcedi750.brec
where tcedi751._index1 refers to tcedi750[/list=1]
What is best in those ?

Thanks and regards
:confused:

In my opinion,
where tcedi751._index1 = {:tcedi750.btno, :tcedi750.brec}
is your best bet. It's what I would use and using the index is supposed to perform better.

:confused:

I still don't understand why my colleagues are switching
from one method to another using '=' operator to
'refers to' operator ...

bad coding/habit ?

My personal preference is to use "refers to" when possible. Why?
Habit :)
= sign locks you into an equal join all the time. With refers to we can use the unref clause
one can get to see the data dictionary level relationships at a glance.

On a side note, I have seen people who have prior experience with other databases use the "=" sign, whereas those of us who cut our teeth in programming by starting with Baan stick to "refers to".

P.S.: Bob, you may have opened a can of worms with this one :D.

There is a quite a bit of information in the Baan help (BAAN SQL topic), which goes over the use of indexing and refers to statements. It may be worthwhile having a read of this.

A good rule of thumb (and good practice) is to always use the index and refers to options (in this case, if you were selecting the records of tcedi750, then you should use tcedi751.cmba refers to tcedi750).

Personally, I would go for option 3, so you did the select on both tcedi750 and tcedi751 at the same time, to save two select statements. But if this was only a select on tcedi751, then both options 1 and 2 are valid. According to the documentation, if you don't fill out a particular index, then index 1 will be used (beware of the order by statement....).

The advantage with using the 'refers to' option (as Vamsi has already stated), is to allow you other referential options, such as;
UNREF modes
PATH options
and also referred by (to go the other way).

In past experience (long long ago, in a galaxy far away) we found that the speed of your select using index or refers to depends on your database. Some handle references better/faster than others. If I remember right, we found that some did not handle the references well at all, and performed a full table scan.

For that reason, I specify the index whenever possible. However, the refers to / as referred by do have greater flexibility, as mentioned above. I believe the newer databases will not have such problems with referances.

You would have to do a speed test on your machine to see which comes out the winner, as I no longer have the information.

Follow this link (http://www.baanboard.com/baanboard/showthread.php?s=&threadid=1956&highlight=Performance+Guide) to download a copy of the performance guide. This was posted by dbclark and may answer some of your questions.

When I first started using Baan a consultant told me if queries were too slow - try breaking them apart. The performance guide explains when to do something like that. So you may find Baan's answer in the guide.

Hope this helps!

Mark

Hi;

When looking to this case ; i want to mention that you better watch out when using your SQL .

From version B52a ; the Baan SQL change a little.
Baan wanted that the Baan SQL looks more like standard SQL . Therefor several constructions are no longer allowed.

(such as referred by; the use of a path; select tcibd001 from tcibd001 (without the .*) ; ...)

If you want to upgrade in the future be aware of this because it can give you a lot of troubles and rework.:eek: