CODEWITHSUNDEEP
sqliteviewdatabase-performance
alexandernst
alexandernst

Reputation: 15089

Performance penalties from sqlite views

I have a select query, as the following:

select distinct
  ...some fields

from
  table_a,
  table_b,
  table_c,
  ...more tables

where
  table_a.id = table_b.id and
  ... (rest of the tables) and
  some_field_that_i_care_about = 42

order by
  my_field

If I run that query as-is, it takes around half a second to get me the results that I expect.

Nevertheless, if I convert that same query to a view (excluding the some_field_that_i_care_about = 42 condition) and then I run:

select *
from the_view
where some_field_that_i_care_about = 42

the query takes around 40 seconds to return the same data.

Why is that happening?

Upvotes: 3

Views: 982

Answers (1)

CL.
CL.

Reputation: 180070

Views are just syntactic sugar for subqueries. Such subqueries usually do not result in a performance penalty because they can be flattened, i.e., merged into the outer query.

However, in this case, the ORDER BY prevents the flattening.

Just drop the ORDER BY from the view; the ordering of the subquery is ignored by the outer query anyway. (If you want the result to be sorted, you must use ORDER BY in the outermost query.)

Upvotes: 4

Related Questions