On Sep 23, 2006, at 1:52 AM, Ruslan Zasukhin wrote:
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?
No idea.
Simpler to manage 1 statement than 2 ?
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.
Hopefully there is some criteria in the WHERE clause that would make
this much smaller and quicker in any case
_______________________________________________
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>
