sqlite single transaction without foreign key or triggers

[John Machin]

On 14/05/2009 8:21 AM, Rob Williscroft wrote in private e-mail 
(presumably by mistake):
> On 13 May 2009, you wrote in comp.lang.python:
> 
>> On May 13, 11:46燼m, a... at pythoncraft.com (Aahz) wrote:
>>> In article <Xns9C09513903E8FrtwfreenetREMOVEc... at 216.196.109.145>,
>>> Rob Williscroft  <r... at freenet.co.uk> wrote:
>>>
>>>
>>>
>>>> Aahz wrote innews:guao50$1jv$1 at panix3.panix.comin comp.lang.python:
>>>>> In article <Xns9C08E179B66D8rtwfreenetREMOVEc... at 216.196.109.145>,
>>>>> Rob Williscroft  <r... at freenet.co.uk> wrote:
>>>>>> db.execute( '''
>>>>>>         update "sessions" set "uid" = ?
>>>>>>         where "uid" = ?
>>>>>>         and exists(
>>>>>>                 爏elect * from "users" where "uid" > = ?
>>>>>>           )
>>>>>>    ''',
>>>>>>    (v['uid'],s.SID, v['uid'])
>>>>>>  )
>>>>> This will be more efficient if you do "select uid from users".
>>>> What will be more efficient ?
>>>> Do you mean the "select * ..." or do you want to take the exists
>>>> sub-query out and put it in a python if ?
>>> "select uid" will be more efficient than "select *", although I
>>> suppose I could be wrong about that given how little I know about
>>> current query optimizers.
> 
> It seems the usual advice about premeture optimisation should apply,
> namely write clear code (*), then optimise the bottlenecks when you 
> actualy find you need to.

_Deliberately_ writing * instead of some constant is premature potential 
pessimisation and thus an utter nonsense. If you think 1 is obscure, 
then * is likewise obscure; write e.g. 'any_old_constant' instead.

> 
> *) for some definition of "clear code".
> 
>> My take is that it won't matter what you select if the optimiser is
>> smart enough; something that requires minimal resources to produce is
>> indicated in case the optimiser is dumb:
>>
>>  ... exists (select 1 from ... )
> 
> I have to maintain some code writen by someone who thinks replacing 
> "*" in queries with "1" is always a good idea (you know just in case), 
> he wrote: 
> 
>     	select count(1) from ...
> 
> of course it didn't do what he thought it did.

If you really have to maintain code that's been written by weird people 
and not subsequently tested, you have my sympathy. However this is not 
relevant. I am certainly not advocating bulk unthinking replacement of * 
by 1 anywhere in a query.

Cheers,
John