Baanboard.com

Baanboard.com (http://www.baanboard.com/baanboard/index.php)
-   Performance & Tuning (http://www.baanboard.com/baanboard/forumdisplay.php?f=61)
-   -   between vs inrange, not in vs ANDed <> (http://www.baanboard.com/baanboard/showthread.php?t=28089)

jcasanov 6th September 2006 17:54

between vs inrange, not in vs ANDed <>
 
Hi,

i'm working in an enterprise that have Baan IV B40c.66. i'm new here and i'm learning with the old method of "trial and error".

i think i understand (10 months and a million cups of coffee after i started) some of the issues for baan performance (at least those that refer to using indexes, avoid some joins in some cases where is better to do the new select in the selectdo part...

Now my question is because i see in programs a lot of inrange's and (field1 > value_min and field1 < value_max) (for this one i'm prefer between because is more legible) and (field1 <> value and field1 <> value and field1 <> value)... i'm guessing that using between and (in or not in) is not standard in BaaN programs are these constructs low performance? or are there other reasons?

btw, we are using BaaN IV B40c.66 with informix 9.21 TC7 (driver level 1) running at Windows NT

regards,
Jaime Casanova

lbencic 6th September 2006 18:35

The difference with 'between' and 'inrange' comes when you are comparing an index with multiple parts (or other combined fields).

Inrange will compare the range for each field in the combined index individually, and Between will apply the range to the combined field all together.

See the WIKI link for further explanation and examples:

http://www.baanboard.com/programmers...ombined_fields

Dikkie Dik 7th September 2006 13:09

As you are using Level 1, you have to keep in mind that not all solutions for you mentioned on this board will help you. Therefore: keep on tracing check out the performance on the queries and find the logic why things are going slow. Feel free to use the documents on this SIG and forum to improve your knowledge on this topic.

I know this is a general answer, but in general this problem is very broad. More specific to your question: there is no specific performance reason for using IN RANGE or BETWEEN, it is more a developer decision.

Hope this helps,
Dick

jcasanov 7th September 2006 18:45

not in
 
Quote:

Originally Posted by jcasanov
Now my question is because i see in programs a lot of inrange's and (field1 > value_min and field1 < value_max) (for this one i'm prefer between because is more legible) and (field1 <> value and field1 <> value and field1 <> value)... i'm guessing that using between and (in or not in) is not standard in BaaN programs are these constructs low performance? or are there other reasons?

thanks to all who answered me...

but part of my question is still unanswered, what about in and not in... its better to use them or use ORed/ANDed constructions to see if a field does/doesn't contain some list of values?

Thanks in advance,
Jaime Casanova

Dikkie Dik 8th September 2006 10:03

IN/NOT IN vs AND/OR
 
The usage of an IN/NOT IN should result in the same data approach as an AND/OR. As the writing of an IN/NOT IN is sometimes easier to show the logic of a query, I mostly prefer this one.

So, for performance it should not matter. Maybe there are exceptions, but I don't know them yet.

Hope this helps,
Dick


All times are GMT +2. The time now is 00:49.


vB.Sponsors
©2001-2017 - Baanboard.com - Baanforums.com