title: Slow query in SQL Server 2008 using linked server. What can I look at?
body: <p>This query originally came from a VB6 program accessing MS Access tables which are linked to external databases through ODBC. It takes about 3:30 to run.</p>
  
  <p>Now I've setup a SQL Server 2008 Express box to evaluate how we can migrate to a better database system. So I setup a linked server to the external server (we call it DWPROD) and when I converted the query (changed iif statements to case) and run it, I let it run for 12 minutes and it still doesn't finish. I'm new to SQL Server, so what can I look at to see what is taking so long? Any suggestions for speeding this up? Any resources recommended so I can learn more about this? This is a simple query compared to what we have so I'll hit this problem again.</p>
  
  <p>Query:</p>
  
  <pre><code>SELECT [FISCAL_YEAR] AS FISCAL_YEAR, 
  [Budgets].[dbo].[Departments].strdepentity AS ENTITY_CODE,
  [Budgets].[dbo].[Departments].depdiv1 AS DIVISION_CODE, 
  FINANCIAL_DEPTID AS DEPTID, 
  FUND_CODE, 
  [CLASS_FLD] AS CLASS_CODE, 
  [PROJECT_ID], 
  [PROGRAM_CODE], 
  [ACCOUNT] AS ACCOUNT_CODE, 
  CASE
      WHEN [ACCOUNT] in ('500020','520000','520220','520240') THEN 2
      WHEN LEFT([ACCOUNT],1)='5' THEN 1
      WHEN Left([ACCOUNT],1)='6' THEN 3
      WHEN Left([ACCOUNT],1)='7' THEN 4
      WHEN Left([ACCOUNT],1)='8' THEN 5
      ELSE 0
      END AS ACCT_GRP_CODE,
  Sum([BUDGET_AMOUNT]) AS GL_BUD_AMT, 
  Sum([ENCUMBRANCE_AMOUNT]) AS GL_ENC_AMT, 
  Sum([EXPENDITURE_AMOUNT]) AS GL_EXP_AMT, 
  CASE
      WHEN Left([CLASS_FLD],2)='12' THEN 0
      WHEN Left([CLASS_FLD],3)='113' THEN 3
      WHEN Left([CLASS_FLD],3)='112' THEN 14
      WHEN Left([CLASS_FLD],3)='115' THEN 10
      WHEN Left([CLASS_FLD],3)='116' THEN 13
      WHEN Left([CLASS_FLD],3)='117' THEN 12
      WHEN Left([CLASS_FLD],3)='118' THEN 11
      WHEN Left([CLASS_FLD],2)='13' THEN 2
      WHEN Left([CLASS_FLD],2)='14' THEN 3
      WHEN Left([CLASS_FLD],1)='4' THEN 4
      WHEN Left([CLASS_FLD],1)='6' THEN 6
      ELSE 9
      END AS FUND_SOURCE 
  
  FROM [DWPROD]..[DISC].[PS_LEDGER_DETAIL] LEFT JOIN [Budgets].[dbo].[Departments] ON    FINANCIAL_DEPTID=[Budgets].[dbo].[Departments].deporg 
  WHERE ((([BUDGET_PERIOD])='2010') And (([ACCOUNTING_PERIOD]) Between 1 And 12)) 
  
  
  GROUP BY 
  [FISCAL_YEAR],
  [Budgets].[dbo].[Departments].strdepentity, [Budgets].[dbo].[Departments].depdiv1, 
  [FINANCIAL_DEPTID],
  FUND_CODE, 
  [CLASS_FLD], 
  [PROJECT_ID], 
  [PROGRAM_CODE],
  [ACCOUNT], 
  CASE
      WHEN [ACCOUNT] in ('500020','520000','520220','520240') THEN 2
      WHEN LEFT([ACCOUNT],1)='5' THEN 1
      WHEN Left([ACCOUNT],1)='6' THEN 3
      WHEN Left([ACCOUNT],1)='7' THEN 4
      WHEN Left([ACCOUNT],1)='8' THEN 5
      ELSE 0
      END,
      CASE
      WHEN Left([CLASS_FLD],2)='12' THEN 0
      WHEN Left([CLASS_FLD],3)='113' THEN 3
      WHEN Left([CLASS_FLD],3)='112' THEN 14
      WHEN Left([CLASS_FLD],3)='115' THEN 10
      WHEN Left([CLASS_FLD],3)='116' THEN 13
      WHEN Left([CLASS_FLD],3)='117' THEN 12
      WHEN Left([CLASS_FLD],3)='118' THEN 11
      WHEN Left([CLASS_FLD],2)='13' THEN 2
      WHEN Left([CLASS_FLD],2)='14' THEN 3
      WHEN Left([CLASS_FLD],1)='4' THEN 4
      WHEN Left([CLASS_FLD],1)='6' THEN 6
      ELSE 9
      END
  HAVING (((FISCAL_YEAR)=2010) 
  AND ((FINANCIAL_DEPTID) Between '100' And '999') 
  AND ((ACCOUNT) Between '500000' And '899999')) 
  ORDER BY [FINANCIAL_DEPTID], [CLASS_FLD], [PROJECT_ID], [ACCOUNT]
  </code></pre>
  
  <p>I know it's long. Thanks for looking.</p>