Am 13.07.2006 um 14:10 schrieb Christian Smith:
Inline.
ditto
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.
That's exactly what it is for. Basically, it's a cache for a table
view in the UI that needs to have a way of identifying/addressing
rows of joined data in multiple tables. Since the table view will
call the controller for any row / column data it needs, my code must
have a way to quickly perform random access in a view-like query
result set.
And no thanks, I don't want to cache the data in memory - been there,
done that, but it wasn't pretty (memory-footprint-wise) ;-)
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';
As I mentioned at the very end of my original mail, the real queries
are much more complex and are, in fact, generated dynamically
according to the user specification which columns should be displayed...
The tables I specified are really just example tables to show the
problem.
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.
Basically, the two are going to be executed right one after the other
and the temp view won't be available afterwards, so the only issue I
have is whether one is going to perform better than the other.
Some quick and dirty test didn't show any difference - in fact for
the query I tried, both ways actually produced the same byte code
instructions (explain output), but this might be just for the query I
chose as my example... I guess I'm looking for some better evidence
that it doesn't really matter ;-)
</jum>
