>I edited this second script to use plain apsw.
>In the vi editor:
>%s/db.execute/db.cursor().execute/
>%s/executescript/execute/
>%s/db.commit()/db.cursor().execute("COMMIT")/
>/Method 9
>.,$d
>
>(the carray tests are left out)
>My test output for 1000 keys is:
>$ python3 keith2b.py 1000
>
> Op 17 sep. 2019, om 04:26 heeft Keith Medcalf het
> volgende geschreven:
>
>
> On Monday, 16 September, 2019 14:22, E.Pasma wrote:
>
>> Stop stop stop
>
> You are right. What a difference a spelling error makes ... No wonder it
> took so long as it was doing table scans -- and the
On Fri, Sep 13, 2019 at 6:38 PM Jens Alfke wrote:
> (b) Execute "SELECT key, … FROM table WHERE key IN (…)", including all of
> the key strings.
>
> If I do (b), SQLite has less setup work to do, and it could potentially
> optimize the b-tree lookup. On the downside, I have to prepare a
> On Sep 13, 2019, at 1:30 PM, Keith Medcalf wrote:
>
> The only thing that is clear is that where the overhead of executing each
> select is significant it is clearly better to execute fewer of them.
Thanks for the research, Keith!
In my case the per-query overhead is lower since I'm
I blows me away that you are able to produce such things as this at the drop of
a hat!
Thanks for your insights and ingenuity and completeness!
Doug
> -Original Message-
> From: sqlite-users
> On Behalf Of Keith Medcalf
> Sent: Friday, September 13, 2019 1:30 PM
> To: SQLite mailing
That depends greatly on the overhead you have for executing each select
statement. So I wrote a little test that uses my customized apsw library from
Python 3. It also works using the as-distributed sqlite3 wrapper (except for
the carray interface, which requires my customized apsw to be
Another possibility... INSERT the keys in a temporary table and do an
appropriate JOIN.Sent from my Samsung Galaxy S7 - powered by Three
Original message From: Simon Slavin
Date: 13/09/2019 17:51 (GMT+00:00) To: SQLite mailing list
Subject: Re: [sqlite] Fastest way to
On 9/13/19, Jens Alfke wrote:
> If I have a set of primary keys (let's say a few hundred) and need to fetch
> data from the table rows with those keys, what's the fastest way to do so?
> The options seem to be:
>
> (a) Execute "SELECT … FROM table WHERE key=?", once for each key.
> (b) Execute
Jens Alfke, on Friday, September 13, 2019 12:38 PM, wrote...
> (a) Execute "SELECT … FROM table WHERE key=?", once for each key.
> (b) Execute "SELECT key, … FROM table WHERE key IN (…)", including all of the
> key strings.
I have found that the ... IN ... has provided a much faster result
On 13 Sep 2019, at 5:38pm, Jens Alfke wrote:
> Does anyone have intuition or actual knowledge about which approach is
> better? Or know of a 3rd better approach?
My guess is (b), but it will depend on your particular setup. Depends on cache
size, storage speed, whether your OS is real or
If I have a set of primary keys (let's say a few hundred) and need to fetch
data from the table rows with those keys, what's the fastest way to do so? The
options seem to be:
(a) Execute "SELECT … FROM table WHERE key=?", once for each key.
(b) Execute "SELECT key, … FROM table WHERE key IN
11 matches
Mail list logo
