Feature: Option to cache table/col/const info

[Salamander]

When working over a slow connection, using the query builder, or even the auto-complete features can be very slow, enough to render them unusable. This is over a VPN, I imagine it would be worse over a modem.

Feature request: a button that would cache in memory the necessary info about the schema to use the auto-complete and query builder without queries to the DB.

Functional suggestions:
Once pressed, this button (in a background thread) would load all table/column/fk constraint info in the "My Objects" list of tables and views, and the button would change color when the info was cached locally. After that point, the text auto-complete and the query builder would use the cached info.

Pressing the button again dumps the memory cache and resets the button.

This could have a huge speedup - doing joins on the the user_const_cols views is horribly slow, but querying the table is very fast.

 

[Salamander]

Having the info locally would (in the far future) allow you to do a lot of tricks, including smart guessing when typing joins, the BFS join enhancement logged earlier, etc.

Smart guessing:

you type
select * from my_users u, my_user_role ur
where u.user_id =

and it fills in a auto-complete of "ur.user_id" because it knows locally about the fk constraint on the u.user_id column.

 

[Marco Kalter]

How fast (or slow) is your network?

 

[Christo Kutrovsky]

I have the same observations.

It's not that the network is slow, it just PL/SQL developer which is using "select *" when it actually needs only a few columns from the resulting very wide query (with lot's of data to be transfered)

The object browser is also very slow. You need to review all your queries to the database, and optimise them for amount of data transfered.

Like:
- expanding index columns - the entire table info is fetched, and never used, and is re-fetched for each index
- table column statistics are selected, and never used...
- The "invalid objects" search is featching *all* objects from the database, and doing the "validation" check in PL/SQL developer, not in the query
- and others...

I will report all my findings, if you are really into fixing those.

This is my #1 most requested feature, as it is slowing me down the most.

I even use the "Browser extender" plugin with my own, narrow (less columns) query for right clicks on indexes, instead of expanding them as it is too slow.

My network speed is ~20 Kb/sec. For tables with lots of columns, i have to wait 10 seconds for each index columns to expand.