On Wed, 17 Jan 2018 10:48:10 +0200
R Smith <[email protected]> escribió:
> Hang on a second - True, there is nothing to be done to improve the
> speed of that specific Query - BUT - there is plenty to be done to
> improve the speed of the database for your specific use case. The usual
> suspects that comes to mind are:
>
> 1. Use a faster Journal mode (Have to weigh speed against
> power-loss-data-integrity-protection),
> 2. Use an in-memory DB (especially if you only reading from it)
> 3. Start a transaction and hold the DB read locks for the duration of
> your application (again, if it won't need writing)
> 4. Ensure there are no other time-consuming bits of code in the sqlite
> api RESET()-STEP() loop. (And if you are accessing it through a wrapper,
> stop that and use the api directly).
>
> We could and would probably give more useful direct advice if we know a
> lot more about your specific use case, platforms, access-method and setup.
What about db normalization?
Change:
CREATE TABLE t1 (
a INTEGER PRIMARY KEY AUTOINCREMENT,
b INTEGER NOT NULL UNIQUE,
c INTEGER NOT NULL,
d INTEGER,
e TEXT,
f INTEGER,
g INTEGER,
h TEXT,
i INTEGER
);
To:
CREATE TABLE t1 (
a INTEGER PRIMARY KEY AUTOINCREMENT,
b INTEGER NOT NULL UNIQUE,
c INTEGER NOT NULL,
d INTEGER,
f INTEGER,
g INTEGER,
i INTEGER,
e INTEGER,
h INTEGER,
FOREIGN KEY (e) REFERENCES t2(j),
FOREIGN KEY (h) REFERENCES t2(j)
);
CREATE TABLE t2 (
j INTEGER PRIMARY KEY
k TEXT
);
You don't need to have in t2 e and h columns, only k one. Table t1 will have
fixed row size (not exactly true) and lot less table size; t2 will store each
text only once, if some e and some h originally had the same content.
You can set pragma foreign_keys = ON to force sqlite check the constraint, or
disable it with pragma foreign_keys = OFF if you trust your data input; e, h
have always values in t2.j.
HTH
--
Eduardo <[email protected]>
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
