SQL help

[SKC]

Hi all,
Is there a better way of writting this SQL :

SELECT DECIMAL(ROUND(AVG(COALESCE(REPT_EXEC_TIME_NBR,0.0)
),1),19,1)FROM TABLE1
WHERE REPORT_ID = temp_ID AND RESOURCE_ID = planResID AND
IFSC_USER_ID IN (SELECT IFSC_USER_ID FROM TABLE2 WHERE BUS_CAT_CD =
lob AND INTERNAL_USER_IND IN ( internalAmex, internalNonAmex )) AND
MONTH (REPT_REQ_DT) = temp_monthID AND YEAR (REPT_REQ_DT) = reportYear
AND DELIV_STATUS_CD IN (reportReady);

This SQL is taken out from a Stored proc.

Thanks in advance...

[AK]

> MONTH (REPT_REQ_DT) = temp_monthID AND YEAR (REPT_REQ_DT) = reportYear

REPT_REQ_DT BETWEEN DATE1 AND DATE2 might perform better

[Tokunaga T.]

cs****@gmail.com (SKC) wrote in message news:<de**************************@posting.google. com>...

Hi all,
Is there a better way of writting this SQL :

SELECT DECIMAL(ROUND(AVG(COALESCE(REPT_EXEC_TIME_NBR,0.0)
),1),19,1)FROM TABLE1
WHERE REPORT_ID = temp_ID AND RESOURCE_ID = planResID AND
IFSC_USER_ID IN (SELECT IFSC_USER_ID FROM TABLE2 WHERE BUS_CAT_CD =
lob AND INTERNAL_USER_IND IN ( internalAmex, internalNonAmex )) AND
MONTH (REPT_REQ_DT) = temp_monthID AND YEAR (REPT_REQ_DT) = reportYear
AND DELIV_STATUS_CD IN (reportReady);

This may be another way. But, I don't know better or not.
SELECT DECIMAL(ROUND(AVG(COALESCE(REPT_EXEC_TIME_NBR,0.0) ),1),19,1)
FROM TABLE1 T1
WHERE REPORT_ID = temp_ID
AND RESOURCE_ID = planResID
AND EXISTS
(SELECT *
FROM TABLE2 T2
WHERE T2.IFSC_USER_ID = T1.IFSC_USER_ID
AND BUS_CAT_CD = lob
AND INTERNAL_USER_IND
IN ( internalAmex, internalNonAmex )
)
AND MONTH (REPT_REQ_DT) = temp_monthID
AND YEAR (REPT_REQ_DT) = reportYear
AND DELIV_STATUS_CD IN (reportReady)
;

[Knut Stolze]

SKC wrote:

Hi all,
Is there a better way of writting this SQL :

SELECT DECIMAL(ROUND(AVG(COALESCE(REPT_EXEC_TIME_NBR,0.0)
),1),19,1)FROM TABLE1
WHERE REPORT_ID = temp_ID AND RESOURCE_ID = planResID AND
IFSC_USER_ID IN (SELECT IFSC_USER_ID FROM TABLE2 WHERE BUS_CAT_CD =
lob AND INTERNAL_USER_IND IN ( internalAmex, internalNonAmex )) AND
MONTH (REPT_REQ_DT) = temp_monthID AND YEAR (REPT_REQ_DT) = reportYear
AND DELIV_STATUS_CD IN (reportReady);

You could try if a correlated subquery is any better:

SELECT DECIMAL(ROUND(AVG(COALESCE(REPT_EXEC_TIME_NBR,0.0) ),1),19,1)
FROM TABLE1
WHERE REPORT_ID = temp_ID AND
RESOURCE_ID = planResID AND
EXISTS ( SELECT IFSC_USER_ID
FROM TABLE2
WHERE BUS_CAT_CD = lob AND
INTERNAL_USER_IND IN
( internalAmex, internalNonAmex ) AND
table2.IFSC_USER_ID = table1.IFSC_USER_ID ) AND
MONTH(REPT_REQ_DT) = temp_monthID AND
YEAR(REPT_REQ_DT) = reportYear AND
DELIV_STATUS_CD = reportReady;

--
Knut Stolze
Information Integration
IBM Germany / University of Jena

[AK]

Knut,

should there ever be an index on REPT_REQ_DT, this predicate would
never utilise that index:

MONTH(REPT_REQ_DT) = temp_monthID AND
YEAR(REPT_REQ_DT) = reportYear AND

so it could run way slower than this one

REPT_REQ_DT BETWEEN ... AND ...

What do you think?

[Knut Stolze]

AK wrote:

Knut,

should there ever be an index on REPT_REQ_DT, this predicate would
never utilise that index:

MONTH(REPT_REQ_DT) = temp_monthID AND
YEAR(REPT_REQ_DT) = reportYear AND

so it could run way slower than this one

REPT_REQ_DT BETWEEN ... AND ...

What do you think?

Yes, the BETWEEN has a good chance of being more efficient.

Besides that, there is another alternative: You could use 2 generated
columns, one with MONTH(rept_req_dt) and another with YEAR(rept_req_dt).
Then create indexes on those two columns.

It might be worthwhile to see what the BETWEEN (along with the index)
changes, and then evaluate the difference that might or might not come with
the rephrased query. I wouldn't change both together, though.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena

[AK]

>

Besides that, there is another alternative: You could use 2 generated
columns, one with MONTH(rept_req_dt) and another with YEAR(rept_req_dt).
Then create indexes on those two columns.

in my experience the optimizer is a little bit reluctant to choose
index OR-ing plans. A single index on both generated columns might do
way better