Friday 13 December 2019

Friday Funny - Festival of serious yahoos

I don't care what you all think, this is my blog, and I continue to laugh at this SQL joke.

select column_value
from apex_string.split('YAHOO~SERIOUS~FESTIVAL', '~')
order by dbms_random.value

If you're a Simpsons fan, you may recall this clip

Yahoo Serious Festival - The Simpsons
If you're not aware, Yahoo Serious is an Australian actor from the movie of the same name. He made it onto Time magazine, then nothing.

But how nifty is that apex_string.split function? And it's cousin join?
Although recently I saw Connor use what appears to be a native option, one that doesn't require an APEX package. On the blog topic list...

Tuesday 10 December 2019

Where would we be if we just believe?

As a science aficionado, there are certain phrases that ... catch the eye.

Recently on twitter there was an interesting thread that continued from Michelle Skamene's post on Top 15 Tuning Tips for APEX.  Michelle provided a wonderful follow-up post summarising the outcomes of the thread.

Point 9 suggests we avoid HTML in our queries, and use HTML expressions. This is undeniably good practice, but there was a question regarding how much performance is gained. Patrick Wolf summarises it well (sorting, XSS, context switching).

I've been curious about this for a while myself, and what better way to verify the truth than do some science ;p

I thought about the recent experimentation I did with interpreted code, and recent client work I've done with bind variables, so I thought I'd not only compare timings with a simple test harness I use, but see what v$sqlarea had to say.

When considering what SQL to compare, I decided to use the simple bit of SQL I used for my AskTOM Office Hours demo app. I saw how quickly the embedded HTML expanded to fit repeated business rules, and why not use something simple. If any difference was to be seen, let's see if it shows up with something basic, just like Juergen suggested.

The first query is just a simple query on scott.emp, with an extra expression to determine if the row is 'special'. This information is used declaratively within APEX, so these calculations are absent from this testing. The complexity has been transferred, but simplified. That's the reason it's best practice.
cursor c_1 is
select /*+ qb_name(c1) */ empno, ename, job, hiredate
   when hiredate < date '2000-01-01'
     then 'special'
   end last_cent
from scott.emp
So, let's just concentrate on the differences in the SQL we can measure.

The second query has the HTML tags embedded, repeated on columns where I substituted the column into the class attribute in the previous example. The code required expands quickly, hence the argument to keep the SQL and presentation layer separate.
select /*+ qb_name(c2) */empno
  ,'<b>'||ename||'<b>' ename
  ,case when hiredate < date '2000-01-01'
    then '<span style="color:purple;font-weight:bold;">'
     ||'<span title="'||job||'">'||to_char(hiredate,'fmddth Mon YYYY')||'</span>'
   end ||'</span>'
   as hireDate
  ,case when hiredate < date '2000-01-01'
     then '<span style="color:purple;font-weight:bold;">'||job||'</span>'
   end job
from scott.emp
The third query introduces context switching to PL/SQL due to the inclusion of htf.bold and apex_escape.html. This protects the output we'd otherwise have escaped using the relevant property. Or you could mitigate usage by sanitising data on the way in.
cursor c_3 is
select /*+ qb_name(c3) */empno
  ,htf.bold(apex_escape.html(ename)) ename
  ,case when hiredate < date '2000-01-01'
    then '<span style="color:purple;font-weight:bold;">'
     ||'<span title="'||apex_escape.html(job)||'">'||to_char(hiredate,'fmddth Mon YYYY')||'</span>'
   end ||'</span>'
   as hireDate
  ,case when hiredate < date '2000-01-01'
     then '<span style="color:purple;font-weight:bold;">'||apex_escape.html(job)||'</span>'
   end job
from scott.emp;
If we just compare throughput, there is a clear loser. Context switching between SQL & PL/SQL is just too much, though I would like to think in recent versions of the database, those functions could be enhanced with such devices as the UDF pragma?
     3.98 secs (.0000796 secs per iteration)
     5.16 secs (.0001032 secs per iteration)
    33.09 secs (.0006618 secs per iteration)
I just run these queries lots of times to measure throughput. Tom Kyte wrote a more enhanced test bed called runstats, if you want juicier details.
-- and start timing...
    l_start := dbms_utility.get_time;

    FOR i IN 1 ..c_iterations
     for x in c_1
     end loop;

    l_end := dbms_utility.get_time-l_start;
    dbms_output.put_line( TO_CHAR(l_end/100, '99990.00') 
                       || ' secs ('||(l_end/c_iterations/100) || ' secs per iteration)' );
What about v$sqlarea?
select substr(sql_text, 1, 22)sql, fetches, parse_calls, buffer_gets, sharable_mem
   ,persistent_mem, runtime_mem, user_io_wait_time
   ,plsql_exec_time, cpu_time, elapsed_time, physical_read_bytes 
from v$sqlarea  
where sql_text like 'SELECT%SCOTT.EMP%'
order by 1

v$sqlarea results

These statistics validate the timings, and I think also validates this as a performance tip that belongs in Michelle's list.
While performance difference with/without HTML may be marginal in this case, the supplementary benefits make it clear best practice.

Tuesday 3 December 2019

Include new APEX templates in an older APEX instance

Have you seen that super awesome theme in the new APEX version, then wondered how long it will it be before your site upgrades so you can actually use it?

What if I told you that your current version could be retrofitted to use that template?

I really like the look of the Content Row template, I think that's going to serve many developers good purpose.
Sure, I could create something similar now, but if I do as much as I can the same, then it should grease the wheels come upgrade time.

Content Row 18.2 vs 19.2

To make this happen, I
  1. Downloaded APEX 19.2
  2. Copied the /theme_42/1.4 folder to my 18.2 instance
  3. Created the report template, including template options
  4. Added the region component in my app
The first two steps are all about making the relevant CSS available to your instance.

After I had the /1.4 folder in place, I thought I would need to increment the Theme folder, but that just introduced other issues.

Instead, I just referenced the ContentRow.css file in the pages I wanted to use it.
This is the path where you can find it, so it can be placed wherever you like on your older instance.
Alternatively, you could include this for all pages in the User Interface attributes.

Then I needed to create the named column (row template) in my 18.2 application, details of which I just copied from a 19.2 instance.

Content Row syntax

I can even transfer the template options, so I started transferring from details on the page
You can also use this query to find instances within my 19.2 application.
select name, display_name, display_sequence, css_classes, group_id
  ,(select display_name
    where template_opt_group_id = group_id) group_name
  , help_text
where application_id = 32532
and report_template = 'Content Row'
order by display_sequence
Then I created the template options in 18.2.

Add Template Option

And sure enough, they become available on my 18.2 region component.

Page Designer outcomes

Due to the differences in availability of template option groups, some manifest a little differently, but I personally prefer a one click checkbox to the more fiddly select list.

All that said, I'm not sure I'm entirely sold on the small screen format, though maybe thing will change when I get more realistic data in there.

Content Row - mobile

Now I'm sure when I change over to use the actual template when I'm in 19.2, I'll need to re-apply the template options - but Shakeeb likes the idea of retaining shared template options, so you never know.

This was all done in 18.2. If you're on an earlier version, your mileage may vary.

If you're not using the Universal Theme, then you're probably not going to get very far.