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.
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:
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.
20 Comments
Hello. works fine with tables, but can it be that join on a statement on views are generally not displayed?
Dumb query, but this works, joining a view to a table
select first_name,region_id
from emp_details_view a,
regions b
where a.region_name = b.region_name;
Hey Jezz,
Currently I’m using version 22.2, in builder I could not see joins between tables even though I unchecked skip loading meta data box in preference. Any tip on this?
Jezz here,
What exactly are you doing? If I write a query in the worksheet and toggle to Query Builder, I see the Join(s)
If you can share more details, I can give you a better answer.
Jeff
Jeff – I can’t get query builder to show me joins on views. I’m struggling joining sysman.mgmt$ job views and I was trying to user QB to help me out. Can’t do views?
Sure you can do views, what two views are you trying to join?
Let me ctrl drag the table on.
I just updated to a new version of SQL developer and I don’t see the query builder at all. Is there something that I may have done to hide it and how do you unhide it to show the option for worksheet and query builder?
Thank you for your help in advance,
Leeza
do you have a sql worksheet open AND an oracle connection open for it?
Hi Jeff,
the feature is wonderful when working with Data Vault Tables – yes we do have costraints defined – as I have to write quite a few joins otherwise. Finding and dragging the correct tables can be time consuming. But of course if it’s slow this is the better solution… .
Cheers,
James
you can also drag and drop the tables all at once to the worksheet – and we’ll add the joins for you there too
Yes, I opened a connection and the worksheet shows where I can type in my SQL but the query builder doesn’t show up at all that I can use to switch back and forth from worksheet to query builder. Can you please help me figure out what is going on? I have version 1.5.5.
oh my…version 1.5.5 is like 12 years old
time to upgrade!
Hey Jeff, one thing I have noticed in 11g at least and I’m not sure if it has been fixed or not is that when I jump to Query Builder after having created a query that my tables end up being spread out up and down with considerable distance. Most of our tables have 100 columns or more so the second table you have to scroll way down to visually see it. If you want to move it to the top there is no easy way to move that table up unless you drag and drop it several times or unless you pull it towards the top of the sheet and just watch it slowly move towards the top. It might also be nice to be able to resize the view so that it is easier to get a visual representation when you are working with queries of a significant size hitting off of a significant number of tables.
that wouldn’t be a db version thing – that’s all SQL Developer code (via the 3rd party library we’re using)
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
Thanks for sharing that Pawel, and also thanks for being so patient!
Nice feature.
Thanks.
Thanks Jeff, that answered my question regarding the missing auto joins in v18.3
No problem – I’m sure several others were wondering as well. I hope you find this solution…acceptable.