[SQL] How to Optimize Combined SQL Query with Multiple Joins and CTEs for Better Performance?

I am experiencing significant performance degradation when combining two SQL queries that run efficiently on their own. The combined query takes around 2 minutes to execute, whereas the individual queries take only 5-10 seconds each.

The first query creates a view using a CTE to process technical data related to shop orders. The second query selects data from several tables and joins them to get information about activities, shop orders, and related technical data. When I combine these queries, the performance drops significantly.

Here are the details of the individual queries and the combined query:

Query 1:


This query creates a view VIEW_TECH_DATA using a CTE:

WITH NumberedData AS (
SELECT
[SHOP_ORDER] as Shop_Order,
[SHOP_ORDER] COLLATE Latin1_General_CI_AS AS SHOP_ORDER_COLLATED,
MKWRTBEZ COLLATE Latin1_General_CI_AS AS MKWRTBEZ,
ATNAM,
ROW_NUMBER() OVER (PARTITION BY [SHOP_ORDER], ATNAM ORDER BY [SHOP_ORDER]) AS RowNum
FROM [my_schema].[my_table]
WHERE LEN(SHOP_ORDER) = 8
AND ATNAM IN ('HG_KATALOGBEZEICHG', 'OEL_VISKOSITAET', 'SCHMIERSTOFF', 'RAL_NUMMER', 'EDATU', 'GL_STUECKZAHL', 'CO_SCHMIERSTOFFMENGE_DEC2', 'GEWICHT', 'ME_KUNDE')
AND (ATNAM != 'EDATU' OR (ATNAM = 'EDATU' AND CAST(MKWRTBEZ as date) > DATEADD(WEEK, -1, GETDATE())))
)
SELECT
Shop_Order,
SHOP_ORDER_COLLATED,
COALESCE(MAX(CASE WHEN ATNAM = 'HG_KATALOGBEZEICHG' AND RowNum = 2 THEN MKWRTBEZ END) + ' ', '') + MAX(CASE WHEN ATNAM = 'HG_KATALOGBEZEICHG' AND RowNum = 1 THEN MKWRTBEZ END) AS Prodotto,
MAX(CASE WHEN ATNAM = 'OEL_VISKOSITAET' THEN MKWRTBEZ END) AS Olio,
MAX(CASE WHEN ATNAM = 'SCHMIERSTOFF' THEN MKWRTBEZ END) AS Codice_Olio,
MAX(CASE WHEN ATNAM = 'RAL_NUMMER' THEN MKWRTBEZ END) AS Vernice,
MAX(CASE WHEN ATNAM = 'EDATU' THEN CAST(MKWRTBEZ as date) END) AS Data_Consegna,
MAX(CASE WHEN ATNAM = 'GL_STUECKZAHL' THEN MKWRTBEZ END) AS Quantita,
MAX(CASE WHEN ATNAM = 'CO_SCHMIERSTOFFMENGE_DEC2' THEN MKWRTBEZ END) AS Qta_Olio,
MAX(CASE WHEN ATNAM = 'GEWICHT' THEN MKWRTBEZ END) AS Massa,
MAX(CASE WHEN ATNAM = 'ME_KUNDE' THEN MKWRTBEZ END) AS Cliente
FROM NumberedData
GROUP BY
Shop_Order,
SHOP_ORDER_COLLATED
HAVING
MAX(CASE WHEN ATNAM = 'EDATU' THEN CAST(MKWRTBEZ as date) END) IS NOT NULL;

Query 2:


This query selects data from several tables and joins them:

SELECT activity.sfc,
Substring(activity.operation, 20, 7) AS Operation,
steps.priority AS Priorita,
shop_order.shop_order,
shop_order.QTY_TO_BUILD AS Quantità,
shop_order.released_date AS Data_Stampa,
shop_order.planned_start_date AS Schedulazione,
shop_order.planned_comp_date AS Data_Fine
FROM dbo.activity_log AS activity
LEFT OUTER JOIN dbo.sfc AS steps ON steps.sfc = activity.sfc
LEFT OUTER JOIN dbo.shop_order AS shop_order ON steps.shop_order_bo = shop_order.handle
WHERE (1 = 1)
AND (activity.sfc NOT IN (SELECT sfc FROM dbo.activity_log WHERE (action_code = 'DC')))
AND (steps.status_bo LIKE '%401')
AND (steps.sfc LIKE '%.00.%')
AND (CAST(steps.CREATED_DATE_TIME AS datetime) >= CAST('2024-05-21 00:00:00' AS datetime))

Combined Query:


When I combine these queries, the performance drops significantly, taking around 2 minutes to execute:

WITH NumberedData AS (
SELECT
[SHOP_ORDER] as Shop_Order,
[SHOP_ORDER] COLLATE Latin1_General_CI_AS AS SHOP_ORDER_COLLATED,
MKWRTBEZ COLLATE Latin1_General_CI_AS AS MKWRTBEZ,
ATNAM,
ROW_NUMBER() OVER (PARTITION BY [SHOP_ORDER], ATNAM ORDER BY [SHOP_ORDER]) AS RowNum
FROM [my_schema].[my_table]
WHERE LEN(SHOP_ORDER) = 8
AND ATNAM IN ('HG_KATALOGBEZEICHG', 'OEL_VISKOSITAET', 'SCHMIERSTOFF', 'RAL_NUMMER', 'EDATU', 'GL_STUECKZAHL', 'CO_SCHMIERSTOFFMENGE_DEC2', 'GEWICHT', 'ME_KUNDE')
AND (ATNAM != 'EDATU' OR (ATNAM = 'EDATU' AND CAST(MKWRTBEZ as date) > DATEADD(WEEK, -1, GETDATE())))
)
, VIEW_TECH_DATA AS (
SELECT
Shop_Order,
SHOP_ORDER_COLLATED,
COALESCE(MAX(CASE WHEN ATNAM = 'HG_KATALOGBEZEICHG' AND RowNum = 2 THEN MKWRTBEZ END) + ' ', '') + MAX(CASE WHEN ATNAM = 'HG_KATALOGBEZEICHG' AND RowNum = 1 THEN MKWRTBEZ END) AS Prodotto,
MAX(CASE WHEN ATNAM = 'OEL_VISKOSITAET' THEN MKWRTBEZ END) AS Olio,
MAX(CASE WHEN ATNAM = 'SCHMIERSTOFF' THEN MKWRTBEZ END) AS Codice_Olio,
MAX(CASE WHEN ATNAM = 'RAL_NUMMER' THEN MKWRTBEZ END) AS Vernice,
MAX(CASE WHEN ATNAM = 'EDATU' THEN CAST(MKWRTBEZ as date) END) AS Data_Consegna,
MAX(CASE WHEN ATNAM = 'GL_STUECKZAHL' THEN MKWRTBEZ END) AS Quantita,
MAX(CASE WHEN ATNAM = 'CO_SCHMIERSTOFFMENGE_DEC2' THEN MKWRTBEZ END) AS Qta_Olio,
MAX(CASE WHEN ATNAM = 'GEWICHT' THEN MKWRTBEZ END) AS Massa,
MAX(CASE WHEN ATNAM = 'ME_KUNDE' THEN MKWRTBEZ END) AS Cliente
FROM NumberedData
GROUP BY
Shop_Order,
SHOP_ORDER_COLLATED
HAVING
MAX(CASE WHEN ATNAM = 'EDATU' THEN CAST(MKWRTBEZ as date) END) IS NOT NULL
)
SELECT
activity.sfc,
Substring(activity.operation, 20, 7) AS Operation,
steps.priority AS Priorità,
shop_order.shop_order,
shop_order.QTY_TO_BUILD AS Quantità,
tech.[Prodotto],
tech.[Olio],
tech.[Vernice],
tech.[Data_Consegna],
shop_order.planned_start_date AS Schedulazione,
prod_cells.[Workcenter]
FROM
dbo.activity_log AS activity
LEFT JOIN dbo.sfc AS steps ON steps.sfc = activity.sfc
LEFT JOIN dbo.shop_order AS shop_order ON activity.shop_order_bo = shop_order.handle
LEFT JOIN VIEW_TECH_DATA AS tech
ON tech.SHOP_ORDER_COLLATED = shop_order.shop_order
RIGHT OUTER JOIN dbo.products_cells_association as prod_cells
ON tech.[Prodotto] LIKE '%' + prod_cells.type_size + '%'
AND tech.[Prodotto] LIKE prod_cells.type_group + '%'

WHERE
NOT EXISTS (SELECT 1 FROM dbo.activity_log sub_activity WHERE sub_activity.sfc = activity.sfc AND sub_activity.action_code = 'DC')
AND steps.status_bo LIKE '%401'
AND steps.sfc LIKE '%.00.%'
AND CAST(steps.CREATED_DATE_TIME AS datetime) >= CAST('2024-09-21 00:00:00' AS datetime)

GROUP BY
activity.sfc,
Substring(activity.operation, 20, 7),
steps.priority,
shop_order.shop_order,
shop_order.QTY_TO_BUILD,
tech.[Prodotto],
tech.[Olio],
tech.[Vernice],
tech.[Data_Consegna],
shop_order.planned_start_date,
prod_cells.Workcenter,
prod_cells.Opratio
ORDER BY
tech.[Data_Consegna];


I would like to add that in the VIEW_TECH_DATA view there is already an index on the SHOP_ORDER column, which makes it very efficient. If there are some other tips on how I could make this combined query more efficient it would be awesome.

I want to optimize the combined query so that it executes in a reasonable amount of time, ideally similar to the performance of the individual queries. Any suggestions for improving the performance of this combined query would be greatly appreciated.

Continue reading...