SELECT WITH ORDER BY SLOW

[rogerio.bassete]

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

[Shalex]

Thank you for your report. We will investigate the issue with extra subselect when using ordering and notify you about the result.

[rogerio.bassete]

Shalex,

Some time? I need urgent!

[Shalex]

Sorry, but we cannot provide any timeframe at the moment.

[rogerio.bassete]

Is there any way to get around the "BUG" until the solution is available? Urgent!

[Shalex]

We do our best to solve the issue as soon as possible. We will notify you when the fix is implemented.