Performance Challenges In Ad Hoc Queries

The major challenges faced by analysts today are large and growing data sets, and legacy systems that worked well for predictable queries (operational reporting), but poorly for ad-hoc querying. This is not surprising given that historic spending has been directed towards optimizing traditional BI. As a consequence an entire industry now exists to provide “work-arounds”:

• Implementing query-aware data models
• Carefully constraining query workloads, query complexity, and query schedules
• Co-locating data partitions sympathetically with the “most important” joins
• Segregating ad-hoc querying into a separate environment

In the end these still remain “work-arounds” and “band-aids”, not real solutions addressing the spectrum of requirements for ad-hoc exploration that include unpredictable data access patterns and data sets that can be incredibly large.

Larger data sets are required when the analysis needs to be “wider and deeper”. This data must be examined at a more detailed level of granularity, with longer time series, much of it historical archived data as well as sub-transactional data. For example, sub-transactional data such as click-stream logs and RFID logs, have low information density, and therefore cannot justify the ROI of a major system investment. Additionally, it is a critical requirement that these large data sets be quickly loaded and unloaded from the analytics system to minimize non-productive usage of the enterprise’s IT infrastructure.