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. — ourmandave
– ourmandave, Commented Sep 3, 2018 at 16:46
Also this related answer 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
– ourmandave, Commented Sep 3, 2018 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
– Aquiles Perez, Commented Sep 3, 2018 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
– Thom A ♦, Commented Sep 3, 2018 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
– Aquiles Perez, Commented Sep 3, 2018 at 22:27

KumarHarsh · Accepted Answer · 2018-09-04 03:44:25Z

0

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

answered Sep 4, 2018 at 3:44

KumarHarsh

5,0941 gold badge22 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Query takes long time with php eloquent and sqlserver

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related