Skip to main content

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)

Comments

Popular posts from this blog

MANIFEST.MF merge JDeveloper for an executable jar

Goto your project > properties. Then click on deployment in the menu. Edit or add a jar deployment profile. Fill in the details under jar options (select Include manifest and give it a main class name) Also remember that the merge functionality only works with a BLANK line at the end of the merge file. REALLY this caught me. My merge file contents: Class-Path: commons-codec-1.3.jar [...empty line here CRLF...]

ADF: LOV Description instead of code

I keep on forgetting how to do this so this is a note to self in terms I understand (ie pictures): Add the related entiy object to yours (in the example we are adding RegionEO(list of values) to CountryEO) Now add the RegionName field from the EO and a transient attribute (I named mine RegionNameLOV) Make the transient attribute updateable and base it on the expression RegionName (the description you wish to display) Add a list of values to RegionNameLOV and map BOTH key to parent fk (region id here) AND RegionName to your transient coulmn (RegionNameLOV).  Map the transient as an Input text with List of Values. Then just drag the lov item onto the page (RegionNameLOV)

OJDeploy: Documentation for the tool

Real DOCS:  http://docs.oracle.com/cd/E26098_01/user.1112/e17455/deploying_apps.htm#OJDUG645 OJDeploy Documentation if you run it from the command line - I keep looking for this so I though I would post it here so I remeber. Oracle JDeveloper Deploy 11.1.2.1.0.6081 Copyright (c) 2003, 2010, Oracle and/or its affiliates. All rights reserved. Usage:   ojdeploy -profile <name> -workspace <jws> [ -project <name> ] [ <options> ]   ojdeploy -buildfile <ojbuild.xml> [ <options> ]   ojdeploy -buildfileschema Arguments:   -profile               the name of the Profile to deploy   -workspace      full path to the JDeveloper Workspace file(.jws)   -project              name of the JDeveloper Project within the .jws where the Profile can be...