Hi,
I'm a big fan of SQLite, but I've been having some terrible speed problems
when doing joins between subqueries. Strangely, if I join twice (first to
the main table and then to its subquery), I can get the same answer in
tenths of seconds and not minutes - or one thousand times faster.
For example, this takes minutes: CPU Time: user 263.759513 sys 2.237379
SELECT s.Id, s.BookingId, s.At, s.DateIn, s.DateOut, s.RoomUID,
s.RatePlanUID FROM StayV s
WHERE NOT EXISTS (SELECT NULL FROM InvoiceItemV i WHERE i.PriceUID IS NULL
AND i.StayUID=s.UID);
While this takes tenths of seconds for the same result: CPU Time: user
0.231575 sys 0.021046
SELECT s.Id, s.BookingId, s.At, s.DateIn, s.DateOut, s.RoomUID,
s.RatePlanUID FROM StayV s
WHERE NOT EXISTS (SELECT NULL FROM InvoiceItem i WHERE i.PriceUID IS NULL
AND i.StayUID=s.UID AND EXISTS (SELECT NULL FROM InvoiceItemV v WHERE
i.UID=v.UID));
The latter looks like more work yet is 1000 times faster. Thoughts?
Background:
Generally, I don't delete or update records for this project. I create new
ones with a matching ID field. This allows cool things like getting a
customers address as it was when the invoices were made without storing it
with each invoice. Or navigating the database as it was yesterday, before
little Jimmy started playing.
This is but one example - here I'm trying to find a complete list of
unbilled stays at a motel. (Or a stay that has been updated, but there's
not matching invoice item.)
For simplicity I use a couple of views, so I can see the Stays as it was at
a given time.
CREATE VIEW StayV AS SELECT * FROM Stay WHERE UID IN (SELECT MAX(UID) FROM
Stay WHERE At <= 20160401000000 GROUP BY Id) AND Remove = 0;
CREATE VIEW InvoiceItemV AS SELECT * FROM InvoiceItem WHERE UID IN (SELECT
MAX(UID) FROM InvoiceItem WHERE At <= 20160401000000 GROUP BY Id) AND
Remove = 0;
Here are the tables:
CREATE TABLE InvoiceItem (
UID INTEGER PRIMARY KEY AUTOINCREMENT,
Id INTEGER,
At INT CHECK(TYPEOF(At) IN ("integer","null")),
UserUID INT NOT NULL CHECK(TYPEOF(UserUID) = "integer"),
Remove INT NOT NULL DEFAULT 0 CHECK(Remove = 0 OR Remove = 1),
ClientId INTEGER, -- MUST be NULL if InvoiceId or StayId filled out.
InvoiceId INTEGER,
StayUID INTEGER,
PriceUID INTEGER, -- MUST be NULL if StayId filled out.
Quantity FLOAT NOT NULL DEFAULT 1,
DescriptionOverride TEXT,
PriceOverride INT -- NULL if unused
);
CREATE TRIGGER InvoiceItemTrigger AFTER INSERT ON InvoiceItem BEGIN
UPDATE InvoiceItem SET Id=NEW.UID WHERE UID=NEW.UID AND NEW.Id IS NULL;
UPDATE InvoiceItem SET At=CAST(STRFTIME('%Y%m%d%H%M%S','now') as "integer")
WHERE UID=NEW.UID AND NEW.At IS NULL;
END;
CREATE TABLE Stay (
UID INTEGER PRIMARY KEY AUTOINCREMENT,
Id INTEGER,
At INT CHECK(TYPEOF(At) IN ("integer","null")),
UserUID INT NOT NULL CHECK(TYPEOF(UserUID) = "integer"),
Remove INT NOT NULL DEFAULT 0 CHECK(Remove = 0 OR Remove = 1),
BookingId INT NOT NULL,
RoomUID INT NOT NULL REFERENCES Room(UID),
RatePlanUID TEXT NOT NULL DEFAULT '', -- TODO
DateIn INT NOT NULL,
DateOut INT NOT NULL,
Eta INT NOT NULL DEFAULT 0,
Adults INT NOT NULL DEFAULT 0,
Children INT NOT NULL DEFAULT 0,
Infants INT NOT NULL DEFAULT 0,
ClientId INT NOT NULL,
Price INT,
Comment TEXT NOT NULL DEFAULT '',
Status INT NOT NULL DEFAULT 0
);
CREATE TRIGGER StayTrigger AFTER INSERT ON Stay BEGIN
UPDATE Stay SET Id=NEW.UID WHERE UID=NEW.UID AND NEW.Id IS NULL;
UPDATE Stay SET At=CAST(STRFTIME('%Y%m%d%H%M%S','now') as "integer") WHERE
UID=NEW.UID AND NEW.At IS NULL;
END;
CREATE INDEX InvoiceItem_StayUID ON InvoiceItem(StayUID);
Thanks.
