[Csync2] recursive selects with sqlite

[Art -kwaak- van Breemen]

Hi,

On Wed, Oct 22, 2008 at 04:39:05PM +0200, Lars Ellenberg wrote:
> 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.
I've got some real live values ;-) :

+ try_it /asdf/1
+ local f=/asdf/1
+ echo or:
or:
+ sqlite3 /var/lib/csync2/lain.db3 'select filename from file where filename = '\''/asdf/1'\'' or (filename > '\''/asdf/1/'\'' and filename < '\''/asdf/10'\'')'
0

real	0m8.460s
user	0m6.820s
sys	0m1.640s
+ echo union:
union:
+ wc -l
+ sqlite3 /var/lib/csync2/lain.db3 'select filename from file where filename = '\''/asdf/1'\'' union all select filename from file where filename > '\''/asdf/1/'\'' and filename < '\''/asdf/10'\'''
0

real	0m0.003s
user	0m0.010s
sys	0m0.000s
+ try_it %25ScRaMbLeD%25/0.big.gen.jpg
+ local f=%25ScRaMbLeD%25/0.big.gen.jpg
+ echo or:
or:
+ sqlite3 /var/lib/csync2/lain.db3 'select filename from file where filename = '\''%25ScRaMbLeD%25/0.big.gen.jpg'\'' or (filename > '\''%25ScRaMbLeD%25/0.big.gen.jpg/'\'' and filename < '\''%25ScRaMbLeD%25/0.big.gen.jpg0'\'')'
+ wc -l
1

real	0m12.377s
user	0m10.860s
sys	0m1.510s
+ echo union:
union:
+ sqlite3 /var/lib/csync2/lain.db3 'select filename from file where filename = '\''%25ScRaMbLeD%25/0.big.gen.jpg'\'' union all select filename from file where filename > '\''%25ScRaMbLeD%25/0.big.gen.jpg/'\'' and filename < '\''%25ScRaMbLeD%25/0.big.gen.jpg0'\'''
+ wc -l
1

real	0m0.044s
user	0m0.000s
sys	0m0.010s
+ try_it %25ScRaMbLeD%25/0
+ local f=%25ScRaMbLeD%25/0
+ echo or:
or:
+ sqlite3 /var/lib/csync2/lain.db3 'select filename from file where filename = '\''%25ScRaMbLeD%25/0'\'' or (filename > '\''%25ScRaMbLeD%25/0/'\'' and filename < '\''%25ScRaMbLeD%25/00'\'')'
+ wc -l
859922

real	0m12.767s
user	0m11.200s
sys	0m1.560s
+ echo union:
union:
+ wc -l
859922

real	0m1.077s
user	0m0.990s
sys	0m0.080s
file where filename = '\''%25ScRaMbLeD%25/0'\'' union all select filename from file where filename > '\''%25ScRaMbLeD%25/0/'\'' and filename < '\''%25ScRaMbLeD%25/00'\'''

(scrambled some things to be hide some details ;-) ).

Anyway:
asdf clearly does not exist.
0.big.gen.jpg is a single file
0 is a rather large directory, but totals less than 10% of the
database ;-).

I've run it a few times after stopping inetd on an idle system
with 4G memory.

I've noticed the slowdown on sqlite2, and that's the reason that
I switched to sqlite3, which did not solve this particular
problem, so I fixed it to union all ;-).

I can try dumping the sqlite3 database into an sqlite2 database
just to make sure...


Regards,
Ard

-- 
A: Because it messes up the order in which people normally read text.
Q: Why is top-posting such a bad thing?
A: Top-posting.
Q: What is the most annoying thing in email?