Re: [sqlite] PS Re: [sqlite] two process problem

[Jay Sprenkle]

I have 2 processes running one is updating portions of a table and

>>one is inserting.
>
>
>Are you using threads? There are some issues using the same database
handle
>with multiple threads.

Each process is single threaded.



Your error messages don't look familiar. What language are you programming
in, and do you use a wrapper or call sqlite directly? Some details/code
might help debug it.

[sqlite] CREATE TABLE AS drops column constraint; PRAGMA TABLE_INFO doesn't show the constraint

[Gerry Snyder]

I assume that both behaviors, as illustrated below, are by design.

My question is whether there is an easier way to make a copy of a table, 
including column constraints, than parsing the sql in sqlite_master . I 
don't mind doing that, since it is not a really big deal in Tcl, but I 
don't want to overlook something better that's already there.


TIA,

Gerry

$ ./sqlite3
SQLite version 3.3.12
Enter ".help" for instructions
sqlite> create table a(a1 text collate nocase,b text);
sqlite> insert into a values('abc','bcd');
sqlite> create table c as select * from a;
sqlite> select sql from sqlite_master;
CREATE TABLE a(a1 text collate nocase,b text)
CREATE TABLE c(a1 text,b text)
sqlite> pragma table_info(a);
0|a1|text|0||0
1|b|text|0||0
sqlite>

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Re: Memory database to file

[A. Pagaltzis]

* David Champagne <[EMAIL PROTECTED]> [2007-02-01 15:45]:
> I suppose since no one replied to this, that it's not possible
> to do it. Just wanted to confirm. Thank you...

http://en.wikipedia.org/wiki/Warnock%27s_Dilemma  :-)

Regards,
-- 
Aristotle Pagaltzis // 

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Re: UPDATE OR REPLACE same as UPDATE?

[A. Pagaltzis]

* Joe Wilson <[EMAIL PROTECTED]> [2007-02-04 00:25]:
> Does anyone know whether UPDATE OR REPLACE is portable to any
> other popular database?

Not to MySQL. I don’t have any experience with other engines,
much as I wish. (I’d much prefer PostgreSQL but I have no
choice.)

Regards,
-- 
Aristotle Pagaltzis // 

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Re: UNIQUE constraint on column

[A. Pagaltzis]

* Dennis Cote <[EMAIL PROTECTED]> [2007-02-03 17:20]:
> I suspect the reduction from executing three statements
> (insert, select, insert) down to only two (insert insert) would
> probably provide about the same performance increase as the 5%
> to 10% speedup he saw by replacing the separate select with the
> VDBE stack lookup hack.

Ah, you mean the SELECT / sometimes-INSERT / INSERT strategy
might not be any faster than INSERT / INSERT-with-subselect
because the former is 3 statements and the latter is just 2?

Hmm, that’s something I’d definitely benchmark before deciding.

It’s a pity that INSERT OR IGNORE (apparently?) does not set
last_insert_id properly regardless of outcome, otherwise it could
be reduced to just two INSERTs doing absolutely no duplicate work.

Regards,
-- 
Aristotle Pagaltzis // 

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] PS Re: [sqlite] two process problem

[Tom Shaw]

At 4:59 PM -0600 2/3/07, Jay Sprenkle wrote:

On 2/3/07, Tom Shaw <[EMAIL PROTECTED]> wrote:


I have 2 processes running one is updating portions of a table and
one is inserting.



Are you using threads? There are some issues using the same database handle
with multiple threads.


Each process is single threaded.

Tom

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] two process problem

[Tom Shaw]

At 4:59 PM -0600 2/3/07, Jay Sprenkle wrote:

On 2/3/07, Tom Shaw <[EMAIL PROTECTED]> wrote:


I have 2 processes running one is updating portions of a table and
one is inserting.



Are you using threads? There are some issues using the same database handle
with multiple threads.


No, two separate processes

Tom


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] UPDATE OR REPLACE same as UPDATE?

[Joe Wilson]

--- [EMAIL PROTECTED] wrote:
> Joe Wilson <[EMAIL PROTECTED]> wrote:
> > Is UPDATE OR REPLACE always equivalent to just UPDATE?
> 
> No.  UPDATE is the same as UPDATE OR ABORT.  Try replacing
> the UPDATE OR REPLACE in the following script with just
> UPDATE to see the difference:
> 
>CREATE TABLE t1(x UNIQUE, y);
>INSERT INTO t1 VALUES(1,2);
>INSERT INTO t1 VALUES(3,4);
>
>UPDATE OR REPLACE t1 SET x=3 WHERE y=2;
>SELECT * FROM t1;

Thanks. That's quite useful, actually. 
I used to do a DELETE followed by an INSERT in this situation.
The UDPATE OR REPLACE construct is more efficient.

Does anyone know whether UPDATE OR REPLACE is portable to any other 
popular database? (Oracle, SQL Server, MySQL, Postgres)
REPLACE() seems to be a string function in other databases.


 

Yahoo! Music Unlimited
Access over 1 million songs.
http://music.yahoo.com/unlimited

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] two process problem

[Jay Sprenkle]

On 2/3/07, Tom Shaw <[EMAIL PROTECTED]> wrote:


I have 2 processes running one is updating portions of a table and
one is inserting.



Are you using threads? There are some issues using the same database handle
with multiple threads.
--
--
The PixAddixImage Collector suite:
http://groups-beta.google.com/group/pixaddix

SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com

[sqlite] two process problem

[Tom Shaw]

I have 2 processes running one is updating portions of a table and 
one is inserting.


I don't accumulate updates but rather update a record at a time to 
keep the time of locking down. (eg begin update commit)  Likewise, I 
only insert one at a time for the same reason.


Each process works fine when running on its own yet when running them 
together I get errors such as:


SQLSTATE[HY000]: General error: 1 SQL logic error or missing database
and
SQLSTATE[HY000]: General error: 8 attempt to write a readonly database

I thought sqlite handled locks. What am I doing wrong?

TIA,

Tom


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] UPDATE OR REPLACE same as UPDATE?

[drh]

Joe Wilson <[EMAIL PROTECTED]> wrote:
> Is UPDATE OR REPLACE always equivalent to just UPDATE?
> 

No.  UPDATE is the same as UPDATE OR ABORT.  Try replacing
the UPDATE OR REPLACE in the following script with just
UPDATE to see the difference:

   CREATE TABLE t1(x UNIQUE, y);
   INSERT INTO t1 VALUES(1,2);
   INSERT INTO t1 VALUES(3,4);
   
   UPDATE OR REPLACE t1 SET x=3 WHERE y=2;
   SELECT * FROM t1;
   
--
D. Richard Hipp  <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] UPDATE OR REPLACE same as UPDATE?

[Joe Wilson]

Is UPDATE OR REPLACE always equivalent to just UPDATE?


 

Food fight? Enjoy some healthy debate 
in the Yahoo! Answers Food & Drink Q
http://answers.yahoo.com/dir/?link=list=396545367

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Another sybase conversion question

[Joe Wilson]

It will work fine, just remember to specify every column in the
table being updated (aka "REPLACEd INTO") or they will contain NULL.

It would be nice if SQLite featured an "INSERT OR MERGE" or "MERGE INTO"
command that would not require specifying all the columns. i.e., grab
the old row's values for the columns not specified to act more like
an update. For a 5 column table it's not a big deal, but when you wish
to REPLACE INTO a table with 50 columns, the SQL gets a bit unwieldy.

Happy credit derivativing.

--- "Anderson, James H (IT)" <[EMAIL PROTECTED]> wrote:
> Thanks, Joe! That looks like it might be the solution. 
> 
> -Original Message-
> From: Joe Wilson [mailto:[EMAIL PROTECTED] 
> Sent: Friday, February 02, 2007 9:58 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Another sybase conversion question
> 
> --- "Anderson, James H (IT)" <[EMAIL PROTECTED]> wrote:
> > The following works fine in sybase, but because in sqlite "update"
> does
> > not support "from" it doesn't work. Is there another way of doing
> this?
> > 
> > Thanks,
> > 
> > Jim
> > 
> > update C1_credDerivEvent
> >set CDEvent = a.CDEvent || ',' || b.CDEvent
> >   from C1_credDerivEvent a,
> >C1_tmp_credDerivEvent b,
> >tmp_eventsc
> >  where a.CDId = b.CDId
> >and b.CDApplicable = 'Yes'
> >and b.CDEvent  = c.CDEvent;
> 
> http://www.sqlite.org/lang_replace.html


 

Want to start your own business?
Learn how on Yahoo! Small Business.
http://smallbusiness.yahoo.com/r-index

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Another sybase conversion question

[Anderson, James H \(IT\)]

Thanks, Joe! That looks like it might be the solution. 

-Original Message-
From: Joe Wilson [mailto:[EMAIL PROTECTED] 
Sent: Friday, February 02, 2007 9:58 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Another sybase conversion question

--- "Anderson, James H (IT)" <[EMAIL PROTECTED]> wrote:
> The following works fine in sybase, but because in sqlite "update"
does
> not support "from" it doesn't work. Is there another way of doing
this?
> 
> Thanks,
> 
> Jim
> 
> update C1_credDerivEvent
>set CDEvent = a.CDEvent || ',' || b.CDEvent
>   from C1_credDerivEvent a,
>C1_tmp_credDerivEvent b,
>tmp_eventsc
>  where a.CDId = b.CDId
>and b.CDApplicable = 'Yes'
>and b.CDEvent  = c.CDEvent;

http://www.sqlite.org/lang_replace.html



 


Be a PS3 game guru.
Get your game face on with the latest PS3 news and previews at Yahoo!
Games.
http://videogames.yahoo.com/platform?platform=120121


-
To unsubscribe, send email to [EMAIL PROTECTED]

-


NOTICE: If received in error, please destroy and notify sender. Sender does not 
intend to waive confidentiality or privilege. Use of this email is prohibited 
when received in error.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Another sybase conversion question

[Anderson, James H \(IT\)]

Thanks, Rich. Yes, I have the book and it's terrific! As you say, the
index is shamefully bad. I'm gradually working my way through it but I,
too, am under pressure to produce results so I'm forced to ask many
questions that I could probably find the answers to myself if I had more
time. 

-Original Message-
From: Rich Shepard [mailto:[EMAIL PROTECTED] 
Sent: Friday, February 02, 2007 6:39 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Another sybase conversion question

On Fri, 2 Feb 2007, Anderson, James H (IT) wrote:

> The following works fine in sybase, but because in sqlite "update"
does
> not support "from" it doesn't work. Is there another way of doing
this?

Jim,

   I'm in the midst of trying to meet a deadline, so I'll let someone
more
knowledgable translate for you.

   But, if you're going to do much with SQLite, I strongly recommend
Mike
Owens' "The Definitive Guide to SQLite" by Apress. The index is
shamefully
bad, but the book is a gem and has been a great help to me. Very highly
recommended -- despite the index. :-)

Rich

-- 
Richard B. Shepard, Ph.D.   |The Environmental
Permitting
Applied Ecosystem Services, Inc.|  Accelerator(TM)
 Voice: 503-667-4517  Fax:
503-667-8863


-
To unsubscribe, send email to [EMAIL PROTECTED]

-


NOTICE: If received in error, please destroy and notify sender. Sender does not 
intend to waive confidentiality or privilege. Use of this email is prohibited 
when received in error.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Re: UNIQUE constraint on column

[Dennis Cote]

On 2/2/07, A. Pagaltzis <[EMAIL PROTECTED]> wrote:


5-10% in his tests, as he wrote a few mails up the thread.
Significant? No. Worthwhile? Apparently so, for his application.



I saw that as well, but based on this:


> is there a more formal mechanism for getting this value without having

do

> to do a separate "select" query?


I got the impression he was executing a separate select statement to return
the rowid into his C code and then passing that value back to the third
insert using the C API to bind that rowid to the final insert. I suspect the
reduction from executing three statements (insert, select, insert) down to
only two (insert insert) would probably provide about the same performance
increase as the 5% to 10% speedup he saw by replacing the separate select
with the VDBE stack lookup hack.

He also never said which API functions he was using to execute the inserts.
I thought he may have been using the sqlite3_exec function which includes
the overhead of compiling the SQL code for each execution. In that case, I
suspect that changing to the prepared statements should provide more benefit
than the VDBE stack hack by eliminating this overhead.

Dennis Cote

Re: [sqlite] Auto-detection of database change in multi-process environment

[John Stanton]

A trigger updating a time modified entry in an Sqlite table would do the 
job.


[EMAIL PROTECTED] wrote:

If you are just looking for a simple detection,  a process could "touch" or
update a flag file, which might be empty or whatever.
I have not seen the C API.  I am not sure about the scope or  effort.
But, there could be other methods.   One might be to  "mark"  records with
pending / changed transactions.   Of course, it would require an extra
column in the database.   Without that,  I would consider creating a
secondary table or database  which other processes could update.  It would
hold a list of records changed/accessed by a given PID or user-id.  When a
process requests a record, update the 2nd database with the key and the
user or PID.  When finished, delete it.  Hope this helps.

Good luck.



   
 Ken   
  [EMAIL PROTECTED]> To 
      
 02/02/2007 09:52   cc 
 AM
   Subject 
   Re: [sqlite] Auto-detection of  
 Please respond to database change in multi-process
   [EMAIL PROTECTED]>   
   
   
   
   
   





As I see it you have only 3 options.

 1. Polling.
 Polling on a table in sqlite or depending upon your app. You could
simply check the file access modifiers to see when the last modifaction
time was.

 2. Set up an IPC semaphore

 3. Set up a socket.



David GIGUET <[EMAIL PROTECTED]> wrote: Hi,

Iam working on the design of a multi-process architecture accessing a
single database. Is there a way for one of the process to detect that
another process has modified the database ? I would like to avoid regular
polling of the database to check for modifications and I also would like
to avoid ipc or creation of an sqlite server on top of the database. If it
does not exist do you think I can open the database file (either with
sqlite or with file system), create a thread with a select or
sqlite_busy_handler that is pending on detection of file modification ?

Thanks for your help,

David



" Ce courriel et les documents qui y sont attaches peuvent contenir des
informations confidentielles. Si vous n'etes pas le destinataire escompte,
merci d'en informer l'expediteur immediatement et de detruire ce courriel
ainsi que tous les documents attaches de votre systeme informatique. Toute
divulgation, distribution ou copie du present courriel et des documents
attaches sans autorisation prealable de son emetteur est interdite."

" This e-mail and any attached documents may contain confidential or
proprietary information. If you are not the intended recipient, please
advise the sender immediately and delete this e-mail and all attached
documents from your computer system. Any unauthorised disclosure,
distribution or copying hereof is prohibited."



-
To unsubscribe, send email to [EMAIL PROTECTED]
-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] OR, IN: which is faster?

[Joe Wilson]

--- chueng alex1985 <[EMAIL PROTECTED]> wrote:
> I don't think so. If the field has been indexed, searching speed will be
> imprved very much. The query "f1 = value1 OR f1 = value2 OR f1=value3 OR
> ..." will be faster if the field f1 has been indexed. On the other hand, the
> clause "f1 in (value1, value2, value3, ...)" seems to be slower then 'OR'
> clause because i think it will get the all value of 'f1' and check whether
> the f1's value is in (value1, value2, value3, ...). If the table has 100K
> records, it will compare 100K times, in contrast, the OR clause only need
> compare few times because of the index when the number of values in (value1,
> value2, value3, ...) list is not too big.

As drh pointed out, if you run the following in sqlite 3.3.12 you'll see 
both the IN and the OR queries produce the exact same instructions, 
resulting in the same query speed if an index is used:

 CREATE TABLE abc(a,b,c);
 CREATE INDEX abc_c on abc(c);
 explain select * from abc where c in (11,22,33);
 explain select * from abc where c=11 or c=22 or c=33;

(same output for both)
0|Goto|0|41|
1|Integer|0|0|
2|OpenRead|0|2|
3|SetNumColumns|0|3|
4|Integer|0|0|
5|OpenRead|1|3|keyinfo(1,BINARY)
6|MemLoad|2|0|
7|If|0|20|
8|MemInt|1|2|
9|OpenEphemeral|2|0|keyinfo(1,BINARY)
10|SetNumColumns|2|1|
11|Integer|11|0|
12|MakeRecord|1|0|b
13|IdxInsert|2|0|
14|Integer|22|0|
15|MakeRecord|1|0|b
16|IdxInsert|2|0|
17|Integer|33|0|
18|MakeRecord|1|0|b
19|IdxInsert|2|0|
20|Rewind|2|38|
21|Column|2|0|
22|IsNull|-1|37|
23|MemStore|1|1|
24|MemLoad|1|0|
25|MakeRecord|1|0|b
26|MemStore|0|0|
27|MoveGe|1|37|
28|MemLoad|0|0|
29|IdxGE|1|37|+
30|IdxRowid|1|0|
31|MoveGe|0|0|
32|Column|0|0|
33|Column|0|1|
34|Column|0|2|
35|Callback|3|0|
36|Next|1|28|
37|Next|2|21|
38|Close|0|0|
39|Close|1|0|
40|Halt|0|0|
41|Transaction|0|0|
42|VerifyCookie|0|2|
43|Goto|0|1|
44|Noop|0|0|

If you drop in the index abc_c, you will see the behavior you mentioned.
The OR query is on the left, and the IN query is on the right:

0|Goto|0|21|  | 0|Goto|0|36|
1|Integer|0|0|  1|Integer|0|0|
2|OpenRead|0|2| 2|OpenRead|0|2|
3|SetNumColumns|0|3|3|SetNumColumns|0|3|
4|Rewind|0|19|| 4|Rewind|0|34|
5|Column|0|2| | 5|MemLoad|0|0|
6|Integer|11|0|   | 6|If|0|19|
7|Eq|98|14|collseq(BINARY)| 7|MemInt|1|0|
8|Column|0|2| | 8|OpenEphemeral|1|0|keyinfo(1,BINARY)
9|Integer|22|0|   | 9|SetNumColumns|1|1|
10|Eq|98|14|collseq(BINARY)   | 10|Integer|11|0|
11|Column|0|2|| 11|MakeRecord|1|0|b
12|Integer|33|0|  | 12|IdxInsert|1|0|
13|Ne|354|18|collseq(BINARY)  | 13|Integer|22|0|
14|Column|0|0|| 14|MakeRecord|1|0|b
15|Column|0|1|| 15|IdxInsert|1|0|
16|Column|0|2|| 16|Integer|33|0|
17|Callback|3|0|  | 17|MakeRecord|1|0|b
18|Next|0|5|  | 18|IdxInsert|1|0|
19|Close|0|0| | 19|Integer|1|0|
20|Halt|0|0|  | 20|Column|0|2|
21|Transaction|0|0|   | 21|NotNull|-1|25|
22|VerifyCookie|0|3|  | 22|Pop|2|0|
23|Goto|0|1|  | 23|Null|0|0|
24|Noop|0|0|  | 24|Goto|0|28|
  > 25|MakeRecord|1|0|b
  > 26|Found|1|28|
  > 27|AddImm|-1|0|
  > 28|IfNot|1|33|
  > 29|Column|0|0|
  > 30|Column|0|1|
  > 31|Column|0|2|
  > 32|Callback|3|0|
  > 33|Next|0|5|
  > 34|Close|0|0|
  > 35|Halt|0|0|
  > 36|Transaction|0|0|
  > 37|VerifyCookie|0|3|
  > 38|Goto|0|1|
  > 39|Noop|0|0|

You can mimic the index-less OR behavior on the column (even in the presence
of such an index) by putting a plus before each column in the where clause,
thus disqualifying the column from using an index:

 select * from abc where +c=11 or +c=22 or +c=33

This "OR" query table scan might be faster than using an index in cases 
where you know that you will be selecting the majority of the rows in 
the table.

> 
> 2007/2/1, [EMAIL PROTECTED] <[EMAIL PROTECTED]>:
> >
> > Ion Silvestru <[EMAIL PROTECTED]> wrote:
> > > If we have a query where we compare a column to a set of values, then
> > > which is faster: OR or IN?
> > > Ex: OR: (mycol = "a") OR (mycol = "b") OR (mycol = "c") OR...
> > > IN: (mycol IN "a", "b", "c" 

Re: [sqlite] Appropriate uses for SQLite

[David M X Green]

I am new to this but are these issues those of trying to get it to do what 
sqlite it is not designed for. I quote the book

The Definitive Guide to SQLite - Chapter 1 --- Networking
"  Again, most of these limitations are intentional—they are a result of 
SQLite’s
design. Supporting high write concurrency, for example, brings with it great
deal of complexity and this runs counter to SQLite’s simplicity in design.
Similarly, being an embedded database, SQLite intentionally does
__not__support__networking__ [my emphasis].  This should come as no surprise.
In short, what SQLite can’t do is a direct result of what it can. It was
designed to operate as a modular, simple, compact, and easy-to-use embedded
relational database whose code base is within the reach of the programmers
using it. And in many respects it can do what many other databases cannot, such
as run in embedded environments where actual power consumption is a limiting
factor. "
--
Is it really a good idea to network a data base that relies on the OS file 
systems like this? Is it ever going to be safe enough?

David M X Green


|||"Alex Roston" (2007-02-02 20:05) wrote: |||>>>

Scott Hess wrote:

On 2/2/07, Dennis Cote <[EMAIL PROTECTED]> wrote:

[EMAIL PROTECTED] wrote:
> The problem is, not many network filesystems work correctly.

I'm sure someone knows which versions of NFS have working file locking,
at least under Linux.


I doubt it is this easy.  You need to line up a bunch of things in the
right order, with the right versions of nfs, locking services, perhaps
the right kernel versions, the right config, etc, etc.

IMO the _real_ solution would be a package which you could use to try
to verify whether the system you have is actually delivering working
file locking.  Something like diskchecker (see
http://brad.livejournal.com/2116715.html).  The basic idea would be to
have a set of networked processes exercising the APIs and looking for
discrepencies.  Admittedly, passing such a test only gets you a
statistical assurance (maybe if you'd run the test for ten more
minutes, or with another gig of data, it would have failed!), but
failing such a test is a sure sign of a problem.

-scott
That's a really useful idea, not only for itself, but also because it 
might lead to debugging some of the network issues, and allowing the 
developers to build a database of stuff that works: "Use Samba version 
foo, with patch bar, and avoid the Fooberry 6 network cards." Or whatever.


My suspicion, in the earlier case with Windows and Linux clients is that 
Windows didn't handle the locking correctly, and that would be worth 
proving/disproving too.


An alternate approach is to use something a little more like a standard 
client-server model, where there's a "server" program which intervenes 
between (possibly multiple) workstations and the database itself. The 
"server" would queue requests to the database, make sure that no more 
than one write request at a time went to the database, and certify that 
writes have been properly made.


The problem with this approach is that it eats quite heavily into 
SQLite's speed advantage, but if you've already put thousands of hours 
into developing your system, it might be a worthwhile hack.


Alex

- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 








-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] not allowing an empty column

[Dan Kennedy]

On Sat, 2007-02-03 at 00:53 -0600, P Kishor wrote:
> how do I add a constraint to a column so neither null nor empty
> strings ("") are allowed?
> 

create table tbl(col CHECK (col IS NOT NULL AND col != ''));






-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] OR, IN: which is faster?

[chueng alex1985]

I don't think so. If the field has been indexed, searching speed will be
imprved very much. The query "f1 = value1 OR f1 = value2 OR f1=value3 OR
..." will be faster if the field f1 has been indexed. On the other hand, the
clause "f1 in (value1, value2, value3, ...)" seems to be slower then 'OR'
clause because i think it will get the all value of 'f1' and check whether
the f1's value is in (value1, value2, value3, ...). If the table has 100K
records, it will compare 100K times, in contrast, the OR clause only need
compare few times because of the index when the number of values in (value1,
value2, value3, ...) list is not too big.

2007/2/1, [EMAIL PROTECTED] <[EMAIL PROTECTED]>:


Ion Silvestru <[EMAIL PROTECTED]> wrote:
> If we have a query where we compare a column to a set of values, then
> which is faster: OR or IN?
> Ex: OR: (mycol = "a") OR (mycol = "b") OR (mycol = "c") OR...
> IN: (mycol IN "a", "b", "c" ...)
>

IN is faster.  However, version 3.2.3 introduced an enhancement
to the SQLite optimizer that automatically converts the OR form
of the expression above into the IN form, thus taking advantage
of the increased speed of IN.  So for SQLite version 3.2.3, there
really is no difference between the two.

See http://www.sqlite.org/optoverview.html#or_opt

--
D. Richard Hipp  <[EMAIL PROTECTED]>



-
To unsubscribe, send email to [EMAIL PROTECTED]

-





--
powered by python