search

microsoft / msphpsql

Microsoft Drivers for PHP for SQL Server
MIT License
1.78k stars 371 forks source link

Cakephp2 Application takes longer time to fetch data from sql server database resulting php memory error #1481

Closed andola-dev closed 2 weeks ago

andola-dev commented 9 months ago

PHP version PHP 7.2

PHP SQLSRV or PDO_SQLSRV version
PHP PDO_SQLSRV 5.6.1

Microsoft ODBC Driver version 17

SQL Server version 2019

Client operating system Windows 2019 Server Edition

Problem description I have a cakephp2 application which runs on PHP 7.2. This application is hosted on a Windows 2019 server edition through the XAMPP. This application connects to the MSSQL server 2019 edition which is hosted on a different Windows machine.

I am using the pdo_sqlsrv 5.6.1 and sqlsrv 5.6.1 to connect to the MSSQL server from the cakephp2 application

However, the application takes longer time to fetch data from sql server database resulting php memory error. When I looked into the SQL server profiler, I found that there are lots of connections created. 

### Tasks
v-makouz commented 9 months ago

What is the memory error? Also do you have a repro script?

andola-dev commented 9 months ago

What is the memory error? Also do you have a repro script?

Thanks @v-makouz. The memory error I am getting is Fatal error: Allowed memory size of 536870912bytes exhausted

I have just added the sqlsrv extension to php and copied the dll files to php ext folder Also changed the database driver from mysql to mssql server Then when I tried to connect to the mssql from the cakephp2 application, My application requires to fetch data from multiple tables During this fetch I found the application is loading very slow and when I looked into the sql profiler I found lot of db connections are creating

v-makouz commented 9 months ago

Do you have a sample code I can use to try to reproduce this?