Reuse prepared statements in the SQL Server driver

[morozov]

Internally SQLSrvStatement::execute() calls sqlsrv_query() which means that even if the same instance of SQLSrvStatement is executed multiple times, it creates a new one-off statement.

Performance-wise, the combination of sqlsrv_prepare() + sqlsrv_execute() is preferred to sqlsrv_query():

The combination of sqlsrv_prepare and sqlsrv_execute separates statement preparation and statement execution in to two function calls and can be used to execute parameterized queries. This function is ideal to execute a statement multiple times with different parameter values for each execution.

However, in case if the statement is not going to be reused, sqlsrv_query() is preferrable:

The sqlsrv_query function is well-suited for one-time queries and should be the default choice to execute queries unless special circumstances apply.

It would be nice to have a way to specify whether the statement is going to be reused so that the most optimal API could be used.

[Ocramius]

@morozov unless a new SQLSrvStatement is created, the same statement should be reused, I'd say.

[morozov]

@Ocramius you're saying there's no need to keep the sqlsrv_query() implementation and sqlsrv_prepare() + sqlsrv_execute() can be used instead? This is actually what I'd like to be able to use, since it's more efficient for reused statements.

As for the test, what kind of test would you like to see? I assume most of the functional tests cover/use SQLSrvStatement::execute(), so having them pass should be sufficient IMO.

[Ocramius]

@morozov patch in #2494 seems good!

[xalopp]

Unfortunately, this change breaks the test suite on my test system for the sqlsrv Driver on Linux 😢 🆘

2) Doctrine\Tests\DBAL\Functional\TypeConversionTest::testIdempotentDataConversion with data set #1 ('boolean', true, 'bool')
Exception: [Doctrine\DBAL\DBALException] An exception occurred while executing 'INSERT INTO type_conversion (id, test_boolean) VALUES (?, ?)' with params [2, 1]:

SQLSTATE [42S02, 208]: [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Invalid object name "type_conversion".
SQLSTATE [42000, 8180]: [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Statement(s) could not be prepared.

With queries:
2. SQL: 'INSERT INTO type_conversion (id, test_boolean) VALUES (?, ?)' Params: '2', '1'
1. SQL: 'CREATE TABLE type_conversion (id INT NOT NULL, test_string NVARCHAR(255), test_boolean BIT, test_bigint BIGINT, test_smallint BIGINT, test_datetime DATETIME2(6), test_datetimetz DATETIMEOFFSET(6), test_date DATE, test_time TIME(0), test_text VARCHAR(MAX), test_array VARCHAR(MAX), test_json_array VARCHAR(MAX), test_object VARCHAR(MAX), test_float DOUBLE PRECISION, test_decimal NUMERIC(10, 2), PRIMARY KEY (id))' Params: 

Client System:

• Ubuntu 16.04 Jessie
• PHP 7.0.10-2+deb.sury.org~xenial+1 (cli) ( NTS )
• sqlsrv Driver current version from https://github.com/Microsoft/msphpsql/tree/PHP-7.0-Linux, installed via phpize, with patches
  • https://github.com/xalopp/msphpsql/commit/285dee1a9395e4a02328ea7f488e708b542812d8
  • https://github.com/xalopp/msphpsql/commit/3763198f8a8460958a4ac8b2fb2dad529f44e5b4
• I also tested with the vanilla binary package of sqlsrv version 4.0.2, with same result.

Server:

• Windows 2012R2
• SQLServer 2016

@morozov could you tell which version of the SQLSRV Driver you're using, and on what platform?

[morozov]

I'm using Windows 2012 Server, SQL Server 2012, PHP 5.6.25 NTS, sqlsrv 3.2 (client and server on the same machine). Just re-ran the failing test, it still passes.

It looks like the table DDL is executed for each data set and fails for all data sets except # 0 (the exception is caught but suppressed). I'd try either creating the table once for the whole suite, or drop it explicitly after each test. I'm just speculating, but the DDL query execution (even failing) may invalidate some internal object cache which prevents the 2nd statement from being prepared.

[xalopp]

@morozov thank you. I see you're using PHP5. Would you mind to test it with PHP7 on Windows
https://github.com/Microsoft/msphpsql/tree/PHP-7.0?
I'm curious if the PHP7 SQLSRV driver also has the Issue I'm facing on Linux.

[morozov]

@xalopp is it okay if I use the distributed sqlsrv 4.0 from here? https://www.microsoft.com/en-us/download/details.aspx?id=20098 oh, I see, there are pre-compiled binaries released. Give me a day, I don't have the needed environment at hand.

[morozov]

@xalopp I've just checked it with PHP 7.0.10 and sqlsrv 4.1.1 on the same server, and the test still passes.

[xalopp]

@morozov thanks for your help. Looks this is a Linux specific bug in the sqlsrv driver :-(

[github-actions]

This thread has been automatically locked since there has not been any recent activity after it was closed. Please open a new issue for related bugs.