SQLSRV Параметризованный запрос, принимающий навсегда в предложении GROUP BY

Я использую SQLSRV 4.3 PDO с PHP 7.1. Я уже тестировал его на SQLSRV 3.2 с PHP 5.6 тоже. Итак, у меня есть следующий запрос:

$sql = "select [ID], [Year], [Month], sum(Value) as Value, 
MIN(FirstDateTime) as FirstDateTime, MAX(SecondDateTime) as 
SecondDateTime from [ValuesTable] where [FirstDateTime] >= ? and 
[SecondDateTime] <= ? and [Year] in (?) GROUP BY [ID], 
[Year], [Month] order by [ID] offset 0 rows fetch next 30 rows only";

И его параметры:

$param1 = '2017-10-01 00:00:00';
$param2 = '2017-10-31 23:59:59';
$param3 = '2017';

Когда я выполняю запрос со следующим кодом:

$result = $db->prepare($sql)
$result->bindParam(1, $param1);
$result->bindParam(2, $param2);
$result->bindParam(3, $param3);
$result->execute();

Это займет вечность, чтобы завершить. Если вместо этого я выполняю следующий небезопасный запрос:

$sql = "select [ID], [Year], [Month], sum(Value) as Value, 
MIN(FirstDateTime) as FirstDateTime, MAX(SecondDateTime) as 
SecondDateTime from [ValuesTable] where [FirstDateTime] >= '2017-10-01 00:00:00' and 
[SecondDateTime] <= '2017-10-31 23:59:59' and [Year] in (2017) GROUP BY [ID], 
[Year], [Month] order by [ID] offset 0 rows fetch next 30 rows only";

Это не займет много времени, чтобы завершить. Я проверил много и исследовал много, и я не мог найти ответ на свою проблему.

Я уже установил атрибуты для PDO, как

$db->setAttribute( \PDO::SQLSRV_ATTR_ENCODING, \PDO::SQLSRV_ENCODING_SYSTEM );
$db->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);

Кроме того, я помещаю типы параметров в bindParam() функция, как в

$result->bindParam(1, $param1, PDO::PARAM_STR);
$result->bindParam(2, $param2, PDO::PARAM_STR);
$result->bindParam(3, $param3, PDO::PARAM_INT);

Я также изменил тип курсора с помощью следующего кода

$result = $db->prepare($sql, array(
   \PDO::ATTR_CURSOR => \PDO::CURSOR_SCROLL, 
   \PDO::SQLSRV_ATTR_CURSOR_SCROLL_TYPE => \PDO::SQLSRV_CURSOR_BUFFERED
   )
);

Параметризованный запрос все еще выполняется вечно, ничего не изменилось. Кроме того, выполнили следующие тесты только для того, чтобы предотвратить кэш-память запросов SQL Server или что-то вроде

  1. Перезапущенный Sql-сервер
  2. Выполнил параметризованный запрос, и его выполнение заняло целую вечность
  3. Перезапущенный Sql-сервер
  4. Выполнил небезопасный запрос, и он был выполнен в кратчайшие сроки

Кто-нибудь догадывается, что я делаю не так?

О, я забыл упомянуть одну вещь: таблица "ValuesTable" - это секционированное представление, в котором есть таблица для каждого года. Как ValuesTable2016, ValuesTable2017. Каждая таблица имеет ограничение проверки, поэтому оптимизатор запросов будет знать, какие таблицы включить в запрос. Представление создается с помощью следующего сценария:

CREATE View [dbo].[ValuesTable] as 

    SELECT * FROM ValuesTable2016 UNION ALL 

    SELECT * FROM ValuesTable2017 UNION ALL 

    SELECT * FROM ValuesTable2018

Если я выполню параметризованный запрос, используя TABLE, а не разделенный VIEW, как

$sql = "select [ID], [Year], [Month], sum(Value) as Value, 
    MIN(FirstDateTime) as FirstDateTime, MAX(SecondDateTime) as 
    SecondDateTime from [ValuesTable2017] where [FirstDateTime] >= ? and 
    [SecondDateTime] <= ? and [Year] in (?) GROUP BY [ID], 
    [Year], [Month] order by [ID] offset 0 rows fetch next 30 rows only";

Запрос выполняется быстро, как в небезопасном. Но, очевидно, мне нужно использовать разделенное представление, так что это не вариант.

Небезопасный запрос всегда выполняется быстро, используя секционированное представление или таблицу. Так что нет смысла предлагать мне изменить его структуру. Дело в том, что непараметризованный запрос выполняется быстро, а параметризованный - навсегда.

Еще одна вещь: я уже протестировал его без pdo, используя sqlsrv_prepare с параметрами связывания и выполняя его с помощью sqlsrv_execute. Это также работает очень медленно.

Источник

1 ответ

Решение

Я нашел обходной путь: если я выполняю следующий запрос, включая объединение всех ТОЛЬКО для таблиц, которые содержат данные (а не это представление с объединением всех для всех таблиц), он выполняется так же быстро, как и непараметризованный запрос:

$sql = "select [ID], [Year], [Month], sum(Value) as Value, 
MIN(FirstDateTime) as FirstDateTime, MAX(SecondDateTime) as 
SecondDateTime from 
(select * FROM [ValuesTable2016] UNION ALL select * FROM [ValuesTable2017]) 
as ValuesTable
where [FirstDateTime] >= ? and 
[SecondDateTime] <= ? and [Year] in (?, ?) GROUP BY [ID], 
[Year], [Month] order by [ID] offset 0 rows fetch next 30 rows only";

С параметрами

$param1 = '2016-10-01 00:00:00';
$param2 = '2017-01-01 23:59:59';
$param3 = '2016';
$param3 = '2017';

$result->bindParam(1, $param1);
$result->bindParam(2, $param2);
$result->bindParam(3, $param3);
$result->bindParam(4, $param4);

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

Так что, это своего рода обходной путь, и он работает. Но я был бы ОЧЕНЬ признателен, если бы кто-то мог объяснить, почему это происходит. Я знаю, что это особенность, которую мало кто заметит, и, возможно, если это действительно проблема с параметризованными запросами, люди не потрудятся ее исправить, потому что я не так важен. Кроме того, судьба всегда делает мою жизнь сложнее, заставляя меня сталкиваться с такой необычной проблемой. Но если у кого-то есть время и доброта, мне бы очень хотелось узнать, что происходит.

Источник
Другие вопросы по тегам