This is called premature optimization. It is the root of all Evil. In DonaldKnuth's paper "StructuredProgrammingWithGoToStatements", he wrote: "Programmers waste enormous amounts of time thinking about, or worrying about, the speed of noncritical parts of their programs, and these attempts at efficiency actually have a strong negative impact when debugging and maintenance are considered. We should forget about small efficiencies, say about 97% of the time: premature optimization is the root of all evil. Yet we should not pass up our opportunities in that critical 3%."
Also, there is absolutely no advantage to be gained over doing just a blind update -- there is no need to perform the select at all -- if you are going to do the update if any records are found to update -- the select will be for "entertainment system" value only and there is no need to do it at all. This is the optimization which has value. If you plan to use the select to discover merely whether or not you need to do the update, then you may as well just do the update. If no update statement finds that there are no updates required, it will not do any updates. If there are updates required, you have cut the time required to perform them in half by eliminating the useless extraneous step. > No. Originally I think since task 2 and 3 are operations performed on the > same set of records, maybe they can be merged to improved the performance > though one is get and another is set. > > Thank you very much > > > -----Original Message----- > > From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users- > > bounces at mailinglists.sqlite.org] On Behalf Of Igor Tandetnik > > Sent: Friday, February 19, 2016 11:04 PM > > To: sqlite-users at mailinglists.sqlite.org > > Subject: Re: [sqlite] Process duplicate field values > > > > On 2/19/2016 1:00 AM, admin at shuling.net wrote: > > > 1. For all conflict records, get the total count of distinct F1 > values. > > > In the above sample, record 1, 2, 3, 4, 5, 6 are conflict records, but > > > the distinct values are only 1, 2, 3 so the total count should be 3. > > > 2. Get the total count of all the conflict records. In the above > > > sample, it should be 6. > > > > select count(*) CountOfConflictGroups, sum(c) CountOfConflictRecords > from > > ( > > select count(*) c from MyTable group by F1 having count(*) > 1 ); > > > > > 3. Set the F2 value of all the conflict records to 9. Keep all > > > other records intact. > > > > update MyTable set F2=9 where F1 in > > (select t.F1 from MyTable t group by t.F1 having count(*) > 1); > > > > > Can task 2 and 3 be implemented in one SQL query > > > > No. One is a "get", the other is a "set". A single SQL query can't do > both. Did > > you mean tasks 1 and 2, perhaps? > > -- > > Igor Tandetnik > > > > _______________________________________________ > > sqlite-users mailing list > > sqlite-users at mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
