Data Analysis - ad-hoc queries (exploratory)

About

An ad-hoc query is a query created on the fly by an analyst in order to answer specific business questions.

It's also known as:

  • speed-of-thought analysis
  • Impromptu query

This queries are:

  • constructed to answer immediate and specific business questions.
  • not known in advance.
  • exploratory ad-hoc queries

Performance

The central difference between ad hoc queries and reporting queries is the limited degree of foreknowledge that is available in order to tune the database administrator for them

As this queries are not known in advance, the database administrator (DBA) cannot optimize the system specifically for this set of queries. Consequently, execution time for those queries can be very long.

For instance, it doesn’t make much sense to use materialized views — for those kinds of queries it’s better to just keep all the raw events, and to build databases which can scan over them very quickly. Modern column stores have become very good at that.

Poke SQL - Query Performance

Type

Olap

OLAP queries allow for the exploration and analysis of business data to discover new and meaningful relationships and trends.

While this class of queries is similar to the “Ad hoc Queries” class, it is distinguished by a scenario-based user session in which a sequence of queries is submitted. Such a sequence may include both complex and simple queries.

Data mining

joins and large aggregations that return large data result sets for possible extraction.

Documentation / Reference


Powered by ComboStrap