Query takes long time with php eloquent and sqlserver

Question

I am facing some problems using SQL Server with PHP, I have this query in my code.

  Api::$eloquent->connection('cis')->select("
    SELECT
        SUM (DET.Peso_Salida) AS total,
      CLI.[Codigo Grupo] AS grupo
    FROM
        BNF2.SECO_DEVOLUCION_DETALLE AS DET
    INNER JOIN BNF2.SECO_DEVOLUCION_MAESTRO AS MAS ON DET.Correlativo_Maestro = MAS.Correlativo_Maestro AND MAS.Codigo_Cosecha=:co1
    INNER JOIN BNF.DETALLES_GENERALES_PESOS_2 AS REC ON DET.Numero_Lote=REC.Numero_Lote AND REC.Codigo_Cosecha=:co2
    INNER JOIN BNF.V_CLIENTES_CON_GRUPO AS CLI ON REC.Codigo_Entidad_Cliente_Actual= CLI.Codigo
    GROUP BY CLI.[Codigo Grupo]  
    ", [':co1' => $this->cosecha, ':co2' => $this->cosecha]);

The query is taking 15s and the SQL Server is having 100% CPU, now if I remove the parameters in the code, query takes 103ms very fast, So I don't understand why parameters are slowing down the query

Api::$eloquent->connection('cis')->select("
SELECT
    SUM (DET.Peso_Salida) AS total,
  CLI.[Codigo Grupo] AS grupo
FROM
    BNF2.SECO_DEVOLUCION_DETALLE AS DET
INNER JOIN BNF2.SECO_DEVOLUCION_MAESTRO AS MAS ON DET.Correlativo_Maestro = MAS.Correlativo_Maestro AND MAS.Codigo_Cosecha=9
INNER JOIN BNF.DETALLES_GENERALES_PESOS_2 AS REC ON DET.Numero_Lote=REC.Numero_Lote AND REC.Codigo_Cosecha=9
INNER JOIN BNF.V_CLIENTES_CON_GRUPO AS CLI ON REC.Codigo_Entidad_Cliente_Actual= CLI.Codigo
GROUP BY CLI.[Codigo Grupo]
", []);

This run very well, any Idea?

Can't know the exact problem, but [here's a bunch of answers about SQL Server becoming slow when parameters were added to the query.](https://stackoverflow.com/questions/4459425/sql-server-query-fast-with-literal-but-slow-with-variable) — ourmandave, Sep 03 '18 at 16:46
Also [this related answer](https://stackoverflow.com/a/443362/3585500) where the solution was to `SET ANSI_NULLS ON` because once you add variable parameters SQL Server has to worry about nulls in joins. — ourmandave, Sep 03 '18 at 16:50
Note: ANSI_NULLS OFF is deprecated and considered a bad practice my partial solution was validate the parameter and cocat it in the string of the query — Aquiles Perez, Sep 03 '18 at 17:01
@AguilesPerez brings a very important point here. The documentation explicitly stated using `ANSI_NULLS OFF` will generate an error in the future. — Thom A, Sep 03 '18 at 19:02
as a partial solution I use "SELECT ... WHERE Codigo_Cosecha=".$cosecha; but $cosecha is validated before as int to prevent errors — Aquiles Perez, Sep 03 '18 at 22:27

score 0 · Answer 1 · answered Sep 04 '18 at 03:44

You should be using parameterised query or Store Procedure.

There can be several reason for slow performance of query.That only Table info (including data type and length) and Query Plan can only reveal.

Index being one of them.

Since your question is like "when using variable then query is slow. Query perform ok with constant value"

We discuss similar thing few days back.

Variables make query performance worse

Query takes long time with php eloquent and sqlserver

1 Answers1