Query Optimization on SQL server 2008

Question

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

As Table "ers_hh_forecast_consumption" has huge records,better insert required records into a temptable using filter conditions and use that temptable in CTE. — Hell Boy, May 26 '15 at 10:37
Do you have any indexes on any of the tables, for instance an index on UTC_DATETIME of the forecast consumption table. Showing the table definitions and indexes would help you to get a decent answer. — Steve Ford, May 26 '15 at 14:42
It is hard to advise, when we don't know what indexes exist. At the same time, you can use SSMS to look at the execution plan of your query to identify optimizations. — Greg, May 26 '15 at 17:30

score 0 · Answer 1 · answered May 26 '15 at 21:12

Basically, without looking at the actual table design and indices, it is difficult to tell exactly what all you would need to change. But for starters, you could definitely consider two things:

In your CTE_CONS query, you are doing a left join on a Datetime field. This is definitely not a good idea unless you have some kind of index on that field. I would strongly urge you to create a index if there isn't one already.
```
CREATE NONCLUSTERED INDEX IX_UTC_DATETIME ON dbo.ers_hh_forecast_consumption 
(UTC_DATETIME ASC) INCLUDE (
 FORECAST_FILE_ID
,FORECAST_CONSUMPTION
 );
```
The other thing you could consider doing would be partitioning your table dbo.ers_hh_forecast_consumption. That way, your read is much less on the table and becomes lot quicker to retrieve records as well. Here is a quick guide on How To Decide if You Should Use Table Partitioning.

Hope this helps!

score 0 · Answer 2 · answered May 27 '15 at 09:02

Apart from the fact that you'll need to offer quite a bit more info for us to get a good idea on what's going on, I think I spotted a bit of an issue with your query here:

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
)

On first sigth you're trying to SUM() the values of T1.FORECAST_CONSUMPTION per T2.CONVERTED_DATE ,T1.FORECAST_FILE_ID combination. However, in the GROUP BY you also add T1.FORECAST_CONSUMPTION again? This will have the exact same effect as doing a DISTINCT over the three fields. Either removed the field you're SUM()ing on from the GROUP BY or use a DISTINCT and get rid of the SUM() and GROUP BY; depending on what effect you're after.

Anyway, could you add the following things to your question :

EXEC sp_helpindex <table_name> for all tables involved.
if possible, a screenshot of the Execution Plan (either from SSMS, or from SQL Sentry Plan Explorer).

Query Optimization on SQL server 2008

2 Answers2