On Jan 21, 2008 12:58 AM, Alexander Batyrshin <[EMAIL PROTECTED]> wrote: > 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.
D. Richard Hipp, do you have any ideas why 3.3.17 version with DISTINCT works faster that new 3.5.4 without DISTINCT? In my test case difference is greater that 0.5 second. -- Alexander Batyrshin aka bash bash = Biomechanica Artificial Sabotage Humanoid ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
