Laravel sqlsrv query execution time is very slow

Question

I have Microsoft SQL Server database (SQLSRV) and I am trying to execute a query via Laravel DB class (Illuminate\Support\Facades\DB):

DB::select(DB::raw("select * from [table]"));

and Laravel Eloquent:

Items::select('*')->get();

and the execution time is in both (Eloquent & DB class) 1397 milliseconds.

I try to execute the same query using php_pdo_sqlsrv library (php_pdo_sqlsrv_81_ts_x64.dll):

$conn = sqlsrv_connect("192.168.0.100, 51484", $connectionInfo);
$tsql = "select * from [table]";
sqlsrv_query($conn, $tsql, array(), array());

and the execution time is 52 milliseconds.

From the above observation, What do I need to config in Laravel to improve the execution time??

I am using:

php:

PHP 8.1.2 (cli) (built: Jan 19 2022 10:18:23) (ZTS Visual C++ 2019 x64)
Copyright (c) The PHP Group
Zend Engine v4.1.2, Copyright (c) Zend Technologies

php external Libraries:

php_sqlsrv_81_ts_x64.dll
php_pdo_sqlsrv_81_ts_x64.dll

Framework:

Laravel Framework 9.47.0

Framework config: (/config/database.php):

<?php


return [

    'default' => env('DB_CONNECTION', 'sqlsrv'),

    'connections' => [

        ....

        'sqlsrv' => [
            'driver' => 'sqlsrv',
            'url' => env('DATABASE_URL'),
            'host' => env('DB_HOST'),
            'port' => env('DB_PORT'),
            'database' => env('DB_DATABASE'),
            'username' => env('DB_USERNAME'),
            'password' => env('DB_PASSWORD'),
            'charset' => 'utf8',
            'prefix' => '',
            'prefix_indexes' => true,            
            // 'encrypt' => env('DB_ENCRYPT', 'yes'),
            // 'trust_server_certificate' => env('DB_TRUST_SERVER_CERTIFICATE', 'false'),
        ],   

      .....    

    ],
];

Thanks in advance!

score 0 · Answer 1 · answered Jun 29 '23 at 08:43

Database Indexing: Ensure that your database tables have appropriate indexes on the columns involved in your queries. Indexing can significantly speed up query execution by allowing the database engine to quickly locate the required data

Laravel provides tools DB::enableQueryLog() and DB::getQueryLog() methods to capture and analyze query execution details.

Laravel sqlsrv query execution time is very slow

1 Answers1