How to speed up Schema Browser - Tables

The Schema Browser runs a lot of background queries to support all the features that we've added to it over the years. By default most/all of these are turned on. If you turn them off, Toad will run fewer queries and run faster. This post details those options.

To see what queries Toad is running, click Database -> Spool SQL -> Spool to Screen. You can use this option to see the effects of your changes that I mention below.

Object Tables. If you don't have any object tables, then you can filter them out. This will cause Toad to select from DBA/ALL_TABLES instead of DBA/ALL_ALL_TABLES. The former is usually, but not always faster. It may make more of a difference when looking at other schemas instead of your own.

Loading Encryption info when you select a table If you don't care which columns are encrypted, hide these columns. This will prevent a query each time you select a table.

Loading column info about every index on the table when you select a table If you can live without this info, hide this column. It will prevent as many queries as you have indexes on each table you select.

Loading Trigger information every time you select a table If you don't care about knowing (or if you already know) which columns are populated by sequence/trigger pairs, you can prevent a query by unchecking this column and unchecking an option in the main options window.

Loading constraint info when you click on the data tab We run some queries each time you click the Data tab to support the "Foreign Key Lookup" feature. You can turn that off here and prevent one or more queries here

That is all. I hope it was helpful. Toad should be noticeably faster in the Schema Browser (and describe windows) with all of these options unchecked.

Update: Ok, there is one more thing, not related to queries. If you notice that the data grid loads slowly on tables with hundreds of columns, it may be due to Options -> Schema Browser -> Data Tab -> "Include Column ID in grid column header". In Toad 17.0 and prior, this option, when checked, slowed things down noticeably on tables with hundreds of columns. That is fixed in 17.1.

1 Like