How To Structure An Index For Group By In Sql Server

The following simple query takes a very long time (several minutes) to execute. I have an index: create index IX on [fctWMAUA] (SourceSystemKey, AsAtDateKey) SELECT MAX([t0].[AsA

Solution 1:

Its difficult to say without looking at an execution plan, however you might want to try the following:

SELECT * FROM
(
    SELECT MAX(t0.AsAtDateKey) AS [Date], t0.SourceSystemKey AS SourceSystem
    FROM fctWMAUA (NOLOCK) AS t0
    GROUPBY t0.SourceSystemKey
)
WHERE SourceSystem in (1,2,3,4,5,6,7,8,9)

Its difficult to tell without looking at an execution plan, but I think that whats happening is that SQL server is not clever enough to realise that the WHERE clause specified is filtering out the groups, and does not have any effect on the records included for each group. As soon as SQL server realises this its free to use some more inteligent index lookups to work out the maximum values (which is whats happening in your second query)

Just a theory, but it might be worth a try.

Solution 2:

I have found that the best solution is the following. It mimics the union version of the query, and runs very quickly.

40 logical reads, and an execution time of 3ms.

SELECT [t3].[value]
FROM [dimSourceSystem] AS [t0]
OUTER APPLY (
    SELECT MAX([t2].[value]) AS [value]
    FROM (
        SELECT [t1].[AsAtDateKey] AS [value], [t1].[SourceSystemKey]
        FROM [fctWMAUA] AS [t1]
        ) AS [t2]
    WHERE [t2].[SourceSystemKey] = ([t0].[SourceSystemKey])
    ) AS [t3]

Solution 3:

Use HAVING instead of WHERE, so that the filtering happens AFTER grouping has occurred:

SELECT MAX(AsAtDateKey) AS [Date], SourceSystemKey AS SourceSystem
FROM fctWMAUA (NOLOCK)
GROUPBY SourceSystemKey
HAVING SourceSystemKey in (1,2,3,4,5,6,7,8,9)

I also don't particularly care for the IN clause, especially when it could be replaced with "<10" or "Between 1 and 9", which are used better by sorted indexes.

Solution 4:

Try to tell SQL Server to use the index:

...
FROM [fctWMAUA] (NOLOCK, INDEX(IX)) AS [t0]
...

Make sure the statistics for the table are up to date:

UPDATE STATISTICS [fctWMAUA]

For better answers, turn on the showplan for both queries:

SET SHOWPLAN_TEXT ON

and add the results to your question.

You can also write the query without a GROUP BY. For example, you can use an exclusive LEFT JOIN excluding rows with older dates:

select cur.SourceSystemKey, cur.datefrom fctWMAUA cur
left join fctWMAUA nextonnext.SourceSystemKey = next.SourceSystemKey
    andnext.date > cur.datewherenext.SourceSystemKey is null
and cur.SourceSystemKey in (1,2,3,4,5,6,7,8,9)

This can be surprisingly fast, but I don't think it could beat the UNION.

Solution 5:

WHERE SourceSystemKey = 3GROUPBY [t0].[SourceSystemKey]

You don't need to group by a fixed field.

Any way I prefer the first sentence. May be I will replace the

 WHERE SourceSystemKey in(1,2,3,4,5,6,7,8,9)

for something like

WHERE SourceSystemKey BETWEEN1AND9

or

WHERE SourceSystemKey >=1AND SourceSystemKey <=9

if SourceSystemKey is an integer. But I don't think it will cause a big change.

What I will test first is rebuild statistics and rebuild all indexes for the table and wait some time. Rebuilding is not instant, it will depend on how busy is the server but this sentence is well structured for the index be used by the optimizer.

Regards.