Ad hoc запросы руководства

Пересказ статьи Kathi Kellenberger. What is an ad hoc query?

Ad hoc запрос — это отдельный запрос, не включенный в хранимую процедуру и не параметризованный или подготовленный. В зависимости от установок сервера, SQL Server может параметризовать некоторые операторы, изначально написанные как ad hoc запросы. Ad hoc не означает динамический.

Вот простой пример ad hoc запроса в SQL Server:

SELECT LastName, FirstName

FROM Person.Person;

SQL Server параметризует этот простой запрос:

SELECT LastName, FirstName

FROM Person.Person

WHERE BusinessEntityID = 7;

При поиске по запросу «что такое ad hoc запрос» я обнаружила один ответ, говорящий, что ad hoc запросы — это запросы, построенные при комбинации ответов на веб-форме. На самом деле это один из способов создать динамический запрос, который может являться ad hoc запросом, а может таким и не быть. Если динамический запрос параметризован, он не является ad hoc запросом.

Вот пример динамического запроса, который параметризован (подготовлен), поэтому он не является ad hoc:

DECLARE @SQL NVARCHAR(MAX);

DECLARE @ID INT;

DECLARE @Param NVARCHAR(MAX);

SET @SQL =

N'SELECT LastName, FirstName

FROM Person.Person

WHERE BusinessEntityID = @ID';

SET @ID = 1;

SET @Param = N'@ID INT ';

EXEC sp_executesql @SQL, @Param, @ID = @ID;

Однако если параметров нет, запрос останется ad hoc. Вот пример ad hoc запроса, который так же является динамическим:

DECLARE @SQL NVARCHAR(MAX);

SET @SQL =

N'SELECT LastName, FirstName

FROM Person.Person;' 

EXEC sp_executesql @SQL;

Чем полезны ad hoc запросы?

Во многих случаях разработчик или DBA может один раз выполнить ad hoc запрос, и больше никогда не использовать его. С другой стороны, один и тот же запрос может выполняться тысячи раз за день из приложения, и при этом, возможно, оставаться ad hoc запросом. В зависимости от запроса может не иметь смысла включать его в хранимую процедуру или параметризовать его.

Ad hoc запросы не являются ни плохими, ни хорошими; как и все остальное, это зависит от того, как они используются. Сошлюсь на интересную статью от Phil Factor, посвященную устранению проблем с некоторыми неэффективными ad hoc операторами.

Что такое ad hoc запрос в базе данных?

Чтобы выяснить, рассматривает ли SQL Server запрос как ad hoc, вы можете проверить тип объекта в кэше плана. Вот запрос из книги «Внутри Microsoft SQL Server 2012» Kalen Delaney и др. Замечу, что вам может потребоваться добавить больше фильтров на [text], если он вернет слишком много строк, чтобы отыскать ваш запрос.

SELECT usecounts, cacheobjtype, objtype, [text]

FROM sys.dm_exec_cached_plans P

CROSS APPLY sys.dm_exec_sql_text (plan_handle)

WHERE cacheobjtype = 'Compiled Plan'

      AND [text] NOT LIKE '% dm_exec_cached_plans%';

Вы увидите тип объекта Adhoc для ad hoc запроса. Для параметризованных запросов вы также увидите строку с типом объекта Prepared. Вызовы хранимых процедур будут возвращать Proc, и имеется несколько других.

Что такое параметр Optimize for Ad Hoc Workload?

Представим себе систему, на которой каждый из большого числа запросов может выполняться только раз. Чтобы избежать ситуации, когда они занимают место в кэше планов, включите параметр Optimize for Ad Hoc Workload. Тогда при первом выполнении запроса в кэше сохраняется только заглушка плана. Если запрос выполняется снова, SQL Server сохранит весь план.

Заключение

Легче сказать, чем не является ad hoc запрос, чем он является. Ad hoc запросы не обязательно являются плохими вещами, они просто часть типичной рабочей нагрузки SQL Server. Если вы подозреваете, что некоторые ad hoc запросы вызывают проблемы, вы можете начать исследование при помощи запроса Kalen. Инструменты мониторинга также могут помочь вам идентифицировать непроизводительные запросы, которые нуждаются в настройке.

Someone recently asked me which queries are ad hoc in SQL Server. An ad hoc query is a single query not included in a stored procedure and not parameterized or prepared. Depending on the server settings, SQL Server can parameterize some statements initially written as ad hoc queries. Ad hoc doesn’t mean dynamic.

Here’s a simple ad hoc query example in SQL Server:

SQL Server will parameterize this simple query:

One answer I found when searching the question “what is an ad hoc query” said that ad hoc queries are built by combining answers from a web form. That’s actually one way to create a dynamic query, which may or may not be ad hoc. If a dynamic query is parameterized, it’s not an ad hoc query.

Here’s an example of a dynamic query that is parameterized (prepared), so it’s not ad hoc:

However, if there are no parameters, the query will remain ad hoc. Here is an example of an ad hoc query that also happens to be dynamic:

Why are ad hoc queries useful?

In many cases, a developer or DBA may run an ad hoc query once and then never run it again. On the other hand, the same query can run thousands of times a day from an application, yet it’s still may be an ad hoc query. Depending on the query, it may not make sense to include it in a stored procedure or parameterize it.

Ad hoc queries are neither bad nor good; like anything else, it all depends on how they are used. Here’s an interesting article from Phil Factor about troubleshooting some poorly performing ad hoc statements.

What is an ad hoc query in a database?

To find out if SQL Server treats the query as ad hoc, you can examine the object type in the plan cache. This query is from “Microsoft SQL Server 2012 Internals” by Kalen Delaney et al. Note that you may need to add more filters on [text] if it returns so many rows that you can’t find your query.

You’ll see the object type Adhoc for an ad hoc query. For parameterized queries, you’ll also see a row with the object type of Prepared. Stored procedure calls will return Proc, and there are a few others.

What is the Optimize for Ad Hoc Workload setting?

As you may imagine, a large number of queries could each run only once on a given system. To avoid having these take up space in the plan cache, enable the Optimize for Ad Hoc Workload setting. Then, the first time a query runs, only a stub of the plan is stored in the cache. If it runs again, then SQL Server will store the entire plan.

Conclusion

It’s easier to say what an ad hoc query isn’t than to say what it is. Ad hoc queries are not necessarily bad things, just part of a typical workload for SQL Server. If you suspect that some ad hoc queries are causing problems, you can begin investigating by using Kalen’s query. A monitoring tool can also help you identify poorly performing queries that need tuning.

When digging into data, sometimes information is needed on a single case basis. An ad hoc query is used to get specific information from a database when the need arises, as opposed to standard queries that are predefined and processed on a regular, recurring basis. 

What are ad hoc queries used for?

Ad hoc queries are single questions or requests for a database written in SQL or another query language by the user on-demand—typically when the user needs information outside of regular reporting or predefined queries. 

In a nutshell, an ad hoc query is any kind of «question» you can ask a data system off the top of your head. For example, if a business tracks daily average users and finds one day that it’s 3 percent lower than the previous day, a user would write a series of ad-hoc queries to try and identify why. You might check to see if specific regions or platforms (iOS/Android) were dramatically lower. A public holiday for one region, such as India, could explain it. Or an iOS issue that cut traffic to your site for iPhone users for half the day could explain it. The opposite of an ad hoc query is regular reporting via panels or dashboards that are numbers you’re constantly tracking and aware of.

Benefits of ad hoc queries

• Customization: Arguably the most obvious benefit of ad hoc queries and one of the primary reasons for using them is their customization. Ad hoc queries help a user dig deeper into data beyond the usual reports to find answers to more niche business questions. 
• Flexibility: Similar to customization, ad hoc queries enable flexibility for users to find answers to specific questions on a case-by-case basis depending on their objectives at the time. 
• More nimble decision-making: Ad hoc querying is done on-demand, which enables businesses to make data-driven decisions faster by quickly responding to dynamic changes. 

Cons of ad hoc queries

Like every technology, ad hoc queries have some drawbacks in addition to their benefits. Primarily, consideration needs to be taken for a potential increase in IT workload. If users with less advanced technical skills do not have the training or ability to run ad hoc queries on their own, it can be a time-consuming task for data scientists to take on. Additionally, depending on the complexity of the ad hoc query, they can come at a significant cost to a system’s processing speed and runtime memory.

Examples of ad hoc queries

Here are some examples of how ad hoc queries can be used in industry-specific cases: 

• Retail: Ad hoc inquiries could be used to determine why sales lagged on a specific day or week. 
• Sales: A business could use ad hoc queries to find data on sales from specific territories, or for specific items.
• Healthcare: A mental health facility could use ad hoc queries to find the number of people admitted to psychiatric hospitalizations on a specific day or week.

Using ad hoc queries with Scuba

Ad hoc queries help businesses quickly dig deeper into their data and get answers to specific questions on demand, as needed. Data analytics tools, like the ones offered by Scuba Analytics, are designed to amplify the benefits of ad hoc queries by making data analytics more accessible and easier to digest for all stakeholders in a business—and enabling fast ad hoc queries on the fly without code or SQL.

Learn more about Scuba’s no-code queries, real-time reporting, and customizable dashboards and templates here. 

Часто в повседневной работе продуктовой компании в отделе Business Intelligence (или его зародыше) могут встречаться ad hoc задачи по выгрузкам из БД. Если их много, то они могут занимать почти все рабочее время без перерывов на более инновационную и результативную работу. В результате страдает как бизнес, так и сотрудники компании.

В статье мы рассмотрим, как взять под контроль поток задач, используя идею CRM-системы, в которой есть функции по автоматизации как легких запросов (обновления задач по cron), так и достаточно тяжелых (обновление по запросу клиента) с возможностью управлять входящими параметрами.

Зачем это нужно

В отделе Business Intelligence OLX мы работаем с отделами монетизации, маркетинга, продукта, безопасности, поддержки пользователей в 3-х странах — Украина, Казахстан, Узбекистан. Иногда прилетают задачи и из других стран. Отдельно стоит выделить работу в B2B Unit, где аналитики нашего отдела создали инновационную CRM-систему для работы колл-центров разных стран. Но об этом чуть позже. А сейчас рассмотрим проблему простых, коротких задач, которые могут отнимать много времени, так называемые ad hoc задачи, которые аналитикам стоит обходить стороной или уметь их держать под контролем, чтобы в них целиком не погрязнуть.

Каждый день приходят разнообразные задачи на выгрузку данных, автоматизации отчетности, которые делятся на быстрые ad hoc задачи и трудоемкие, которые требуют больше времени для анализа.

Количество обращений может достигать десятки в день, и к каждой задаче необходим индивидуальный подход. Однако встречаются однотипные задачи, которые можно автоматизировать, дав возможность менеджерам не стоять в очереди для получения данных, а самим управлять операционным процессом.

Кроме того, автоматизация дала бы возможность прозрачно вести учет обращений от каждого менеджера, делать историю обращений и времени обработки запросов в БД. До этого под каждую задачу писались скрипты на Python, R, PHP. Все скрипты были в разных местах на сервере, что создавало определенные неудобства и многочисленные дубли кода. Стоит отметить, что в компании используются разные инструменты под разные типы задач. Я же сторонник использования BI CRM.

За первый месяц внедрения BI CRM обработала около 500 запросов менеджеров, не считая автоматических выгрузок по cron. Что позволило значительно снизить нагрузку на BI отдел, увеличить скорость получения данных для менеджеров, построить аналитику по частотности SQL-запросов. С точки зрения разработчика, это кажется вполне логичным и обоснованным решением — создать единую контролируемую точку входа и сохранять различные метрики по использованию. Однако к этой идее мы пришли не сразу. Итак, начнем с истории.

Каталог SQL-запросов

Вначале было создано нечто вроде каталога SQL-запросов. Принцип довольно прост. Есть таблица с SQL-скриптами, есть PHP-скрипт, который по cron выполняет каждый запрос SQL. У такого решения есть недостатки. Нет нормального фронтенд-интерфейса для работы, нет статистики по запросам, отсутствует интерактивность и т. п. Мы все еще продолжали часто пользоваться отдельными скриптами, которые запускались по cron.

API для работы с БД в Excel

Когда стало приходить много задач типа «выгрузи мне данные по такому-то параметру», появилась идея API для работы с БД в Excel. Что уже лучше, впервые менеджер сам мог использовать API, не обращаясь к аналитику. Можно контролировать обращения менеджеров, нагрузку на БД. API состоял из двух типов SQL-запросов и представлял собой изнутри, по сути, все тот же каталог SQL-запросов. Первый тип запроса взял символ «R» из CRUD и позволял прочитать все, что находилось в базе по какому-то ID. Второй тип — предопределенные запросы, составленные аналитиком. Например, аналитик создает функцию посчитать количество объявлений пользователя и называет ее «get_count_ads». После этого к этой функции можно обращаться по GET-запросу.

У такого подхода был существенный минус — отсутствовала возможность пакетной обработки данных, что серьезно нагружало базу данных. Например, менеджер не мог добавить в функции сразу десятки тысяч пользователей и приходилось делать много маленьких запросов. Таким образом, информацию можно было получить только по одному объявлению или пользователю. Также менеджер должен разбираться с API интерфейсом, что было очень неудобно, и не у всех версия Excel поддерживала функцию webservice.

Идея BI CRM

И вот пришла идея сделать это цивилизованно, через CRM. По сути, отдел аналитики является интеллектуальной поддержкой для других отделов. Менеджеры — наши клиенты, с которыми можно работать по аналогии с технической поддержкой, но на более высоком уровне.

CRM дала возможность вести полную аналитику обращений менеджеров, SQL-запросов, хранить все запросы в одном месте, делать анализ самих запросов, их истории, контролировать нагрузки на базы.

Инструменты создания: PHP, MySQL

Базы, с которыми работает система: Amazon Redshift (PostgreSQL), MS SQL, Amazon MySQL.

Архитектура интерфейса

Все задачи подразделяются на автоматически обновляемые по cron или в ручном режиме.

Если первые очень похожи на старый, добрый автообновляемый каталог запросов SQL, то вторые — это новое custom-решение. Здесь задача называется «Шаблон». Аналитик создает шаблон SQL-запроса и задает плейсхолдеры. Выглядит шаблон таким образом:

SELECT * FROM payments
WHERE id_user IN (#1)
AND created_at  BETWEEN #2 AND #3

Здесь #1 — это плейсхолдер для подстановки набора user_id, #2 и #3 — интервал дат.

Существует несколько типов плейсхолдеров, которые может добавлять аналитик:

Примеры: Text — textarea для ввода списка чисел или списка слов. Input — небольшое текстовое поле, Category — предопределенный набор данных в виде списка, который хранится в системе.

В результате сохранения шаблона, CRM создаст «Представление» — View этого шаблона. Это интерфейс для менеджера, в который он будет вводить данные, в нашем случае — это список user_id и даты.

После ввода входящих данных менеджер получает файл с исходящими данными.

Отдельно стоит упомянуть более сложные типы плейсхолдеров: категории, города и т. п. Технически они хранятся в таблице predefined в формате: id, element_id, name, type, id_country, value. Где type — это тип, например category, value — это список id через запятую. Он представляет из себя список значений id, например категорий. Впоследствии значения используются в запросе: SELECT * FROM predefined_items WHERE id IN (values). То есть все сложные плейсхолдеры имеют стандартный формат записи.

Что под капотом

Внутренняя часть основывается на самописной CRM (техническое название движка — crud crm), которая уже использовалась в OLX ранее и хорошо себя зарекомендовала. Система представляет собой конструктор CRM для быстрого развертывания (в течение часа) CRM разных видов, будь то для техподдержки пользователей или колл-центра. Именно на этой CRM обрабатываются вопросы и баг-репорты клиентов по OLX-доставке. Внутри это CRUD-система (PHP, Twig, MySQL), ядро которой состоит из соответственно добавления, чтения, обновления, удаления любых типов данных. Есть предустановленный шаблон, который автоматически генерирует визуальные элементы на основе последовательности, заданной в конфигурации. Есть возможность использовать свои custom-шаблоны, что мы и делали, так как менеджеры в основном хотят видеть только то, что им нужно.

Вдохновением послужил PHP CrudKit (к сожалению, сейчас он уже не поддерживается). Мы даже использовали его на некоторых своих проектах, но столкнулись впоследствии со сложностями кастомизации интерфейса, так как он был немного недоработанным. Однако его простота и скорость развертывания вдохновила создать свою CRUD CRM.

Пример обновления столбцов в ядре:

foreach ($listColumns as $column) {
	$sql = "UPDATE $table SET `$column`='{$itemArr[$column]}' WHERE id=$id";
	$this->db->exec($sql);
}

Пример инициализации CRUD CRM в index.php:

require_once "init.php";
/*
 *  Подключение ядра
 */
/* ... */
/*
 * Инициализация ядра
 */

$appCrud = new Crud($connections);

$appCrud->addTwig($twig);

// Создание URL
$appCrud->addPath("bicrm");
/*
 * Создание страницы (в админке, в левой части появится пункт меню)
 * table_name - название таблицы для CRUD, column_names - человеческие названия столбцов в БД
 */
$appCrud->addPage(array(
    "title" => "BI CRM SQL",
    "table_name" => "query",
    "column_names" => array("id" => "Id","id_db" => "DB","title" => "Title",
        "id_schedule"=>"Scedule","id_category"=>"Category",
        "id_rewrite_report"=>"Write Type",
        "running"=>"Running",
        "last_update_date" => "Last update","email" => "Email"),
 
  
));

/*
 * Сколько элементов выводить на страницу
 */

$appCrud->setMaxItemsOnPage(20);
/*
 * Задаем отображение столбцов из БД в интерфейс пользователя (HTML)
 */
$appCrud->setTypeField("query","text");
/*
 * Повесим на столбец first_date_start JS календарь
 */
$appCrud->setTypeField("first_date_start", "datepicker");

/*
 * id_db будет выпадающим списком, данные брать из таблицы db (в таблице db данные содержатся в формате id, value)
 */

$appCrud->setTypeField("id_db", "combobox",array("table" => array("db")));
$appCrud->setTypeField("id_schedule", "combobox",array("table" => array("schedule")));
$appCrud->setTypeField("id_category", "combobox",array("table" => array("category")));
$appCrud->setTypeField("id_rewrite_report", "combobox",array("table" => array("rewrite_report")));

$appCrud->addPage(array(
    "title" => "BI CRM Template",
    "table_name" => "template",
    "column_names" => array("id" => "Id","id_db" => "DB","title" => "Title",
        "id_category"=>"Category",
        "id_rewrite_report"=>"Write Type",
        "running"=>"Running",
       "email" => "Email"),

));

if($appCrud->isLoggedUser()) {
    $appCrud->render("index");
} else {
    $appCrud->render("login");
}

Таким образом можно создавать различные типы полей и управлять ими.

Внутренняя архитектура BI CRM

Обновление запросов организовано следующим образом:

По cron запускается scheduler, который ищет задачи для выполнения по условиям:

• задача активирована и не выполняется в данный момент;
• дата и время запуска задачи меньше либо равна сегодняшней.

По каждой задаче вычисляется интервал между временем последнего запуска в минутах. Если он больше заданной частоты обновления в интерфейсе или равен ей, то выполняется метод startTask, который в свою очередь запускает процесс по GET-запросу.

Задачи запускаются по URL, чтобы не нарушать целостность всего процесса. Если какая-то из задач выведет ошибки, то на другие задачи это не повлияет.

Что в итоге

• Систематизирована работа аналитиков и менеджеров, ведется учет полного цикла их работы.
• Менеджеры получают данные моментально.
• У коллег появилось время для работы над более сложными задачами. Решена проблема потери времени и ресурсов из-за ad hoc задач.

В будущем запланированы:

• интеграция PivotTable.js для построения pivot и визуализации данных прямо в «Представлении»;
• умный редактор SQL-кода;
• интеграция Python, R, PHP-скриптов в BI CRM.