Re: [sqlite] ROWID-based queries slow in FTS db

2015-01-30 Thread farkas andras
Thank you for the quick fix and the info, I will wait for 3.8.9 to trickle down 
into DBD::SQLite. AF
Dan Kennedy  írta:
>On 01/30/2015 10:49 PM, Dominique Devienne wrote:
>> On Fri, Jan 30, 2015 at 4:38 PM, Dan Kennedy  wrote:
>>
>>> On 01/29/2015 02:29 AM, farkas andras wrote:
>>>
 [...] but searches based on ROWID are atrociously slow and hog massive
 amounts of memory [...]

>>> Looks like range constraints on rowids were only taken into account when
>>> there was also a MATCH term in the WHERE clause. Now fixed here:
>>>
>>>http://www.sqlite.org/src/info/85dc12625d300f
>>>
>>> The fix should be in 3.8.9.
>>
>> Just curious Dan. The tests added do not seem to check the query plans
>> despite the report being about a performance issue. I only skimmed them,
>> and Im unfamiliar with TCL and the exact specifics of SQLite testing, 
>> so I
>> could well have missed them, but I do recall seen other perf tests checking
>> execution plans, in addition to checking correctness. Did I miss them?
>
>Fair point. It would be better if there were tests to show that the 
>queries were being correctly optimized.
>
>But the change was fairly trivial, and I didnt think there was much 
>chance that it would fail to optimize the queries correctly. Also, its 
>a pretty obscure optimization (one complaint in how many years?), so I 
>figured it wasnt all that important. Finally its fiddly to test in 
>this case, as the EXPLAIN QUERY PLAN (or even EXPLAIN) output is not 
>sufficient to figure out if its working properly or not. So I just 
>checked by hand that the optimization is working.
>
>On the other hand, that the change could contain some bug related to 
>integer overflow or some other boundary condition is a real risk. So the 
>tests focus on that.
>
>Dan.
>
>
>___
>sqlite-users mailing list
>sqlite-users@sqlite.org
>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] ROWID-based queries slow in FTS db

2015-01-30 Thread Dan Kennedy

On 01/30/2015 10:49 PM, Dominique Devienne wrote:

On Fri, Jan 30, 2015 at 4:38 PM, Dan Kennedy  wrote:


On 01/29/2015 02:29 AM, farkas andras wrote:


[...] but searches based on ROWID are atrociously slow and hog massive
amounts of memory [...]


Looks like range constraints on rowids were only taken into account when
there was also a MATCH term in the WHERE clause. Now fixed here:

   http://www.sqlite.org/src/info/85dc12625d300f

The fix should be in 3.8.9.


Just curious Dan. The tests added do not seem to check the query plans
despite the report being about a performance issue. I only skimmed them,
and I'm unfamiliar with TCL and the exact specifics of SQLite testing, so I
could well have missed them, but I do recall seen other perf tests checking
execution plans, in addition to checking correctness. Did I miss them?


Fair point. It would be better if there were tests to show that the 
queries were being correctly optimized.


But the change was fairly trivial, and I didn't think there was much 
chance that it would fail to optimize the queries correctly. Also, it's 
a pretty obscure optimization (one complaint in how many years?), so I 
figured it wasn't all that important. Finally it's fiddly to test in 
this case, as the EXPLAIN QUERY PLAN (or even EXPLAIN) output is not 
sufficient to figure out if it's working properly or not. So I just 
checked by hand that the optimization is working.


On the other hand, that the change could contain some bug related to 
integer overflow or some other boundary condition is a real risk. So the 
tests focus on that.


Dan.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] ROWID-based queries slow in FTS db

2015-01-30 Thread Dominique Devienne
On Fri, Jan 30, 2015 at 4:38 PM, Dan Kennedy  wrote:

> On 01/29/2015 02:29 AM, farkas andras wrote:
>
>> [...] but searches based on ROWID are atrociously slow and hog massive
>> amounts of memory [...]
>>
>
> Looks like range constraints on rowids were only taken into account when
> there was also a MATCH term in the WHERE clause. Now fixed here:
>
>   http://www.sqlite.org/src/info/85dc12625d300f
>
> The fix should be in 3.8.9.


Just curious Dan. The tests added do not seem to check the query plans
despite the report being about a performance issue. I only skimmed them,
and I'm unfamiliar with TCL and the exact specifics of SQLite testing, so I
could well have missed them, but I do recall seen other perf tests checking
execution plans, in addition to checking correctness. Did I miss them?
Thanks, --DD
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] ROWID-based queries slow in FTS db

2015-01-30 Thread Dan Kennedy

On 01/29/2015 02:29 AM, farkas andras wrote:

Hi all, Im using FTS through DBD::SQLite (perl) to query large text databases 
(~10GB, ~10 million records). The regular FTS MATCH searches work fine (they usually 
run under a second), but searches based on ROWID are atrociously slow and hog massive 
amounts of memory. Im trying to retrieve a couple of adjacent rows like so:
my $q_c = $dbh->prepare( "SELECT * FROM ftstable WHERE (ROWID BETWEEN 1000 AND 
1040)" );
# my $q_c = $dbh->prepare( "SELECT * FROM ftstable LIMIT 1040 OFFSET 1000" ); # 
tried this too, it isnt any better
$q_c->execute();
The execute takes several minutes and uses ~600 MB of memory. Now, 
http://www.sqlite.org/changes.html writes that:
3.8.1: FTS4 queries are better able to make use of docid<$limit constraints to 
limit the amount of I/O required
Theres also this thread, indicating that rowid searches on FTS databases 
are optimized: 
http://sqlite.1065341.n5.nabble.com/FTS-full-text-query-vs-query-by-rowid-td77534.html
 I was using 3.7.x so I updated DBD::SQLite and that got me up to SQLite 3.8.7, but 
I see no significant improvement. Explain query gives the same result as the linked 
thread: 0|0|0|SCAN TABLE tmdata VIRTUAL TABLE INDEX 393216.
Maybe there is a better way to write the query? If not, is there any hope that 
this will get fixed?


Looks like range constraints on rowids were only taken into account when 
there was also a MATCH term in the WHERE clause. Now fixed here:


  http://www.sqlite.org/src/info/85dc12625d300f

The fix should be in 3.8.9.

Dan.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users