November 14, 2013 at 5:19 am
Please find the query below
SELECT TOP 12 CPL.PERIODID AS MONTHID,ISNULL(TEMP.OUTSTANDINGAMOUNT,0) OUTSTANDINGAMOUNT
FROM
(SELECT PL.PERIODID as MONTHID,
SUM((PL.BASEVALUE)*CASE WHEN CURR.RATE IS NULL THEN 1 ELSE CURR.RATE END ) OUTSTANDINGAMOUNT
FROM
VW_FACT_PLTRANS PL
INNER JOIN
VW_DIM_COMPANY C
ON PL.COMPANY_SK=C.COMPANY_SK
INNER JOIN DIVISIONMAPPING D
ON C.COMPANYID=D.COMPANYID
INNER JOIN
VW_DIM_SUPPLIER SUP
ON PL.SUPPLIER_SK=SUP.SUPPLIER_SK AND PL.COMPANY_SK=SUP.COMPANY_SK
INNER JOIN VW_DIM_LEDGERS L
ON SUP.LEDGER=L.LEDGERCODE AND SUP.COMPANY_SK=L.COMPANY_SK AND L.LEDGER_DESC IN ('Expense Ledger','Expenses Ledger','Expense','Expenses')
LEFT OUTER JOIN VW_FACT_CURRENCY CURR
ON PL.COMPANY_SK=CURR.COMPANY_SK AND
CURR.CURRENCY IN ('GBP','STER')
WHERE D.ACTIVE IN ('YES','NO') AND
PL.POSTED=1 AND
PL.DOCID IN ('EXIN','EXCN')
AND PL.PERIODID<='201302' AND C.COMPANY_SK IN (SELECT COMPANY_SK FROM DIM_COMPANY)
GROUP BY PL.PERIODID) TEMP
RIGHT OUTER JOIN VW_DIM_FISCAL_CALENDAR_PERIOD CPL
ON CPL.PERIODID=TEMP.MONTHID
WHERE CPL.PERIODID<='201302'
ORDER BY CPL.PERIODID DESC
In this Query FACT_PLTRANS is taking 77%v when u check using execution plan. I have checked and tried what i have any suggestion is welcome.
November 14, 2013 at 5:39 am
Not enough information.
Please post table definitions, index definitions and actual execution plan (as a .sqlplan file)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply