sql 2008服务器响应慢,SQL Server 2008中使用链接服务器的查询速度缓慢。我可以看什么？...

我重新写你的查询：

WITH ledger_detail AS (

SELECT pld.fiscal_year,

pld.financial_deptid AS DEPTID,

pld.fund_code,

pld.class_fld AS CLASS_CODE,

pld.project_id,

pld.program_code,

CASE

WHEN pld.account IN ('500020','520000','520220','520240') THEN 2

WHEN LEFT(pld.account,1) = '5' THEN 1

WHEN LEFT(pld.account,1) = '6' THEN 3

WHEN LEFT(pld.account,1) = '7' THEN 4

WHEN LEFT(pld.account,1) = '8' THEN 5

ELSE 0

END AS ACCT_GRP_CODE,

pld.budget_amount,

pld.encumbrance_amount,

pld.expenditure_amount,

pld.account AS ACCOUNT_CODE,

CASE

WHEN LEFT(pld.class_fld, 2) ='12' THEN 0

WHEN LEFT(pld.class_fld, 3)='113' THEN 3

WHEN LEFT(pld.class_fld, 3)='112' THEN 14

WHEN LEFT(pld.class_fld, 3)='115' THEN 10

WHEN LEFT(pld.class_fld, 3)='116' THEN 13

WHEN LEFT(pld.class_fld, 3)='117' THEN 12

WHEN LEFT(pld.class_fld, 3)='118' THEN 11

WHEN LEFT(pld.class_fld, 2)='13' THEN 2

WHEN LEFT(pld.class_fld, 2)='14' THEN 3

WHEN LEFT(pld.class_fld, 1)='4' THEN 4

WHEN LEFT(pld.class_fld, 1)='6' THEN 6

ELSE 9

END AS FUND_SOURCE

FROM [DWPROD]..[DISC].[PS_LEDGER_DETAIL] pld

WHERE pld.budget_period = '2010'

AND pld.accounting_period BETWEEN 1 AND 12

AND pld.fiscal_year = 2010

AND pld.financial_deptid BETWEEN '100' AND '999'

AND pld.account BETWEEN '500000' AND '899999')

SELECT x.fiscal_year,

y.strdepentity AS ENTITY_CODE,

y.depdiv1 AS DIVISION_CODE,

x.deptid,

x.fund_code,

x.class_code,

x.project_id,

x.program_code,

x.acct_grp_code,

SUM(x.budget_amount) AS GL_BUD_AMT,

SUM(x.encumbrance_amount) AS GL_ENC_AMT,

SUM(x.expenditure_amount) AS GL_EXP_AMT,

x.account AS ACCOUNT_CODE,

x.fund_source

FROM ledger_detail x

LEFT JOIN [Budgets].[dbo].[Departments] y ON y.deporg = x.financial_deptid

GROUP BY x.fiscal_year, y.strdepentity, y.depdiv1, x.deptid, x.fund_code, x.class_code, x.project_id, x.program_code, x.acct_grp_code

ORDER BY x.financial_deptid, x.class_fld, x.project_id, x.account

即使你有[DWPROD]指标.. [DISC]的account和class_fld [PS_LEDGER_DETAIL]，使用功能(LEFT)在他们上呈现他们这个查询不可用。

另外，您在HAVING子句中包含筛选条件，而不是WHERE子句。

UPDATE：非CTE等效

SELECT x.fiscal_year,

y.strdepentity AS ENTITY_CODE,

y.depdiv1 AS DIVISION_CODE,

x.deptid,

x.fund_code,

x.class_code,

x.project_id,

x.program_code,

x.acct_grp_code,

SUM(x.budget_amount) AS GL_BUD_AMT,

SUM(x.encumbrance_amount) AS GL_ENC_AMT,

SUM(x.expenditure_amount) AS GL_EXP_AMT,

x.account AS ACCOUNT_CODE,

x.fund_source

FROM (SELECT pld.fiscal_year,

pld.financial_deptid AS DEPTID,

pld.fund_code,

pld.class_fld AS CLASS_CODE,

pld.project_id,

pld.program_code,

CASE

WHEN pld.account IN ('500020','520000','520220','520240') THEN 2

WHEN LEFT(pld.account,1) = '5' THEN 1

WHEN LEFT(pld.account,1) = '6' THEN 3

WHEN LEFT(pld.account,1) = '7' THEN 4

WHEN LEFT(pld.account,1) = '8' THEN 5

ELSE 0

END AS ACCT_GRP_CODE,

pld.budget_amount,

pld.encumbrance_amount,

pld.expenditure_amount,

pld.account AS ACCOUNT_CODE,

CASE

WHEN LEFT(pld.class_fld, 2) ='12' THEN 0

WHEN LEFT(pld.class_fld, 3)='113' THEN 3

WHEN LEFT(pld.class_fld, 3)='112' THEN 14

WHEN LEFT(pld.class_fld, 3)='115' THEN 10

WHEN LEFT(pld.class_fld, 3)='116' THEN 13

WHEN LEFT(pld.class_fld, 3)='117' THEN 12

WHEN LEFT(pld.class_fld, 3)='118' THEN 11

WHEN LEFT(pld.class_fld, 2)='13' THEN 2

WHEN LEFT(pld.class_fld, 2)='14' THEN 3

WHEN LEFT(pld.class_fld, 1)='4' THEN 4

WHEN LEFT(pld.class_fld, 1)='6' THEN 6

ELSE 9

END AS FUND_SOURCE

FROM [DWPROD]..[DISC].[PS_LEDGER_DETAIL] pld

WHERE pld.budget_period = '2010'

AND pld.accounting_period BETWEEN 1 AND 12

AND pld.fiscal_year = 2010

AND pld.financial_deptid BETWEEN '100' AND '999'

AND pld.account BETWEEN '500000' AND '899999') x

LEFT JOIN [Budgets].[dbo].[Departments] y ON y.deporg = x.financial_deptid

GROUP BY x.fiscal_year, y.strdepentity, y.depdiv1, x.deptid, x.fund_code, x.class_code, x.project_id, x.program_code, x.acct_grp_code

ORDER BY x.financial_deptid, x.class_fld, x.project_id, x.account