Editor Window Hangs when edit SQL script

Toad for Oracle
1

I assume this is an option setting that I have not set correctly… But I can’t find it…

I have a large SQL file (~35,000 lines) that basically has my “SQL History” in it so I can find stuff I have done before and copy it for new stuff… I always open this file on TOAD startup.

I have one machine with 12.6.0.53 installed and it works just fine (and has for many years).

I have a new machine with 12.10.0.30 installed and it behaves differently. Opening the file is fine. The first execution of any query in the file takes over a minute (I ran the SQL Tracker and the only SQL is the actual select I am running and the query itself only takes msecs to return). After that all queries in the file will run in the expected amount of time until you make ANY edits to the file. Then the delay occurs again. I can add a space on an empty line and it hangs.

It is as if it is parsing the entire file, but I can’t figure out how to stop it.

Anybody got any suggestions?

2

It sounds like it’s waiting on a parse. When you make edits any existing parser results are invalidated. Parsing occurs in a background thread, but if you try to do something requiring a parse like execute then Toad will wait for parser data. 12.6 would attempt to parse only that text affected by an edit. This worked well enough, but was not without bugs and there was quite a bit of code fired on each keystroke to accommodate. 12.10 does a full parse for guaranteed accuracy and faster text editing, but quick edit/execute cycles are going to be slower than before, especially with larger text.

Michael

3

I just had a thought just after sending that might work, but it’s too late for 12.11. We should be able to do a shallow parse and get just the statement at caret when parser data is invalid to eliminate the full parse wait time. That would be a hybrid of 12.6/12.10 behavior that should provide the best of both methods. I will log to look at following 12.11 release.

Michael

4

Thanks for explaining. That all makes sense.

5

Hi Michael,

I just downloaded and am starting to use v 13.

It seems to have this same issue. Any idea if your idea above made it into any plans for a future release?

I’m heading back to 12.6 again.

6

I was told it was a bug, and will be fixed in a patch release soon.

Michael LaPointe

TD Bank AMCB | USTS Information Management

IT Solutions Design Specialist, Data Intergration

6 Atlantis Way

Lewiston, Me.

M: 207-330-5127

Email: michael.lapointe@td.com

From: gary.lynch [mailto:bounce-garylynch@toadworld.com]

Sent: Tuesday, May 15, 2018 8:47 AM

To: toadoracle@toadworld.com

Subject: RE: [Toad for Oracle - Discussion Forum] Editor Window Hangs when edit SQL script

RE: Editor Window Hangs when edit SQL script

Reply by gary.lynch

Hi Michael,

I just downloaded and am starting to use v 13.

It seems to have this same issue. Any idea if your idea above made it into any plans for a future release?

I’m heading back to 12.6 again.

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle Forum notifications altogether.

Toad for Oracle - Discussion Forum

Flag
this post as spam/abuse.

This message and any attachments may contain confidential or privileged

information and are intended only for the use of the intended recipients

of this message. If you are not the intended recipient of this message,

please notify the sender by return email, and delete this and all copies

of this message and any attachments from your system. Any unauthorized

disclosure, use, distribution, or reproduction of this message or any attachments is prohibited and may be unlawful.

7

It seems to have this same issue. Any idea if your idea above made it into any plans for a future release?

My apologies, Gary. I've not invested the time into this. It's not a trivial change, but it is a necessary one. I've elevated the priority on the logged issue so hopefully it will be more visible to me, now.

I was told it was a bug

I would call it a "chore" in desperate need of attention! The code does function as intended, it just performs poorly with large packages and needs optimization.

Michael

8

Does the issue have an issue ID (TOR number)?

That way I could look at release notes for each release to see if it has been addressed without bothering you.

This is the section in release notes I am referring to:

Issue ID Link (If from Forums) Resolved Issue

TOR-435 Session browser resets filters, they don’t stay set.

9

It does (TOR-1690) but our bug tracking system is internal only. I don’t know why we put those in the release notes.

Michael

10

You can disable the “Check rules as you type” option on the Code Analysis|General page in Options and see if that makes a difference. I have an 18,000+ line package and with that option enabled I’m looking at ~42 secs for Toad to fully dissect it. With the CA rules as you type disabled it’s down to 9 secs. Still not fast enough, but perhaps something you can do in the meantime.

11

With Toad 12.12.0.39 I played with a 35,000 line file with on each line the following text: select x, y, z from mytab, histab;

Editing goes fluently with “Check rules as you type” switched off.

When switched on then editing still goes fluently unless you wait for a second or five, then Toad doesn’t listen anymore for a few seconds, may even show a spinning wait wheel and/or freeze window, then comes back to life and shows Code Analysis results and catches up with your pending keystrokes, although if you type ahead many keystrokes (say 10+) then the last ones can get lost. In any case Code Analysis results (squiggles) show up no later than 15 seconds.

When I double the content to 70,000 lines then the 15 seconds goes up to 55.

Gary, would it be possible to repeat this test w/ 35,000 lines your side and compare results?

Thanks,
Andre (parser team)

12

The first step is complete for next beta (13.1.0.21) which should be available Monday. For normal (F9) execution statement detection should be much faster. I have a sample text of 20k select statements and after text modification pre-execute processing takes ~1 second. It’s all done in the background so if you made an edit and left it alone for a second it would be instant. Similarly a 20K CREATE PROCEDURE sample takes ~3 seconds to process. The time taken to process is affected by how many statements are upstream from the one you are executing. If you are very near the beginning the processing time is almost nothing regardless of how many statements exist.

Execute Snippet (Ctrl+Enter/Shift+F9) and execute selection still require additional work that I hope to complete in the upcoming days.

Michael