Hallo Zusammen,
ich habe ein SQL-Statment mit mehreren UNION-Befehlen.
Diese UNION Befehle besitzen mehrere JOINs. Kann ich die JOINS, die in allen vorkommen, auch am Ende einmalig aufrufen???
Dann würde ich ja deutlich Performancesteigerungen haben. Und momentan dauert es leider ewig…
Query:
SET SEARCH_PATH TO public, "test";
SELECT *
FROM (SELECT a.link_id AS LinkID, GREATEST(a.From_Ref_Speed_Limit, a.To_Ref_Speed_Limit) as SpeedLimit,
CASE WHEN a.speed_limit_source = '1' THEN 1 WHEN a.speed_limit_source = '2' THEN 2 ELSE 0
END as LegalType, 0 as LimitType, 0 as vehicletype, '' as timedomain
FROM rdf_nav_link a ) AS A1
WHERE SpeedLimit is not null
UNION ALL
SELECT *
FROM (SELECT a.link_id AS LinkID, d.special_speed_limit AS SpeedLimit, 1 AS LegalType,
CASE WHEN d.dependent_speed_type = 1 THEN 1 WHEN d.dependent_speed_type = 2 THEN 2 WHEN d.dependent_speed_type = 3 THEN 3 WHEN d.dependent_speed_type = 7 THEN 4 ELSE 0 END as LimitType, CASE WHEN g.automobiles = 'Y' AND g.trucks = 'Y' AND g.motorcycles = 'Y' THEN 0 WHEN g.automobiles = 'Y' AND g.trucks = 'N' AND g.motorcycles = 'N' THEN 1 WHEN g.automobiles = 'N' AND g.trucks = 'Y' AND g.motorcycles = 'N' THEN 2 WHEN g.automobiles = 'N' AND g.trucks = 'N' AND g.motorcycles = 'Y' THEN 3 ELSE 0 END as vehicleTypes,CASE WHEN d.dependent_speed_type = 4 THEN (SELECT t.time_domain FROM rdf_time_domain t WHERE d.condition_id = t.feature_id) ELSE '' END as timedomain
FROM rdf_nav_link a
JOIN rdf_nav_strand l ON a.link_id = l.link_id
JOIN rdf_condition f ON l.nav_strand_id = f.nav_strand_id
JOIN rdf_condition_speed d ON f.condition_id = d.condition_id AND d.special_speed_limit IS NOT NULL
JOIN rdf_access g ON f.access_id = g.access_id) AS A2
UNION ALL
SELECT *
FROM (SELECT a.link_id AS LinkID, 0 AS SpeedLimit, 1 AS LegalType,5 as LimitType,CASE WHEN g.automobiles = 'Y' AND g.trucks = 'Y' AND g.motorcycles = 'Y' THEN 0 WHEN g.automobiles = 'Y' AND g.trucks = 'N' AND g.motorcycles = 'N' THEN 1 WHEN g.automobiles = 'N' AND g.trucks = 'Y' AND g.motorcycles = 'N' THEN 2 WHEN g.automobiles = 'N' AND g.trucks = 'N' AND g.motorcycles = 'Y' THEN 3 ELSE 0 END as vehicleTypes,'' as timedomain
FROM rdf_nav_link a
JOIN rdf_nav_strand l ON a.link_id = l.link_id
JOIN rdf_condition f ON l.nav_strand_id = f.nav_strand_id AND f.condition_type = 11
JOIN rdf_access g ON f.access_id = g.access_id) AS A3
ORDER BY LinkID