"n" methods to improve Oracle Apex performance

subscriptions

pl/sql packages

Authorisation Schemes

-- define some supporting objects

-- now you can just select from a "table"

http://www.amazon.com/Pro-Oracle-Application-Express-ebook/dp/B001U0PFCC

copy object

-- create a pipelined function that

communicates with LDAP

Page Source

pop

culture

nerds?

just

don't forget

the one

percenters...

... or you could use bulk binding

forms style architecture

database on different tier

you could loop through the array...

Sage Computing Services

"n" methods to improve Apex performance

Scott Wesley

Systems Consultant & Trainer

larry lessig - TED 2005

the Apex page builder

the law is strangling creativity

so let's consider...

setting everything up once...

Application Builder

Authentication Schemes

...allows simpler conditions

do your conditions really need to run that SQL?

Shared Components

Page Template

the motion sickness will wear off... momentarily.

= 8 hours / month waiting

= 90 seconds / month waiting

but this could save minutes

that runs once a day?

that executes a thousand times a day?

it also pays to monitor your application

Should I tune a 3 second page

or tune a 1 second page

-- you may wish to add your own

Weighted Page Views

or draw your own conclusion

Pop Quiz:

table design

table size

just remember

a number of factors can impact performance

= bottleneck

hardware

Page Builder

this might be no good...

experiment

do I wait...

database version

more feedback?

data distribution

surely you don't ever take advice...

server load

...and segues

or do I give the user feedback

this is probably more likely:

but back in sequence...

because propensity for re-use in Apex

and that's ok.

I know you know you should use this:

and not this:

from Oracle?

phase of the moon

add regions used on

multiple pages here

statistics

how about debugging?

Apex 4.0 brings much improvement

I'm running out of...

a

final

word

small table

it's not always the SQL

big table

Page Zero

with your condition based on a shared function

hmm...

Page Attributes

correcting user entered data

you can also reduce network traffic

but don't use page numbers

have a think about caching your pages & regions

use page alias

before page submission

by adding simple javascript validation

network traffic

http://forums.oracle.com/forums/thread.jspa?threadID=486516

be aware of your pagination

Report Region Attributes

ever put CSS in your SQL?

consider validation sequence

if you can 'see' the number of rows...

don't execute your validation...

just package your conditions

... consider setting a (respectable) upper limit

think about

SQL

too

...if some errors have occurred already

in case Oracle changes the behaviour

of these persistent variables

please don't...

want some reasons why?

hinders re-use

need to change SQL to change format

hard parses

can increase number of

impacts scalability

templates are there for this purpose

as is CSS

and item formatting attributes

CSS - in the right place

... much better

scalar

so what's this I hear about AJAX?

subquery

caching

que?

consider using

it will probably be more applicable to your own functions

usage of the v function...

(or this can't be avoided)

but if you

have

to use your own function

is more likely

to be found

in a package

because the documentation says so

regardless of complexity

hasn't Tom taught you to use

but it's more preferable to parameterise:

BIND VARIABLES

but why does this happen?!

do you know what else the documentation says?

yet?

because of weirdness like this...

but I just want to do this?

why?

because it helps...

you shouldn't really need to do this

ui defaults

testing

and to stop bugs...

which is why you should be

familiar with this next function

first of all

which is why we're here today

so use COALESCE instead of NVL

& efficiency

some worthy thoughts on

page / application

item usage

...and don't be afraid to be verbose

shall we go back to the page builder?

close your eyes ;-)

PL/SQL

SQL

easy!

and context switching between

zero

speaking of multiple db hits

still consider the use of views....

with declarative cascading LOVs

...to stop multiple hits on the database

still has it's place in Apex

page

but without the lag

don't forget about those scripts yet...

remember post-query network latency?

this is still potential

for some really clever stuff

let's do SQL related stuff for a bit

new topic time

(again)

shared components

the related application process

what about "forms style"

when-validate-item

Call an AJAX Process

it just calls some PL/SQL

plugins

when something happens...