[Solved] Replace Subselect for something more efficient

EverSQL Database Performance Knowledge Base

Replace Subselect for something more efficient

I have this query which takes a long time, partly because the number of records in the table excedd 500 000 records, but the join I have to use slows it down quite a lot, at least to my beliefs

SELECT TOP (10) PERCENT H1.DateCompteur, CASE WHEN (h1.cSortie - h2.cSortie > 0)         
THEN h1.cSortie - h2.cSortie ELSE 0 END AS Compte, H1.IdMachine
FROM dbo.T_HistoriqueCompteur AS H1 INNER JOIN
     dbo.T_HistoriqueCompteur AS H2 ON H1.IdMachine = H2.IdMachine AND H2.DateCompteur =
     (SELECT MAX(DateCompteur) AS Expr1
        FROM dbo.T_HistoriqueCompteur AS HS
       WHERE (DateCompteur < H1.DateCompteur) AND (H1.IdMachine = IdMachine))
ORDER BY H1.DateCompteur DESC

The order by is important since I need only the most recent informations. I tried using the ID field in my sub select since they are ordred by date but could not detect any significant improvement.

SELECT TOP (10) PERCENT H1.DateCompteur, CASE WHEN (h1.cSortie - h2.cSortie > 0)         
THEN h1.cSortie - h2.cSortie ELSE 0 END AS Compte, H1.IdMachine
FROM dbo.T_HistoriqueCompteur AS H1 INNER JOIN
     dbo.T_HistoriqueCompteur AS H2 ON H1.IdMachine = H2.IdMachine AND H2.ID =
     (SELECT MAX(ID) AS Expr1
        FROM dbo.T_HistoriqueCompteur AS HS
       WHERE (ID < H1.ID) AND (H1.IdMachine = IdMachine))
ORDER BY H1.DateCompteur DESC

the table I use look a little like this (I got much more columns but they are unused in this query).

ID bigint
IdMachine bigint
cSortie bigint
DateCompteur datetime

I think that if I could get rid of the sub select, my query would run much faster but I can't really find a way to do so. What I really want to do is to find the previous row with the same IdMachine so that I can calculate the difference between the two cSortie values. The case in the query is because something it's reseted to 0 and in this case, I want to return 0 instead of a negative value.

So my question is this : Can I do better than what I already have ??? I plan to put this in a view if that makes a difference.

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. Avoid Correlated Subqueries (query line: 13): A correlated subquery is a subquery that contains a reference (column: DateCompteur) to a table that also appears in the outer query. Usually correlated queries can be rewritten with a join clause, which is the best practice. The database optimizer handles joins much better than correlated subqueries. Therefore, rephrasing the query with a join will allow the optimizer to use the most efficient execution plan for the query.
  2. 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.
Optimal indexes for this query:
ALTER TABLE `T_HistoriqueCompteur` ADD INDEX `t_historiquecompte_idx_idmachine_datecompteur` (`IdMachine`,`DateCompteur`);
ALTER TABLE `T_HistoriqueCompteur` ADD INDEX `t_historiquecompte_idx_idmachine_datecompteur_1` (`IdMachine`,`DateCompteur`);
The optimized query:
SELECT
        TOP (10) PERCENT H1.DateCompteur,
        CASE 
            WHEN (h1.cSortie - h2.cSortie > 0) THEN h1.cSortie - h2.cSortie 
            ELSE 0 END AS Compte,
H1.IdMachine 
FROM
dbo.T_HistoriqueCompteur AS H1 
INNER JOIN
dbo.T_HistoriqueCompteur AS H2 
    ON H1.IdMachine = H2.IdMachine 
    AND H2.DateCompteur = (
        SELECT
            MAX(HS.DateCompteur) AS Expr1 
    FROM
        dbo.T_HistoriqueCompteur AS HS 
    WHERE
        (
            HS.DateCompteur < H1.DateCompteur
        ) 
        AND (
            H1.IdMachine = H1.IdMachine
        )
) 
ORDER BY
H1.DateCompteur DESC

Related Articles



* original question posted on StackOverflow here.