Toad World® Forums

Lag before results are displayed in the editor's data grid


#21

And earlier you said Toad Script Runner is also showing lag, but F5 is not? Toad Script Runner and F5 both use the same script engine to execute although there might be some diffs in how the engine is configured in TSR, but this is out of my area of knowledge.


#22

Yes, F5 in TSR has a lag. But F5 in Toad itself is fast.

I just checked again.


#23

Morning All,

(Well, it is here!)

I'm wondering about this problem. Does turning on spooling to the screen
show where the times are being lost? If you see the query returning
super quickly, then it's "obviously" what is happening between the
execution and Toad formatting the results into the grid.

Another thing, what is your fetch array size defined as? If it's low,
then Toad will have to make many network traversals to pull back the
full results set. I think the default is 50, but I might be wrong, I
configure mine to 500 always.

You'll see when you pull back a big result set, on the status bar it
will say something like '50 rows retrieved, more...' (or similar, I'm at
home, Saturday, and not near a database - for now!)

Do you have any "weird" data types? XMLTYPE, LONG, RAW, LONG RAW, BLOB,
CLOB or similar that might need some fomr of processing before display?

How about dates/timestamps? Do they get implicitly converted or
explicitly with TO_CHAR() - I'm just wond3ring if Toad has to process
the returned data types from Oracle and convert them to text to display
in the grid.

You could try also tracing (10046 event) the code and seeing in the
trace file where the time is actually being spent. But that would
require running with F5 to get the various alter session statements as
well as your code, and I think I read earlier in the thread that F5 is ok?

I assume that "editor->execute script in SQL*Plus" returns quickly?

Just a few thoughts, ignore at your leisure. :slight_smile:


#24

Hey,

Thanks for replying. Sorry for not getting back to you earlier.

When I "spool SQL to screen", I see an output like this.

----------------------------------
-- Session: SYSTEM@<my_db_name>
-- Timestamp: 08:47:23.819
select user from dual;

My default fetch size is 500 rows. That is the default value for every installation I believe.

I understand your question about implicit/ explicit conversion and the CLOB datatypes. These could be a problem. But, in my case, a "select user from dual;" also takes time before being displayed in the data grid.


#25

Hi Thomas,

Hmmm, it appears, sadly, that the spooling to the screen doesn't actually help. You only get a timestamp at the beginning, that's a shame, as it would be nice to get one at the end as well to compare with. I shall have to find an old lamp somewhere and give it a rub.....

Maybe, turn it on again, then type the select user from dual query into the editor, the F9 and F9 again very quickly?

Does it make any difference if you:

  • login as a different, non system, user? (I doubt it, but .....)
  • select dummy from dual, rather than USER?
  • select something from another table of your own, WHERE ROWNUM < 2 (or similar).

Just thinking aloud, it sounds a little strange.

Cheers,
Norm. [TeamT]


#26

Newest version of Toad has an "elapsed time" (really, execution time) option in spool sql . Thomas's delay does seem like something going on with the grid but I don't know what that something would be.

image


#27

Hi Norm, jdorlon

Here is the spool output of 3 queries I executed using "Ctrl + Enter" as fast as I could. If you see the times, there is a 12 second gap. That is how long it takes for the "Statement Processing..." popup box to disappear and the data grid to show.

----------------------------------
-- Session: STHOMAS@<db_name>
-- Timestamp: 19:56:08.014
select * from dual;

----------------------------------
-- Session: STHOMAS@<db_name>
-- Timestamp: 19:56:20.218
select sysdate from dual;

----------------------------------
-- Session: STHOMAS@<db_name>
-- Timestamp: 19:56:32.451
select 'sendhelp' from dual;

I ran these queries on a different database this time. I logged in as myself and not system.

Your question regarding if I see a difference with a different user, different table and different query - unfortunately I don't.

If it helps, for my last query, just below the data grid, it shows 34 msecs. This is to the left of "Row 1 of 1 Total Rows".

If I restart Toad, this issue shows up in about 5 minutes.

Thanks for the help again!


#28

Execute script in SQL*Plus is very quick.


#29

Hi John,

Newest version of Toad has an "elapsed time" (really, execution time)
option in spool sql . Thomas's delay does seem like something going on
with the grid but I don't know what that something would be.

I obviously need an upgrade!

I'm wondering, as I do, if this is a 32 bit Toad? and 32 bit Windows and
we are suffering from the old Windows Resources problem again? I'm not
sure this is still a problem on Windows 64 bit though, I can't get mine
to stay up long enough to run out!

Have we tried temporarily renaming the toad user files folder, just in
case? Maybe there's something in there that affects grids?

I'm afraid I can't think of anything else that might be responsible for
this problem.

@Thomas: Does this problem only affect you and/or your computer? Are you
able to use another computer for a while (longer that the recently
reported 5 minutes before it happens again) to see if it affects just
you or anyone?

I'm clutching at straws here though, I admit. But I hate an unsolvable
problem!

Are you on 32 bit Windows or 64 bit?


#30

Hi Norm,

This only affects me and my work desktop. I have a work laptop and it works fine there. I have similar Toad settings on both installations.

I have a 64 bit machine. If it helps, I can copy and paste the output of Help > Support Bundle so that you can see any other setup information that might prove useful.

Thanks again!


#31

Evening Thomas,

This only affects me and my work desktop. I have a work laptop and it > works fine there. I have similar Toad settings on both installations.
Interesting, very interesting. And yet, puzzling too.

I have a 64 bit machine. If it helps, I can copy and paste the output of
Help > Support Bundle so that you can see any other setup information
that might prove useful.
Well, it wouldn't help me I'm afraid, I don;t have access to the Toad
code, John on the other hand does. I'm just a DBA who tries to help out
on the Toad Forums. (Fora? Forii? Who knows!)

I have to admit though, I'm truly perplexed with this problem. 64 bit
Windows should have enough resources - so I'm pretty much happy to rule
that out.

It's only on this one computer, and not on others. It affects you no
matter which account on the database you connect to. It runs fine with
F5 (run as script) but not with F9 (Execute statement).

It's a bit drastic, but maybe there's something lurking in your user
files area? I don;t know what to be honest, but how about:

  • Close Toad.
  • Find your user files (c:\users\your_name\appdata\quest software\Toad
    for Oracle\13\user files (I think, I'm at home, well away from
    databases!) rename the user files directory to something else.
  • Start Toad - Your connections etc will be gone, temporarily.
  • Use it and abuse it as normal, after 5 minutes does the problem appear
    again?
  • Close Toad.
    Delete the new User Files and rename the old one back.
  • Restart Toad.

You should be back to where you were before messing about! :wink:

If the problem doesn't happen, there's something nasty in your user
files. I've had the odd BSOD recently which corrupted by user files area
every time it happened. Eventually, I found a couple of files in there
which were massive, and full of garbage, just lots and lots of the same
character (something like CHR(1) if I remember) and Toad was not happy
at all.

I now have a new PC and everything "just works" - for now, it is Windows
after all!

The words "clutching at straws" spring to mind here though.

Thanks again!
I'm not sure I've been of any use whatsoever!

Good luck.


#32

When the lag occurs, is your CPU busy? (By busy. I mean 50% if you have 2 cores, 25% if you have 4, etc).


#33

Hi,

I've checked my CPU usage when this issue occurs and It usually stays between 0 to 6%.

I have ~24GB of ram and my system's memory usage is about 9 to 12GB.


#34

Hi,

I renamed the AppData/"Toad for Oracle" folder to "Toad for Oracle.bak" and started Toad. I also renamed the "User File" to .bak.

The problem reappears in ~10 minutes :frowning:

You mentioned that my connection and custom setting would be lost because of the rename. That didn't happen. My connections were there and only my passwords weren't "remembered" by default. My Auto Replace shortcuts were also there.

Did I rename the wrong folder?

This is the foler I renamed by appending .bak -> C:\Users<my OS username>\AppData\Roaming\Quest Software\Toad for Oracle


#35

I think what happened is that when you restarted Toad after renaming that folder, Toad re-imported your settings from a previous version.

To restore options to "like new", go to Utilities -> Copy User Settings. Check "Reset to a clean set of user files from the base installation".

Toad will rename your "12.12" folder to "12.12_bak" and then create a new "12.12" folder.

The problem may still come back in 10 minutes, but it's worth a try.

After that you can revert to old settings if you want be restoring that folder to its old name. Or if it works, we can copy over important settings like connections, etc.