Sloow tab completion

  • Allan


    When I connect Henplus to a large Oracle(2000+ tables) database, the tab completion often use 10 minutes to finish. When I use auto-completion (ctrl-space) with Squirrel SQL it works fast as a lightning. I suspect it might be that Squirrel SQL builds a local cache of all the keywords (table names etc) for completion?

    Henplus is very fast in some instances, (for instance if I have just use tab-completion on the same word just previously) but often after a while it seems to have to re-fetch all keywords from the db?

    I'm thinking a solution might be to use a file in the .henplus folder for keywords, this way it could also remember keywords for tab completion different dbs between sessions.

    • Allan

      Sorry, I meant to write 200+ tables, not 2000+ tables in the last post.

    • Henner Zeller
      Henner Zeller

      The tab-completion fetches tables (and caches them) so that at stage
      case (1)
        select * from <tab>   [ fetches all table names ]
      tables are expanded. In addition to that, all columns of associated tables are fetched (and cached) in the where clause; in the next example all column name for table1 and table2 are fetched for the tab-completion
      case (2)
        selct * from table1, table2 where <tab>  [ fetches all columns for table1, table2 ]

      These cases ususally should be fast. However, they're done on demand, they're not pre-fetched when henplus is connecting. Prefetching on connect could indeed improve the speed (made a note on my todo-list ;-) )

      There could be case however that requires henplus to recursively fetch _all_ tables and _all_ columns if you do
      case (3)
      select <tab>            [ fetches all columns for all tables ]
      In this case, henplus does not know yet the table-names to select from, thus is forced to fetch all column-names from all potential tables (200tables and then 200 selects for the meta data of each of these tables). This indeed can be slow; potentially it is possible to fetch these all at once, I've to check this. Is this the case (3) that leads to sloooooow tab completion in your case ? That would be kindof expected the way henplus works currently. If it already is slow in case (1) or case (2), then meta-data fetching in your database seems to be slow.

      The reason why it sometimes seems to 'forget' the cache is a very stup^H^H^Himple cache-invalidation algorithm: whenever there is a seemingly DDL statement that says something like create..., drop... or alter..., henplus assumes some changes of the table meta data (without further parsing) and simply drops its whole cache. This is probably not the best to do; better would be some kind of dirty-flag so that the old cache can still be used.

      -henner (who just updated his todo-list to address these issues)

    • Allan

      Hey Henner, thanks for the fast reply!

      I seldom use tab completion for column names before the table name has been specified as mentioned in case(3) (even though I think it should be possible without being to slow). My situation is normally the first and second scenarios you described. I think meta-data fetching in my db might be slow, because doing a describe on a table also mostly takes 5-15 seconds (sometimes up to a minute). Good thing you told me about the cache-invalidation algorithm though. Now that I know how it works, I know how to avoid cache-deletion except when necessary.  :)   

      A bit on the side though, but I think some kind of storage mechanism for tab-completion keywords (or some kind of structured representation of the database without the data) would be a good feature. Perhaps you could create a tree-structure that tells Henplus which columns belong to which table (and other meta-data about table). As I mentioned in the first post, this could be stored (perhaps in the .henplus directory) so the user does not have to wait for Henplus to fetch this data on re-connect. I'm not sure whether it is possible to have several threads accessing the same connection, but if it is, then you could have a thread running in the background that manages this without disturbing the user too much.

      This would benefit all users, perhaps more so those who use Henplus to connect to a remote db through slow connections, such as those that have MySQL db on ISP server, but no access to install any software there.