Hi all,
I need to create a temporary table holding ID column values from a
number of tables, so that I have a unique way to access the data for
display purposes: say I have 2 related tables
CREATE TABLE person (
id INTEGER PRIMARY KEY,
name TEXT,
age INTEGER
);
CREATE TABLE address (
id INTEGER PRIMARY KEY,
person_id INTEGER,
type INTEGER,
street TEXT,
town TEXT,
zip INTEGER
);
Each person can have multiple address entries.
When displaying a filtered subset of the persons with all their
addresses, I'd like to create a temporary table holding the person.id
and address.id columns for each row being displayed:
CREATE TEMP TABLE display AS
SELECT
person.id as person_id,
address.id as address_id
FROM
person
LEFT JOIN
address ON address.person_id=person.id
WHERE
person.name LIKE '%foo%'
ORDER BY
person.name,
address.type
;
To complicate things, I have enumeration tables that are used in the
display (let's say for the address type), which can also be filtered
for, e.g.
CREATE TABLE address_type_enum (
key INTEGER,
value TEXT
);
INSERT INTO address_type_enum (1, 'Home');
INSERT INTO address_type_enum (2, 'Work');
so that the real query to create the temp table might look like this:
CREATE TEMP TABLE display AS
SELECT
person.id as person_id,
address.id as address_id
FROM
person
LEFT JOIN
address ON address.person_id=person.id
WHERE
person.name LIKE '%foo%'
AND
(SELECT value FROM address_type_enum WHERE key=address.type) LIKE
'work'
ORDER BY
person.name,
(SELECT value FROM address_type_enum WHERE key=address.type)
COLLATE NOCASE
;
Here, I have two subqueries fetching the same value, so I figured it
would be helpful to use an intermediate view:
CREATE TEMP VIEW display_view AS
SELECT
person.id as person_id,
address.id as address_id,
person.name as person_name,
SELECT value FROM address_type_enum WHERE key=address.type) as
address_type
FROM
person
LEFT JOIN
address ON address.person_id=person.id
;
CREATE TEMP TABLE display AS
SELECT
person.id as person_id,
address.id as address_id
FROM
display_view
WHERE
person_name LIKE '%foo%'
AND
address_type LIKE 'work'
ORDER BY
person_name,
address_type COLLATE NOCASE
;
Now, finally, here's my question:
Is there a difference in performance whether I do the filtering and
ordering in the CREATE VIEW statement or in the CREATE TABLE AS
SELECT statement?
Would this one perform better:
CREATE TEMP VIEW display_view AS
SELECT
person.id as person_id,
address.id as address_id,
person.name as person_name,
SELECT value FROM address_type_enum WHERE key=address.type) as
address_type
FROM
person
LEFT JOIN
address ON address.person_id=person.id
WHERE
person_name LIKE '%foo%'
AND
address_type LIKE 'work'
ORDER BY
person_name,
address_type COLLATE NOCASE
;
CREATE TEMP TABLE display AS
SELECT
person.id as person_id,
address.id as address_id
FROM
display_view
;
Any ideas?
Thanks,
</jum>
P.S.: These are not real queries, they're just examples of much
larger and more complex queries I have in the real application. Also,
the tables may contain a few hundred thousand records each, so don't
be mistaken by the apparently straight forward table layout given...
