[sqlite] Performance issue with CTE

[Philippe Riand]

Just to let you know, the solution using SELECT * FROM (query with 
offset/limit) works perfectly well. Thanks a lot for the suggestion!

[sqlite] Performance issue with CTE

[Philippe Riand]

I think the doc is right. I overcame the problem by using a construct like:
SELECT field1, field2? WHERE PKEY IN (SELECT PKEY ? WHERE  OFFSET n LIMIT 
m)
That executes a sub query.

But your solution looks actually better, as it is:
SELECT * FROM  (SELECT field1, field2? WHERE  OFFSET n LIMIT m)

I?ll try it. Thanks for the suggestion.

[sqlite] Performance issue with CTE

[Philippe Riand]

Thanks. I know about the technique your mentioned, but the point is not about 
the use of offset or not. The same issue will happen but using a key.
See my other reply above.

[sqlite] Performance issue with CTE

[Kees Nuyt]

On Thu, 1 Oct 2015 13:40:23 +0200, Clemens Ladisch
 wrote:

> OFFSET is inefficient because the database still has to compute all the
> rows before skipping over them.
>
> To do paging, remember the first and last date values on the page, and
> for the previous/next page, just continue from there:
>
>  SELECT ...
>  FROM MyTable
>  WHERE date > :LastDateOnPreviousPage
>  ORDER BY date
>  LIMIT 25;
>
> (If dates are not unique, you have to use more columns.)

Indeed. More info on this technique:
http://sqlite.org/cvstrac/wiki?p=ScrollingCursor 
(page is obsolete but still works)

-- 
Regards,

Kees Nuyt

[sqlite] Performance issue with CTE

[Clemens Ladisch]

Philippe Riand wrote:
> I have a table with 500,000+ records. The table has a date column,
> that I?m using to sort my queries (the columns has an index). Simple
> queries on the table work very well, using ORDER BY, LIMIT & OFFSET.
> I?m actually extracting ?pages? of rows that I?m displaying in a web
> page.

OFFSET is inefficient because the database still has to compute all the
rows before skipping over them.

To do paging, remember the first and last date values on the page, and
for the previous/next page, just continue from there:

  SELECT ...
  FROM MyTable
  WHERE date > :LastDateOnPreviousPage
  ORDER BY date
  LIMIT 25;

(If dates are not unique, you have to use more columns.)

> Now, instead of a simple select, I?d like to execute a recursive query
> using CTE, because the rows are organized in an hierarchy. And I?m only
> interested by a page, let's say the first n root records.

What exactly do you mean with the last sentence?  Are you implying that
the page contains all children, regardless of how many there are?


Regards,
Clemens

[sqlite] Performance issue with CTE

[E.Pasma]

Op 1 okt 2015, om 04:10 heeft Philippe Riand wrote:

> I have a table with 500,000+ records. The table has a date column,  
> that I?m using to sort my queries (the columns has an index). Simple  
> queries on the table work very well, using ORDER BY, LIMIT & OFFSET.  
> I?m actually extracting ?pages? of rows that I?m displaying in a web  
> page. Great!.
>
> Now, instead of a simple select, I?d like to execute a recursive  
> query using CTE, because the rows are organized in an hierarchy. And  
> I?m only interested by a page, let's say the first n root records.
> Unfortunately, the doc says that the ?initial-select? in a recursive  
> common table exception may not include ORDER BY, LIMIT or OFFSET. As  
> a result SQLIte probably scans the whole table, which leads to very  
> poor performance? With other databases (like PostgreSQL), I don?t  
> have the problem because they accept ORDER BY, LIMIT and OFFSET on  
> the initial-select, which limits the scan for the initial-select.
>
> What would be the proper solution with SQLite? One would involve a  
> first query that selects the initial row ids, and then pass them as  
> a condition to the initial-select in the recursive. But does anyone  
> has a better proposal?

Is the documentation really right? Yes, it is a syntax error to write  
ORDER BY or LIMIT before UNON.
Semantically however there is no limitation. The ORDER BY must be  
inside a subquery (an inline view):

select * from (select * from t order by k)
union ...

My SQLite version is 3.6.11

[sqlite] Performance issue with CTE

[Philippe Riand]

I have a table with 500,000+ records. The table has a date column, that I?m 
using to sort my queries (the columns has an index). Simple queries on the 
table work very well, using ORDER BY, LIMIT & OFFSET. I?m actually extracting 
?pages? of rows that I?m displaying in a web page. Great!.

Now, instead of a simple select, I?d like to execute a recursive query using 
CTE, because the rows are organized in an hierarchy. And I?m only interested by 
a page, let's say the first n root records.
Unfortunately, the doc says that the ?initial-select? in a recursive common 
table exception may not include ORDER BY, LIMIT or OFFSET. As a result SQLIte 
probably scans the whole table, which leads to very poor performance? With 
other databases (like PostgreSQL), I don?t have the problem because they accept 
ORDER BY, LIMIT and OFFSET on the initial-select, which limits the scan for the 
initial-select.

What would be the proper solution with SQLite? One would involve a first query 
that selects the initial row ids, and then pass them as a condition to the 
initial-select in the recursive. But does anyone has a better proposal?