> From: Phil M <[EMAIL PROTECTED]>
> Date: Wed, 20 Sep 2006 23:33:32 -0700
>
> On Sep 20, 2006, at 11:28 PM, Norman Palardy wrote:
>
>> You can probably do something like this
>>
>> xxx is the row id (or other unique key for the row you want to set
>> true)
>>
>> update table set booleanColumn = coalesce(id=xxxx,true,false)
>> where .... (some criteria to identify all the other members of the
>> band)
Aha, Phil already have show similar solution :-)
> Ah... see I didn't know about the Coalesce() function. But according
> to the SQLite documentation:
>
> coalesce(X,Y,...)
> Return a copy of the first non-NULL argument. If all arguments are
> NULL then NULL is returned. There must be at least 2 arguments.
>
> So I think that for SQLite the statement needs to be written like this:
>
> UPDATE musicians SET id, leadsinger = COALESCE(id=7,true,false)
> WHERE 1
Question is why to try put this operation into single command.
Because of pure SQL interest ?
Because to get better performance?
I can say that case with 2 updates, at least for Valentina can be faster.
---------------------------
How works most of DBS on
1) 2 updates:
SCAN of table (= SCAN of column) with assignment = false
INDEX SEARCH of id = 7
UPDATE of one record.
2) single command with COALESCE
SCAN of table (= SCAN of column) with
CHECK id =1
ASSIGMENT = true or false.
So second case have overhead of CHECK.
which for table with many records can become comparable to index search
or even become bigger.
---------------------------
How works Valentina.
1) 2 updates:
SCAN of column. Not of the whole table.
so if table has million records of avg size 100 bytes,
i.e. Table size is 100MB. But we scan column Boolean
which take only 1 million bits = 128 Kb
then Valentina scan only 128Kb instead of 100MB.
800 times faster then do other dbs.
INDEX SEARCH of id = 7
UPDATE of one record.
comparable to others.
2) single command with COALESCE
again we get overhead of million times CHECK of bit.
Million checks this is much more than INDEX SEARCH on
million records which is just 20 comparisons.
--
Best regards,
Ruslan Zasukhin
VP Engineering and New Technology
Paradigma Software, Inc
Valentina - Joining Worlds of Information
http://www.paradigmasoft.com
[I feel the need: the need for speed]
_______________________________________________
Unsubscribe or switch delivery mode:
<http://www.realsoftware.com/support/listmanager/>
Search the archives of this list here:
<http://support.realsoftware.com/listarchives/lists.html>
