I have a scenario where I want to move 99+% of the records from one database
to another, initially empty but for a set of table definitions (in practice,
copied from a template file). On my Linux platform, I find that the INSERT
INTO archive.my_table SELECT * FROM my_table WHERE (...) takes
w-r--r-- 1 ceich ceich 512 Jan 29 10:46 db/emu.db-journal
But I did notice my code was doing a BEGIN EXCLUSIVE. Would this cause an
unmodified database to be locked anyway?
Chris
On Thu, Jan 29, 2009 at 2:02 AM, Dan <danielk1...@gmail.com> wrote:
>
> On Jan 29, 2009, at
I have read http://sqlite.org/atomiccommit.html which seems to imply that
all db files in a transaction are locked at the same level as the
transaction progresses (i.e. all Reserved, then all Exclusive, ...). This
makes sense when all the files are being changed, but I have a use case
where I am
device_event SET event_correlation_id = new.event_id
WHERE event_id = new.event_correlation_id;
END;
Chris
On Mon, Nov 17, 2008 at 9:11 AM, Chris Eich <[EMAIL PROTECTED]> wrote:
> [Sorry if you see this twice; I sent it yesterday and see it in the
> archives, but never got it in my in
[Sorry if you see this twice; I sent it yesterday and see it in the
archives, but never got it in my inbox. --Chris]
On Sun, Nov 16, 2008 at 12:59 PM, Chris Eich <[EMAIL PROTECTED]> wrote:
> I have an events table where SET events for a given device_id and
> event_code are followed b
I have an events table where SET events for a given device_id and event_code
are followed by CLR events.
CREATE TABLE device_event (
event_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
device_id INTEGER NOT NULL REFERENCES device,
event_date INTEGER NOT NULL,
event_code INTEGER
I think the simplest approach for me is to get rid of the join on device.
After I did so, SQLite uses the index on interval_end_date. I'll just need
to grab the device -> device_type mapping and interpret it myself.
Chris
On 7/11/07, Chris Eich <[EMAIL PROTECTED]> wrote:
Me agai
Me again. I said at the end of the last thread that I had learned a lot.
What I learned is that my query's performance problems were not due to
picking a bad index (given my dataset, the two indices were identical for
this query).
Instead the problem seems to be caused by ORDER BY and LIMIT
Joe, the two plans are actually equal (within a few %).
Thanks for all your help folks! I've learned quite a bit and I hope other
readers did too.
Chris
0
Chris
On 7/11/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
Joe Wilson <[EMAIL PROTECTED]> wrote:
> --- Chris Eich <[EMAIL PROTECTED]> wrote:
> > Thanks, I tried both 3.3.13 and 3.4.0. With 3.3.13 the +'s didn't make
a
> > difference; the loops always were in d,
order) the device_id
can't be plugged into the dpi1 index. EXPLAIN QUERY PLAN output doesn't show
how much of the index will be used--does anyone have a quick hack to show
that (I'm studying where.c :-)?
Chris
On 7/10/07, Joe Wilson <[EMAIL PROTECTED]> wrote:
--- Chris Eich <[EMAIL
The output of EXPLAIN QUERY PLAN doesn't change when I use the CROSS JOIN
trick to disable table reordering:
sqlite> explain query plan SELECT d.device_type, dpi.* FROM device d,
device_perf_interval dpi WHERE d.device_id=dpi.device_id AND
dpi.interval_type=1 AND dpi.interval_duration=300;
1, d.device_id, 300))
...
Did I miss something?
Chris
P.S. The vast majority of dpi rows have the same type and duration, so an
index on just those wouldn't help much.
On 7/10/07, Joe Wilson <[EMAIL PROTECTED]> wrote:
--- Chris Eich <[EMAIL PROTECTED]> wrote:
> I realiz
I realize that but thought that device_id would be included because of the
d.device_id=dpi.device_id term. What am I missing?
Chris
On 7/10/07, Igor Tandetnik <[EMAIL PROTECTED]> wrote:
> and two indexes on the latter table:
>
> CREATE INDEX dpi1 ON device_perf_interval(
>interval_type,
>
I have a database with the following tables:
CREATE TABLE device (
device_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
device_type INTEGER NOT NULL, -- lookup in device_type
...
);
CREATE TABLE device_perf_interval (
interval_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
15 matches
Mail list logo
