I am using sqlite query to to find a motor with best set of calibration values using a certain formula. In the original query I am ordering the computed column (to select top 1), this I believe slows down my query the most.
I am trying to make this query as fast as possible. Currently execution time is about 0.300 seconds, and I was able to reduce it to 0.150 seconds by restricting table with Volume BETWEEN (0.9*1.7006359100341797) AND (1.1*1.7006359100341797)
and by using tempTable to do the sorting of computed table (BestMotor
).
What else can I do to improve this more?
Here is my original query:
SELECT T.MotorVendor, ((1/(CalX)+1/(CalY))) AS BestMotor FROM (
SELECT MotorVendor,
(SELECT CalX
FROM MotorTable AS T2
WHERE FileType = 'text' and T2.MotorVendor = Tools.MotorVendor
ORDER BY abs(Volume - 1.7006359100341797)
LIMIT 1
) AS CalX
FROM (SELECT DISTINCT MotorVendor,
FROM MotorTable) AS Tools) AS T
WHERE T.CalX != '' AND T.CalY != ''
ORDER BY BestMotor DESC
LIMIT 1;
And here is query using tempTable to order the computed column (with index on that computed column in tempTable):
DELETE FROM TempTable;
INSERT INTO TempTable SELECT T.MotorVendor, ((1/(CalX)+1/(CalY))) AS BestMotor FROM (
SELECT MotorVendor,
(SELECT CalX
FROM MotorTable AS T2
WHERE FileType = 'text' and T2.MotorVendor = Tools.MotorVendor AND
Volume BETWEEN (0.9*1.7006359100341797) AND (1.1*1.7006359100341797)
ORDER BY abs(Volume - 1.7006359100341797)
LIMIT 1
) AS CalX
FROM (SELECT DISTINCT MotorVendor,
FROM MotorTable) AS Tools) AS T
WHERE T.CalX != '' AND T.CalY != ''
ORDER BY BestMotor DESC
LIMIT 1;
SELECT MotorVendor, BestMotor FROM TempTable ORDER BY BestMotor DESC LIMIT 1;
Aucun commentaire:
Enregistrer un commentaire