Bug #74196 Extraneous / unnecessary column output in derived tables (i.e. `Filter1`, etc.)
Submitted: 2 Oct 2014 16:37 Modified: 29 Sep 2015 21:40
Reporter: Ryan Griffith (OCA) Email Updates:
Status: Analyzing Impact on me:
None 
Category:Connector / NET Severity:S3 (Non-critical)
Version:6.9.2 OS:Microsoft Windows
Assigned to: Assigned Account CPU Architecture:Any

[2 Oct 2014 16:37] Ryan Griffith
Description:
Certain queries produce extraneous column output which is creating a compounding performance issue for me.

MySQL is not performing well when selecting from a derived / materialized table. This is exacerbated by the fact that the derived table contains ALL the columns from ALL tables within the projection. An example is below:

SELECT 
    `Distinct1`.`client_id`
FROM
    (SELECT DISTINCT
        `Filter1`.`client_id`
    FROM
        (SELECT 
        `Extent1`.`id`,
            `Extent1`.`tenure_id`,
            `Extent1`.`task_id`,
            `Extent1`.`client_id`,
            `Extent1`.`entry_date`,
            `Extent1`.`active`,
            /* Extent1 Columns omitted for brevity.... */

            `Extent2`.`div_id`,
            /* Extent2 Columns omitted for brevity.... */

            `Extent3`.`div_id` AS `DIV_ID1`
            /* Extent3 Columns omitted for brevity.... */

    FROM
        `time_entry` AS `Extent1`
    INNER JOIN `tenure` AS `Extent2` ON `Extent1`.`tenure_id` = `Extent2`.`id`
    INNER JOIN `tenure` AS `Extent3` ON `Extent1`.`tenure_id` = `Extent3`.`id`
    WHERE
        `Extent2`.`div_id` IN (2 , 3)) AS `Filter1`
    WHERE
        ((`Filter1`.`entry_date` >= @p__linq__0)
            AND (`Filter1`.`entry_date` <= @p__linq__1))
            AND (`Filter1`.`DIV_ID1` IS NOT NULL)) AS `Distinct1`

---------------------------------------------------
The exact same query performed against MS SQL / Microsoft's implementation is vastly different:
---------------------------------------------------
SELECT 
    [Distinct1].[client_id] AS [client_id]
    FROM ( SELECT DISTINCT 
        [Filter1].[client_id1] AS [client_id]
        FROM ( SELECT [Extent1].[client_id] AS [client_id1], [Extent1].[entry_date] AS [entry_date], [Extent3].[div_id] AS [div_id1]
            FROM   [itc].[time_entry] AS [Extent1]
            INNER JOIN [itc].[tenure] AS [Extent2] ON [Extent1].[tenure_id] = [Extent2].[id]
            INNER JOIN [itc].[tenure] AS [Extent3] ON [Extent1].[tenure_id] = [Extent3].[id]
            WHERE [Extent2].[div_id] IN (cast(2 as bigint), cast(3 as bigint))
        )  AS [Filter1]
        WHERE ([Filter1].[entry_date] >= @p__linq__0) AND ([Filter1].[entry_date] <= @p__linq__1) AND ([Filter1].[div_id1] IS NOT NULL)
    )  AS [Distinct1]

How to repeat:
Any question that produces a derived filter table.

Suggested fix:
Can we glean on some of the logic in Entity Framework / SQL Server's implementation of the the DbExpressionVisitor to figure out how to clean up this over-projection? I've dug deep into the code, but do not have enough domain knowledge to feel that I could tackle this as quickly as a core contributor.
[9 Oct 2014 15:35] Ryan Griffith
Hi Fernando, do you know if this is something that will be worked on? I'm trying to figure out what direction to go with my software project because this issue is creating significant performance issues.

Thank you for your consideration
[9 Oct 2014 16:00] Fernando Gonzalez.Sanchez
Hi Ryan, 

We are currently working on a few bugs in EF Sql Generation, including queries that are plain broken.

We are likely to release a 6.9.5 in a few weeks, but the fix for this one will not make it in that particular release (but a later one maybe).

Thanks
[9 Oct 2014 16:02] Ryan Griffith
Thanks Fernando, I appreciate all your hard work.

Any word as to when you're going to get a source control system? Its my understand the only way to contribute is to send diffs.
[29 Sep 2015 21:40] Ryan Griffith
Hello,

Just checking back into this bug. This causes issues for the query performance (significant issues) causing me to have to place all my common queries into flattened views so I can keep performance up.

Thanks Fernando.
[29 Sep 2015 22:30] Fernando Gonzalez.Sanchez
Hi Ryan, I moved away from Connector/NET project, another member of the team will check it.

Thanks.