> On my PC the following query requires about 53 seconds: > select * from TABG a, TABB b where (a.S='3' or a.S='12 or...) and > b.G=a.G order by a.G asc; > > (On Oracle with the same scheme and data it requires only 0.4 > seconds.)
In my experience, even though SQLite has very low overhead and is pretty lightweight, performance can get hurt pretty badly for complicated queries (or even fairly simple ones like yours) when it chooses the wrong optimization paths. I've had situations where changing "SELECT ... FROM table1, table2 ..." to "SELECT ... FROM table2, table1 ..." makes an enormous difference in execution time, because when SQLite has more than one index to choose from, it seems to choose randomly. Sometimes it's wrong, and sometimes quite badly so. This is why many other DBMs put a lot of effort into developing things like cost-based optimizers, so these kinds of issues can be dealt with nicely. I'm not sure if SQLite plans to add such things, but I'm not sure it fits with the stated goals of simplicity. So I guess the moral is that when performance gets slow, you really have to scrutinize the execution plans in SQLite more than in other databases I'm used to, rather than just adding indexes you *think* should help and trusting the database to do the "right" thing. -Ken --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
