[Solved] EF / TPT / linq query to get previous record by ID generates wrong SQL when entity is selected
Looking to automatically optimize YOUR SQL query? Start for free.

EverSQL Database Performance Knowledge Base

EF / TPT / linq query to get previous record by ID generates wrong SQL when entity is selected

Database type:

I did the following tests in LinqPAD:

A. loading just the id of an entity.

(from s in Set<Sale>() 
 where s.ID < 9000000 
 orderby s.ID descending 
 select s.ID).Take(1)

which returns the ID: 8999998 (correct) and optimized (see below)

SELECT
   `Extent1`.`ID`
FROM 
   `Sale` AS `Extent1` 
INNER JOIN 
   `Entity` AS `Extent2` ON `Extent1`.`ID` = `Extent2`.`ID`
WHERE 
   `Extent1`.`ID` < 9000000
ORDER BY 
   `Extent1`.`ID` DESC LIMIT 1

B. loading the whole entity (s.Id become s in the query, there are no other differences)

(from s in Set<Sale>() 
 where s.ID < 9000000 
 orderby s.ID descending 
 select s).Take(1)

which returns the correct result but it does so by loading the whole data in process (see the SQL below / i removed fields to make it shorter)

SELECT
    `Project1`.`C1`, 
    ....
FROM 
    (SELECT
       `Extent1`.`ID`, 
       ..... 
       @gp1 AS `C1`
    FROM 
       `Sale` AS `Extent1` 
    INNER JOIN 
       `CompanyEntity` AS `Extent2` ON `Extent1`.`ID` = `Extent2`.`ID` 
    INNER JOIN 
       `Entity` AS `Extent3` ON `Extent1`.`ID` = `Extent3`.`ID`
    WHERE 
       `Extent1`.`ID` < 9000000) AS `Project1`
ORDER BY 
    `Project1`.`ID` DESC LIMIT 1

As you can see the orderby and limit are moved outside the nested query.

My question is: how to make EF generate a decent SQL in the second case ?

Note: using TPT strategy, EF6, MySql (also tested on SQL Server).

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.
  2. Sort and Limit Before Joining (modified query below): In cases where the joins aren't filtering any rows, it's possible to sort and limit the amount of rows using a subquery in the FROM clause, before applying the joins to all other tables.
Optimal indexes for this query:
ALTER TABLE `Sale` ADD INDEX `sale_idx_id` (`ID`);
The optimized query:
SELECT
        extent1_id 
    FROM
        (SELECT
            `Extent1`.`ID` AS extent1_id 
        FROM
            `Sale` AS `Extent1` 
        WHERE
            `Extent1`.`ID` < 9000000 
        ORDER BY
            `Extent1`.`ID` DESC LIMIT 1) AS `Extent1` 
    INNER JOIN
        `Entity` AS `Extent2` 
            ON `Extent1`.extent1_id = `Extent2`.`ID` 
    WHERE
        1 = 1 LIMIT 1

Related Articles



* original question posted on StackOverflow here.