1

I have a performance question, how entity framework binding of values can affect performance?

Im making a few tests on LINQPad and this code runs extremely fast, and generates a fast query if run directly on the database...

var dataFim = new DateTime(2015,12,12);
var dataInicio = new DateTime(2015,01,01);
long? empresaClienteId = 1081;

 var query = Transacoes
                    .Select(t => new { t.Data, t.Estorno, t.EmpresaClienteId, t.KmFinal, t.KmInicial, t.ValorBruto})
                .Where(t => t.Data.Value >= dataInicio
                            && t.Data.Value < dataFim
                            && t.Estorno == "N"
                            && t.EmpresaClienteId == empresaClienteId
                            && (t.KmFinal - t.KmInicial) < 500)
                .GroupBy(t => new { t.Data.Value.Month, t.Data.Value.Year })
                .OrderBy(t => new { t.Key.Month, t.Key.Year })
                .Select(t => new
                {
                    Mes = t.Key.Month,
                    Ano = t.Key.Year,
                    ValorTotal = t.Sum(r => r.ValorBruto ?? 0),
                    KmPercorridoTotal = t.Sum(r => (r.KmFinal ?? 0) - (r.KmInicial ?? 0))
                });


query.AsNoTracking().Dump();

... But, If I change this variable to var

 var empresaClienteId = 1081;

or long

long empresaClienteId = 1081;

Then suddenly the query takes forever (but not if running direclty on the database)

both the model and the request parameter are non nullable longs, and so is the storage column mapping.

Any Ideas why this happens and how to handle this? Having the long? variable is not desirable since thats a non nullable parameter

PS: Im using Oracle Database and devart

Rafael Loreto
  • 11
  • 3
  • 3
    Check generated SQL (via Entity Framework log or via session browser at Oracle) and its execution plan. – Ulugbek Umirov Jul 29 '15 at 14:57
  • See http://stackoverflow.com/a/21111655/3691688 – touko Jul 29 '15 at 15:00
  • Glimpse (available via nuget) has demystified the sql that was being generated by entity-framework / linq for me. If you don't know of this already, there are also some libraries (SQLFunctions and EntityFunctions) that allow you to more explicitly define how you want your queries to be defined. http://stackoverflow.com/questions/15637317/what-is-the-difference-between-sqlfunctions-and-entityfunctions – AnotherDeveloper Jul 29 '15 at 15:38
  • Thanks for the answers guys, but the generated queries run directly on the database both run very fast, the difference should be only when translating to entities – Rafael Loreto Jul 29 '15 at 15:59

0 Answers0