Wednesday, October 27, 2010

ADF VO criteria performance problem

One of our screens backed by a huge table was slow. (+1 10 seconds just for the query alone). And here is what I found out:

Our query with a criteria to filter the tablename and key < 1 second
SELECT
a.ele_name AS ELEMENT_NAME, a.pk AS PKEY, a.audit_result AS AUDIT_RESULT
FROM audit a, mds_users u
WHERE a.user = u.code
AND a.ele_name = :tablename
AND a.pk = :key

ADF converted query 12 seconds looks something like this:
SELECT * FROM (SELECT
a.ele_name AS ELEMENT_NAME, a.pk AS PKEY, a.audit_result AS AUDIT_RESULT
FROM audit a, users u
WHERE a.user = u.code) QRSLT WHERE ( ( ( (UPPER(PKEY) = UPPER(:key) ) AND (UPPER(ELEMENT_NAME) = UPPER(:tablename) )) ) ) ORDER BY AUDIT_DATE DESC

So I remove the UPPER from the query and all was good. Note to self always check this or you will suffer. (Ignore case check box in the criteria if you are looking for this)

No comments:

Post a Comment