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.
The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:
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`);
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