Query Builder: Where Are My Joins?

One of the improvements for version 18.3 was a much improved, performant query builder.

Of course, not all things are free. Or in fact, nothing is free.

So who paid for these performance gains? We disabled one of its primary features.

But first, let’s take a step a back and explain the scenario.

How it Was, Pre 18.3

The query builder would allow you to reverse engineer a query in a worksheet to a visual representation. And it would allow you to build queries from scratch, by dragging and dropping them into the query builder design area.

I advocated that users do a combination of both, especially if they were new to SQL in general. The query builder is also useful for helping build a ‘picture’ of your queries.

The Problem
It was tremendously slow. It could take 30 or more seconds to visually render an existing query. And dragging and dropping new tables into a query could take 10 seconds, each time you did it. So slow, that I didn’t recommend to people that they should use it anymore.

Why was it slow?

Well, the folks that build the solution (it’s a 3rd party library that we have licensed) had some pretty gnarly queries used that do look-ups on the table to find foreign keys and ‘related’ tables. This would do two things. It would ‘draw the pretty lines for you’, and it would give you a list of related objects for each table.

Like this –

Nice, right?

The Solution

While that was a nice feature, it just cost too much. It was slow to the point of users wouldn’t give it a 2nd try, and I wouldn’t reccommend they even try it in the first place.

The easiest solution was to ‘nuke’ those ‘bad fk lookup queries.’

So in 18.3, we don’t do that, and the Query Builder renders in a second or less for more queries. That’s crazy-good. It just also means, that now you need to draw or code the joins yourself.

Here’s the option to disable if you want the magic, auto joins back.

You can also just drag and drop the queries to the worksheet FIRST, say YES to the JOINS, then toggle to the Query Builder.

Here, let me show you how I mean:

I really like this.

Can we do better?

We could try to refactor the 3rd party vendor’s bad SQL, but every time we do that, upgrades get much trickier. I’d like to have our cake and eat it too, but I also need to make game-time decisions and try to make for the best user experience. I think we’ve found a good compromise here…especially if my assumption that many folks will use the Query Builder for existing queries.

If I’m wrong, here’s your chance to tell me.

The good news is, we have a new release every 3 months now, so tweaks, fixes, and improvements are never that far away.

Related Posts

Comments 5

Great improvement. I wanted to tell colleges, who have been working with MS Access, to switch to SQL Developer using the Query Builder, but when I realized how slow it is, I was looking for another approach. Now I can tell them. It will be a great tool to make the transision from Access to SQL Developer as main tool for viewing data and making new datamarts.
cheers