Query Manager

Query Managers serve a dual purpose: they act as both a database administration tool for managing queries submitted to a database management system (DBMS) and an end-user tool for querying the contents of data stored in a database.

Why is a Query Manager Important?

A query manager is a user interface for data professionals such as database administrators, data engineers, developers, and others to write, store and run queries. Without query managers, database access would be limited to application programs. Database providers and third parties provide query management tools to enhance data retrieval, manipulation, and optimization. These tools provide features like performance monitoring, cross-platform support, and additional functionalities.

The query management function of a database system manages requests for data retrieval, modification, and optimization. Visibility into query preparation, execution, and results caching allows administrators to better tune performance.

End User Query Managers

All relational database systems provide query tools. On-premises tools such as Actian Director allow users to run queries against Actian Vector database instances. Open-source tools such as DBeaver can be used to manage queries across many different databases. Cloud-based solutions such as the Actian Data Platform provide a built-in query and reporting tool. These tools offer the ability to save, recall, edit, and resubmit previous queries.

Many Business Intelligence (BI) solutions like Microsoft Power BI and Tableau provide query management tools, including highly intuitive visual drag-and-drop query builders.

Query Management for Database Administration

In a database administration context, query managers can be used for:

• Workload management: Control how incoming queries are queued and distributed, even prioritizing queries based on importance, resource requirements, and user roles.
• Caching and result sets: Set the parameters and requirements to store frequently accessed query results in memory to improve query performance
• Parallelism and memory use: Optimize memory to work on multiple threads to execute different parts of a query simultaneously.
• Query performance tuning: Analyze query execution statistics to identify bottlenecks and optimize queries to your organization’s needs.

Accelerating SQL Queries

Query management for admins is about optimizing performance to get results faster and more efficiently using resources. Below are some ways to achieve these goals:

• Most relational database systems maintain histograms of the data stored in a table. DROP STATISTICS and CREATE STATISTICS are helpful to run afterwards as these have a significant refresh of table contents. This aids the query optimizer in creating the most efficient query execution plans.
• Analytic database systems depend highly on indexes to efficiently access individual or groups of records. Columnar databases can bypass primary indexes as long as the table is sorted by its primary key.
• Queries of very large tables that must be scanned in full will run much faster by parallelizing their execution and partitioning or sharding the table across multiple devices.
• Workload management capabilities allow the administrator to manage the number of queries running concurrently against a database. By controlling the number of active queries using several queues, the database instance is harder to overload during periods of high demand.
• Read the EXPLAIN plan in the query profile report to understand how a query will be processed. This is a great way to see where an additional index can help to accelerate access to critical data.
• Query result caching is a relatively new capability that creates an in-memory copy of a result set. The database instance inspects the syntax of incoming queries to see if it matches any of the cached queries. If the underlying data is unchanged, the new query can bypass formal parsing, execution planning, and data retrieval and simply return the existing result set. This dramatically reduces execution time and is transparent to applications and interactive users.

Actian Query Management

The Actian Data Platform is a high-performance data warehouse with built-in data integration and BI for real-time analytic workloads. Performance features include vectorized parallel queries and columnar storage for high-speed data processing. With Actian’s high concurrency capabilities, organizations can support a multitude of simultaneous queries, transactions, and analytical tasks without sacrificing performance.