there are couple of places that needsd tuining in the query level    1 . stats for the table is must for good performance  2.  when user is joining two tables make sure there are using the large table in the last and the first table is smaller  3. you can also use HINTS to imporve query performance. 4. hive table's file format is big a factor  5. choosing when to use paritioning vs bucketing.    6.allocate good memory to hiveserver2 and metastore  7.heapsize  8 .load balancer on the host    https://www.cloudera.com/documentation/enterprise/5-9-x/topics/admin_cm_ha_hosts.html#concept_qkr_bfd_pr ... View more

I've set-up hive.prewarm.enabled=true   and it did not improve the slow latency to start and initialize executors. It still takes about 15seconds to initialize things. Any idea ? ... View more

On the setting changes, stats, as stated will help with counts as that info is precalculates and stored in the metadata. The CBO and stats also help a lot with joins. It is possible that the OS cache is more to do with the improvement if this was a subsequent run with little activity. You could look at Hive on Spark for better consistent performance. Set hive.execution.engine = spark; On the times, the big impact between job submission and start is the the scheduler. That is a deep topic. It is best if you read up on them and review your settings and ask any specific questions that come up, preferably in a new topic. The other factor, not captured on the job stats, is the time it takes to return the results to the client. This will vary depending on the client and there isn't much to do about it. In general small result sets can be handle by the hive CLI. You can increase the client heap if needed. Otherwise use HS2 connections like beeline or HUE. ... View more

Thanks for your feedback. ... View more