Re: [sqlite] Efficient select for multiple ids

2004-09-15 Thread D. Richard Hipp 
Ken Cooper wrote:
SELECT id, . FROM additionalchunks WHERE id=? OR id=? OR . 

or
SELECT id, . FROM additionalchunks WHERE id in (? ? ? ? ? .)
Which of these is the more efficient...
The second, assuming you have an index on additionalchunks.id.
Without an index, they are about the same.
--
D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565

RE: [sqlite] Efficient select for multiple ids

2004-09-15 Thread Ken Cooper 
The '.'s below should be ellipses.

-Original Message-
From: Ken Cooper [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, September 15, 2004 1:56 PM
To: [EMAIL PROTECTED]
Subject: [sqlite] Efficient select for multiple ids

I am implementing a virtualized listview on the result of a query by first
retrieving all unique ids for each row in the query result, then on demand
looking up additional information based on the ids in small chunks, say 20
at a time. I was thinking the best way to approach this second query was to
create and bind queries something like the following against the 20 ids I
have at the time:

 

SELECT id, . FROM additionalchunks WHERE id=? OR id=? OR . 

 

or

 

SELECT id, . FROM additionalchunks WHERE id in (? ? ? ? ? .)

 

Which of these is the more efficient way to approach the problem, or does
someone have a better suggestion? I remember seeing something referring to
this issue posted awhile back, but I'm having trouble locating it (googling
on 'where' and 'or' and 'in' is rather painful.).

 

Ken

[sqlite] Efficient select for multiple ids

2004-09-15 Thread Ken Cooper 
I am implementing a virtualized listview on the result of a query by first
retrieving all unique ids for each row in the query result, then on demand
looking up additional information based on the ids in small chunks, say 20
at a time. I was thinking the best way to approach this second query was to
create and bind queries something like the following against the 20 ids I
have at the time:

 

SELECT id, . FROM additionalchunks WHERE id=? OR id=? OR . 

 

or

 

SELECT id, . FROM additionalchunks WHERE id in (? ? ? ? ? .)

 

Which of these is the more efficient way to approach the problem, or does
someone have a better suggestion? I remember seeing something referring to
this issue posted awhile back, but I'm having trouble locating it (googling
on 'where' and 'or' and 'in' is rather painful.).

 

Ken