"Alexander Batyrshin" <[EMAIL PROTECTED]> wrote: > 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. >
I have not researched it. Somewhere along the line, somebody sent me a patch that caused DISTINCT to be converted into a GROUP BY and processed that way. The claim was this made the processing faster in most cases. Perhaps you have discovered the case where it actually slows things down. -- D. Richard Hipp <[EMAIL PROTECTED]> ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
