Toad World® Forums

UE behaviour with several single SQL statements in one page


#1

Hi,
this is a big one and probably one of the first things that I noticed (and people will notice) coming from other similar tools (Toad, old SQLNav, etc.).

I am writing down the following as a kind of use case that reflects what I am doing every day.

In my job I have to write and execute every day lots of single queries to diagnose problems in the payroll system, so I have to retrieve and check data from the db and reuse the information I found to write new queries.

I thought this was an easy test, but actually the current UE is making this task next to impossible.

I write down a simple query and execute it. The UE will spawn a new tab with the selected query without bringing it in foreground and in focus. The first sensation is that nothing happened if the query is fast, while everything will seem to hang if the query is slow. The fact that the results of my query are hidden is really disorienting.

Then I notice that there is a new tab, I switch to it and I find the results. Fine, now I want to improve the query and add a new condition. Guess what?
The query is read only in this new tab and I cannot modify it. So I have to go again to the old tab to change the query and re-execute it. That’s really bad usability.

Can you imagine what nightmare it can be when there are dozens of related queries in one file and I want to execute them manually one by one, reusing the data found in one query to feed another query?? How will I know which tab belongs to which query when I am looking for the results??

Anyway, I finally found my data and I want to write down a new query using some information coming from the previous query results. I select the desired field in the results pane and I look for a button to copy the value. Mmmmh, nothing in sight. Ah, maybe the standard cut icon (next to the printer in the edit toolbar) will do the job. Press it, switch tab, position the cursor and paste. Nothing happens…
Too bad, I go back and I try the universal copy+paste shortcut (that is CTRL+C and CTRL+V). Nothing happens again…
That’s really very bad usability.

Now I have two single SQL statements in my UE page, how do I execute one instead of all of them? I have to select every time the chosen query and press Shift+F9.
Ok, fine, but it would have been nice to have the autoselection of the current SQL statement as in TOAD, PL/SQL developer, SQL Developer, the open source TORA, etc. etc. (yes, I tried all of them…)

Good, I execute the query and I notice that I cannot use substitution variables. That’s another very bad thing (but I already pointed it out in another thread).

I start writing a query on the all objects table (remember I am testing this on a VERY big db) and suddenly the auto completion panel pops up. I don’t want to select an object type, at least I want it to show me the objects with the same starting letters as those I already wrote down. No way, press ESC to get rid of it.

So let me see where I was? Ah, yes, it was:
SELECT * FROM all_objects a WHERE a.

As soon as I write the dot, the autocompletion panel pops up again, but then it lags and after a few seconds it freezes the program. I see the dreaded label Not Responding at the top of the SQLNav window. I wait. Well, now I am forced to kill the program because after 15 minutes it was still not responding.

I hope to have given you a clue on several problems I encountered on a very short test session of Beta4. I am not going to test (yet) the fancy stuff, I think that we should get straight the easy things first.

Thanks for the attention,
Paolo


#2

Hey Paolo,
Thanks for this concise workflow…

We will go through this in detail today, and tackle the individual issues that you raise.
This is definitely the type of feedback we need to get the UE workflow right!!

One thing that I will say now, is that there is a button on the toolbar (in each tab window) to toggle between ‘read only’ and ‘editable’ (see attachment). This will allow you to edit the sql in the individual tabs.

Please stand by, for a more detailed response to your post!!

  • Jaime -
    read_only_button.jpeg

#3

Jaime,
I am wondering why anybody should need to have a read only panel for the query…
I don’t see any use in this kind of button. What was the need for it?

As for the usability aspect, less is better (as always).

This is something I’ve seen going downhill very fast with Toad (personal experience) and I would like you to avoid doing the same error. Have you ever seen how many icons does have the standard toolbar configuration of Toad??? It’s crazy!! How someone is supposed to find something there on an high res screen? and how much space is left to edit SQL on a low res screen?? and the search engine in the option panel?? there are so many config options that to find the right one you HAVE to use a SEARCH BOX… It’s a complete usability disaster!!!

Anyway SQLNav is a lot better in this regard, but if you have the possibility to remove some unnecessary or minor buttons from the GUI, I think you should take the chance and do it.

Let me know what do you think about it.

Thanks,
Paolo


#4

If you think back to the earlier beta, the result tabs were always editable.
We introduced the ‘read only’ aspect, recently, because we had feedback from one of our Oracle developers, that he found it confusing that the sql statements were editable, but if he edited them, the change was not propagated back to the original script.
He insisted that the results should not be editable. We compromised by adding the edit button.
I guess it goes to show that people have different use cases, and we (somehow) need to cater for them.
I totally agree with all your comments especially in relation to less is better and we don’t need more options. SQL Nav already has a lot of options settings!!

You have definitely given us food-for-thought Paolo.
Thank you!!

  • Jaime -

#5

Jaime,
I think that the feedback you got from that developer is correct.
He is very right saying that it’s confusing when you edit a query and when he closes that tab, the changes are not propagated back to the script.

The real point that should be discussed is not read only tab or not, but it is the need to spawn a new tab with the executed query and the query results that is completely different and separated from the “main” tab.
Avoiding this, will automatically solve the problems I described and the confusion of that developer will disappear (also no need to have a read only button).

I am happy that I can do something to help you.

Thanks,
Paolo


#6

Hi Paolo,

Thank you very much for your feedback. But let me explain a few things.

The problem with query results going to a new tab comes from your habit to add a semicolon after each SQL statement. In UE, such statements are considered (part of) a script. Since scripts can have any number of statements, the results of each query go to a separate tab. Once you stop adding semicolons, your results will be displayed on the same tab. Note that there’s nothing in the SQL Reference about semicolons and scripts, it’s SQL*Plus creator’s invention.

The next iteration of Beta will introduce a concept of ‘sub-tabs’ which will greatly reduce the number of tabs to choose from and will ease navigation among them.

BTW when you run a script containing queries, the left-side gutter displays a special icon in front of each successful query. You can display the results by simply double-clicking the icon. This bevavior closely resembles the behavior of the old SQL Editor.

We will remove the Read Only button from the toolbar.

Now to the second issue. Data can be copied from a single or multiple cells by using the Copy to Clipboard option from the right-click menu. We can add a button for this to the toolbar, and add the Ctrl+C shortcut.

Speaking of running a single statement from a script, this is done the same way as in the old SQL Editor - by pressing F8; there is also an Execute Step button on the toolbar. I’m surprised this has caused some difficulty. Perhaps you were too irritated by something else

Substitution variables do work. To enter a value, you have to press F2. We’ll think how to improve usability of that.

Code Completion will be greatly improved in the next beta. Both automatic Code Completion and automatic dot lookup can be disabled in Preferences (Code Editors > General). You can activate them any time by Ctrl+Space. I don’t know why dot lookup hangs on your database. Is it huge? How is the overall performance?

Speaking of the number of options on the toolbar, I don’t think we have too many of them. I’m afraid, some users will be demanding to put back to the toolbars the buttons they frequently use. Also, we haven’t introduced any new preferences with UE, and intend to remove some obsolete ones. So there’s no tendency to become more complex, cluttered and hard to manage.

Regards,
Roman

PS In the next Beta, the semicolon after a single SQL statement will be ignored.


#7

Hi Roman,
your assumption is wrong and I’ll explain you why.

When I started working on Oracle, I had the luck to use SQLNav 3, where the semicolons were not accepted as a part of a SQL statement and still now I have this habit of NOT putting semicolons at the end of my statements.
I didn’t notice the behaviour you describe about semicolons, so I will have to double check again all the steps I followed in my workflow, but it would be strange that I did the opposite of what I am used to do.

About the result copy functionality, I didn’t really try to right click on the results, so I didn’t know the existence of that functionality. I guess that the keyboard shortcut is mandatory for all the programs that want to adhere to the Microsoft Windows GUI guidelines. Moreover, there is no need to add a new button in the UE toolbar, since there is one already in the Edit toolbar at the top of the window.

To execute the statements I didn’t try the F8 shortcut, but I used Shift+F9, because it is the one that appears in the SQLNav menu. Aren’t they the same?
I have also seen the execute step button, but looks like it assumes that I want to execute a script step by step, so it will always spawn the results in a new tab.

In my eyes, the need to give an additional command to SQLNav to use the substitution variables is another usability problem.
I guess you are parsing the statement before running it against the db: why not popping up the substitution variable panel when you spot an empty variable in the statement, just before executing the query?

The database performance is usually good here, even if it’s quite a big one, so I may have found a specific case when the Code Completion gets stuck. I will try to test it better next time.

I understand that there will be lots of different user requests about completely different default buttons in the toolbars, but noone said that you have to REMOVE the toolbars. Just ship the software out of the box with a sensible default configuration that includes just a few of the most used buttons in a couple of toolbars and leave to the users the ability to easily customize their own toolbars, with their own “most used” functions. I think it’s a fair trade, between GUI simplicity and configurability.

In the end, I was not irritated, I cannot really feel like that after the nice present that you sent me.

Thanks for the attention,
Paolo


#8

Hi Paolo,

We had a meeting on Friday afternoon, to discuss your useability issues.
We agreed to raise CRs for most of your issues (some of your issues have already been resolved in internal builds), and we were inspired to raise several others, as a result of discussing your workflow(s).

Your issue: “I write down a simple query and execute it. The UE will spawn a new tab with the selected query without bringing it in foreground and in focus”
Solution: When a new tab is spawned, the focus will move to that tab, so you can view the results. If more than one tab is spawned, the focus will move to the first new tab.

Your issue: “How will I know which tab belongs to which query when I am looking for the results??”
Solution: With the new sub-tab system, this issue should be alleviated. The next beta should have the sub-tabs. Let’s see how it goes!?

Your issue: “I go back and I try the universal copy+paste shortcut (that is CTRL+C and CTRL+V). Nothing happens again… That’s really very bad usability”
Solution:- All standard short-cuts will be implemented.

  • We will also ensure that shortcuts from the previous editor(s), where practicable, will be mirrored.
  • It is also our intention to ensure that there is full keyboard navigation across the UE and the tab / sub-tab functionality.

Your issue: “Good, I execute the query and I notice that I cannot use substitution variables”
Solution: As per your suggestion, we will be launching the substitution variable window, when an empty variable is encountered in the statement (similar to the old functionality).

As I said, we have identified many other (small) useability issues while exploring this.
Let’s face it, the UE is still very definitely a work-in-progress.
It has changed quite a bit, since its introduction (because of user feedback) and I’m sure it will continue to change, until we get-it-right.
And with the excellent feedback we receive from out beta users, we will achieve this for the next GA release!!

Thanks for your help guys!!

  • Jaime -

#9

Hi Paolo,

I don’t understand how you managed to get results on a separate tab without using semicolons. Anyway, in the next beta, results of a single query will always be displayed on the split-screen, whether with semicolons or without.

F8 executes the statement at cursor, whereas to use Shift+F9, you have to first highlight the part of the text you want to run.

The Copy button on the main toolbar, like all the items in the Edit menu, works only when an editor is at focus. They never applied to the data grid. But Ctrl+C shortcut is supposed to work everywhere, no questions about that.

The good thing about the new substitution mechanism is that you don’t have to actually run the script in order to enter the values, just enable defines/substitutions at any time. In the next beta, execution of a script will automatically scan for defines/substitutions, will display the Parameters and/or Substitutions panels if necessary, and will warn the user that values need to be entered.

Finally, nobody is talking about removing the toolbars. I’m saying that UE toolbars are quite small now and we are not going to put every option on the toolbars, even if some users demand it. And there are easy ways to customize them.

Regards,
Roman