Bosses: A table A left join another table B, on different conditions, the need to join the same table for many times, and in the select out the results to take in the join table B first not null columns, found out the result of this time is relatively slow, can have the means to optimize?
Such as the following SQL CC_SAPAccountingDocumentDetail need to repeatedly left join V_CC_GetLatestCostCenter on different conditions,
Finally select the results will take out the first non-empty on V_CC_GetLatestCostCenter CostCenterDescription fields, there is no other written with high efficiency,
Thank you,

The current SQL is as follows:
The SELECT a.S upplierCode, a.Y ear,

COALESCE (CASE a.S pecialCostCenterCode WHEN 'THEN NULL
The ELSE a.S pecialCostCenterCode END, CASE a.C ostCenterCode WHEN 'THEN NULL ELSE a.C ostCenterCode END,
CASE a.A ctualCostCenter WHEN 'THEN NULL ELSE a.A ctualCostCenter END, N') AS ActualCostCenter,

COALESCE (scost. CostCenterDescription, dc ostCenterDescription, Arthur c. ostCenterDescription, N ') AS CostCenterDescription,
COALESCE (scost. CostCenterType, dc ostCenterType, Arthur c. ostCenterType, N ') AS CostCenterType,
COALESCE (scost. CostCenterTypeDescription, dc ostCenterTypeDescription, Arthur c. ostCenterTypeDescription, N ') AS CostCenterTypeDescription,
COALESCE (a.S pecialProfitCenter, Amy polumbo rofitCenter) AS ProfitCenter

The FROM dbo. CC_SAPAccountingDocumentDetail AS a
Atul gawande at the INNER JOIN dbo. CC_SAPAccountingDocumentMain AS b ON uid=b.G uid AND b.R st='True'
The LEFT OUTER JOIN dbo. V_CC_GetLatestCostCenter AS c ON a.C ostCenterCode=Arthur c. ostCenterCode
ON the LEFT OUTER JOIN dbo. V_CC_GetLatestCostCenter AS d a.A ctualCostCenter=, dc ostCenterCode
The LEFT OUTER JOIN dbo. V_CC_GetLatestCostCenter AS scost ON a.S pecialCostCenterCode=scost. CostCenterCode
The LEFT OUTER JOIN dbo. CC_Subject AS I ON i. ubjectCode=al-qeada eneralLedgerSubjectCode AND i.G radeCode='01'
The LEFT OUTER JOIN dbo. CC_Subject AS j ON j.S ubjectCode=a.S ubjectCode AND j.G radeCode='02'

CodePudding user response:

Can put a few key field statement, please? Write what is the thinking of the SQL, for example, going to empty, so, for the first time, I will filter out null data as a base table, and then on the basis of the query, can remove redundant query?