[Csync2] recursive selects with sqlite

[Lars Ellenberg]

On Wed, Oct 22, 2008 at 10:55:27AM +0200, Art -kwaak- van Breemen wrote:
> On Tue, Oct 21, 2008 at 11:49:06PM +0200, Lars Ellenberg wrote:
> > On Tue, Oct 21, 2008 at 06:20:38PM +0200, Art -kwaak- van Breemen wrote:
> > > I've said it earlier that the recursive selects are a pain for
> > > mysql.
> > 
> > sqlite? mysql? which one?
> 
> mysqlite ;-)
> 
> > > The patch I've mentioned earlier seems to work ok, so I now
> > > submit it for inclusion
> > 
> > anyways, I'll run that by an SQL native speaker and commit :)
> 
> It might be best to first apply the mark fix, and then the sqlite
> fix. I've somehow managed to have double patches, and letting the
> sqlite patch fail is ok, but the mark patch should not fail.

ok.
but: my benchmarks show that it is in fact a HUGE improvement (factor 20
or so in my test setup) in case it is a recursive operation on a
non-recursive, single filename.

but the union DOUBLES the time compared to the "select this or (that and something)"
when it _is_ a recursive operation.

add many file entries to try.db: /asdf/1...10_000_000
do

try_it() {
	local f=$1
time sqlite try.db "
	select filename from file 
	where filename = '$f'
	or (filename > '$f/' and filename < '${f}0')" > /dev/null

time sqlite lge.db "
	select filename from file
	where filename = '$f'
	union all select filename from file
	where filename > '$f/' and filename < '${f}0'" > /dev/null
}

try_it "/asdf/1"
	(20 x improvement for UNION)
try_it "/asdf"
	(2 x improvement for SELECT OR)

hmm.
now, which case is more likely in the real world?
does it need to be configurable?


-- 
: Lars Ellenberg
: LINBIT | Your Way to High Availability
: DRBD/HA support and consulting http://www.linbit.com

DRBD® and LINBIT® are registered trademarks of LINBIT, Austria.