While Hive is perfect for analytical queries and is amazing for highly parallel workloads with lots of parallel queries, it is not as fast for small queries as traditional databases yet. You will not get queries faster than 2-3 seconds in total even under perfect circumstances. This is due to the architecture. Rule of thumb: - If Tez has to create a new session ( application master ), i.e. a query on a cold system, you can expect 10-15s pre time. You can fix this by pre-creating sessions. However that takes a bit of the cluster even if you don't need it. - If Tez has to create task containers you can expect 2-3s extra. Tez can reuse containers and there is also prewarm to precreate containers but it's tuning depends a lot on your usecase. If you don't know what you are doing you can make it worse - In general the Hiveserver has a bit of overhead ( around 1s ) for plan compilation communication with the metastore etc. So yes at the moment you will not get faster than 2-3s, realistically 4-5s. If you need sub second responses look at Phoenix for example. However things will soon get better for these short queries: - LLAP is already available as a tec preview ( long running processes that have an ORC data cache and remove the startup needs. - Hive will have an Hbase backed metastore which should speed up the hiveserver2 and more. In short look out for this space.
... View more
