The joy of Oracle outer join

I've been working on report definitions lately. The work involves writing various database queries which are the data source that feed the reports.

One of our requirements is that the reports are to be able to run on an Oracle or SQL Server database. So I'm keen to use common SQL that will work on both DB platforms as much as possible in order to avoid having to maintain separate Oracle and SQL Server queries.

Some of the reports require outer joins. In the past this would have required separate SQL. Oracle used an awkward (+) notation to indicate outer joins. SQL Server uses the more standard OUTER JOIN syntax.

I'd kind of resigned myself to this but then I remembered that Oracle now in 9 and later now supports the ANSI OUTER JOIN style for indicating outer joins. This is great and I was able to do my queries using outer joins reusing the same SQL for Oracle and SQL Server without requiring separate DB specific syntax. Thank you Oracle for adding this.

One thing I'd been a bit suspicious of with OUTER JOIN was that it seemed hard for me to grasp what was going on with how the tables were joined. There were joins going on in the FROM section as well as standard joins down in the WHERE clauses. I found it hard to grasp this.

But then I kind of had a eureka moment when I realised that in the FROM clause the OUTER JOINs were creating a virtual table off the primary table which was outer joined to the other tables. So then everything made sense to me thinking about the OUTER JOIN clauses as forming a single virtual table which was then joined and evaluated in the usual way down below in the WHERE clause.

Of course all of an SQL query is about forming a virtual table which is the query results but once I was able to visualize what was happening in the FROM clause the OUTER JOIN now seems quite natural and intuitive. I hope I won't have to ever go back to trying to remember which side to put the (+) on.

HTTP session attributes and Python

I've done a good bit of Servlet development over the last couple of years. I've generally found Servlet to be pretty good to work with. I feel Servlet is about the best part of J2EE.

The thing about Servlet was that when it came out in the late 1990s it had to compete for market share and developer mind share. They had to convince people to start using it. So Servlet does things that are developer friendly. For example within Servlet development the programmer has access to the full JDK. So you can do stuff like launch threads and use synchronized methods or go to the file system. This is great and I've developed a new appreciation for the richness of the Java5 JDK.

Also with JSP/Servlet it is fast and easy for a new user to get running quickly. Tomcat and the JDK are free to download. From a standing start you can get a hello world page up quickly in just a few minutes. The web.xml can be minimal and mostly stays out of the developer's way.

With Servlet the container helps the developer and adds useful functions like the HttpSession. What I really like about HttpSession is the session attributes. This is a delightful little feature. You just start using them by name with like session.setAttribute() and read them back somewhere else with session.getAttribute(). This is very useful to pass stuff around between different functions. I like it because it reminds me of Python where you just start using stuff and don't have to worry about it being declared or strongly typed. It's very convenient and developer friendly. The fast, convenient and easy to use session attributes are an example of how Servlet is designed to be developer friendly.



Unfortunately the Servlet experience is not typical of working with J2EE. I suspect with many of the other J2EE components like EJB the developer community was considered captive. Since they didn't have to compete for developer mind share they didn't spend very much time or effort thinking about the developer experience, developer productivity or being developer friendly.

No I'm not a spammer

I clicked over to this site to start a post and I'm greeted by this.




Sorry about the small image. In summary Blogger thought this is a spam blog. I wasn't allowed to make new posts and had to click on the link to get them to review the blog to get off the spam blacklist.

I clicked the link then Blogger reviewed me and of course said I'm OK in this e-mail.


from: Blogger Help
to: "cbmc64@gmail.com {U 947063870279 B 19504237}"
date: Aug 24, 2007 8:55 PM
subject: Re: [#190813934] Blogger Beta non-spam review and verification request: http://cbmc64.blogspot.com/
mailed-by: trakken.google.com

Hello,

Your blog has been reviewed, verified, and cleared for regular use so that
it will no longer appear as potential spam. If you sign out of Blogger and
sign back in again, you should be able to post as normal. Thanks for your
patience, and we apologize for any inconvenience this has caused.

Sincerely,
The Blogger Team


--
So I'm glad to no longer be considered a spammer. I wonder why I got marked as spam. Perhaps I have haters who flagged me for some unknown reason, lol. I'm not sure what else about this site might make the Blogger algorithm think I'm spam.

No worries, I'm glad the site wasn't deleted. I was thinking of making some changes to this site layout anyway so now this is more reason to. Being mistaken for spam though is pretty discouraging.

Productizing

It's remarkable how much stuff can be added to a product without adding any new end user features. Often an initial product release is tied to a specific sale to an individual customer. The customer has a specific environment and the development team is able to get the initial release done on time by building to just that setup.

After the initial sale you want to sell it to additional customers. At this point the initial customer specific release becomes more productized. That is, the application is made able to run in different settings. These are some of the things we've added to the main product in my office since the original release.

-added support for additional application servers
-added support for additional databases
-added support for new host servers i.e. Windows
-fully tokenized the user interface labels to support additional languages
-add support for right to left rendering in the user interface
-make it easier to customize or rebrand the user interface
-add support for running in a clustered application server with failover
-spent time on performance analysis and improved performance in high load environments
-Javadoc code review and improvements
-JUnit code review and improvements
-refactoring and streamlined the internal modules structure
-use xdoclet to generate EJB baggage
-added support for several new iterations of the device specification and new data models, while maintaining backwards compatibility and workarounds for non compliant devices
-rewrote almost the entire inherited code base

Together these changes required several person years of effort to implement. These structural changes added no new end user features, but were necessary to make the product saleable outside of the original customer.

This type of productizing is what makes future releases take longer than the original v1.0 release for a specific customer and environment. Making things generic and customizable always takes longer than being customer specific and rigid. Adding support for new databases, application servers and host servers causes the number of development and testing combinations to become much larger and that necessarily slows down the development cycle.

Investing in backup technology

There are some standard desktop hardware technologies around that can maintain developer productivity in the event of common failures.

A longstanding thing is desktop uninterruptible power system. Our office is in an industrial park. Power outages are a fact of life here. They occur during the day several times a year. A very basic desktop UPS for around $30 each gives the users 10 minutes backup power on their desktop to save their work in progress, shut everything down cleanly, and log out.

In some cases in addition to lost unsaved work it can be difficult and time consuming to restart some applications if they do not stop cleanly. JBoss and WebLogic are like this.

Without desktop UPS people can lose up to several hours work. When this happens it is very frustrating for the user. In addition to lost productivity it also affects morale. The power is going to go out at least once a year. What's three hours of productivity plus employee satisfaction worth? Is it worth a one time expenditure of $30 per desk? I'd think and hope so. What's the marginal cost of a $30 UPS relative to the amount spent on each cubicle?

A more recent innovation is the dual hard drive PC. With hard drive costs coming down these can now be added to a PC for around $300. With dual HD, when your primary hard drive fails, which it inevitably will, your backup is right there and you don't lose anything you had set up on your PC.

For a programmer, we have all kinds of stuff on our PC hard drives. Little Python and shell scripts we write. Stuff we download like Ethereal and all kinds of other stuff, JDBC drivers, JBoss, all kinds of other stuff we use every day. PDF and Javadoc documentation. Plus all the settings like Firefox bookmarks and Office settings, Eclipse. Getting a PC set up again is a tedious, frustrating and unenjoyable process for a developer to go through.

When a programmer loses his hard drive, then the time to get back in business is around two full business days. And hard drives fail. I'd say around 50% fail in the first 30 months in a new PC. I'd guess about 75% fail in the first 5 years. In my office I've been there 6 years. In that time I've been lucky to only lose a hard drive once at work. However almost everyone else I work with has had at least two HD fails in the last two years. Some have had three. Everyone has had at least one.

Is two days of developer productivity worth the $300 extra to include a dual HD on new PCs. I'd think so. If not then why is he working there, it costs more than $150 per day for him to be there. This is a small investment that will enhance productivity and morale and is well worth it.


I guess something that could inhibit a company from making these small investments to enhance productivity and employee satisfaction would be the concept of visible vs. invisible costs. If the employees time is not billable then the immediate cost of lost employee time to things like power outages and hard disk crashes is zero. There's no direct and immediate cost to things like employee frustration at avoidable lost time. However there is a real and measurable cost to things like desktop UPS or dual HD. So unfortunately the visible cost outweighs the much larger though invisible cost and the investment is not made.

For billable employees, if billable time is lost then that's financially painful to the employer and they should be inclined to make these small investments to keep that billable tap open. Still, for non billable programmers their time should be really just as valuable to the employer as billable programmer time is to a consulting type software company.

Software Project Variables

A little while ago I was at a house party. I got talking to a fellow who does creative work for marketing campaigns. I asked him if it's true that two thirds of the money spent on advertising is wasted. He agreed that it was.

He said with advertising you can have it fast cheap or good, pick two. That rule applies in a lot of areas. I remember the first time I saw fast cheap good. It was a sign on the wall of an auto body shop when I was a child.

A veteran developer at the office who has team led some key projects in company history had a slight twist on it with software projects. In a software project ther are four variables that are naturally balanced against each other. They are scope, schedule, resources and quality.

Scope is the features contained in the release. Sometimes the scope is based on a sale or a contract agreement so all of the features must be implemented to get paid. Sometimes with a more internally defined project there may be room to cut or scale back features so the project can be completed within the desired timeframe. Problems can occur in a software project when the original scope expands. If scope increases then this must be balanced against the other three factors. The problems can occur when features are just "added" but no allowance is made in terms of resources or schedule for the new work.

Like scope, schedule is often pre set based on external agreements so cannot be changed. Sometimes in a project this is the one variable which does not change throughout. Often the software team is evaluated by meeting scheduled dates above any other objective. Of the four project variables schedule is often the most difficult one to change after the project begins.

Scope and schedule are often balanced against each other. I've been on many projects over the years where somebody wanted to add something during the project. When the project manager informs that any addition in scope can only be accomodated by slipping the schedule, the feature request goes away. I can recall many instances over the years where the person requesting the feature changed his mind when faced with a schedule slip. I can't recall a single time when someone agreed to slip a schedule of an in progress software project in order to add new features.

Resources generally means the head count of people working on the project. This can be increased somewhat if necessary. But as Brooks points out, you quickly reach a point of diminishing returns when adding resources. One type of resource which is often added is overtime. That allows the team to stay smaller and more manageable while being able to get more done in the same amount of calendar time. Overtime does have its costs though as quality tends to slip in the long sessions and the extra hours tend to be less productive per hour as it adds up.

Which brings it to quality. The reality is that quality tends to be the easiest place to take the hit on when a project is too big and too complex for the time and resources allocated. The thing about quality as opposed to say features or schedule is that the programming team does not have to get permission to slip on it. It can just happen and nobody knows about the quality issues until after the fact.

Unlike a schedule slip or missing features, a quality shortcoming is not immediately obvious. So it's easier to maintain an illusion of a successful software project when in fact the project was not as successful as hoped. A quality slip can take different forms besides obvious defects in the running product. Low quality can appear when the code is poorly structured and hard to maintain. Code copy and paste is used instead of refatoring common pieces. Javadoc and JUnit is skimpy or skipped entirely. Quality can appear as an unintuitive user interface lacking polish. Quality problems can appear in the form of low performance or crashes under load.

For the develment team lead and manager, when a software project is getting started they need to communicate to the stakeholders the factors that are always balanced. Sometimes the people outside the development team want to push for lots of major features and an aggressive schedule. The software team needs to communicate clearly up front when it is obvious that the hit will be on quality if that is the only factor which is allowed to vary.