vendredi 25 septembre 2015

Optimize SQLite query with ordering by computed column?

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