> Actually my understanding would suggest that INSERT OR REPLACE should
> execute slower than UPDATE + INSERT (or INSERT + UPDATE).
After some experimentation, I think that they turn out to be a wash in my case.
INSERT OR REPLACE is implemented in sqlite as INSERT OR DELETE THEN INSERT,
which d
> > > BTW, in case you don't do that yet your best performance will be if
> > > you prepare your UPDATE and INSERT statements only once and then do
> > > bind + step + reset in that 100k times loop.
> > >
> >
> > In principle I agree, but since the temporary-table version is blindingly
> > fas
On Wed, Mar 13, 2013 at 11:48 AM, David King wrote:
>> BTW, in case you don't do that yet your best performance will be if
>> you prepare your UPDATE and INSERT statements only once and then do
>> bind + step + reset in that 100k times loop.
>
>
> In principle I agree, but since the temporary-tabl
On Wed, 13 Mar 2013 14:19:05 -0400
Igor Tandetnik wrote:
> > I'm not sure about SQLite, but in principle the query optimizer can
> > often use the base table's index for a derived value. Consider
> >
> > A join B on A.a = 1 + B.a
> > or
> > A join B on A.a < sqrt(B.a)
> >
> > An index on
> > The logic is, "keep a given (k1, k2) pair around for one day for each
> > time it's been seen". I could calculate it when it's needed, but
> > since it's a computed value, I couldn't then have an index on it.
>
> I'm not sure about SQLite, but in principle the query optimizer can
> often use t
> BTW, in case you don't do that yet your best performance will be if
> you prepare your UPDATE and INSERT statements only once and then do
> bind + step + reset in that 100k times loop.
In principle I agree, but since the temporary-table version is blindingly fast
up the the update-the-disk por
On Wednesday, 13 March, 2013 at 06:15, Michael Black wrote:
> You're simply missing the where clause on your update so you're updating the
> entire database every time you do an insert.
> update_counter(k1,k2 count=count+1,expires=now+count*1day) where field1=k1
> and field2=k2;
>
> And a (k1,k2)
On 3/13/2013 8:49 AM, James K. Lowden wrote:
I'm not sure about SQLite, but in principle the query optimizer can
often use the base table's index for a derived value. Consider
A join B on A.a = 1 + B.a
or
A join B on A.a < sqrt(B.a)
An index on B.a is useful to finding the valu
On Tue, Mar 12, 2013 at 11:03 PM, David King wrote:
>> > At first I was just doing something like this pseducode:
>> > update_counter(k1, k2, count=count+1, expires=now+count*1day)
>> > if rows_updated != 1: insert_counter(k1, k2, count=1, expires=now+1day)
>>
>> Assuming these 2 statements consti
You're simply missing the where clause on your update so you're updating the
entire database every time you do an insert.
update_counter(k1,k2 count=count+1,expires=now+count*1day) where field1=k1
and field2=k2;
And a (k1,k2) index would help that update a lot.
_
On Tue, 12 Mar 2013 21:20:11 -0700
David King wrote:
> > > At first I was just doing something like this pseducode:
> > > update_counter(k1, k2, count=count+1, expires=now+count*1day)
> > > if rows_updated != 1: insert_counter(k1, k2, count=1, expires=now
> > > +1day)
> >
> > Might I suggest tha
>> First of all in the statement above you don't gain benefit from
>> uniqueness and replace about 10k rows twice.
>
> Are you sure? The SELECT in the INSERT OR UPDATE selects "FROM
> trans_counters_v AS c", the grouped temporary view. So it should only see any
> given key pair once before it sta
> > At first I was just doing something like this pseducode:
> > update_counter(k1, k2, count=count+1, expires=now+count*1day)
> > if rows_updated != 1: insert_counter(k1, k2, count=1, expires=now+1day)
>
> Assuming these 2 statements constitute each of the 10k-100k steps you
> mentioned above an
On Tue, Mar 12, 2013 at 8:29 PM, David King wrote:
> I'm trying to find an efficient way to store simple incrementing integers but
> I'm having trouble finding an efficient way to do it
>
> My database looks like:
>
> CREATE TABLE counters
> k1, k2,
> count, -- how many we've seen
> expires
> > At first I was just doing something like this pseducode:
> > update_counter(k1, k2, count=count+1, expires=now+count*1day)
> > if rows_updated != 1: insert_counter(k1, k2, count=1, expires=now+1day)
>
> Might I suggest that instead of trying to store an ever-changing value, you
> simply figur
On 13 Mar 2013, at 3:29am, David King wrote:
> At first I was just doing something like this pseducode:
>
> update_counter(k1, k2, count=count+1, expires=now+count*1day)
> if rows_updated != 1: insert_counter(k1, k2, count=1, expires=now+1day)
Might I suggest that instead of trying to store an
I'm trying to find an efficient way to store simple incrementing integers but
I'm having trouble finding an efficient way to do it
My database looks like:
CREATE TABLE counters
k1, k2,
count, -- how many we've seen
expires,
PRIMARY KEY (k1, k2)
);
CREATE INDEX counters_expires_idx ON cou
17 matches
Mail list logo
