SELECT WITH ORDER BY SLOW

SELECT WITH ORDER BY SLOW

Postby rogerio.bassete » Wed 27 Jul 2016 14:50

Hello,

I have a performance problem in a SQL statement generated for your .Net driver. From what I analyzed, it occurs due to an improper sub-select.

Any solution?

WITH OUT ORDER BY
Code: Select all
var fonteDeDados = Contexto.Set<Mercadoria>()
   .AsNoTracking()
   .Where(parametros.Expressao)
   .Take(10)
   .Select(s => new
   {
       s.Id
   })
   .ToList();


Code: Select all
SELECT
"Extent1".id
FROM org000001.mercadoria AS "Extent1"
INNER JOIN org000001.mercadoriabase AS "Extent2" ON "Extent1".idbase = "Extent2".id
WHERE "Extent1".ativo = true AND
      "Extent2".idmercadoriaespecie = 0 AND
      "Extent1".idempresa = 4 AND
      (public.unaccent(upper("Extent2".codigo)) LIKE public.unaccent(upper('OLEO%')) OR public.unaccent(upper("Extent2".descricao)) LIKE public.unaccent(upper('OLEO%')))
LIMIT 10;


WITH ORDER BY
Code: Select all
var fonteDeDados = Contexto.Set<Mercadoria>()
   .AsNoTracking()
   .Where(parametros.Expressao)
   .OrderBy(o => o.Id)
   .Take(10)
   .Select(s => new
   {
       s.Id
   })
   .ToList();


Code: Select all
SELECT
"Project1".id
FROM ( SELECT
   "Extent1".id
   FROM  org000001.mercadoria AS "Extent1"
   INNER JOIN org000001.mercadoriabase AS "Extent2" ON "Extent1".idbase = "Extent2".id
   WHERE "Extent1".ativo = true AND
         "Extent2".idmercadoriaespecie = 0 AND
              "Extent1".idempresa = 4 AND
              (public.unaccent(upper("Extent2".codigo)) LIKE public.unaccent(upper('OLEO%')) OR public.unaccent(upper("Extent2".descricao)) LIKE public.unaccent(upper('OLEO%')))
)  AS "Project1"
ORDER BY "Project1".id ASC
LIMIT 10


Is possible generate this form?

Code: Select all
SELECT
"Extent1".id
FROM org000001.mercadoria AS "Extent1"
INNER JOIN org000001.mercadoriabase AS "Extent2" ON "Extent1".idbase = "Extent2".id
WHERE "Extent1".ativo = true AND
      "Extent2".idmercadoriaespecie = 0 AND
      "Extent1".idempresa = 4 AND
      (public.unaccent(upper("Extent2".codigo)) LIKE public.unaccent(upper('OLEO%')) OR public.unaccent(upper("Extent2".descricao)) LIKE public.unaccent(upper('OLEO%')))
ORDER BY "Extent1".id ASC
LIMIT 10;


-- Look this case and solution for ORACLE.
https://bugs.mysql.com/bug.php?id=75272
rogerio.bassete
 
Posts: 15
Joined: Sat 05 Aug 2006 20:55
Location: Brazil

Re: SELECT WITH ORDER BY SLOW

Postby Shalex » Thu 28 Jul 2016 12:34

Thank you for your report. We will investigate the issue with extra subselect when using ordering and notify you about the result.
Shalex
Devart Team
 
Posts: 7780
Joined: Thu 14 Aug 2008 12:44

Re: SELECT WITH ORDER BY SLOW

Postby rogerio.bassete » Mon 01 Aug 2016 20:32

Shalex,

Some time? I need urgent!
rogerio.bassete
 
Posts: 15
Joined: Sat 05 Aug 2006 20:55
Location: Brazil

Re: SELECT WITH ORDER BY SLOW

Postby Shalex » Tue 02 Aug 2016 12:56

Sorry, but we cannot provide any timeframe at the moment.
Shalex
Devart Team
 
Posts: 7780
Joined: Thu 14 Aug 2008 12:44

Re: SELECT WITH ORDER BY SLOW

Postby rogerio.bassete » Wed 24 Aug 2016 12:28

Is there any way to get around the "BUG" until the solution is available? Urgent!
rogerio.bassete
 
Posts: 15
Joined: Sat 05 Aug 2006 20:55
Location: Brazil

Re: SELECT WITH ORDER BY SLOW

Postby Shalex » Fri 26 Aug 2016 15:10

We do our best to solve the issue as soon as possible. We will notify you when the fix is implemented.
Shalex
Devart Team
 
Posts: 7780
Joined: Thu 14 Aug 2008 12:44


Return to dotConnect for PostgreSQL