On Jan 20, 2008 11:32 PM, <[EMAIL PROTECTED]> wrote: > "Alexander Batyrshin" <[EMAIL PROTECTED]> wrote: > > Hello everyone. > > I've discover performance degradation due to update 3.3.17 -> 3.5.4. > > This SQL query work very slowly: > > > > DELETE FROM > > population_stamp > > WHERE > > town_id IN ( > > SELECT DISTINCT town_id FROM population_stamp > > EXCEPT > > SELECT id FROM town > > ); > > > > I'll bet it will go a lot faster in both 3.3.17 and 3.5.4 if > you omit the DISTINCT operator. EXCEPT implies DISTINCT anyway, > so you are not losing anything. But SQLite does not optimize > out redundant DISTINCTs so it is computing the DISTINCT twice.
It's faster only for 3.5.4, but still slowly for 3.3.17 (time in seconds: us - user, sy - system) 3.5.4 +DISTINCT: 5.474us 0.287sy 3.5.4 -DISTINCT: 3.397us 0.259sy 3.3.17 -DISTINCT: 4.129us 0.228sy 3.3.17 +DISTINCT: 2.959us 0.180sy These is timing example for my small testing database. With real database difference will be more visible. -- Alexander Batyrshin aka bash bash = Biomechanica Artificial Sabotage Humanoid ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
