Here is my code to create the view. You might be able to infer:
* that measurements are made at different locations (about 600)
* that different kinds of measurements are made (about 5)
* that the measurements are repeated several times (about 5000)
This is all for one experiment and the database will eventually hold data for several experiments.
-- This is for work so all names have been changed before posting CREATE VIEW Summary AS SELECT positionID, measurementType, max(value) max, min(value) min, (max(value) - min(value)) range, avg(value) mean FROM ExperimentalResults GROUP BY positionID, measurementType; -- and some indexes to speed things along CREATE INDEX ExperimentalResults_positionID ON ExperimentalResults(positionID); CREATE INDEX ExperimentalResults_SummaryScore ON ExperimentalResults(positionID, measurementType);
So far so good. However, when I ask for the summary for a single position like below, it takes a long time.
SELECT * FROM Summary WHERE positionID = 300;
I can't prove it but I think SQLite is computing the whole view in order to choose which rows. When I run the query above from the command line: there is a pause, then some rows are printed, then there is another pause. If instead I run the query below, the pauses disappear:
SELECT positionID, measurementType, max(value) 'max', min(value) 'min', ('max' - 'min') range, avg(value) mean FROM ExperimentalResults WHERE positionID = 300 GROUP BY positionID, measurementType;
Thanks for reading this far. I have some specific questions about how to proceed:
1. Have I missed something obvious that is killing the view's performance (e.g. are my indexes the correct ones to make)
2. Is this a case of poor optimisation within SQLite or would I have the same problem in with other DBMS?
3. What are your thought on making Summary a table instead of a view? I might get the performance I need but it seems like a nasty hack.
4. Do you have any other suggestions to help me?
Thanks again for reading.