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)
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)
Comments
Post a Comment