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.

[joe-udwin-lrn]

@meet-bhagdev ,

@david-garcia-garcia said: "Disabling MARS + Using client buffered queries + Enabling numeric fetches seem the best performance wise combination."

It would be great to have a wiki page with the current benchmark metrics and suggestions on the ideal configuration to improve the performance of the driver with an explanation of the pros and cons of making each change.

[meet-bhagdev]

@Joe-U-Questionmark We are working on a wiki page with suggestions :) @ulvii has started a new wiki page: https://github.com/Microsoft/msphpsql/wiki/Recommendations-for-improving-the-performance-of-PDO_SQLSRV-and-SQLSRV It's a work in progress so feedback is welcome :)

We are also starting to do some perf benchmark testing. Will keep this thread updated as we have some results. What do you think would be some good metrics for us to measure.

[ulvii]

We also implemented caching to reduce the number of calls to MSODBCSQL driver, which should result in 15-20% performance improvement when fetching strings. The changes are merged to the dev branch and will be available with the next release.

[meet-bhagdev]

@all checking in if you were able to observe some perf improvements with the latest driver: https://github.com/Microsoft/msphpsql/releases/tag/v4.1.8-preview

[dhazelett]

@meet-bhagdev I'll try this on one of our QA servers at some point this week. We had to revert back to odbc as well because the query times were awful previously.

I'll update with our findings asap.

[rax1606]

Was the performance issue resolved finally? I have been experiencing the same with sqlsrv on debian in Docker. Not sure whether I am installing the latest version or not but I am using the below code to install the drivers in my docker image:

RUN apt-get update && curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add - && curl https://packages.microsoft.com/config/debian/8/prod.list > /etc/apt/sources.list.d/mssql-release.list && apt-get install -y --no-install-recommends locales apt-transport-https && echo "en_US.UTF-8 UTF-8" > /etc/locale.gen && locale-gen && apt-get update && apt-get -y --no-install-recommends install msodbcsql unixodbc-dev

RUN docker-php-ext-install mbstring pdo pdo_mysql && pecl install sqlsrv pdo_sqlsrv xdebug && docker-php-ext-enable sqlsrv pdo_sqlsrv xdebug

Can someone help me here. I am experiencing drastic performance hit here as compared to what I get when I deploy the app on linux with dblib.

[david-puglielli]

@rax1606 You are not installing the latest version. pecl install sqlsrv only installs the last stable release but there have been several releases since. Please replace the second RUN statement with

RUN docker-php-ext-install mbstring pdo pdo_mysql && pecl install sqlsrv-5.2.0RC1 pdo_sqlsrv-5.2.0RC1 xdebug && docker-php-ext-enable sqlsrv pdo_sqlsrv xdebug

and let us know if performance improves.

[dhazelett]

Performance issues have not been resolved. I may be able to privately provide repro scripts. But it's actually gotten worse in some cases. We're still running odbc+freetds.

This is on 5.2.0 RC1 and on the 4.x branch.

[david-puglielli]

@dhazelett Repro scripts would be very useful - anything that shows the performance gap between our driver and your other setup would help with our investigation.

[dhazelett]

@david-puglielli Sorry for the delayed response, github didn't notify me :(

Let me get my DBA to see if he can get me a stripped down schema, the supporting sproc, and some data and I'll get back to you asap.

[rax1606]

@david-puglielli As you mentioned pecl install sqlsrv installs the latest stable version, now that version-5.2.0 is stable I am getting this version installed automatically.

Moreover,I switched to ubuntu:16.04 (as I read somewhere that sqlsrv is not well supported on debian, I don't remember where,but somewhere in the other issues listed in this repo).

I devised my docker image on the same lines as https://github.com/Microsoft/mssql-docker/blob/master/oss-drivers/msphpsql/Dockerfile and installed apache2 on top of that.

But I don't see any performance improvement what so ever. I have been struggling with this for last 2 weeks now. In fact, this time it went worse. :(

Was just wondering if the issue is going to be resolved soon or I should start looking for alternates. In fact, am not aware of what alternates I can try here. Am badly looking for any guidance that can help me out.

I have also heard about the dblib drivers, can I use that as an alternate here.

Just to clarify, I am using PHP7 and somewhere I have read dblib is no more supported after PHP5, am not sure where, but I have read this.

Please guide me through this. I need the performance to be very good, but at this point it is not even acceptable. To give you an example, for one of the queries it took 25 minutes to respond.

[david-puglielli]

@rax1606 If you have any kind of repro script that shows the performance issue, that would be very helpful. 25 minutes is a very long time to wait for any query, so we too would definitely like to find out why it's taking so long. The repro script doesn't have to be the one you are specifically using now, anything that is slower than it should be (if you can provide a comparison to say, pdo_odbc, that would be good) will help us investigate. I do not know if the dblib drivers are an alternative, but I believe pdo_odbc should work.

Also, I had a look at the dockerfile you linked, please note that this file installs version 13.1 of the ODBC driver but the latest is version 17. See this page for instructions on installing the latest.

[dhazelett]

@rax1606 You can use FreeTDS and ODBC.

(I do not recall if debian requires unixodbc package or not)

~$ sudo apt-get install unixodbc freetds php-odbc

then add a connection option to freetds

; /etc/freetds/freetds.conf
[MyServerConnectionName]
  host = rds.myhost.com
  port = 1433
  tds version = 7.2

Finally ensure this entry exists:

; /etc/odbcinst.ini
[FreeTDS]
Description=ODBC FreeTDS Driver
Driver=/usr/lib64/libtdsodbc.so.0
UsageCount=1

When you try to connect in php/pdo you dsn should be

$pdo = new \PDO('odbc:DRIVER=FreeTDS;SERVERNAME=MyServerConnectionName;', $username, $password)

[sean256]

@rax1606 I'm the user who first reported this issue and reading your comments makes me think your issue may lie elsewhere.

To give you an example, for one of the queries it took 25 minutes to respond.

25 min sounds like a problem with either the query (and it's execution plan), the database or both. Have you run the EXACT same query outside of php and this driver? Ideally in SQL Studio or another tool which does not use these drivers. Either way, giving these devs comparisons of query times in other tools and drivers would be helpful.

[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