Query Optimization on SQL server 2008

I have a small sql query that runs on SQL Server 2008. It uses the following tables and their row counts:

dbo.date_master - 245424
dbo.ers_hh_forecast_consumption - 436061472
dbo.ers_hh_forecast_file - 15105
dbo.ers_ed_supply_point - 8485

I am quite new to the world of SQL Server and am learning. Please guide me on how I'll be able to optimize this query to run much faster.

I'll be quite happy to learn if anyone can mention my mistakes and what I am doing that makes it take sooo long to query the resulting table.

WITH CTE_CONS AS
(
    SELECT T2.CONVERTED_DATE
          ,T1.FORECAST_FILE_ID
          ,SUM(T1.FORECAST_CONSUMPTION) AS TOTAL
    FROM dbo.ers_hh_forecast_consumption AS T1
    LEFT JOIN dbo.date_master AS T2 ON T1.UTC_DATETIME=T2.STRDATETIME
    WHERE T2.CONVERTED_DATE>='2015-01-01' AND T2.CONVERTED_DATE<='2015-06-01'
    GROUP BY T2.CONVERTED_DATE, T1.FORECAST_FILE_ID, T1.FORECAST_CONSUMPTION
),
CTE_MPAN AS
(
    SELECT T2.FORECAST_FILE_ID
          ,T2.MPAN_CORE
    FROM CTE_CONS AS T1
    LEFT JOIN dbo.ers_hh_forecast_file AS T2 ON T1.FORECAST_FILE_ID=T2.FORECAST_FILE_ID
),
CTE_GSP AS
(
    SELECT T2.MPAN_CORE
          ,T2.GSP_GROUP_ID
    FROM CTE_MPAN AS T1
    LEFT JOIN dbo.ers_ed_supply_point AS T2 ON T1.MPAN_CORE=T2.MPAN_CORE
)

SELECT T1.CONVERTED_DATE
      ,T1.TOTAL
      ,T2.MPAN_CORE
      ,T1.TOTAL
FROM CTE_CONS AS T1
LEFT JOIN CTE_MPAN AS T2 ON T1.FORECAST_FILE_ID=T2.FORECAST_FILE_ID
LEFT JOIN CTE_GSP AS T3 ON T2.MPAN_CORE=T3.MPAN_CORE

One thing to look,are the columns in JOINS and WHERE clause have indexes on them?
If yes,then check whether the statistics are updated and also check for index fragmentation.

thanks

1 Like