SELECT WITH ORDER BY SLOW

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for PostgreSQL
Post Reply
rogerio.bassete
Posts: 15
Joined: Sat 05 Aug 2006 20:55
Location: Brazil

SELECT WITH ORDER BY SLOW

Post by 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

Shalex
Site Admin
Posts: 8241
Joined: Thu 14 Aug 2008 12:44

Re: SELECT WITH ORDER BY SLOW

Post by 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.

rogerio.bassete
Posts: 15
Joined: Sat 05 Aug 2006 20:55
Location: Brazil

Re: SELECT WITH ORDER BY SLOW

Post by rogerio.bassete » Mon 01 Aug 2016 20:32

Shalex,

Some time? I need urgent!

Shalex
Site Admin
Posts: 8241
Joined: Thu 14 Aug 2008 12:44

Re: SELECT WITH ORDER BY SLOW

Post by Shalex » Tue 02 Aug 2016 12:56

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

rogerio.bassete
Posts: 15
Joined: Sat 05 Aug 2006 20:55
Location: Brazil

Re: SELECT WITH ORDER BY SLOW

Post by rogerio.bassete » Wed 24 Aug 2016 12:28

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

Shalex
Site Admin
Posts: 8241
Joined: Thu 14 Aug 2008 12:44

Re: SELECT WITH ORDER BY SLOW

Post by 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.

Post Reply