2 Replies - 465 Views - Last Post: 30 January 2014 - 09:10 AM Rate Topic: -----

#1 cfoley  Icon User is online

  • Cabbage
  • member icon

Reputation: 1954
  • View blog
  • Posts: 4,055
  • Joined: 11-December 07

SQLite performance problem with views

Posted 30 January 2014 - 05:57 AM

I'm using SQLite to help organise and analyse a dataset. I have a table with all the measurements and I want to create a view with max, min, mean, etc... Once this is done, I want to do several further calculations based on this summary data. (comparing experiments to their controls, contrasting the results of different conditions to each other, etc...)

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.

Is This A Good Question/Topic? 0
  • +

Replies To: SQLite performance problem with views

#2 baavgai  Icon User is online

  • Dreaming Coder
  • member icon

Reputation: 5801
  • View blog
  • Posts: 12,640
  • Joined: 16-October 07

Re: SQLite performance problem with views

Posted 30 January 2014 - 07:32 AM

I think you nailed the problem, actually. "I can't prove it but I think SQLite is computing the whole view in order to choose which rows."

You need to stop thinking of SQLite as a database "server." There is no middle tier imposing optimized heuristics, data caches, usage statistics, etc. No, this is just you, a binary file, and a logic engine that allows the data in that file to be accessed via SQL.

To that end, if you plan querying against a view, you'll doubtless be better off going with your second try and binding a variable against a query that takes a parameter.
Was This Post Helpful? 1
  • +
  • -

#3 cfoley  Icon User is online

  • Cabbage
  • member icon

Reputation: 1954
  • View blog
  • Posts: 4,055
  • Joined: 11-December 07

Re: SQLite performance problem with views

Posted 30 January 2014 - 09:10 AM

OK, thanks for the comments. I might fire up Oracle for the sake of comparison but I'll probably end up executing larger queries instead of creating views.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1