Hi friends,

In software develoment one of the most common tasks is to join tables, in Baan there are different ways to do this. I will show the ways i know using some examples.

Number 1 (using refers to):

select tcibd100.*, tcibd001.cuni
from tcibd100 , tcibd001
where tcibd100._index1 = {:i.item}
and tcibd100.item refers to tcibd001
selectdo
|* Some process
endselect


Number 2 (using fields)

select tcibd100.*, tcibd001.cuni
from tcibd100 , tcibd001
where tcibd100._index1 = {:i.item}
and tcibd001.item = tcibd100.item
selectdo
|* Some process
endselect


Number 3 (using selectdo)

select tcibd100.*
from tcibd100
where tcibd100._index1 = {:i.item}
selectdo
select tcibd001.cuni
from tcibd001
where tcibd001._index1 = {:tcib100.item}
selectdo
|* Some process
endselect
endselect


My question is.....is there a performance difference between these options? and if yes which one is the best (faster)?

In my case i'm having performance problems with some queries thats why i want to kwon which one is the best option, if there is another way plese make me kwon.

Thanks for your help.

Well it depends on the situation. I thought #1 was suppose to be deprecated eventually. Anyway - I would check with infor and see if they have an optimization guide for LN.

Also there is a forth option using:
where tcibd100._index1 = {:i.item}
and tcibd001._index1 = tcibd100.item

I would use this over 2 and 3.

Thanks mark_h, i have search in infor web by description and i have not found anything, if you could give a document number it could be great.

Also i saw a number 5 way to query.

select tcibd100.*, tcibd001.cuni
from tcibd100 , tcibd001
where tcibd100._index1 = {:i.item}
and tcibd100._index1 = tcibd001._index1
selectdo
|* Some process
endselect


Thanks again for the info.

What I use is m2017bus - performance optimization guide. I believe this is a Baan 4 document, there could be a document specific to your version. I would contact infor.

Refer to the thread - Optimizing join (http://www.baanboard.com/baanboard/showthread.php?t=27718)

Thanks, i will study the documents you suggested and i will be posting if i find something.