Count very slow

Discussion of open issues, suggestions and bugs regarding Entity Framework support in ADO.NET Data providers
Post Reply
rick.duarte
Posts: 35
Joined: Fri 23 Jan 2009 23:07
Location: Rio de Janeiro, Brazil

Count very slow

Post by rick.duarte » Mon 21 Mar 2011 22:05

I'm using dotConnect for Oracle 6.10.121 to execute a query on Oracle 10g with Entity Developer that is very slow.
This code took 1 minute and 25 seconds:

Code: Select all

var w1 = ctx.DadoObjetos
    .Where(q => q.CdTipoRegistro == "JDS" && q.DtGeracao >= DtInicio && q.DtGeracao  new { q.CdLPAR, q.CdObjeto });
result.Count = w1.Count();
I got the generated query using ToTraceString():

Code: Select all

SELECT "GroupBy1".a1 AS c1
  FROM (
    SELECT COUNT (1) AS a1
    FROM (
      SELECT DISTINCT 
        "Extent1".lpar_cd AS lpar_cd,
        "Extent1".os_cd AS os_cd
      FROM scdrd.dado_obj "Extent1"
      WHERE (("Extent1".do_tp_reg = 'JDS')
      AND ("Extent1".do_dta_gera_reg >= :p__linq__0))
      AND ("Extent1".do_dta_gera_reg < :p__linq__1)
  ) "Distinct1"
) "GroupBy1"
The generated query took 1 second on SQL*Plus and 2 seconds using ExecuteStoreCommand.

Do you have any suggestion?

Best regards,

Henrique

AndreyR
Devart Team
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Post by AndreyR » Tue 22 Mar 2011 15:42

Is it a first query in your project?
Try adding Pregenerated Views to your model, this speeds up the execution greatly.
If you are using Devart Entity model, go to the Mode Properties (double-click the model surface), and enable the View Generation check box.
If you are using Microsoft Entity model, take a look at this article.

Post Reply