Increase Speed when multiline editing in SQL file

[zitot]

Multiline editing delay — 15 seconds just to rename a table alias

I ran into a slowdown that surprised me — maybe this is normal, but it felt odd.

Here’s exactly what I did:

Selected ~32 lines up to the period (.), intentionally skipping the table name

Pasted the lines below my existing SELECT for use in the next CTE

Used Alt + Left Click + Drag to multi-select all the periods

Started typing the new table alias

Editor paused for about 15 seconds before the change finished applying

During that time, I thought it had frozen, but I noticed the line:column indicator moving rapidly

Here’s a simplified example of what I was editing:

SELECT
    .A,
    .B,
    .C,
    .D,
    ...

Then I typed the new alias, like xyztablename, expecting:

SELECT
    xyztablename.A,
    xyztablename.B,
    xyztablename.C,
    xyztablename.D,
    ...

The length of time scales with the length of the tablename you type. For a 10 char table name, on 32 lines, that was 14 seconds. If you, uhh, had a longer table name, you could be waiting a lot longer.

Specs:

CPU: i5-7500

~900 line SQL file

It’s not huge, but I didn’t expect a basic multi-cursor edit to take that long.

Anyone else experience this? Is it due to symbol parsing or live linting? And is there a way to delay or disable those temporarily (during just multiline edits) to make edits like this smoother?

 

[Marco Kalter]

I cannot immediately reproduce a delay using this scenario. Response is instantaneous when I type.

To obtain some more diagnostic information, can you modify the PL/SQL Developer shortcut and add the DebugSQL parameter? For example:

"C:\Program Files\PLSQL Developer 16\plsqldev.exe" DebugSQL

Reproduce the problem and send me the debug.txt file that is generated in the %APPDATA%\PLSQL Developer 16 directory (e.g. C:\Users\\AppData\Roaming\PLSQL Developer 16).

 

[zitot]

Hi Marco,

At this time IT has not yet added the debug parameter, but I can no longer reproduce this issue to the same extent. At worst, a second of slowdown which is acceptable.
I've disabled code assistant as i was suspecting it to be the cause but to be honest it didn't make much difference. The editor is a little laggy either way, so maybe it was the VPN having an issue last last week.

 

[D.Wenzel]

@Zitot - we made a "private" version for the canames.sql
the ALL_* data-dictionarty views ar a slow downer in our dev-environment

We..
* have many, many synonms but
* can use DBA_* data-dictionarty views but
* must also downgrade the optimizer
* and also a NO_PARALLEL hint to avoid unwanted parallel sql made by the db

D.

/*
  The name + type results of these queries will be used by the Code Assistant
  if the "Describe Context" option is enabled. After typing 3 or more characters
  the Code Assistant will show a list of matching names.
  Separate multiple queries with semi-colons and use the :schema bind variable
  to restrict names to the currently connected user.
  In case of an error the query results will be omitted. No error message will
  be displayed.
  Place this file in the PL/SQL Developer installation directory for all users,
  or in the "%APPDATA%\PLSQL Developer" directory for a specific user.
*/
select /*+ optimizer_features_enable('9.2.0') no_parallel*/ object_name, object_type
  from sys.dba_objects o
 where o.owner = :schema
   and o.object_type in ('TABLE', 'VIEW', 'PACKAGE','TYPE', 'PROCEDURE', 'FUNCTION', 'SEQUENCE')
;
select /*+ optimizer_features_enable('9.2.0') no_parallel*/ object_name, object_type
  from (select  connect_by_root(synonym_name) object_name, a.object_type
          from sys.dba_synonyms s
           CROSS JOIN LATERAL (select o.object_type
                  from sys.dba_objects o
                 where 1 = 1
                   and s.table_owner = o.owner
                   and s.table_name = o.object_name
                   and o.object_type in ('TABLE',
                                         'VIEW',
                                         'PACKAGE',
                                         'TYPE',
                                         'PROCEDURE',
                                         'FUNCTION',
                                         'SEQUENCE')  ) a
         where 1 = 1
           and connect_by_isleaf = 1
           and connect_by_iscycle = 0
         start with s.owner in ('PUBLIC', USER)
        connect by nocycle s.owner = prior s.table_owner
               and s.synonym_name = prior s.table_name)
 where 1 = 1
   and object_type is not null
;
select /*+ optimizer_features_enable('9.2.0') no_parallel*/ db_link as object_name, 'DATABASE LINK' as object_type
  from sys.dba_db_links o
  where o.owner = :schema
     or o.owner = 'PUBLIC'
;