1) Currently, ADStudio executes the query SHOW TABLE EXTENDED in <db> LIKE `*` to appropriately group objects into either tables or views. However, Hive server seems to be very slow in executing this query given the data in the TEST environment.
2) The patch will add a new property in Server Properties > Advanced tab. The property will be called "Group Table and View Objects" and will have following options:
[Default] (existing behavior)
SHOW TABLE EXTENDED (Single query to group table and view objects)
SHOW TABLE (Merge table and view objects)
SHOW TABLE and DESCRIBE (Separate query per object)
"SHOW TABLE (Merge table and view objects)". This is the one we discussed on the phone. When this option is checked, ADStudio will not execute the SHOW TABLE EXTENDED query. Instead, it will execute "SHOW TABLE in <db>". This query executes very fast in the TEST environment. The downside w/ this query is that ADStudio cannot tell whether the objects being returned are tables or views. Hence, ADStudio will display the same list of objects in both the "Tables" section and the "Views" section.
"SHOW TABLE and DESCRIBE (Separate query per object)". This option will execute a SHOW TABLE query and then for every returned object, issue a DESCRIBE FORMATTED query to determine whether the object is a table or view object. Depending upon the total # of tables and views in the TEST environment, this option might or might not be a good fit. It is merely provided as another variant to try, if desired.
Note: SHOW TABLE does cause issues with QB, ER Modeler, Schema Generator and Compare because there is no longer a separation of Tables and Views. They are grouped together. This is a known and accepted risk in order to improve performance. Might not be good for all Hive customers.