|
Best practices
1.1. With PostgreSQL databases, the server performance might suffer if you use the FULL OUTER JOIN clause in your queries. The impact is more significant when the query contains a few of such clauses.
That's why it is not recommended that you use FULL OUTER JOIN in queries. Whenever possible, reconsider the query text to replace this clause with an equivalent.
For example, the query
SELECT
ISNULL(SalesPlan.Products, ActualSales.Products) AS Products,
ISNULL(SalesPlan.Sum, 0) AS SumPlan,
ISNULL(ActualSales.Sum, 0) AS ActualSum
FROM
SalesPlan AS SalesPlan
FULL JOIN ActualSales AS ActualSales
BY SalesPlan.Products = ActualSales.Products
can be replaced with the following query that doesn't contain FULL OUTER JOIN:
SELECT
TargetActualSales.Products AS Products,
SUM(TargetActualSales.SumTarget) AS SumTarget,
SUM(TargetActualSales.SumActual) AS SumActual
FROM
(SELECT
SalesPlan.Products AS Products,
SalesPlan.Sum AS SumPlan,
0 AS SumActual
FROM
SalesPlan AS SalesPlan
UNION ALL
SELECT
ActualSales.Products,
0,
ActualSales.Sum
FROM
ActualSales AS ActualSales) AS TargetActualSales
GROUP BY
TargetActualSales.Products
1.2. You can ignore the above mentioned recommendation and use FULL OUTER JOIN only when other clauses cannot deliver the required result. Note that when you execute a query in PostgreSQL, 1C:Enterprise substitutes FULL OUTER JOIN with an equivalent clause. All query attributes, such as TOP, DISTINCT, and ORDER BY, remain intact. In this scenario, there is no need to get rid of FULL OUTER JOIN in your query.
|
