I had an email exchange with a contact at this same customer this week. He did mention the lack of "APEX skills" in the organization, but as I candidly told him, the vast majority of their issues were a lack of Oracle skills, not APEX skills. They had many people, not necessarily proficient in data modeling or SQL performance tuning or Oracle, building applications intended to service thousands of concurrent end users. As I told him:
I wouldn't focus too much on "APEX skills". The problems you've had at your company are "Oracle skills". How to design a data model. How to write and tune efficient SQL access. How to be aware of data security aspects. How to author and maintain secure, efficient PL/SQL. APEX is simply the veneer over a database application.
Anytime I start a new APEX application, I actually don't touch Oracle Application Express. I usually start in SQL Developer Data Modeler and start designing the foundation of my application in a logical and efficient data model. Once I have that perfected, it's usually quite straightforward to build the Web interface in APEX on top of this.
Just today, I received another shining example of this philosophy of "Oracle skills". One of my favorite customers at a large enterprise in Europe, where they have hundreds of APEX applications deployed across the enterprise, sent me the following:
....we talked a couple of months ago about the weird visibility of APEX performance in companies. Just to give you another example, we also fight against the perception that APEX is slow, because people know that applications were built with APEX, and if the response time is bad, then APEX is slow. It's that simple. We had a complaint about a couple of APEX pages. One page took around 15 seconds to render with a complicated chart, the other page took around 3 minutes to show up.
The result of the analysis was - guess - bad DB design paired with bad SQL. They had a history table with 1.5 billion records, bad partitioning and they populated all LOV filters from this table, distinct, order by. At the end, the fact table was just 7 million records to filter from the LOV filter elements. We just built up a new table with all distinct values of the dimensions (the data is loaded only once every night) as base table to serve all filter elements. We dropped the unnecessary indexes and built a reasonable one and.....voila we were down to 2 seconds for the chart and 3 seconds for the page that took 180 seconds before.
It's very hard to explain that APEX is neither fast nor slow, because it is not doing anything relevant for itself but just query the database with the good or bad SQL that the developers provide against a good or poorly designed Oracle database, which is more or less busy with 50 other projects and data loads.
If you look at the remedy to improve the performance of their application, it was little to zero APEX and almost all Oracle knowledge and skill. By the way, creating a select list against 1.5 billion rows is going to take an equally long amount of time using .NET, PHP, Java, Jython, Perl, or whatever.
What's the intended takeaway from this blog post? Understand the architecture of APEX and how it interacts with the database, but also invest in understanding the Oracle database, SQL and database design.
7 comments:
Should have used MongoDB... :)
Just kidding. I agree 100%. Database skills seem to be at an all time low these days. :(
Cheers
Tim...
This generalizes to "you need to have the skill to figure out where the problem is, as well as the skill to fix the problem."
I've seen it over and over, a crew "expert" in whatever the tool of the project is, pushing it to the limit without thought of how that is pushing elsewhere, the database in the common case.
You don't really even need the skill to fix the problem, really just the skill to recognize it and find someone who has the skill. That's basic project management.
What you see here with Apex (or anything perceived as generally slow) is simply the face of the problem. If you get the flu, your face may turn green, but doing a face transplant won't help.
Put a fast productivity environment on top of this, focusing on making every little piece get out the door fast without thought of how the backend will take it - well, sounds like facebook, google, linkedin SOP.
Those of us who the CIO knows they need but doesn't know why, will have full employment for the forseeable future.
I'm doing performance tuning contract for an APEX application right now.
Pages were taking nearly 4 minutes to display. As expected it's nothing to do with APEX and everything to do with solution design.
After rewriting the solution, without changing the data model, pages are displaying in under 2 seconds.
In the wrong hands anybody can write a dog of a solution in any technology!
I couldn't agree more Joel, my fault is that I usually give priority to db stuff rather than bells and whistles and I pay a price for that.
Kind regards
Flavio
I'd fire him based on his own statement "...and 3 seconds for the page that took 180 seconds before."
-> He deserves it, alone for tolerating this obviously malicious code, which regularly starts 3 minute database attacks! :-)
Well-said.
Good post. I can confirm that. I have seen a lot of funny things so far. APEX is great - it gives you a possibility to create an application in no time but it also gives you a chance to copy and paste your code almost everywhere. You can hide you code and never find it again - item defaults, source values or post calculations...
The funniest thing ever was one application developed by all together three companies specialized on consultancy and programming of customized applications - two of them are big players here in Germany when it comes to Oracle. They created a page with around fifteen buttons - all with conditional display based on a PL/SQL function. The conditions where somewhat complex and the whole code was almost a size of an A4 paper. Instead of using a packaged function and input parameters they simply copied and slightly modified those conditions for each button. After a while they change their data model a bit... For APEX 3.2 there was no advisor to help you check your code. On the other page they had two-three reports with a select like this:
SELECT *
FROM table_with_two_thousand_recors
WHERE ID IN (SELECT ID
FROM table_with_five_milion_recors)
The user of the application also complained about APEX. I had a hard time to convince them that their problems have nothing to do with APEX :)
Regards,
Denes
Post a Comment