php pdo prepared-statement sqlsrv bindparam

SQLSRV Parametrized query taking forever in a GROUP BY clause

I'm using the SQLSRV 4.3 PDO with PHP 7.1. I've already tested it on SQLSRV 3.2 with PHP 5.6 too. So, I have the following query:

$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";

And its parameters:

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

When I execute the query with the following code:

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

It takes forever to complete. If, instead, I execute the following insecure query:

$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";

It takes no time to complete. I've tested a LOT and researched A LOT and I couldn't find an answer to my problem.

I've already set attributes to PDO, like

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

Also, I put the parameters' types into the bindParam() function, like in

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

I also modified the cursor type with the following code

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

The parametrized query is still taking forever, nothing changed. Also, did the following tests just to prevent any sql server query memory cache or something:

Restarted Sql Server
Executed the parametrized query and it took forever to complete
Restarted Sql Server
Executed the insecure query and had it executed in no time

Does Anybody have a guess of what am I doing wrong?

Oh, I forgot to mention one thing: The "ValuesTable" table is a partitioned view, which has a table for each year. Like ValuesTable2016, ValuesTable2017. Each table has a check constraint so the query optimizer will know which tables to include in the query. The view is created with the following script:

CREATE View [dbo].[ValuesTable] as 

    SELECT * FROM ValuesTable2016 UNION ALL 

    SELECT * FROM ValuesTable2017 UNION ALL 

    SELECT * FROM ValuesTable2018

If I execute the parametrized query using the TABLE and not the partitioned VIEW, like

$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";

The query runs fast, as in the insecure one. But, obviously, I need to use the partitioned view, so this is not an option.

The insecure query always runs fast, using the partitioned view or table. So there's no use suggesting me to change its structure. The thing is, the unparametrized query runs fast and the parametrized one does take forever.

One more thing: I already tested it without the pdo, using the sqlsrv_prepare with bind parameters and executing it with sqlsrv_execute. It also runs very slow.

Solution

I came up with a workaround: If I execute the following query, including the union all ONLY on tables that contains the data (and not that view with the union all on all the tables) it executes as fast as the unparametrized query:

$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";

With the parameters

$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);

So I guess that, for some reason, when I execute a parametrized query with a group by clausule AND a union all on all related tables with the check constraint, it takes forever to complete. If I do it without the group by clausule it's fast. If I do it only with the tables that I'm querying on, it's fast. If I execute it insecurely with the parameters inside the query, it's fast.

SO, it's kind of a workaround and it works. But I would appreciate VERY MUCH if someone could explain why such a thing is happenning. I know it's a particularity that few people would notice and maybe if it's really a problem on parametrized queries, people wouldn't bother to fix it because I'm not that important. Also, fate always contributes to make my life harder by making me face this kind of uncommon problem. But if anyone have some time and some kindness, I really would like to know what's going on.