Performance issue, using odbc is faster than sqlsrv with PDO

[sean256]

While screwing around with trying to get connection pooling working I discovered that sqlsrv is much slower during execute than using odbc.

• OS: CentOS 7.2
• PHP: PHP 7.0.12
• SQL Driver: ODBC Driver 13 for SQL Server

Test code (in milliseconds)

$startTime = microtime(true);
function printElapsedTimeSinceLastEvent($event=''){
	global $startTime;
	$elapsed = microtime(true) - $startTime;
	echo $event . round($elapsed*1000) ."\n";
	$startTime = microtime(true);
}

// Connection sqlsrv driver
$pdo = new PDO ("sqlsrv:server=10.12.12.123;database=someDatabase",'someUser','somePassword');
printElapsedTimeSinceLastEvent("SQLSRV Connected: ");
$stmt = $pdo->prepare("SELECT TOP 10 * FROM users");
$stmt->execute();
printElapsedTimeSinceLastEvent("SQLSRV Execute: ");
$array = $stmt->fetchAll(PDO::FETCH_ASSOC);
printElapsedTimeSinceLastEvent("SQLSRV Fetch: ");

//Connection using ODBC driver
$pdo = new PDO ('odbc:DSN=someODBCDSN;UID=someUser;PWD=somePassword');
printElapsedTimeSinceLastEvent("ODBC Connected: ");
$stmt = $pdo->prepare("SELECT TOP 10 * FROM users");
$stmt->execute();
printElapsedTimeSinceLastEvent("ODBC Execute: ");
$array = $stmt->fetchAll(PDO::FETCH_ASSOC);
printElapsedTimeSinceLastEvent("ODBC Fetch: ");

Output:

SQLSRV Connected: 151
SQLSRV Execute: 109
SQLSRV Fetch: 1
ODBC Connected: 150
ODBC Execute: 58
ODBC Fetch: 0

[meet-bhagdev]

@sean256 Very interesting find. Let us see what is going on here in our test labs. My hunch is that there is some conversion logic when using sqlsrv that causes the perf differences when comparing it to vanilla ODBC

[richerlariviere]

I have the same slowness with PDO_SQLSRV. see call graph. My profiling points to the PDOStatement::fetch method.

OS: Ubuntu 16.04.1 (tested on CentOS 7.2 too)
PHP: PHP 7.0.8

[jansor]

@richerlariviere Just wondering what tool did you use to create the call graph? Thanks for the info.

[richerlariviere]

Hi @jansor, I am using gprof2dot to generate the call graph. I'm generating the data using xdebug profiling feature. Here is how I used the program:

gprof2dot --total=callstacks -f callgrind cachegrind.out | dot -Tpng -o output.png

[meet-bhagdev]

@richerlariviere @jansor @sean256 We are investigating this right now. From the surface it looks like SQLSRV fetch functions compared to ODBC are very extensive and try to predict and cover more cases. This could be one of the reasons for the slowness. This is not conclusive and we should have more to share in the future.

[ghost]

@ALL,
Any chance also we've this in windows versions?
Seems like we've some performance issues with select queries but to the day that @sean256 wrote this thread, we didn't suspect anything now we've.
We've perfectly working PHP 5.6 versions in production servers.
We take a copy of the server and change the PHP to 7 with the same config and change the sql driver for 7 and also odbc driver. Our database connections & reports & etc slows down.

Also need to check too.

Edit:
Checked and our performance issue was not connected to driver. Runs like hell...

[v-mabarw]

@mlhkr If I understand your edit correctly, you found that your issue was with the environment and not the driver?

[ghost]

@v-mabarw exactly. Driver's query speed is amazing.

[meet-bhagdev]

@mlhkr Good to know! Is there any way we can help with any other performance issues you are facing?
@sean256 @richerlariviere We are still looking into your observation

[richerlariviere]

@meet-bhagdev I'm not sure what kind of observation are you looking for?

[meet-bhagdev]

@richerlariviere I'm talking about the performance issues 👍

[richerlariviere]

The only performance issue I got is the one with the PDO driver. SQLSRV is super fast on my 2 setups.

[richerlariviere]

Any update for the performance issue? I tried with the latest build and got the same problem.

[sean256]

Something my team discovered about this, increased latency seems to impact sqlsrv far more than other drivers. When testing sqlsrv against dblib on a sever very close to the DB they both perform very well. As the latency increases sqlsrv gets far worse than dblib.

A set of quires on the "near by" server (latency < 5ms) they would average ~400ms for both dblib and sqlsrv.

From our office where we dev (latency of 29ms) dblib would hit ~500ms but sqlsrv is averaging at 1.2 seconds.

[v-kigos]

Do you use dblib extension with PHP7 ?

[meet-bhagdev]

@sean256 We have not tested latency scenarios. What do the results look like when both sqlsrv and dblib are very close to the db? Also does the aforementioned repro script still stand for this scenario?

[sean256]

I no longer have the environment to test this so I can't give you solid numbers, sorry about that. When they were close to the DB they both performed very well.

[david-garcia-garcia]

Trying to figure out why our Drupal projects run slower than that of our MySql friends, I've setup a test script that shows PDO_SQLSRV being x4 to x5 times slower than the PDO_MYSQL driver.

Either my test script is super flawed, or we have serious issues with the PDO driver for Microsoft SQL Server.

http://pastebin.com/vzpt7Ara

Both MySQL and MSSQL servers are on local machine during tests, using PHP 7.0.14 x64 with ODBC 13.1.

This is the output of the test script:

INSERT LAPSED:1957.930803299
LAPSED FETCH_ASSOC (5000):27.679920196533
LAPSED FETCH_OBJ (5000):24.142026901245
LAPSED FETCH_ASSOC NON BUFFERED (5000):39.545059204102
LAPSED FETCH_OBJ NON BUFFERED (5000):43.994903564453

INSERT LAPSED:12562.846183777
LAPSED FETCH_ASSOC (5000):5.3069591522217
LAPSED FETCH_OBJ (5000):7.0128440856934
LAPSED FETCH_ASSOC NON BUFFERED (5000):5.2840709686279
LAPSED FETCH_OBJ NON BUFFERED (5000):7.127046585083

Inserting the data took x6 longer on MySQL (probably due to me having no clue on to how properly setup MySQL).

The issue is when retrieving data. SQL Server PDO shows a x2 improvement in using buffered queries vs not using them (great!).

But when compared to MySQL, SQL Server PDO fetches data x5 times slower.

To make thing matter worse, if in the sample I comment out the following line:

$connection_options['pdo'][PDO::SQLSRV_ATTR_FETCHES_NUMERIC_TYPE] = TRUE;

(So that everything is fetched as a string), then the PDO numbers get even worse, like x3 slower than the original:

sqlsrv:server=localhost;Database=php_d7test
INSERT LAPSED:2691.6589736938
LAPSED FETCH_ASSOC (5000):101.07612609863
LAPSED FETCH_OBJ (5000):105.18407821655
LAPSED FETCH_ASSOC NON BUFFERED (5000):72.057008743286
LAPSED FETCH_OBJ NON BUFFERED (5000):77.020883560181
INSERT LAPSED:12446.696043015
LAPSED FETCH_ASSOC (5000):5.4290294647217
LAPSED FETCH_OBJ (5000):6.7908763885498
LAPSED FETCH_ASSOC NON BUFFERED (5000):5.3608417510986
LAPSED FETCH_OBJ NON BUFFERED (5000):6.864070892334

Actually in this case non buffered perform better than buffered. And in the best case, they are x16 slower than MySql.

Either my test/setup is totally flawed, the MSSQL PDO driver has very bad performance or a combination of both.

Worth link into.

[meet-bhagdev]

@david-garcia-garcia We are looking into this. Were you able to test this with different datasets?

[v-dareck]

@sean256 if your SQL Server is remote then disabling MARS may work. Set MultipleActiveResultSets=0 in the connection string. You should notice a performance improvement.

The caveat is that disabling MARS means you can only have one active statement/result at a time. So depending on how your PHP code is structured you could encounter errors when executing a second statement or accessing a result. There are examples on MSDN that explain MARS. They maybe in other languages but the same concepts apply to PHP. You should review the MSDN documentation so you understand how disabling MARS could affect your code.

@david-garcia-garcia we are looking at the performance difference with PDO ODBC as a baseline instead of MySQL. PDO_SQLSRV is still slower than PDO ODBC and we are investigating how to close that gap.

[joe-udwin-lrn]

@v-dareck @meet-bhagdev It is great news to hear you are starting to take the performance characteristics seriously. With such a performance critical product, benchmark metrics should be publicised with every release and the team should be aspiring to beat the competition.

[david-garcia-garcia]

if your SQL Server is remote then disabling MARS may work.

That helped. A little. "If your SQL Server is remote". When is the SQL Server not remote except on a development environment?

So depending on how your PHP code is structured you could encounter errors

If using client buffered queries, disabling MARS should not have any impact, because result set is fetched into PHP the moment the pdo statement is executed.

Tested on the Rackspace Cloud with Virtual Servers, disabling MARS yields +25% better speed on a real world application (Drupal 7) for the PDOStatement::execute coverage.

I.e. an execution path that runs 152 statements spend average 201ms in PDOStatement::execute with MARS enabled, and 150ms with MARS disabled. In this specific scenario 50ms are irrelevant to the complete execution path.

I've got execution paths with > 16,000 statements run (pure crap code, but nothing to do about it - it's open source implementation that won't change). In that specific case 95 seconds spent in PDOStatement::execute went down to ~70 seconds.

So... summarizing the different results I've been seeing:

Disabling MARS + Using client buffered queries + Enabling numeric fetches seem the best performance wise combination.

[meet-bhagdev]

@Joe-U-Questionmark Point taken on the benchmark metrics. We are not there yet but we will work towards it. We are currently focusing on improving the performance by addressing issues such as this one.

@david-garcia-garcia thanks for testing the recommendation. We will continue the testing here and document it clearly in our docs once we are fully satisfied with the perf improvements.

[rax1606]

@sean256
Actually, the timings I was talking about in my earlier comment was for a rest api that I am hitting and it internally executes multiple queries on the DB. I tried out all the queries directly from my SQL client and the combined execution time for all of them was in few seconds only.
Moreover, I have the same setup working on WINDOWS with optimal performance. So I don't think there would be anything wrong with the execution plan for the queries that is making it perform so bad on LINUX.

@david-puglielli
I tried with the latest msodbcsql17 too, no difference. It's performing the same way.
As far as the repro script is concerned I am herewith attaching my Dockerfile (
Dockerfile.txt).
My PHP app is developed in yii2 framwork, and it connects to the SQL db via

'dsn' => 'sqlsrv:Server=dbhost_ip;Database=db_name;MultipleActiveResultSets=false',
'username' => 'username',
'password' => 'password',

@dhazelett
I am hesitating from using FRETDS as I have read in the comments of one the issues listed here that

[dhazelett]

@rax1606 that is a fair point if that is a requirement, I'd not recommend it.

[rax1606]

I have something new to point here about the performance issue I have been facing.

The issue that I was having seems to be there only for docker containers. I could find one ubuntu box in my company,with SQLSRV-4.0.7 and the setup works perfectly fine. The db performance is very nice there.

But even after setting the SQLSRV version to 4.0.7 on my docker container, I couldn't get it work fine. The response time increase by about 6 times to be specific, when I run my application in docker.

I was wondering if I am missing something in my docker configuration? Or is there anything else that I should be taking care while devising a docker image for php and sqlsrv.

I have already uploaded my docker file sript and the way I am connecting to the db's.

Please guide me any ways you can. I really need my application working in docker and I seem to have hit a dead end at this point.

Thanks.

[David-Engel]

@rax1606 Looking at your docker script:
Inferences:

1. Your SQL Server is not in the docker container so that's not the issue.
2. It does not tell me what your PHP code is or what queries you are running or what your data looks like - big unknown for troubleshooting.

You mentioned your code is hitting a rest api. Is the rest api hosted in the docker container or is that external, too?

Have you checked the basics? Specifically, are you allocating enough CPU and memory to docker to support what you are running? Are you pulling a lot of data (inadvertently maybe?) from SQL Server into the rest api and then into PHP for processing? It doesn't seem like it should require much, but without seeing any code, it's possible you are doing things that require more memory and/or CPU than you are allocating to docker.

David

[dhazelett]

@david-puglielli here's a funny one. 5.2.0, both ODBC 13 & 17 exhibited same behavior.

Here we found a problem with a query's performance, where odbc/freetds had sub ms response times

This one is from a backend laravel app, in order:

1. using built in Eloquent query builder
2. running the query raw, adding an extra quote level.
3. ignore, this is a problem query on our production frontend, that has a ~500ms response time, when running 5.2.0+ODBC-17

same as above, but removing the quotes from the parameter:

I'm going to try to get some repo stuff on monday for you guys.

[codecrafting-io]

After passing more than a year dealing with this performance issue, to me it's comes down to choice at this point. Or I choose to stay with SCROLL cursors with the BUFFERED option, despite consuming more RAM, or I choose ODBC and miss some new settings and more importantly encoding support. Most of PHP frameworks will use PDO and expect support for numRows which does not work with FORWARD cursors, so I must use SCROLLABLE cursors. To me the most weird performance hit it's the fact that using cursors with PDO ODBC it barely affects the performance, AND using SCROLL cursor with PDO SQLSRV has a MASSIVE impact on performance. And the optmizations of char encoding, stringify fetches, even MARS have a small weight in comparison to the difference of performance of PDO ODBC and PDO SQLSRV

[yitam]

hi @codecrafting-net

I suppose you are comparing our PDO_SQLSRV driver to PDO ODBC? Note that you do not have to use BUFFERED option to get row count. For example, there exists static cursor on the server side that lets you get rowCount(). Buffered queries are only recommended for small to medium result sets.

Using AdventureWorks DB for example, you can do the following:

$conn = new PDO( "sqlsrv:server=$server ; Database = AdventureWorks2014", "$uid", "$pwd");

$query = "select * from Person.ContactType";
$stmt = $conn->prepare($query, array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL, PDO::SQLSRV_ATTR_CURSOR_SCROLL_TYPE => PDO::SQLSRV_CURSOR_STATIC));
$stmt->execute();
print $stmt->rowCount();
print " rows in result set."; // OUTPUT: 20 rows in result set.

Hope this clarifies things. If not, please create a new issue with more details.

[codecrafting-io]

Thanks for respond @yitam, but that's the problem. If I do not use SQLSRV_CURSOR_BUFFERED all other cursors have a TERRIBLE performance, at least on my environment. Just as example:

$query = "SELECT TOP 1000 * FROM reports";
$stmt = $conn->prepare($query, array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL, PDO::SQLSRV_ATTR_CURSOR_SCROLL_TYPE => PDO::SQLSRV_CURSOR_STATIC));
$stmt->execute();
echo count($stmt->fetchAll());

Takes 1.1 to 1.3 seconds and if I change to SQLSRV_CURSOR_BUFFERED takes 80 to 70 ms, so a abysmal difference. I am using count($stmt->fetchAll()) to force a fetch, since just making the query and get the numRows is fast, but the problem is fetching the results. Changing to PDO ODBC with a dsn like odbc:driver={ODBC Driver 11 for SQL Server};server=SERVER_NAME;database=DB_NAME; without cursors I have the same results for buffered result, and with cursors (only using PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL) I have arround 700ms which is faster than PDO SQL SERVER but much slower than buffered or without cursors.

Just to clarify things, follows my environment settings:

• SQL Server 2012 SP1
• Windows Server 2012
• IIS as http server
• PHP 7.2.0 x64 NTS
• PHP SQL Server driver 5.2.0

This can't be a CPU, network thing since all tests are done with the same environment

For last I just not creating a new issue beacause I still think is about performance in ODBC comparison, but if it is better I can create a new one.

[yitam]

Hi @codecrafting-net

Just so you know, when using buffered cursors, our drivers store all results in memory when executing the query, so when you do fetching, the results are already in memory. On the other hand, without cursors, we do make a trip to the server to get the results. That's why buffered cursors are recommended for small to medium result sets.

You might want to compare the time taken for a combination of execute and fetch, and the performance with or without cursors should not be that significantly different. Otherwise, please create a new issue. Thank you.

[codecrafting-io]

@yitam Ok I get this, but why PDO without cursors or even just ODBC is as fast as buffered, but consuming way fewer resources? Look, to me I saying that the difference with or without cursors is abysmal, this SELECT TOP 1000 * with cursors take 1.1s and without it drops to 80ms. If the usage of cursors are suposed to be much slower to maintain a stable result set, ok, but I am not certain if that is the case. Just to be clear I don't have intensions to have a cursor to have the changes on DB or anything is just for that numRows that my framework uses.

[yitam]

hi @codecrafting-net

Many thanks for your inputs, and it would certainly help us to improve if you can provide details, say a more concrete repro scenario in a new issue. Your contributions will be much appreciated.

Thank you.

[Raymonf]

Same here, except with Laravel. I can't change (or rather, don't know how to) the scroll type.

Queries take 1~4s to execute for no reason.

[dhazelett]

I can add to the laravel thing too, in some queries simply not escaping the query gains a bit of performance too. For instance we had a where in like so:

select [name] from [table] where [id] in (...)

Returned results in 30s or more, just removing them using DB::raw() it dropped to 1s

[Raymonf]

Looks like rolling back to pdo_sqlsrv 4.3.0 and ODBC Driver 13.0.1 fixes the lag... or something. No idea what's going on.

[yitam]

Hi @Raymonf and @dhazelett

As I said to @codecrafting-net, please create a new issue and provide details and examples (table schemas, sample queries, etc.) to help us investigate and/or reproduce the problems you're experiencing. Thank you for your contributions.

[yitam]

hi @Raymonf

Concerning the performance difference between 4.3.0 and 5.2.0, we have fixed one of the problems that might contribute to degrading performance in our latest 5.2.1-preview.

Would you be able to give it a try?

[Raymonf]

@yitam

Sorry for the late reply. I don't want to mess something working up right now so I'll create a Docker container of my site or something.

As for reproduction, even a simple select 1+1; took at least 2 seconds.

[yitam]

@Raymonf please provide us more details in a new issue, in particular your environment, SQL Server version, etc.

What I mentioned about the changes from 5.2.0 to the latest 5.2.1-preview mainly affected how the drivers bind parameters.

Nonetheless, I just did a quick test comparing 5.2.0 and the latest 5.2.1-preview with ODBC 17.1 in diff platforms. I do not use Docker in any of them.

The following table shows the time taken to run a simple query select 1+1 using pdo_sqlsrv connecting to a SQL Server on the network. Note that my Ubuntu 16.04 env is a VM with much less memory.

Version	Platform	Time (secs)
5.2.0	mac Sierra	0.00190210
5.2.1-preview	Windows 10 (x86)	0.00201916
5.2.1-preview	Windows 10 (x64)	0.00156808
5.2.1-preview	Ubuntu 16.04 vm	0.05027604

[Raymonf]

@yitam It looks like 5.2.1-preview fixed whatever problem 5.2.0 had with horrible performance.

Nevertheless, the SQL Server version is 14.0 running on Windows Server 2016 (not 1709). Ping between servers is <2ms, so that was definitely not the issue. The original server (which had the issue before downgrading) is running Ubuntu 16.04 x64, and the Docker container (which does not have the issue) runs Debian Stretch.

Thanks!

[yitam]

Hi @Raymonf, your prompt reply and details are appreciated. Thank you for letting us know that 5.2.1-preview has shown improvement. Again, please feel free to create a new issue if you want us to investigate further.

[bwhiteford]

Hello everyone! Thanks for all of the great chatter about this issue, I have read through all of the comments to make sure I'm up to speed. We have a very busy intranet only Drupal site currently running PHP 5.3 with a version 2.5 of the SQL Server PDO extension on Server 2008 R2 (Very old, I know). We are attempting to upgrade the site to PHP 7.2 and SQL Server PDO extension 5.3, but unfortunately the performance issues render the site unusable. The web server jumps to 100% CPU immediately, and stays there permanently. Running on 5.3 with extension version 2.5 we hover around 40% with a much more responsive site.

We also tried PHP 5.6 with extension version 3.2 and the issue was exactly the same. I noticed today in the release notes for version 3.2 there is a performance issue for machines running Windows 7 or Server 2008 R2. Is it safe to say this is our problem since it looks like the performance issues weer fixed in the 5.2.1-preview and beyond?

Thanks for the help!

[yitam]

hi @bwhiteford, we would like to check if you have any updates for us. Just so you know, performance improvement is of high priority, and any inputs or suggestions are welcome to help us proceed further.

[pedrorocha-net]

I was having really bad performance here and the solution on https://stackoverflow.com/questions/45598786/slow-sqlsrv-pdo-query-performance-with-parameterized-queries helped me a lot.

Basically, add
$conn->setAttribute( PDO::SQLSRV_ATTR_ENCODING, PDO::SQLSRV_ENCODING_SYSTEM );

[david-puglielli]

@pedrorocha-net We're glad it helped. FYI, the latest preview release of the drivers includes additional optimisations for handling UTF-8 data, so that may help with performance as well.

[esundberg]

Also read this.

https://stackoverflow.com/questions/36869716/php-sqlsrv-has-intermitent-delay-on-sqlsrv-fetch-array-sqlsrv-fetch

Disabled due to load times. Simple page 1.7 Seconds
$options = array( "Scrollable" => SQLSRV_CURSOR_KEYSET);

Changed this and the same page load times went down to .04 Seconds
$options = array( "Scrollable" => SQLSRV_CURSOR_CLIENT_BUFFERED);
$stmt = sqlsrv_query($Conn, $sql, $params, $options);

[yitam]

Hi @sean256 and others

I can't seem to reproduce the original issue with the latest pdo_sqlsrv and ODBC drivers with PHP 7.4. If any of you still experience similar problems please provide a repro script or scenario. Otherwise, we will close this issue due to inactivity.

[yitam]

Closing this issue due to inactivity or lack of repro steps.

[mkopinsky]

For others who bump into this issue while looking into performance issues:

There's more info on this on the wiki in this very repo: https://github.com/Microsoft/msphpsql/wiki/Recommendations-for-improving-the-performance-of-PDO_SQLSRV-and-SQLSRV