Suggest you review the predicates in the SQL statement and the explain plan of your vendor.
For instance, what did you have previously, what did you have with your alternate form of predicate (filter).
------------------------------
NIGEL CAMPBELL
------------------------------
Original Message:
Sent: Mon November 29, 2021 12:27 PM
From: Rick Van Enkevort
Subject: IN_RANGE VS BETWEEN
Henk, thanks for the quick feedback. It was only looking at dates. I changed it to BETWEEN and seems to run faster. It's going against payroll data.
------------------------------
Rick Van Enkevort
Original Message:
Sent: Mon November 29, 2021 12:19 PM
From: HENK CAZEMIER
Subject: IN_RANGE VS BETWEEN
Hello Rick,
IN_RANGE is a short-hand form for creating multiple predicates that use IN and BETWEEN , >= and <= operators.
If the IN_RANGE is simple, then the generated predicates are simple as well.
E.g:
The filter:
ORDER_NUMBER IN_RANGE { 1161:1171 }
generates:
"ORDER_DETAILS0"."ORDER_NUMBER" BETWEEN 1161 AND 1171
Here is a more complex example
The filter:
ORDER_NUMBER IN_RANGE { 1161:1171 , 1526:1532, 1555, 1558, 2021:}
generates:
ORDER_DETAILS0.ORDER_NUMBER IN (
1555,
1558 ) OR
ORDER_DETAILS0.ORDER_NUMBER BETWEEN 1161 AND 1171 OR
ORDER_DETAILS0.ORDER_NUMBER BETWEEN 1526 AND 1532 OR
ORDER_DETAILS0.ORDER_NUMBER >= 2021
I hope this helps with pinpointing where the performance problem is lying.
How complex is the IN_RANGE that you're using?
------------------------------
Kind regards,
HENK CAZEMIER
Original Message:
Sent: Mon November 29, 2021 10:01 AM
From: Rick Van Enkevort
Subject: IN_RANGE VS BETWEEN
I have a query that is using IN_RANGE for dates and runs a very long time. Does anyone know between the IN_RANGE or BETWEEN functions, which one is more efficient?
------------------------------
Rick Van Enkevort
------------------------------
#CognosAnalyticswithWatson