Inline.
Jens Miltner uttered:
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
[snip schema]
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
;
What purpose would this table serve? It only has ids in it, so is not any
more useful for searching than the base tables, unless you're going to be
using it as a cache and processing the result multiple times.
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
;
This is horrendously complex for what you're trying to achieve. Why not
just join on the address type? To get work addresses, something like:
SELECT
person.id AS person_id,
address.id AS address_id
FROM
person
LEFT JOIN address ON address.person_id=person.id
LEFT JOIN address_type_enum ON key=address.type
WHERE
address_type_enum.value LIKE 'work';
Here, I have two subqueries fetching the same value, so I figured it would be
helpful to use an intermediate view:
[snip intermediate view stuff]
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?
I'd only create a temporary table if the data is going to be reused
multiple times. Else, I'd create a view. You can always create a temporary
table from the view, as you've shown below, but it is not necassary unless
you want to cache the data.
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
;
This would be a better bet. Define views to create your views by whatever
criteria you see fit. Store in a temproary table for caching purposes.
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...
--
/"\
\ / ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
X - AGAINST MS ATTACHMENTS
/ \
