Re: [sqlite] Union all patch, preliminary tests

[Richard Hipp]

Thanks for running these tests.  Clearly I should have made this change a
long time ago

On Tue, Oct 30, 2012 at 9:31 AM, Eleytherios Stamatogiannakis <
est...@gmail.com> wrote:

> In my preliminary tests, the patch has worked wonderfully. In these first
> tests i have only tested plain SQLite functionality (without virtual table
> or additional functions) on a 1.5GB DB. Some remarks:
>
> - It is lightning fast. For queries that fit to the free space (some of
> them could not finish before due to space constraints) and previously took
> minutes to finish, with the patch they are instantaneous and they do not
> touch the drive. The results are the same.
>
> - I was expecting that only simple scans over unioned queries would be
> affected by the patch. When i tried to do a filter on a unioned table
> composition, and the query was again instantaneous, i was flabbergasted.
> The patch really pushes index accesses downwards into the unioned tables. I
> didn't expect the patch to go that far. I'm amazed,
>
> - Group by on unioned all tables was also instantaneous without filling
> the hard disk.
>
> - Oddly, order by works the same both with the patch and without the patch
> (SQLite 3.7.14.1). I assume that order by had a similar optimization in
> previous versions of SQLite too?
>
> - On all of the above tests, the free space on the hard disk wasn't
> affected by the running query.
>
> More tests will be done in the context of madIS, but they will take more
> time. I want to test plain SQLite first, because this is what most people
> will use in practise.
>
> Best regards,
>
> lefteris.
>
>
> __**_
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users
>



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

[sqlite] Union all patch, preliminary tests

[Eleytherios Stamatogiannakis]

In my preliminary tests, the patch has worked wonderfully. In these 
first tests i have only tested plain SQLite functionality (without 
virtual table or additional functions) on a 1.5GB DB. Some remarks:


- It is lightning fast. For queries that fit to the free space (some of 
them could not finish before due to space constraints) and previously 
took minutes to finish, with the patch they are instantaneous and they 
do not touch the drive. The results are the same.


- I was expecting that only simple scans over unioned queries would be 
affected by the patch. When i tried to do a filter on a unioned table 
composition, and the query was again instantaneous, i was flabbergasted. 
The patch really pushes index accesses downwards into the unioned 
tables. I didn't expect the patch to go that far. I'm amazed,


- Group by on unioned all tables was also instantaneous without filling 
the hard disk.


- Oddly, order by works the same both with the patch and without the 
patch (SQLite 3.7.14.1). I assume that order by had a similar 
optimization in previous versions of SQLite too?


- On all of the above tests, the free space on the hard disk wasn't 
affected by the running query.


More tests will be done in the context of madIS, but they will take more 
time. I want to test plain SQLite first, because this is what most 
people will use in practise.


Best regards,

lefteris.


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