You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
The problem i am facing since .NET 8.0 and Pomelo 8.0 is that the same LINQ query outputs different SQL. The latest SQL output is a query that's much worse in terms of performance than the original one.
I cannot see any breaking changes in EF 8.0 that could explain this, is there something that has changed translation wise in Pomelo.MySql?
LINQ:
return (from g in (from item in (from c in _context.GComprobanteRecibido
where (areaId == null || c.GLiquidacionAreaId == areaId)
&& (areasOcultas == null || !areasOcultas.Contains(c.GLiquidacionAreaId))
select new
{
Area = c.GLiquidacionAreaId,
Monto = c.ComprobanteMonto * c.GComprobanteTipo.Signo,
Periodo = c.ComprobantesFecha.Year * 100 + c.ComprobantesFecha.Month,
})
where (periodo == null || item.Periodo == periodo)
group item by new { item.Area, item.Periodo } into grouped
select new
{
Monto = grouped.Sum(x => x.Monto),
Periodo = grouped.Key.Periodo,
Area = grouped.Key.Area,
Count = grouped.Count()
})
join area in _context.GLiquidacionArea
on g.Area equals area.Id into areas
from area in areas.DefaultIfEmpty()
select new ComprobantesByPeriodoAndArea
{
Monto = g.Monto,
GLiquidacionArea = area,
Periodo = g.Periodo,
Count = g.Count
});
Output before .NET 8 Pomelo 8:
SELECT `t`.`Monto`, `g1`.`id`, `g1`.`acepta_asociacion_cuil_pedido_ok`, `g1`.`acepta_auditoria_facturas`, `g1`.`acepta_carga_facturas`, `g1`.`activo`, `g1`.`admite_trazabilidad`, `g1`.`creado`, `g1`.`facturado_orden_de_activo`, `g1`.`imputa_en_liquidaciones`, `g1`.`is_discapacidad`, `g1`.`liquida_solo_gerenciadores_virtuales`, `g1`.`nombre`, `g1`.`pedido_externo`, `g1`.`plataforma_hospitales`, `t`.`Periodo`, `t`.`Count`
FROM (
SELECT COALESCE(SUM(`g`.`comprobante_monto` * CAST(`g0`.`signo` AS decimal(65,30))), 0.0) AS `Monto`, (EXTRACT(year FROM `g`.`comprobante_fecha`) * 100) + EXTRACT(month FROM `g`.`comprobante_fecha`) AS `Periodo`, `g`.`g_liquidacion_areaId` AS `Area`, COUNT(*) AS `Count`
FROM `g_comprobante_recibido` AS `g`
INNER JOIN `g_comprobante_tipo` AS `g0` ON `g`.`g_comprobante_tipoId` = `g0`.`id`
GROUP BY `g`.`g_liquidacion_areaId`, (EXTRACT(year FROM `g`.`comprobante_fecha`) * 100) + EXTRACT(month FROM `g`.`comprobante_fecha`)
) AS `t`
LEFT JOIN `g_liquidacion_area` AS `g1` ON `t`.`Area` = `g1`.`id`
Output after .NET 8
SELECT `t0`.`Monto`, `g2`.`id`, `g2`.`acepta_asociacion_cuil_pedido_ok`, `g2`.`acepta_auditoria_facturas`, `g2`.`acepta_carga_facturas`, `g2`.`activo`, `g2`.`admite_trazabilidad`, `g2`.`creado`, `g2`.`facturado_orden_de_activo`, `g2`.`imputa_en_liquidaciones`, `g2`.`is_discapacidad`, `g2`.`liquida_solo_gerenciadores_virtuales`, `g2`.`nombre`, `g2`.`pedido_externo`, `g2`.`plataforma_hospitales`, `t0`.`Periodo`, `t0`.`Count`
FROM (
SELECT (
SELECT COALESCE(SUM(`t1`.`comprobante_monto` * CAST(`g0`.`signo` AS decimal(65,30))), 0.0)
FROM (
SELECT `g1`.`id`, `g1`.`archivado`, `g1`.`CUIT`, `g1`.`cae_invalidado_mensaje`, `g1`.`cae_validado`, `g1`.`cargado_comprobantes_masivos`, `g1`.`cargado_desde_mis_comprobantes`, `g1`.`cargado_modulo_hospitales`, `g1`.`cargado_por_dev_ok`, `g1`.`codigo_sss`, `g1`.`comentario`, `g1`.`comentario_privado`, `g1`.`comentario_publico`, `g1`.`comprobante_cae`, `g1`.`comprobante_link`, `g1`.`comprobante_monto`, `g1`.`comprobante_numero`, `g1`.`comprobante_punto_de_venta`, `g1`.`comprobante_fecha`, `g1`.`confirmado`, `g1`.`confirmado_fecha`, `g1`.`confirmado_userId`, `g1`.`creado`, `g1`.`exportado_s3`, `g1`.`fecha_vencimiento`, `g1`.`g_auditoriaId`, `g1`.`g_comprobante_tipoId`, `g1`.`g_gerenciadorId`, `g1`.`g_integracion_expedienteId`, `g1`.`g_liquidacion_areaId`, `g1`.`hospital_nombre`, `g1`.`i_integracion_dr_envio_detalleId`, `g1`.`integracion_periodo_prestacion`, `g1`.`leidoQR`, `g1`.`nro_liquidacion`, `g1`.`recibido`, `g1`.`referencia_externa`, `g1`.`refes`, `g1`.`sur_expedienteId`, `g1`.`s_userId`, (EXTRACT(year FROM `g1`.`comprobante_fecha`) * 100) + EXTRACT(month FROM `g1`.`comprobante_fecha`) AS `Periodo`
FROM `g_comprobante_recibido` AS `g1`
) AS `t1`
INNER JOIN `g_comprobante_tipo` AS `g0` ON `t1`.`g_comprobante_tipoId` = `g0`.`id`
WHERE (`t`.`g_liquidacion_areaId` = `t1`.`g_liquidacion_areaId`) AND ((`t`.`Periodo` = `t1`.`Periodo`) OR (`t`.`Periodo` IS NULL AND (`t1`.`Periodo` IS NULL)))) AS `Monto`, `t`.`Periodo`, `t`.`g_liquidacion_areaId` AS `Area`, COUNT(*) AS `Count`
FROM (
SELECT `g`.`g_liquidacion_areaId`, (EXTRACT(year FROM `g`.`comprobante_fecha`) * 100) + EXTRACT(month FROM `g`.`comprobante_fecha`) AS `Periodo`
FROM `g_comprobante_recibido` AS `g`
) AS `t`
GROUP BY `t`.`g_liquidacion_areaId`, `t`.`Periodo`
) AS `t0`
LEFT JOIN `g_liquidacion_area` AS `g2` ON `t0`.`Area` = `g2`.`id`
I cannot see any breaking changes in EF 8.0 that could explain this, is there something that has changed translation wise in Pomelo.MySql?
There have been many translation changes between Pomelo 7.0.x and 8.0.x and EF Core 7.0.x and 8.0.x. The issue you are experiences does not seem to be a breaking change (since it still works, just slower, if I understand you correctly). Most likely, it is a translation change in EF Core, but we can't say for sure.
@robherman If you want us to track down the changes ones responsible for your complex query in question, please post an MRE, so we can reproduce the exact query on our end.
Also, please update the OP with the exact MySQL version you are using, post the Pomelo and EF Core version that you were using before the upgrade to Pomelo 8.0.2 and the average duration of the query in ms before and after the upgrade.
I cannot see any breaking changes in EF 8.0 that could explain this, is there something that has changed translation wise in Pomelo.MySql?
There have been many translation changes between Pomelo 7.0.x and 8.0.x and EF Core 7.0.x and 8.0.x. The issue you are experiences does not seem to be a breaking change (since it still works, just slower, if I understand you correctly). Most likely, it is a translation change in EF Core, but we can't say for sure.
@robherman If you want us to track down the changes ones responsible for your complex query in question, please post an MRE, so we can reproduce the exact query on our end.
Also, please update the OP with the exact MySQL version you are using, post the Pomelo and EF Core version that you were using before the upgrade to Pomelo 8.0.2 and the average duration of the query in ms before and after the upgrade.
The issue
The problem i am facing since .NET 8.0 and Pomelo 8.0 is that the same LINQ query outputs different SQL. The latest SQL output is a query that's much worse in terms of performance than the original one.
I cannot see any breaking changes in EF 8.0 that could explain this, is there something that has changed translation wise in Pomelo.MySql?
LINQ:
Output before .NET 8 Pomelo 8:
Output after .NET 8
Further technical details
MySQL version: 8
Operating system: Ubuntu 22.04
Pomelo.EntityFrameworkCore.MySql version: 8.0.2
Microsoft.AspNetCore.App version: 8.0.2
The text was updated successfully, but these errors were encountered: