The Incident Archive

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 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)