Sometimes I get a little preachy in my presentations. OK, maybe I get a LOT preachy. In exchange for being stuck with me for 90 minutes, I promise every person will receive at least one new technique or trick that will greatly affect their productivity when working with the database. Fortunately for me, most agree that this is a fair trade!

Things generally go at a pretty rapid pace until I bring up one of my favorite IDE utilities – the ability to click through a SQL statement instead of typing it out by hand. Most developers are first introduced to such a concept when they use Access. It’s not a great first experience, and I understand why this might add to the resistance. If you still don’t know what I’m talking about, here’s a couple of pictures.

Instead of typing this

A SELECT statement grabbing a few columns from 8 tables and joining them.

You should be able to click into this

I can build the same query in only a few clicks

Why Is This Bad?

I know most of us can’t type this fast. Why not let the tool automatically build the framework for our query? When we’re ready to code the ‘fancy stuff’, we can still do that.

For most of you I know it’s just a matter of habit. I’m not suggesting you change the way you work if that’s already working for you. But if you need to squeeze a few more minutes into your day, this may be the most direct path to that goal.

I posted this on Twitter the other day to get some feedback, and as always I had a lot of ‘yeah, you are wrong again Jeff’ replies. Thanks Twitter friends! In all seriousness though, I had a couple of really good constructive feedback, most notably from @Boneist and @senseware

Dawn – I hadn’t thought about that. I can definitely relate to having fingers on keyboard channeling some inner path to my conscience and enabling my ideas to take solid form.

Luke – This can still be important even in an Oracle database. That’s why a good IDE will allow you to specify your join order as needed!

So Why Else Am I Wrong Here?

Feel free to tell me why I’m right too πŸ™‚

Why I Think I Am More Right Than Wrong

A few years ago, in a former life, I saw a survey of several thousand Oracle customers. The goal of the survey was to identity what our tool was doing right or wrong. One of the questions we asked focused on productivity and features. By a wide margin, the clear ‘winner’ was the ‘Query Builder’ feature. Respondents said that for every time they opened the window, they estimated savings of 3 to 5 minutes. Now think about that for a second. How often do you write a query that involves more than a couple of tables? How many of those queries have long or hard-to-type column names? Now multiply that by 5 minutes. I think the promise of that kind of time savings compels you to at least try what I’m talking about. Pretty please.

Of course I couldn’t pass up an opportunity to let you know that my new favorite Oracle IDE also has a Query Builder πŸ™‚

Ok, sermon’s over. Now what’s for lunch?

Author

I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

9 Comments

  1. Pingback: Click AND Type your Queries with the Integrated Query Builder

  2. Say I wanted products that had any promotion, but I wasn’t interested in the promotion details, would it build an EXISTS subquery ? Or would it do a join to PROMOTIONS and expect me to ween out the duplicates ?

    There’s also a good chance I’ll want to add other filter conditions, or put various functions around queried columns. So I want table aliases and maybe some column aliases to. Oh, and I prefer ANSI JOINs.

    Finally, I often have a single SQL window open with five or six SQL statements in. I think I can handle that better than multiple graphical queries.

    Oh, and I’m an old 1.0 fuddy duddy.

    • JeffS

      You are the driver Gary, it goes where you tell it to go.

      EXISTS SUBQUERY – Yes

      Aliased fields – Yes

      ANSI JOINs – Yes, Yes, Yes

      I’m not saying use a graphical query builder for 100% of all your queries, I’m saying use it to START your queries. You’ll end up in your editor eventually…why not get a head start?

      If you were really a fuddy duddy, you wouldn’t be here or on Twitter, so I’m not buying that excuse!

    • So Alex, I totally agree.

      But access to the database is often driven by business needs and requirements verus technical proficiency – which is why if I were a DBA I would have a test you must pass before I gave you a login to one of my databases!

      Then I would probably be fired for being a troublemaker.

    • JeffS

      Only cause you wanted one. I don’t trust my fingers either though, so I’m screwed either way πŸ™‚

Write A Comment